Skip to main content

Postgres Migration CLI

Installation​

npm is the default package manager for Node.js, and to where 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

Always Applied Migrations​

Use the file extensions .always.sql, .always.js, .always.ts, .always.cjs, .always.mjs, or .always.mts to designate a migration to be always applied regardless if it has already been previously applied. This is especially helpful for maintaining alignment between the schema and database objects like functions and triggers.

migrations/20220101000000_add_function.always.sql
-- It is import 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);

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>