Migrations

Each schema change is a numbered file; a command applies the pending ones on deploy and records what it ran.

Syntaxnpx node-pg-migrate up

Tables have to exist before your code can query them, and they keep changing as the product changes. A migration is one file holding one change: create a table, add a column, add an index. The files are applied in order, and the database records which ones have run. node-pg-migrate keeps that list in a table it creates called pgmigrations. Running the command applies only the files missing from that list, so running it twice is harmless.

A deploy is the step that puts a new version of your code on a server. Migrations belong in it, not in someone typing SQL into a console, so that every environment goes through the same changes in the same order.

How it works

Create a file, then apply the pending ones:

npx node-pg-migrate create add_users_email
DATABASE_URL=postgres://localhost/app npx node-pg-migrate up

Files land in migrations/ and each one exports up and down functions that take a pgm helper. Add "migrate": "node-pg-migrate up" to the scripts in package.json and the deploy runs npm run migrate before starting the new code.

What trips people up

Treat an applied migration as read-only. The database has it recorded as done, so your edit never runs and the file stops describing the real schema. Correct it with a new migration instead.

During a deploy the old code and the new code both serve requests for a while, so each change has to suit both. Add a column as nullable, ship the code that fills it, backfill the old rows, and only then add the NOT NULL constraint: three migrations, nothing broken in between.

Example

Try it yourself
Loading editor…
Press Run to execute the code.

When to use it

  • Create the tables a new feature needs without anyone running SQL by hand in production.
  • Bring a teammate's fresh clone up to the current schema with one npm run migrate.
  • Add a NOT NULL column to a live table without a failed request during the deploy.
  • Know exactly which schema version a server is running from the pgmigrations table.

More examples

A migration file, and the npm script that runs it

Putting the command in package.json means the deploy never needs to know the tool's name or flags. node-pg-migrate reads DATABASE_URL by default, so the same script works in every environment.

Example · javascript
// migrations/1714500000000_add_users_email.js
// pgm is the helper node-pg-migrate passes in; up applies the change,
// down undoes it.
export const up = (pgm) => {
  pgm.addColumns('users', {
    email: { type: 'text', notNull: false }, // nullable first; see below
  });
  pgm.createIndex('users', 'email', { unique: true });
};

export const down = (pgm) => {
  pgm.dropColumns('users', ['email']);
};

// package.json
// {
//   "scripts": {
//     "migrate": "node-pg-migrate up",
//     "migrate:new": "node-pg-migrate create",
//     "start": "node server.js"
//   }
// }
//
// The deploy runs, in order:
//   npm ci
//   DATABASE_URL=... npm run migrate
//   npm start

Adding a NOT NULL column in three safe steps

A single migration that adds the column as NOT NULL fails on a table with rows, and one that drops a column still read by the previous version breaks live requests. Split any change the old code cannot survive into add, backfill, enforce.

Example · javascript
// Step 1 (deploy 1): add it nullable with a default. Old code ignores the
// column; new code can already read and write it.
export const up = (pgm) => {
  pgm.addColumns('orders', {
    currency: { type: 'text', notNull: false, default: 'EUR' },
  });
};

// Step 2 (same deploy or a later one): fill the existing rows. Do it in
// batches so one statement does not lock the table for minutes.
export const up = (pgm) => {
  pgm.sql(`UPDATE orders SET currency = 'EUR' WHERE currency IS NULL`);
};

// Step 3 (deploy 2, once all running code writes the column): enforce it.
export const up = (pgm) => {
  pgm.alterColumn('orders', 'currency', { notNull: true });
};

Running migrations in a container start-up command

The important part is the exit code: a failed migration must stop the deploy rather than start a server against a schema its code does not match. Use this when the platform gives you one start command and no separate migration step.

Example · javascript
// A plain Node script, so there is no shell logic in the deploy config.
// migrate-then-start.js
import { spawnSync } from 'node:child_process';

// Apply pending migrations. stdio: 'inherit' sends the tool's output to
// this process's own output, so the deploy log shows what ran.
const migrate = spawnSync('npx', ['node-pg-migrate', 'up'], {
  stdio: 'inherit',
});

if (migrate.status !== 0) {
  console.error('migrations failed; not starting the server');
  process.exit(1); // a non-zero exit code fails the deploy
}

await import('./server.js');

Related

node db connectnode db queriesnode db transactionsnode npm scriptsnode env config

Discussion

  • Be the first to comment on this lesson.