node-pg-migrate

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

Migration operations such as pgm.createTable() and pgm.sql() queue SQL statements. The runner executes them in order after up or down completes: when a synchronous function returns, its returned Promise resolves, or its run() callback is called.

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 ​

Use an async migration when you need to wait for asynchronous work, such as fetching information from an external server. You can add a second argument to receive a callback and call it when that work is complete:

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) => {
  await doSomethingAsync();

  pgm.createTable('names', { id: 'serial', name: 'text' });
  pgm.insert('names', { name: 'Ada' });
};

Both SQL statements in this example are queued and executed after the Promise returned by up resolves. Adding async does not change how migration operations work. These operations return void, so awaiting pgm.createTable() or pgm.insert() does not execute the queued SQL.

For several records, pass an array to queue one multi-row INSERT:

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

  pgm.createTable('names', { id: 'serial', name: 'text' });
  pgm.insert('names', [{ name: 'Ada' }, { name: 'Grace' }]);
};

pgm.db.query() and pgm.db.select() execute queries directly and return Promises. Await them, or return their Promise, so the migration waits for them to finish. A direct query cannot depend on a table or column that an earlier queued operation has yet to create. To insert data into a table created in the same migration, queue the insert with pgm.insert(), as above.

Direct queries also run before the migration's own BEGIN, so unless migrations share a single transaction (singleTransaction, the CLI default), a write made with pgm.db.query() is not rolled back if a queued statement fails.

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;

type Reference =
  | Name
  | (Exclude<Name, string | PgLiteralValue> & {
      columns?: string | string[];
    });

Use a Reference object to select the columns targeted by a foreign key. The table, schema, and column names are quoted and follow the decamelize option. Omit columns, or use an empty array, to reference the table's primary key. Explicit columns must match a primary key, unique constraint, or suitable unique index; composite foreign keys must supply matching columns in order.

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.