node-pg-migrate

Appearance

Defining Migrations ​

Running Migrations ​

When you run node-pg-migrate create a new migration file is created that looks like this:

javascript
export const shorthands = undefined;

export const up = function up(pgm) {};

export const down = function down(pgm) {};

pgm is a helper object that provides migration operations and run is the callback to call when you are done.

shorthands is optional for column type shorthands. You can specify custom types which will be expanded to column definition.

Example ​

js
export const shorthands = {
  id: { type: 'uuid', primaryKey: true },
  createdAt: {
    type: 'timestamp',
    notNull: true,
    default: new PgLiteral('current_timestamp'),
  },
};

It will in pgm.createTable('test', { id: 'id', createdAt: 'createdAt' }); produce SQL

sql
CREATE TABLE "test" ("id" uuid PRIMARY KEY, "createdAt" timestamp DEFAULT current_timestamp NOT NULL);

These shorthands are inherited from previous migrations. You can override/change value by simply defining a new value for a given shorthand name if it is used in current and all following migrations (until changed again).

IMPORTANT

Calling the migration functions on pgm doesn't migrate your database. These functions just add sql commands to a stack that is run.

Automatic Down Migrations ​

If export const down is not present in a migration, node-pg-migrate will try to automatically infer the operations that make up the down migration by reversing the operations of the up migration. Only some operations have automatically inferrable equivalents (the details below on each operation). Sometimes, migrations are destructive and cannot be rolled back. In this case, you can set export const down = false to tell node-pg-migrate that the down migration is impossible.

Async Migrations ​

In some cases, you may want to perform some async operation during a migration, for example, fetching some information from an external server, or inserting some data into the database. To make a migration block operate in async mode, add another callback argument to the function signature. However, be aware that NONE of the pgm operations will be executed until run() is called. Here's an example:

javascript
export const up = function up(pgm, run) {
  doSomethingAsync(function () {
    run();
  });
};

Another way how to perform some async operation is to return Promise from up or down function. Example:

javascript
export const up = function (pgm) {
  return new Promise((resolve) => {
    // doSomethingAsync
    resolve();
  });
};

or

javascript
export const up = async (pgm) => {
  // doSomethingAsync
};

Using schemas ​

Instead of passing string as name to pgm functions, you can pass an object with keys schema and name. E.g.

javascript
pgm.createTable(
  { schema: 'my_schema', name: 'my_table_name' },
  { id: 'serial' }
);

will generate

sql
CREATE TABLE "my_schema"."my_table_name" ("id" serial);

TIP

By default the generated SQL is emitted on a single line. Set the pretty option (or pass --pretty on the CLI) to format statements with linebreaks and indentation for easier reading.

Type ​

ts
type Name = string | { schema?: string; name: string } | PgLiteralValue;

Renaming and schemas ​

renameTable, renameType, renameDomain, renameView, renameMaterializedView, renameSequence, renameIndex, renameFunction, renameOperatorClass, and renameOperatorFamily preserve the source object's schema, including during automatic reversal:

javascript
pgm.renameView({ schema: 'app', name: 'old_view' }, 'new_view');
// up:   ALTER VIEW "app"."old_view" RENAME TO "new_view";
// down: ALTER VIEW "app"."new_view" RENAME TO "old_view";

An omitted, undefined, or empty ('') destination schema inherits the source schema. An empty source schema is equivalent to omitting it. A nonempty destination schema must match the source schema after identifier rendering: with decamelize: true, for example, appSchema and app_schema match. Use { schema, name } for the source when specifying a destination schema; these operations cannot infer a schema from a string name or search_path. A different schema raises an error. Moving an object between schemas requires explicit SQL and a corresponding down migration; see the individual operations for examples and restrictions.

PgLiteral inputs must contain a single unqualified identifier, either an ordinary unquoted identifier or a nonempty double-quoted identifier. Surrounding SQL whitespace is allowed. Raw identifiers are neither quoted nor decamelized: PostgreSQL folds unquoted names to lower case, while quoted names preserve case. Use double quotes for reserved words. Dots and escaped double quotes inside a quoted identifier are supported: pgm.func('"new.name"') is one name, whereas pgm.func('app.new_name') is qualified and rejected.

WARNING

Qualified PgLiteral sources and destinations now raise an error during SQL generation in both directions. Previously, a qualified source could work going up with a manually written down migration, including in already-applied migrations that are replayed on a fresh database. Replace it with { schema, name }, which uses normal identifier quoting and decamelization, or use pgm.sql with explicit SQL in both directions. Other raw forms, including expressions, comments, multiple tokens, and U& Unicode escape syntax, are also rejected by these ten rename operations. Other uses of PgLiteral are unchanged.

Locking ​

node-pg-migrate automatically checks if no other migration is running. To do so, it uses an advisory lock (see #239). Lock is held for the duration of DB session, so if migration scripts froze up, you need to kill it, before running another migration script.

Migration methods ​

The pgm object that is passed to each up/down block has many different operations available. Each operation is simply a function that generates some sql and stores it in the current pgm context.

By default, all migrations will be run in a transaction. To disable transactions for a specific migration, call pgm.noTransaction() This is required for some SQL operations that cannot be run within a transaction. It should be used carefully.