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.