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

• 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