@gabbe/pg-migrate

A SQL-first PostgreSQL migration tool with a CLI and TypeScript API.

Install

npm install @gabbe/pg-migrate

This installs the pg-migrate command and the package API (ESM only).

Requires Node.js 22+ and PostgreSQL 14+.

Quick start

npx pg-migrate create add_users

Edit the new file in migrations:

-- migrate:up
CREATE TABLE users (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email text NOT NULL UNIQUE
);

-- migrate:down
DROP TABLE users;

Then apply and validate it:

npx pg-migrate up --url postgres://localhost/app
npx pg-migrate validate --url postgres://localhost/app

validate checks file structure and history, not PostgreSQL syntax.

CLI

pg-migrate <command> [arguments] [options]
pg-migrate help [command]
pg-migrate <command> --help

| Command | Action | | --------------- | --------------------------------------------- | | create <name> | Create a timestamped migration file. | | status | Show applied and pending migrations. | | validate | Validate file structure and database history. | | up | Apply all pending migrations in order. | | down | Revert the latest applied migration. |

Use --target <version-or-filename> with up to apply through a migration. Use it with down to revert all migrations after it. A down target remains applied. A version contains 14 digits, for example 20260811120000.

| Option | Use | | ------------------------ | ------------------------------------------------- | | -c, --config <path> | Environment file. | | -d, --directory <path> | Migration directory. | | -t, --table <name> | History table; database commands only. | | -u, --url <url> | PostgreSQL URL; database commands only. | | --target <target> | Target version or filename; up and down only. | | --no-color | Disable color. | | -q, --quiet | Show only errors and requested help. | | -v, --verbose | Show detailed progress. | | -h, --help | Show help. |

Final results and help go to stdout. Progress and errors go to stderr. An empty up or down plan leaves stdout empty. A failure sets exit code 1. Quiet mode takes precedence over verbose mode.

Configuration

Settings use this precedence: command option, process environment, environment file, then default.

| Variable | Use | Default | | --------------- | ------------------- | ------------------- | | PGM_CONFIG | Environment file | .env | | PGM_DIRECTORY | Migration directory | migrations | | PGM_TABLE | History table | schema_migrations | | PGM_URL | PostgreSQL URL | None |

The default .env file is optional. A file selected with --config or PGM_CONFIG must exist. For example:

PGM_DIRECTORY=db/migrations
PGM_TABLE=app.schema_migrations
PGM_URL=postgres://localhost/app

Database commands require --url or PGM_URL. A history table can include a schema. Each table or schema identifier must start with a lowercase letter or underscore and contain only lowercase letters, numbers, and underscores.

Connection attempts stop after 10 seconds. Lock and statement waits have no default timeout. For deployments, set them in the URL, in milliseconds:

postgres://localhost/app?lock_timeout=5000&statement_timeout=60000

Migration files

create makes the directory if necessary and creates this UTC filename:

<YYYYMMDDHHMMSS>_<name>.sql

Names must match [a-z0-9][a-z0-9_]*. Versions must be unique. Each file must be valid UTF-8 and have this structure:

-- migrate:up
CREATE TABLE example (id bigint PRIMARY KEY);

-- migrate:down
DROP TABLE example;

Only white space can occur before the first marker. Each marker must occur exactly once, migrate:up must come first, and its section must not be empty. The down section can be empty. An exact marker line is reserved, including in SQL comments and strings. Every .sql entry in the directory must be a regular file with a valid migration filename. The tool does not scan subdirectories.

The tool sends each section to PostgreSQL without parsing SQL statements. The down section can be empty. In that case, down runs no migration SQL and removes only the migration history row. It does not restore data or reverse schema changes. A later up command sends the original up section again. Use an empty down section only when this behavior is intentional.

Safety and history

Applied migrations must be a continuous sequence of the files on disk. The tool stops for a missing version, duplicate version, or history gap.

The history table stores the filename and SHA-256 checksum of each applied migration. Database commands stop if an applied file is edited or renamed. Checksums use the exact file bytes. File metadata and paths do not affect them. Use this Git rule to keep SQL line endings stable on all systems:

*.sql text eol=lf

up, down, and validate wait for an advisory lock for the selected history table. Each migration runs in its own transaction with its history change. If a migration fails, its transaction rolls back, but earlier migrations from the same command stay complete.

status checks filenames, checksums, and history without validating migration file contents, creating the history table, or taking the lock. validate checks UTF-8, markers, filenames, checksums, and history, not PostgreSQL syntax. up and down check UTF-8 and markers only in their execution plan.

Limitations

• Each migration runs in a transaction. Migration SQL must not contain transaction-control commands or statements that cannot run in a transaction. The tool does not detect transaction-control commands.
• psql meta-commands, variable substitution, and COPY FROM STDIN are not supported.
• validate checks file structure and history, not PostgreSQL SQL syntax.
• The tool does not create schemas or set search_path. Create the history table schema first. Set the required search_path or use schema-qualified names in migration SQL.
• up, down, and validate must use the same PostgreSQL server session for the complete command. Use a direct connection if a pool assigns server connections per transaction or statement.

TypeScript API

import {
  migrate,
  rollback,
  status,
  validate,
  type DatabaseOptions,
  type LogEvent,
} from "@gabbe/pg-migrate";

const options = {
  directory: "migrations",
  table: "schema_migrations",
  url: "postgres://localhost/app",
  log(event: LogEvent): void {
    process.stderr.write(`${event.type}\n`);
  },
} satisfies DatabaseOptions;

const migrationStatus = await status(options);
const validation = await validate(options);
const applied = await migrate(options);
const reverted = await rollback({ ...options, target: "20260811120000" });

All options are explicit. The API does not read CLI options or environment variables, and file creation is available through the CLI only. status returns ordered migration state and counts. validate returns counts. migrate and rollback return executed filenames. The optional log callback receives typed progress events. Failures throw an Error.

License

MIT