CLI Usage

Database Connection

You can specify your database connection information using config.

// config/default.json
{
  "db": {
    "url": "postgres://postgres:password@localhost:5432/database",
  },
}

or

// config/default.json
{
  "db": {
    "user": "postgres",
    "password": "",
    "host": "localhost",
    "port": 5432,
    "database": "database",
  },
}

You could also specify your database url by setting the environment variable DATABASE_URL.

DATABASE_URL=postgres://postgres@localhost/database node-pg-migrate

You can specify a custom JSON file with config (the format is same as for db entry of config file), for example:

// path/to/config.json
{
  "user": "postgres",
  "password": "",
  "host": "localhost",
  "port": 5432,
  "database": "database",
}

TIP

If a .env file exists, it will be loaded using dotenv (if installed) when running the node-pg-migrate binary. If the .env file is not on the same level where the command has been called, you can use the --envPath option to point to the location of your .env file.

Depending on your project's setup, it may make sense to write some custom grunt/gulp/whatever tasks that set this env var and run your migration commands. More on that below.

TLS with a trusted certificate authority

The CLI accepts the same connection settings as pg.Client. To trust a private CA while checking the server certificate and hostname, use sslrootcert and sslmode=verify-full in DATABASE_URL. The path is on the machine running the CLI. Percent-encode the path, especially spaces, &, #, %, and literal + characters (for example, with encodeURIComponent(path)):

DATABASE_URL='postgres://user:password@db.example.com/database?sslmode=verify-full&sslrootcert=%2Fpath%2Fto%2Froot-ca.crt' node-pg-migrate up

For JavaScript or TypeScript configuration, load the CA's PEM contents into ssl.ca. For example, migrations.config.mjs (the same code works in migrations.config.ts):

import { readFileSync } from 'node:fs';

export default {
  'database-url-var': {
    // This example explicitly reads DATABASE_URL, even if -d selects another variable.
    connectionString: process.env.DATABASE_URL,
    ssl: {
      ca: readFileSync('/path/to/root-ca.crt', 'utf8'),
      rejectUnauthorized: true,
    },
  },
};

node-pg-migrate up --config-file migrations.config.mjs

Here, the configuration's database-url-var value is a connection object that replaces the connection read from the environment. It is distinct from the --database-url-var NAME CLI flag, which selects an environment variable. The top-level host/database/ssl configuration and the url key are used only when no connection has been resolved yet. A set DATABASE_URL takes precedence over these forms; the configuration's database-url-var key overrides it. Without DATABASE_URL, the top-level connection form also accepts ssl.

For the configuration-object example, keep DATABASE_URL free of sslmode, sslrootcert, sslcert, and sslkey: node-postgres replaces the explicit ssl object when any of these parameters appears in the URL. Use either the URL's TLS settings or the explicit object.

Trust the CA that issued the server certificate and ensure that certificate's hostname or IP matches the connection. With an explicit ssl object and no TLS URL parameters, --no-reject-unauthorized disables certificate verification. When the URL contains sslmode, sslrootcert, sslcert, or sslkey, node-postgres replaces that object, so both --reject-unauthorized and --no-reject-unauthorized have no effect. Providing the trusted CA keeps verification enabled. Certificate verification defaults to enabled; the examples set rejectUnauthorized: true explicitly.

Available Commands

node-pg-migrate uses subcommands. Each command exposes only its relevant options and has its own help output, e.g. node-pg-migrate up --help or node-pg-migrate create --help. Options must be passed after the command (for example node-pg-migrate up 2 -m migrations).

You can print the installed version with node-pg-migrate --version (alias -i).

Command	Description
node-pg-migrate create {migration-name}	creates a new migration file with a timestamp prepended to the name you provide. Dashes replace spaces and underscores.
node-pg-migrate up	runs all up migrations from the current state.
node-pg-migrate up {N}	runs N up migrations from the current state.
node-pg-migrate down	runs a single down migration.
node-pg-migrate down {N}	runs N down migrations from the current state.
node-pg-migrate redo	reverts the last migration, then applies pending migrations.
node-pg-migrate redo {N}	reverts the last N migrations, then applies pending migrations.

redo uses one connection and advisory lock for both phases. On PostgreSQL, with the default --single-transaction, it commits only after both phases succeed. If reapplying a migration fails, the reverted changes and migration-history rows are rolled back together. Retrying therefore selects the same migrations instead of reverting earlier ones.

As before, the up phase applies all pending migrations, including migrations that were pending before redo. When selecting a migration by name, the up phase remains limited to that migration. --no-single-transaction runs each migration in its own transaction, so completed migrations remain committed after a later failure. A migration that calls pgm.noTransaction() also breaks the shared transaction; changes committed before or during that migration cannot be rolled back.

CockroachDB does not provide full atomicity for DDL. With --single-transaction or --dry-run, the runner disables and verifies autocommit_before_ddl before setup or migrations, refusing to continue if it cannot be disabled. This prevents DDL from ending the shared transaction early. Schema changes can still fail at commit with XXA00 after other changes have committed; rollback cannot undo those changes. The runner reports this condition, and you should inspect the schema, data and migration history before retrying.

The shared transaction follows CockroachDB's transactional DDL limitations. For a table that existed before the transaction, an UPDATE cannot reference a column added during that transaction, including by another migration in the same run. For this pattern, use pgm.noTransaction(), or --no-single-transaction with autocommit_before_ddl enabled. Both opt-outs can retain committed changes after a later failure.

Outside dry runs, --no-single-transaction leaves autocommit_before_ddl unchanged. When enabled (the v25 default), DDL commits the preceding transaction before executing, even inside a migration's own transaction; a failed run can retain changes and history updates.

Dry Runs

node-pg-migrate up --dry-run prints the SQL a real run would execute and applies none of it. The whole session runs inside a read-only transaction (BEGIN; SET TRANSACTION READ ONLY;), so the guarantee is enforced by the database rather than by node-pg-migrate itself:

• the migrations schema and the migrations table are not created - a missing table is reported as > Would create migrations table ...;
• --fake --dry-run prints the INSERT/DELETE it would run and records nothing;
• --create-schema and --create-migrations-schema report > Would create schema ...;
• no advisory lock is taken, so a dry run can never block a deployment;
• a statement a migration issues itself - pgm.db.query(...) - is refused by the server.

Reading the database still works, so pgm.db.select(...) inside a migration behaves as usual.

redo --dry-run prints the selected down migrations followed by the pending up migrations. It calculates the remaining migration history in memory without changing the stored history or applying either phase.

Limitations

A dry run prints; it does not validate. Because nothing is applied, a migration cannot see what an earlier pending migration would have created:

• a migration that reads schema or data produced by an earlier migration of the same run will fail;
• a migration that writes through pgm.db.query(...) fails with a message pointing at that write - the read-only transaction refuses it;
• during redo --dry-run, direct database reads still see the unchanged database, not the state the down phase would have produced.

Migration History

Each run records the migrations it applies in the migrations table: migrations-table (pgmigrations) in migrations-schema, which defaults to the first schema, which defaults to public. That location only comes from configuration, so before creating anything a run checks that the history really is there.

When the migrations table is missing or empty, but another schema of the database has a table of that name - a table with the id, name and run_on columns of a migrations table, not a view - that already records migrations, the run is refused rather than starting the history over: replaying every migration either fails half-way (relation "…" does not exist) or silently duplicates objects into the wrong schema. A refused run creates nothing - not the migrations table, and not the schemas --create-schema asks for. This is what happens when:

• the history and your objects live in a schema your role's own search_path points at (so psql finds everything), but --schema is not given and the run falls back to public;
• --schema was used on earlier runs and has since been dropped;
• a --schema list was reordered: the migrations table follows its first entry.

The error names the table it found and the option that points the run at it, such as --schema app. Where a run looks for another history depends on how it was configured:

Configuration	Looks for another history in
migrations-schema	nowhere: the location is taken at its word
schema (on the command line or in the config)	the other schemas of that list
neither	every schema, since public is only a fallback

From the API, a runner() call without schema counts as neither.

So one schema per tenant keeps working - up -s tenant_a and up -s tenant_b each keep their own history. To start a new history on purpose next to an existing one, pin it with --migrations-schema, for example --migrations-schema public.

redo re-applies into the migrations table it has just reverted from, even when that left the table empty. Two separate runs cannot tell: after down 0, the up that follows finds an empty migrations table, just like the one a failed replay leaves behind, and is refused while another schema holds a history. Pass --migrations-schema to start over there.

A migrations table in another schema that the connected role cannot read counts as a history. The run's own migrations table is looked up in the system catalogs, so a role without privileges on it gets the database's permissions error rather than an attempt to create it again.

migrations-table, migrations-schema and schema name the objects exactly: decamelize does not apply to them.

Configuration

TIP

See all options for a command by running its help, e.g. node-pg-migrate up --help.

Most of the configuration options can be also specified in the config file.

You can adjust defaults by passing arguments to the command. The migration-file-language, migration-filename-format and template-file-name options are only available on the create command; the remaining options below apply to the up, down and redo commands:

Argument	Aliases	Default	Description
config-file	f	undefined	The file with migration JSON config
config-value		db	Name of config section with db options
schema	s	public	The schema(s) on which migration will be run, used to set search_path
create-schema		false	Create the configured schema if it doesn't exist
database-url-var	d	DATABASE_URL	CLI flag: name of the environment variable containing the database URL. In a config file, this key holds the connection URL string or client configuration object itself
migrations-dir	m	migrations	The directory containing your migration files. This path is resolved from cwd(). Alternatively, provide a glob pattern and set --use-glob. Note: enabling glob will read both, --migrations-dir and --ignore-pattern as glob patterns
use-glob		false	Use glob to find migration files. This will use --migrations-dir and --ignore-pattern to glob-search for migration files.
migrations-schema		same value as schema	The schema storing table which migrations have been run, see
create-migrations-schema		false	Create the configured migrations schema if it doesn't exist
migrations-table	t	pgmigrations	The table storing which migrations have been run
ignore-pattern		undefined	Regex pattern for file names to ignore (ignores files starting with . by default). Alternatively, provide a glob pattern and set --use-glob. Note: enabling glob will read both, --migrations-dir and --ignore-pattern as glob patterns
migration-filename-format		timestamp	Choose prefix of file, utc (20200605075829074), timestamp (1591343909074), or index (0012)
migration-file-language	j	js	Language of the migration file to create (js, ts, sql, cjs, mjs, cts, mts)
template-file-name		undefined	Utilize a custom migration template file with language inferred from its extension. The file should export the up method, accepting a MigrationBuilder instance.
envPath		same level where it's invoked	Retrieve the path to a .env file. This feature proves handy when dealing with nested projects or when referencing a global .env file.
timestamp		false	Treats number argument to up/down migration as timestamp (running up migrations less or equal to timestamp or down migrations greater or equal to timestamp)
check-order		true	Check order of migrations before running them, to switch it off supply --no-check-order
single-transaction		true	Runs migrations in a shared transaction, including both phases of redo; to switch it off supply --no-single-transaction. See the transaction limitations above
no-lock		false	Disables locking mechanism and checks
advisory-lock-mode		fail	Specify behavior when the migration advisory lock is already held by another process (fail, wait)
fake		false	Mark migrations as run without actually performing them, (use with caution!)
dry-run		false	Print the SQL that would run without applying anything, see
decamelize		false	Runs decamelize on table/column/etc. names used in migrations (not on migrations-table, migrations-schema or schema)
pretty		false	Formats the generated SQL statements with linebreaks and indentation, to switch it on supply --pretty (omit or use --no-pretty for single-line statements)
verbose		true	Print all debug messages like DB queries run, to switch it off supply --no-verbose
reject-unauthorized		undefined	Sets SSL rejectUnauthorized on an explicit SSL object; has no effect when TLS URL parameters replace that object. Prefer configuring a trusted CA
tsconfig-paths		false	Enable jiti tsconfig paths resolution when loading TS/JS migration files. Pass true to auto-discover the nearest tsconfig.json, or a path to a specific tsconfig.json (e.g. --tsconfig-paths ./tsconfig.json)

For SSL connection to DB you can set PGSSLMODE environment variable to value from list other than disable. e.g. PGSSLMODE=require node-pg-migrate up (pg will take it into account)

node-postgres reads PGSSLMODE but does not load certificates from PGSSLROOTCERT, PGSSLCERT, or PGSSLKEY. Configure certificate paths in the URL or PEM contents in ssl, as shown in TLS with a trusted certificate authority.

JSON Configuration

TIP

You can use config or your own json file with configuration (config-file command line option).

You can also specify your database connection here, see.

Other available options are:

{
  "schema": "public",
  "create-schema": false,
  "database-url-var": "postgres://user:password@db.example.com/database",
  "migrations-dir": "migrations",
  "use-glob": false,
  "migrations-schema": "public",
  "create-migrations-schema": false,
  "migrations-table": "pgmigrations",
  "migration-filename-format": "utc",
  "migration-file-language": "js",
  "ignore-pattern": "/SKIP$/",
  "template-file-name": "templates/new-migration.js",
  "check-order": true,
  "verbose": true,
  "decamelize": false,
  "pretty": false,
  "tsconfig-paths": "./tsconfig.json",
}

If you want to vary the configuration (e.g. for different environments), you can provide a map of configuration groups and specify which to use with the --config-value command line option.

For example with a config like:

{
  "dev": {
    "migrations-schema": "public",
    "verbose": true,
  },
  "prod": {
    "migrations-schema": "myapp",
  },
}

The command node-pg-migrate up --config-file=migrations.config.js --config-value=prod will apply the prod configuration. There are no constraints on how you name your configuration groups.