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.

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	redoes last migration (runs a single down migration, then single up migration).
node-pg-migrate redo {N}	redoes N last migrations (runs N down migrations, then N up migrations).

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.

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;
• redo --dry-run prints the down migrations and then reports No migrations to run! for the up half, because the down half was never applied.

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	Name of env variable with database url string
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	Combines all pending migrations into a single transaction so that if any migration fails, all will be rolled back, to switch it off supply --no-single-transaction
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 parameter. Use for e.g. self-signed certificates on the server. see
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)

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": "SECRET_DB_URL",
  "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.