Postgres Migration CLI
Installation
- npm
- yarn
- pnpm
npm is the default package manager for Node.js, and to where
Your project is using npm if it has a
Run the following command in your terminal:
pgmcli is published.Your project is using npm if it has a
package-lock.json file in its root folder.Run the following command in your terminal:
terminal
npm install -g pgmcli
Optional Config
You can optionally create a .pgmcli file in your project root to store your connection details. This file will be automatically loaded when running pgmcli commands. You can override these values by passing them as flags to the command. You can specify the path to the config file using the --config flag.
.pgmcli
{
"$schema": "https://raw.githubusercontent.com/igrek8/monorepo/main/packages/pgmcli/schema.json",
"user": "myuser",
"table": "migration",
"db": "mydb",
"client": {
"ssl": false
}
}
Commands
Initial Setup
pgmcli install -h
Creates migrations directory and table
Options:
--host <string> host
--port <number> port
-u, --user <string> user
-p, --password <string> password
--db <name> database
--dir <name> migrations directory (default: "migrations")
--table <name> migrations table (default: "migrations")
--config <path> config path (default: ".pgmcli")
-h, --help display help for command
Create Migration
pgmcli create -h
Creates a migration file
Options:
--name <name> migration file name (.ts, .js, .cjs, .mjs, .mts, .sql)
--plan show plan
--dir <name> migrations directory (default: "migrations")
--tag <name> tag where revert block begins (default: "REVERT BEGIN")
--config <path> config path
-h, --help display help for command
Status
pgmcli status -h
Shows migrations statuses
Options:
--host <string> host
--port <number> port
-u, --user <string> user
-p, --password <string> password
--db <name> database
--dir <name> migrations directory (default: "migrations")
--table <name> migrations table (default: "migrations")
--config <path> config path (default: ".pgmcli")
-h, --help display help for command
Apply Migrations
pgmcli apply -h
Applies migrations
Options:
--host <string> host
--port <number> port
-u, --user <string> user
-p, --password <string> password
--db <name> database
--dir <name> migrations directory (default: "migrations")
--table <name> migrations table (default: "migrations")
--config <path> config path (default: ".pgmcli")
--until <migration> apply until the specified migration (inclusive)
--plan show plan
--log-level <level> log level (choices: "DEBUG", "LOG", "INFO", "NOTICE", "WARNING", "ERROR", default: "INFO")
--meta <jsonb> extra meta associated with apply
--tag <name> tag where apply block ends (default: "REVERT BEGIN")
-h, --help display help for command
Migration Parameters
Migration files support parameters specified using special comments to control migration behavior. These parameters are defined at the top of the file using the format -- @ <key> [value].
Supported Parameters:
run- Controls when a migration is executed. Set toalwaysto apply a migration every time regardless if it has already been previously applied. Useful for maintaining alignment between the schema and database objects like functions and triggers.transaction- Controls transaction handling. Set tofalseto run the migration outside of a transaction. Required for operations that cannot run within transactions, such asCREATE INDEX CONCURRENTLYorREINDEX CONCURRENTLY.
SQL Example:
migrations/20220101000000_add_function.sql
-- @ run always
-- It is important to use REPLACE so that the function can be updated
CREATE OR REPLACE FUNCTION add(integer, integer) RETURNS integer
AS 'SELECT $1 + $2;'
LANGUAGE SQL
IMMUTABLE
RETURNS NULL ON NULL INPUT;
-- DO NOT REMOVE - THIS LINE SEPARATES APPLY AND REVERT OPERATIONS. REVERT BEGIN
DROP FUNCTION add(integer, integer);
JavaScript/TypeScript Example:
migrations/20220102000000_create_index.ts
import { Client } from 'pg';
export const upParams = {
transaction: 'false',
};
export async function up(client: Client): Promise<void> {
await client.query('CREATE INDEX CONCURRENTLY idx_users_email ON users(email)');
}
export async function down(client: Client): Promise<void> {
await client.query('DROP INDEX CONCURRENTLY idx_users_email');
}
Revert Migrations
pgmcli revert -h
Reverts migrations
Options:
--host <string> host
--port <number> port
-u, --user <string> user
-p, --password <string> password
--db <name> database
--dir <name> migrations directory (default: "migrations")
--table <name> migrations table (default: "migrations")
--config <path> config path (default: ".pgmcli")
--until <migration> revert until the specified migration (inclusive)
--plan show plan
--log-level <level> log level (choices: "DEBUG", "LOG", "INFO", "NOTICE", "WARNING",
"ERROR", default: "INFO")
--tag <name> tag where revert block begins (default: "REVERT BEGIN")
-h, --help display help for command
Uninstall
pgmcli uninstall -h
Drops migrations table
Options:
--host <string> host
--port <number> port
-u, --user <string> user
-p, --password <string> password
--db <name> database
--dir <name> migrations directory (default: "migrations")
--table <name> migrations table (default: "migrations")
--config <path> config path (default: ".pgmcli")
-h, --help display help for command
Integration
GitHub Actions
You can verify migrations in your GitHub CI workflow by following this guide.
.github/workflows/job-verify.yml
name: Verify Migrations
on: workflow_call
jobs:
verification:
runs-on: ubuntu-latest
container: node:22
services:
postgres:
image: postgres
env:
POSTGRES_HOST_AUTH_METHOD: trust
options: >-
--health-cmd pg_isready
--health-interval 10s
--health-timeout 5s
--health-retries 5
env:
POSTGRES_HOST: postgres
POSTGRES_USER: postgres
steps:
- name: Checkout
uses: actions/checkout@v4
- name: Initialize migrations
run: npx pgmcli install
- name: Apply migrations
run: npx pgmcli apply
- name: Revert migrations
run: npx pgmcli revert --until <your_last_migration>