Skip to content
NSchema

Quickstart Guide

This guide will walk you through setting up NSchema in an empty directory and applying a basic Schema to a real PostgreSQL database. It assumes you meet the pre-requisites and have a connection string to a database to hand.

  1. Install NSchema.

    Terminal window
    dotnet tool install nschema --global

    NSchema is installed as a .NET tool. It can also be installed locally, but global is easier for demonstration.

  2. Scaffold the project.

    Terminal window
    nschema new

    The new command sets up the boilerplate for a fresh project:

    • config.sql — holds configuration for stuff like database providers, or state backends.
    • config.env.prod.sql — the configuration to layer on top when running with --environment prod.
    • schemas/example.sql — your hello world sample schema.

    It then runs init, to install the required plugins and pin them in the nschema.lock lockfile.

    Create or edit the SQL files to describe the schema you want, using NSQL:

    -- schemas/example.sql
    CREATE SCHEMA app;
    

    CREATE TABLE app.widgets (
      id   bigint NOT NULL,
      name text,
      CONSTRAINT widgets_pkey PRIMARY KEY (id)
    );

  3. Set up the database connection.

    -- config.sql
    ENGINE (
      version = '[5.0,6.0)'
    );
    

    PLUGIN postgres (
      source  = 'NSchema.Postgres',
      version = '[5.0,6.0)'
    );
    

    DATABASE postgres (
      -- Prefer the NSCHEMA_DATABASE_CONNECTION_STRING environment variable, which
      -- overrides the value below.
      connection_string = ''
      -- Credentials may be supplied separately from the connection string (e.g. from
      -- a secret store) via NSCHEMA_DATABASE_USERNAME / NSCHEMA_DATABASE_PASSWORD.
      -- They override any user/password embedded in connection_string.
    );
    

    STATE file (
      path = './nschema.state.json'
    );

    The value in config.sql is the default, so you could enter connection_string directly, but connection strings need to remain secret, so the safer approach is to set the connection string by environment variable instead:

    Terminal window
    export NSCHEMA_DATABASE_CONNECTION_STRING="Host=localhost;Database=app;Username=postgres;Password=postgres"

    See Configuration for the four statements, and Environment variables for the full list of overrides.

  4. Check the schema files are well-formed (optional, but a fast pre-flight):

    Terminal window
    nschema validate

    validate checks for syntax errors, and also any structural errors like invalid foreign keys or tables without columns.

  5. Preview the migration.

    Terminal window
    nschema plan

    plan computes the changes and prints them. The comparison is made against the recorded state, which will be empty on a fresh project, so it never depends on reading the live database.

  6. Apply it.

    Terminal window
    nschema apply

    apply shows the same plan, then prompts for confirmation before making any changes. Answer yes to proceed and apply the changes.

Congrats! You just deployed your first database using NSchema.

What next?

Section titled “What next?”