node-pg-migrate

Appearance

Getting Started ​

Preconditions ​

  • Node.js 20.11 or higher
  • PostgreSQL 13 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

sh
$ npm add pg
sh
$ pnpm add pg
sh
$ yarn add pg
sh
$ bun add pg

Installation ​

sh
$ npm add -D node-pg-migrate
sh
$ pnpm add -D node-pg-migrate
sh
$ yarn add -D node-pg-migrate
sh
$ bun add -D node-pg-migrate

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.js

Quick Example ​

IMPORTANT

This example assumes you are using npm as your package manager. If you are using another package manager, replace npm with the appropriate command.

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

jsonc
{
  "scripts": {
    // ..
    "migrate": "node-pg-migrate", 
  },
}
jsonc
{
  "scripts": {
    // ..
    "migrate": "node-pg-migrate -j ts", 
  },
}

Now, lets create your first migration:

bash
npm run migrate create my-first-migration
# creates migrations/xxx_my-first-migration.js or ts

Open it and change the contents to:

js
exports.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');
};

exports.down = (pgm) => {};
ts
import { MigrationBuilder } from 'node-pg-migrate';

export const up = (pgm: MigrationBuilder) => {
  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');
};

export async function down(pgm: MigrationBuilder): Promise<void> {}

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 🎉

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

Run npm run migrate create posts_lead, edit xxx_posts_lead.js:

js
export const up = (pgm) => {
  pgm.addColumns('posts', {
    lead: { type: 'text', notNull: true },
  });
};
ts
import { MigrationBuilder } from 'node-pg-migrate';

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

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

Want to know more? ​