PgMigrations

Lightweight, zero-dependency PostgreSQL migration tool for Node.js.

Features:

• Run migrations up or down (versioned, repeatable, before, after)
• Execute arbitrary psql commands using your project configuration
• Create database schema dumps
• Run PostgreSQL database unit tests in functions or procedures

Table of Contents

• Installation
• Quick Start
• Commands
• Examples
• Migration Naming Convention
• Migration Order
• Configuration
• Testing
• Contributing
• License

Installation

npm install pgmigrations

Prerequisites: PostgreSQL client tools (psql, pg_dump) must be installed on your system. These are included with the standard PostgreSQL installation.

If you need to install client tools separately:

• Linux: apt-get install -y postgresql-client
• Windows: Use the official PostgreSQL installer and select "Command Line Tools" only

Quick Start

1. Create a configuration file db.js in your project root:

module.exports = {
    host: "localhost",
    port: "5432",
    dbname: "mydb",
    username: "postgres",
    password: "postgres",
    migrationDir: "./migrations"
}

2. Create a migration file in ./migrations/V001__initial.sql

3. Run migrations:

npx pgmigrations up

Commands

Usage:
  pgmigrations [command] [switches]

Commands:
  up              Run migrations in order: before, before repeatable, up,
                  repeatable, after.
  down            Run only down migrations.
  history         Display the current migration schema history.
  run | exec      Run a command, script file, or script directory with psql.
  dump | schema   Run pg_dump with --schema-only --encoding=UTF8.
  psql            Run arbitrary psql command or open psql shell.
  test            Run database tests.
  config          Display the current configuration.

Switches:
  -h, --help      Show help
  --list          List available migrations or tests
  --dry           Dry run mode (rolls back changes)
  --full          Execute all migrations, ignoring schema history
  --dump          Dump SQL to console instead of executing
  --verbose       Enable verbose logging
  --config=[file] Load custom config file(s). Can be used multiple times.

Examples

Execute Commands

# Execute a query
npx pgmigrations run "select * from city"

# Execute a script file
npx pgmigrations run ./script1.sql

# Execute all SQL files in a directory
npx pgmigrations run ./dir

# List all tables
npx pgmigrations run \\dt

# Display psql help
npx pgmigrations run --help

# Open psql interactive shell
npx pgmigrations psql

Schema Dumps

# Display schema to console
npx pgmigrations dump

# Write schema to file
npx pgmigrations dump --file schema.sql

Migrations

# List available migrations
npx pgmigrations up --list

# Run all UP migrations
npx pgmigrations up

# Dry run (preview changes without applying)
npx pgmigrations up --dry

# Run DOWN migrations
npx pgmigrations down

Testing

# Run database tests
npx pgmigrations test

# List available tests
npx pgmigrations test --list

Migration Naming Convention

Versioned Migrations

Format: [prefix][version][separator][description][suffix]

Versioned migrations run once per version number, in order.

| Type | Prefix | Example | Description | |------|--------|---------|-------------| | Up (Version) | V | V001__create_users.sql | Standard migration | | Down (Undo) | U | U001__drop_users.sql | Rollback migration |

• Version: Can be any text or number (e.g., 001, 1.0.0, 20231201)
• Separator: Default is __ (double underscore)
• Description: Underscores are converted to spaces in the history table
• Suffix: .sql (stored as SQL in the history table)

Repeatable Migrations

Format: [prefix][separator][description][suffix]

Repeatable migrations execute based on content changes.

| Type | Prefix | Behavior | |------|--------|----------| | Repeatable | R | Runs when file content changes | | Repeatable Before | _R | Runs before versioned migrations, when content changes | | Before | _B | Always runs before versioned migrations | | After | _A | Always runs after versioned migrations |

Examples:

R__create_views.sql        # Repeatable
_R__setup_extensions.sql   # Repeatable before
_B__pre_migration.sql      # Always before
_A__post_migration.sql     # Always after

Finalize Migrations

Migrations with the TEST prefix are executed as separate files outside the migration transaction. This is useful for test scripts.

Migration Order

Migrations execute in this order:

1. Before migrations (_B)
2. Repeatable before migrations (_R)
3. Versioned migrations (V)
4. Repeatable migrations (R)
5. After migrations (_A)

Configuration

The tool loads db.js from the current directory by default. Use --config=[file] to load additional config files (merged in order).

Command Line Overrides

Any configuration option can be overridden from the command line using --key=value format:

npx pgmigrations up --migrationDir=./migrations --verbose=true

Command line values take precedence over config files. If the specified option doesn't exist, an error will be thrown.

Connection Settings

module.exports = {
    host: "localhost",      // PostgreSQL host
    port: "5432",           // PostgreSQL port
    dbname: "mydb",         // Database name
    username: "postgres",   // Username
    password: "postgres"    // Password
}

Connection parameters can also be set via environment variables. See the PostgreSQL documentation for details.

Environment Settings

| Option | Default | Description | |--------|---------|-------------| | psql | "psql" | Path to psql executable | | pgdump | "pg_dump" | Path to pg_dump executable | | schemaDumpAdditionalArgs | ["--no-owner", "--no-acl"] | Additional pg_dump arguments | | verbose | false | Enable verbose logging | | env | true | Load .env file (true = .env, string = custom path) |

Custom executable paths:

• Linux: /usr/lib/postgresql/{version}/bin/psql
• Windows: C:\Program Files\PostgreSQL\{version}\bin\psql.exe

Migration Settings

| Option | Default | Description | |--------|---------|-------------| | migrationDir | "" | Migration directory (required). Can be string or array. | | upDirs | [] | Directories for UP migrations | | downDirs | [] | Directories for DOWN migrations | | repeatableDirs | [] | Directories for repeatable migrations | | repeatableBeforeDirs | [] | Directories for repeatable BEFORE migrations | | beforeDirs | [] | Directories for BEFORE migrations | | afterDirs | [] | Directories for AFTER migrations |

Migration Prefixes

| Option | Default | Description | |--------|---------|-------------| | upPrefix | "V" | Up version migration prefix | | downPrefix | "U" | Down (undo) migration prefix | | repeatablePrefix | "R" | Repeatable migration prefix | | repeatableBeforePrefix | "_R" | Repeatable before prefix | | beforePrefix | "_B" | Before migration prefix | | afterPrefix | "_A" | After migration prefix | | separatorPrefix | "__" | Separator between prefix and description |

Directory Options

| Option | Default | Description | |--------|---------|-------------| | recursiveDirs | false | Search subdirectories recursively | | dirsOrderedByName | true | Order by directory name, then migration type | | dirsNaturalOrder | true | Use natural ordering (like VS Code Explorer) | | dirsOrderReversed | false | Reverse directory order | | keepMigrationDirHistory | false | Keep old migration files in temp directory |

History Table Settings

| Option | Default | Description | |--------|---------|-------------| | historyTableSchema | "pgmigrations" | Schema for history table (auto-created) | | historyTableName | "schema_history" | Name of history table (auto-created) |

Advanced Options

| Option | Default | Description | |--------|---------|-------------| | migrationExtensions | [".sql"] | File extensions to treat as migrations | | allFilesAreRepeatable | false | Treat files without valid prefix as repeatable | | repeatableByScriptPath | true | Identify repeatables by full path (vs. name only) | | skipPattern | null | Regex pattern to skip files | | useProceduralScript | false | Use PL/pgSQL script instead of SQL transaction | | runOlderVersions | false | Run versioned migrations even if higher version applied | | warnOnInvalidPrefix | false | Warn if .sql files have invalid prefix | | tmpDir | OS temp + ___pgmigrations | Temporary directory for scripts |

Sorting Options

| Option | Default | Description | |--------|---------|-------------| | sortByPath | true | Sort non-versioned migrations by path then name | | sortFunction | See below | Custom sort function for migration names | | versionSortFunction | See below | Custom sort function for versions |

Default sort functions:

sortFunction: (a, b, config) =>
    config.sortByPath
        ? a.script.localeCompare(b.script, "en")
        : a.name.localeCompare(b.name, "en")

versionSortFunction: (a, b, config) =>
    a.version.localeCompare(b.version, "en", {numeric: true})

Hash Function

Custom hash function for content comparison:

hashFunction: function(data) {
    const hash = crypto.createHash('sha1');
    hash.update(data);
    return hash.digest('hex');
}

Script Tags

When parseScriptTags: true (default), special tags are parsed at build time:

Import Tag

Include content from another file:

-- # import ./test.sql

Output after build:

-- # import ./test.sql
-- (contents of test.sql inserted here)

Inside a block comment:

/*
# import ./test.sql
*/

Output:

/*
# import ./test.sql
(contents of test.sql inserted here)
*/

Dependencies Tag

Declare dependencies between repeatable scripts:

-- # dependencies script_name, another_script, path/to/script.sql

If a repeatable script needs to run (based on hash), its dependencies will also execute.

Environment Variables

When parseEnvVars: true (default), environment variable placeholders are replaced:

CREATE USER ${USERNAME};

The .env file in the project root is automatically loaded.

Version Directory Options

| Option | Default | Description | |--------|---------|-------------| | appendTopDirToVersion | false | Add top directory name to version number | | appendTopDirToVersionSplitBy | "__" | Split directory name by this string | | appendTopDirToVersionPart | 0 | Which part of split to use |

Testing

Database tests execute scripts in the migration directory in parallel mode.

| Option | Default | Description | |--------|---------|-------------| | failureExitCode | -1 | Exit code on test failure | | migrationAdditionalArgs | [] | Additional psql arguments for migrations |

Contributing

Contributions are welcome! Please submit a pull request with a description of your changes.

License

This project is licensed under the MIT License.