Node.js database migration management built exclusively for postgres. (Also tested with CockroachDB.)

Maintainers

Meet our maintainers and contributors on the team page.

Preconditions

• Node.js 22.12.0 or higher
• PostgreSQL 14 or higher (lower versions may work but are not supported officially)

If you don't already have the pg library installed, you will need to add pg as either a direct or dev dependency

npm add pg

Installation

npm add --save-dev node-pg-migrate

If you run migrations in an environment that skips dev dependencies (for example npm ci --omit=dev in a production image), add node-pg-migrate and pg to dependencies instead.

Installing this module adds a runnable file into your node_modules/.bin directory. If installed globally (with the -g option), you can run node-pg-migrate and if not, you can run ./node_modules/.bin/node-pg-migrate

Quick Example

Add "migrate": "node-pg-migrate" to scripts section of your package.json so you are able to quickly run commands.

Run npm run migrate create my-first-migration. It will create file xxx_my-first-migration.js in migrations folder.
Open it and change contents to:

export const up = (pgm) => {
  pgm.createTable('users', {
    id: 'id',
    name: { type: 'varchar(1000)', notNull: true },
    createdAt: {
      type: 'timestamp',
      notNull: true,
      default: pgm.func('current_timestamp'),
    },
  });
  pgm.createTable('posts', {
    id: 'id',
    userId: {
      type: 'integer',
      notNull: true,
      references: '"users"',
      onDelete: 'CASCADE',
    },
    body: { type: 'text', notNull: true },
    createdAt: {
      type: 'timestamp',
      notNull: true,
      default: pgm.func('current_timestamp'),
    },
  });
  pgm.createIndex('posts', 'userId');
};

Save migration file.

Now you should put your DB connection string to DATABASE_URL environment variable and run npm run migrate up. (e.g. DATABASE_URL=postgres://test:test@localhost:5432/test npm run migrate up)

You should now have two tables in your DB :tada:

If you want to change your schema later, you can e.g. add lead paragraph to posts:

Run npm run migrate create posts_lead, edit xxx_posts-lead.js:

export const up = (pgm) => {
  pgm.addColumns('posts', {
    lead: { type: 'text', notNull: true },
  });
};

If posts already contains rows, add lead without notNull, populate it, then set notNull:

export const up = (pgm) => {
  pgm.addColumns('posts', { lead: { type: 'text' } });
  pgm.sql(`UPDATE posts SET lead = left(body, 200)`);
  pgm.alterColumn('posts', 'lead', { notNull: true });
};

If an empty string is suitable for existing and future rows, use notNull: true and default: '' when adding lead instead.

Run npm run migrate up and there will be a new column in posts table :tada:

Want to know more? Read docs:

Docs

Full docs are available at https://salsita.github.io/node-pg-migrate

For changes between major versions, see the Upgrading guide.

Explanation & Goals

Why only Postgres? - By writing this migration tool specifically for postgres instead of accommodating many databases, we can actually provide a full featured tool that is much simpler to use and maintain. I was tired of using crippled database tools just in case one day we switch our database.

Async / Sync - Everything is async in node, and that's great, but a migration tool should really just be a fancy wrapper that generates SQL. Most other migration tools force you to bring in control flow libraries or wrap everything in callbacks as soon as you want to do more than a single operation in a migration. Plus by building up a stack of operations, we can automatically infer down migrations (sometimes) to save even more time.

Naming / Raw Sql - Many tools force you to use their constants to do things like specify data types. Again, this tool should be a fancy wrapper that generates SQL, so whenever possible, it should just pass through user values directly to the SQL. The hard part is remembering the syntax of the specific operation, not remembering how to type "timestamp"!

Contributing

👋 Welcome, new contributors!

Whether you're a seasoned developer or just getting started, your contributions are valuable to us. Don't hesitate to jump in, explore the project, and make an impact.

Please read the contributing guide before opening a pull request.

License

MIT