Node PostgreSQL Migrator

A simple database change management tool for PostgreSQL

• A lightweight CLI tool for managing PostgreSQL database changes
• Written in JavaScript for Node.js
• Use in any Node.js project with npx
• Pre-bundled (no extra dependencies or build steps)
• Not tied to any framework or ORM
• Open source (MIT License)

Features

• Write change scripts (migrations) in SQL
• Apply changes to a database using a convenient CLI
• Works great in a team setting (multiple developers can track and handle changes)
• Supports automated change deployment across multiple environments
• Automatically tracks and applies only new (pending) change scripts
• Automatically detects and warns when previous scripts have been altered
• Automatic rollback to previous state if any migrations fail (atomic transactional safety)
• Auditable change history log

What about rollback (revert)?

This is not supported. Instead, thorough change testing, and a "roll-forward" strategy is advised (if a breaking change gets released, create a new migration script to correct the problem). While some other tools do claim "rollback" support, usually this means overhead of writing 'revert scripts', which in practice can be flaky/unreliable.

How to use

Installation and setup

The database must already exist and have permissions granted.

Install the tool directly from Gitlab (published NPM packages may be available in future):

npm install --save git+https://gitlab.com/oliasoft-open-source/node-postgresql-migrator.git

Then run the tool with npx:

npx migrator --db=postgres://username:password@host:port/database --dir=./path/to/migrations

Where the db option is the PostgreSQL connections string, and the dir option is the path to the migrations directory, from the Node project root.

How do I set the database connection dynamically?

In future, we may support environment variables. For now, you can use a wrapper script.

In package.json:

"scripts": {
  "migrator": "npx babel-node --presets @babel/env sql-migrator.js"
}

In sql-migrator.js (wrapper script):

import { spawn } from 'child_process';
import { getDbConnectionConfiguration } from '../server/db/connectionConf';

const run = async () => {
  const connectionConf = await getDbConnectionConfiguration();
  const migrationsPath = './server/db/migrations';
  const command = `npx migrator --db='${connectionConf}' --dir='${migrationsPath}'`;
  const child = spawn(command, ['--color=always'], { shell: true });
  child.stdout.on('data', (data) => console.log(data.toString()));
  child.stderr.on('data', (data) => console.error(data.toString()));
  child.on('exit', (code) => process.exit(code));
};

run().then();

Then invoke with npm run migrator.

Writing change scripts (migrations)

A change script (migration file) should be written for each logical changeset (for example, when adding a new product feature).

• migrations (change scripts) should be stored in one directory (passed to the tool as the dir option)
• subdirectories:
  • are allowed and encouraged for organizing files
  • schema and seed directories are sensible at the top-level (to distinguish between structural schema changes and seed values)
  • subdirectories have no bearing on script execution order
• migration files:
  • filename must be in the format YYYY-MM-DDTHHmmss-description.sql (ISO 8601 without colons :)
  • the timestamp in the filename defines the execution order

###Rules

Using transactions is not allowed in migrations (COMMIT and ROLLBACK are forbidden). This is because the migrator tool uses a top-level transaction to make the entire migration operation atomic (and sub-transactions would interfere with this).

Best practices

• changes shall be expressed by creating a new change script
  • do not by alter / edit a previously released migration script
  • if you do, the tool will warn you, and stop execution
    • this can be overridden with the --force option, which will re-execute altered scripts
    • this is fine during local development, but strongly not recommended for released migrations
• it's recommended to make scripts repeatable (no SQL errors if executed twice)
  • for exceptional circumstances where this is not possible, files named *.once.sql will never be re-executed by the --force option

Change script repeatability

It is good practice to make change scripts repeatable. This means they should not throw errors if executed twice. This is achieved by adding guards to SQL commands. We do this as a good practice even though the migrator tool will not re-execute scripts under normal circumstances. Some example guards are provided below.

For schema migrations:

• CREATE TABLE IF NOT EXISTS ...
• CREATE INDEX IF NOT EXISTS ON ...
• CREATE OR REPLACE FUNCTION ...
• ALTER TABLE ... ADD COLUMN IF NOT EXISTS ...
• ALTER TABLE ... DROP COLUMN IF EXISTS ...
• For checking column existence:

DO $$ BEGIN
    IF EXISTS (SELECT column_name
      FROM information_schema.columns
      WHERE table_name='foo' and column_name='bar')
    THEN
        UPDATE foo SET bar = 123;
    END IF;
END $$;

• for renaming a column name if it exists:

DO $$ BEGIN
  IF EXISTS(SELECT column_name
    FROM information_schema.columns
    WHERE table_name='foo' and column_name='bar')
  THEN
      ALTER TABLE "foo" RENAME COLUMN "bar" TO "baz";
  END IF;
END $$;

• For adding a foreign key constraint:

DO $$ BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname 'fk_foo_barid_bar_id' ) THEN
        ALTER TABLE foo
            ADD CONSTRAINT fk_foo_barid_bar_id
            FOREIGN KEY (barid)
            REFERENCES bar(barid);
    END IF;
END $$;

For data (seed) migrations:

• When there is a constraint:
  • INSERT INTO ... ON CONFLICT DO NOTHING
• Otherwise
  • INSERT INTO ... SELECT ... WHERE NOT EXISTS (...)

How to contribute

Contribution is welcome via issue tickets and merge requests.

• coding style (ESLint and prettier) is mandatory (applied via pre-commit hook)
• to run a development instance:
  • npx babel-node --presets @babel/env src/migrator.js --db=postgres://username:password@host:port/db --dir=./test/__testdata__/migrations
• to build (production): npm run buld (this transpile and bundles a production build to the dist directory)
  • the build dist directory should be committed
• to run a production instance:
  • node ./dist/cli.js --db=postgres://username:password@host:port/db --dir=./test/__testdata__/migrations
• to test: npm run test
  • integration tests mock the DB with pg-mem (in-memory database)
  • coverage reports are in test/coverage (not committed)