Adopting an existing database

If you already have a database with an unmanaged schema, you don’t have to write your SQL from scratch. The nschema import command reads a live schema and writes it out as NSQL source files, so you can adopt an existing database and manage it going forward.

1. Declare the database. The import command reads from a live database, so it needs a PLUGIN declaration and a DATABASE statement. Create a configuration file in your project directory with just those. The schema files themselves will come from the import:

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

   Then run nschema init to install the plugins.

   Supply the connection string as an environment variable, so it stays out of source control (see Configuration):

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

2. Import the database schema into a directory:

   nschema import --out-dir ./schemas

   This writes the live database schema out as .sql files. To adopt only part of the database, you can use the same --scope argument that works with plan and apply:

   nschema import --out-dir ./schemas --scope app.orders --scope billing

   As a safety measure, import won’t import into a directory that already contains .sql files; pass --force if you really mean to re-import over existing files.

3. Review and commit. Read the generated files, tidy them if you like, and check them into source control.

4. Apply to adopt. NSchema diffs your project against the recorded state, so running apply will first refresh your state with the latest database snapshot, before displaying any differences as a migration plan. This first plan should be empty, if you haven’t made any changes, but the output will show a number of objects to be “adopted” (see below).

   nschema apply

5. Manage changes from here. Edit the .sql files and use the normal plan / apply workflow from now on.

Adoption is gradual

The state store records what the entire database contains, for dependency resolution, but NSchema only controls what the project declares. In the state, this is referred to as the managed set. When you declare a new object, or adopt an existing one, it’s added to the managed set, and a drop is what happens when an object in the managed set no longer exists in the declared project. That way, NSchema won’t ever drop anything that it doesn’t manage, so importing a whole database and then declaring one schema at a time is safe: the parts you haven’t adopted yet are simply left alone.