Node.js Database Migrations with Postgrator

A quick overview of how I manage database migrations in my Node.js projects with Postgrator.

Install Postgrator

Postgrator supports several databases; one of them is PostgreSQL, which is usually my database of choice. Assuming that the database client is already installed and set up (pg in my case), install Postgrator:

npm install postgrator

Create the migration script

In order to run up or down migrations, a script is needed. The one I use is located inside the src directory:

import pg from 'pg';
import Postgrator from 'postgrator';

import { dirname } from 'path';
import { fileURLToPath } from 'url';

const __dirname = dirname(fileURLToPath(import.meta.url));

async function migrate() {
  const client = new pg.Client({ connectionString: process.env.DB_CONNECTION_URL });

  try {
    await client.connect();

    const postgrator = new Postgrator({
      migrationPattern: __dirname + '/migrations/*',
      driver: process.env.MIGRATIONS_DRIVER,
      database: process.env.MIGRATIONS_DB,
      schemaTable: process.env.MIGRATIONS_SCHEMA_TABLE,
      currentSchema: process.env.MIGRATIONS_CURRENT_SCHEMA,
      execQuery: (query) => client.query(query),
    });

    const result = await postgrator.migrate();

    if (result.length === 0) {
      console.log('No migrations run for schema "public". Already at the latest one.');
    }

    console.log('Migration done.');
    process.exitCode = 0;
  } catch (err) {
    console.error(err);
    process.exitCode = 1;
  }

  await client.end();
}

migrate();

Configure environment variables

In an .env file, I define the following variables:

MIGRATIONS_DRIVER=pg
MIGRATIONS_DB=my_database
MIGRATIONS_SCHEMA_TABLE=migrations
MIGRATIONS_CURRENT_SCHEMA=public

Create a migrations directory

Lastly, I create a migrations directory inside src/ that contains all of my migrations. As described in the package’s documentation:

Postgrator on npm

migrations/
  ├─ 001.do.sql
  ├─ 001.undo.sql
  ├─ 002.do.optional-description.sql
  ├─ 002.undo.optional-description.sql
  ├─ 003.do.sql
  ├─ 003.undo.sql
  ├─ 004.do.js
  ├─ 004.undo.js
  └─ ... and so on

Add migration script in `package.json`

The last step before applying migrations is to add an npm script in package.json:

"scripts": {
  "migrate": "node --env-file .env src/migrate.mjs"
}

Now migrations can be applied with:

npm run migrate

Tips and best practices

Always create both .do and .undo scripts so you can roll back safely.
Use sequential numbering (001, 002, 003…) to avoid conflicts in teams.