node-pg-migrate

By default, node-pg-migrate runs all operations within a single transaction. However, certain operations (like pgm.addTypeValue) may fail if the type was created in a previous migration and the current migration is wrapped in a new transaction.

To handle these cases, wrap your migration logic in pgm.noTransaction().

Warning: Using pgm.noTransaction means that if an error occurs during the migration, the changes made up to that point will not be rolled back, potentially leaving your database in a partially migrated state.

PostgreSQL treats unquoted identifiers as case-insensitive (folding them to lowercase), but quoted identifiers are case-sensitive.

Because node-pg-migrate always quotes all identifiers, you must ensure that:

1. Your manual SQL queries also use quotes for identifiers.
2. Or, you use only lowercase identifiers to avoid confusion.

Decamelize Flag: You can use the decamelize configuration flag to automatically convert camelCase identifiers to snake_case (lowercase) using the decamelize package.

If you do not provide an export const down function, node-pg-migrate will attempt to automatically infer the rollback operations by reversing the up operations.

Note that not all operations can be automatically reversed. If a migration is destructive and cannot be rolled back, set export const down = false to prevent the tool from attempting an impossible rollback.

When using createTable or addColumns, you can define columns using a key/value object. Each key is the column name, and the value is an object containing configuration options.

Commonly used options include:

• type: The PostgreSQL data type (e.g., 'text', 'integer').
• array: Set to true for ARRAY or a number for ARRAY[n].
• unique: Boolean to add a unique constraint.
• primaryKey: Boolean to make the column the primary key.
• notNull: Boolean to set NOT NULL.
• default: A string for the DEFAULT clause (can be a literal, null, or a pgm.func() expression).
• references: The table name for a foreign key.
• onDelete / onUpdate: Constraints for foreign key actions.
• comment: A string to add a comment to the column.

Follow these steps to apply a new schema change to your database:

1. Create the migration file: Run npm run migrate create <name>. This generates a new file in the migrations/ directory.
2. Define the migration: Edit the generated file to include up (to apply changes) and down (to revert changes) functions. The up function receives a pgm (or MigrationBuilder in TS) object to perform schema operations.
3. Apply the migration: Set your DATABASE_URL environment variable and run npm run migrate up.

Example command: DATABASE_URL=postgres://user:pass@localhost:5432/db npm run migrate up

To use node-pg-migrate, you must first ensure you have the pg library installed as a dependency. Then, install node-pg-migrate as a development dependency.

Note that installing this module adds a runnable file to your node_modules/.bin directory. If installed locally, you can run it via ./node_modules/.bin/node-pg-migrate.js or by adding it to your package.json scripts.

npm add pg
npm add --save-dev node-pg-migrate

To simplify running TypeScript migrations, add a migrate script to your package.json using the -j ts flag. This allows you to execute migrations using your preferred package manager.

{
  "scripts": {
    "migrate": "node-pg-migrate -j ts"
  }
}

Before using node-pg-migrate, ensure your environment meets the following requirements:

• Node.js: version 22 or higher.
• PostgreSQL: version 14 or higher (lower versions may work but are not officially supported).
• Dependencies: The pg library must be installed in your project.

Starting with v9, node-pg-migrate uses jiti to handle TypeScript and mixed-extension migrations automatically. You no longer need ts-node, tsx, or Babel.

Removed CLI flags

• --ts-node
• --tsx
• --tsconfig

Handling Path Aliases

If you used --tsconfig to resolve path aliases in tsconfig.json, you must now use the --tsconfig-paths flag. You can pass true to enable auto-discovery or provide a specific path to the config file.

# Auto-discover tsconfig.json
node-pg-migrate up -j ts --tsconfig-paths true

# Use a specific tsconfig.json
node-pg-migrate up -j ts --tsconfig-paths ./config/tsconfig.json

// Update your package.json scripts from this:
{
  "scripts": {
    "migrate": "ts-node node_modules/.bin/node-pg-migrate -j ts"
  }
}
// To this:
{
  "scripts": {
    "migrate": "node-pg-migrate -j ts"
  }
}