pgroll

A thread-safe, lightweight, and flexible database migration tool for PostgreSQL.

pgroll runs your plain-SQL migrations through the postgres (PostgresJS) client. Migrations are applied inside a transaction and guarded by a session-level advisory lock, so it is safe to run concurrently from multiple processes — only one migration runs at a time.

Supported PostgreSQL clients

• [x] PostgresJS
• [ ] node-postgres (pg)

Features

• up — Apply all pending migrations.
• down — Roll back all applied migrations (down to version 0).
• go — Migrate forward or backward to a specific version (go 0 reverts everything).
• create — Generate a matching up/down migration file pair.

Requirements

• Node.js ≥ 24.16.0 (the package ships as ESM)
• A reachable PostgreSQL database

Installation

Install globally to use the CLI anywhere:

npm install -g pgroll

…or add it to a project and run it with npx:

npm install pgroll

Configuration

Connecting to PostgreSQL

The CLI connects using a connection URL or the standard libpq environment variables (read by the underlying postgres client).

Option 1 — connection URL via the -u, --url flag:

npx pgroll --url "postgres://user:password@localhost:5432/mydb" up

Option 2 — environment variables:

| Variable | Description | Default | | ------------ | ------------- | ------------ | | PGHOST | Server host | localhost | | PGPORT | Server port | 5432 | | PGDATABASE | Database name | — | | PGUSER | User name | OS user name | | PGPASSWORD | Password | — |

PGHOST=localhost PGDATABASE=mydb PGUSER=me PGPASSWORD=secret npx pgroll up

When --url is provided, it takes precedence over the PG* variables.

Migration files

Migrations live in a directory (default ./migrations, override with -d, --migrationDir <path>). Each migration is a pair of plain .sql files distinguished by suffix:

20240619121610402_init_up.sql     # applied on "up"
20240619121610402_init_down.sql   # applied on "down"

• The leading number is a timestamp generated by create; it determines order.
• up migrations are applied in ascending filename order; down migrations in descending order, so rollbacks unwind in the reverse of how they were applied.
• A migration's version is its position in that ordered list (the first up migration is version 1).

CLI

pgroll [global options] <command>

Global options

| Option | Description | | --------------------------- | --------------------------------------------------------------- | | -d, --migrationDir <path> | Directory holding the migration files (default ./migrations). | | -u, --url <url> | PostgreSQL connection URL (overrides PG* env vars). | | -V, --version | Print the pgroll version. | | -h, --help | Show help. |

Commands

Apply all pending migrations:

npx pgroll up

Roll back every applied migration:

npx pgroll down

Migrate to a specific version (moves up or down as needed; 0 rolls everything back):

npx pgroll go <version>

Create a new migration pair (writes <timestamp>_<name>_up.sql and ..._down.sql with placeholder contents for you to fill in):

npx pgroll create <name>

A typical workflow:

npx pgroll create add_users_table   # creates the up/down file pair
# …edit the generated *_up.sql / *_down.sql files…
npx pgroll up                        # apply it

Programmatic API

pgroll can also be used as a library. Pass it a postgres client instance and drive migrations directly:

import postgres from 'postgres';
import { Migrator } from 'pgroll';

const sql = postgres('postgres://user:password@localhost:5432/mydb');
const migrator = new Migrator(sql, './migrations');

// Apply all pending migrations
await migrator.up();

console.log('Current version:', await migrator.getCurrentVersion());

// Migrate to a specific version, logging progress as it goes
await migrator.go(0, { eventHandler: info => console.log(info) });

await sql.end();

new Migrator(dbClient, migrationsDir?)

| Parameter | Type | Description | | --------------- | ------------------- | ------------------------------------------------------------- | | dbClient | Sql (PostgresJS) | A postgres client instance. | | migrationsDir | string (optional) | Directory of migration files. Defaults to <cwd>/migrations. |

Methods

| Method | Description | | --------------------- | ----------------------------------------------------------------------- | | up(opts?) | Apply all pending up migrations. | | down(opts?) | Roll back all applied migrations (to version 0). | | go(version, opts?) | Migrate forward or backward to version (0 reverts everything). | | getCurrentVersion() | Resolve to the highest applied version (0 if none have been applied). |

opts is { eventHandler: (info: string) => void } — an optional callback invoked with a human-readable message as each migration is applied.

How it works

On the first run, pgroll creates a bookkeeping table:

CREATE TABLE IF NOT EXISTS migrations (
  name       varchar(500) PRIMARY KEY,
  version    smallint NOT NULL,
  applied_at timestamp DEFAULT CURRENT_TIMESTAMP
);

Each up/down/go run reserves a single connection, takes a session-level pg_advisory_lock, and applies the relevant migration files within a transaction — recording or removing the corresponding rows in migrations as it goes. The advisory lock serializes concurrent runs across processes, and the surrounding transaction means a failed migration rolls back cleanly.

License

ISC