PostgreSQL Schema Migrations with Schema Support

Common errors

• Migration failed: Error: Migration file '5_add-table.sql' has been altered since it was applied. Aborting.

  cause An SQL migration file that was previously successfully applied has been changed. The library stores a hash of the migration file content and detects mismatches.
  fix Never modify migration files after they have been run in any environment. If a change is needed, create a new migration file with a higher sequence number to apply the correction or new schema change.

• Error: Database "database-name" does not exist.

  cause The target PostgreSQL database specified in the configuration does not exist, and `ensureDatabaseExists` was not set to `true` or was set to `false`.
  fix Set `ensureDatabaseExists: true` in your database configuration object passed to `migrate` if you want the library to create the database automatically. Alternatively, manually create the database before running migrations.

• Error: A migration with version '5' already exists ('5_add-existing-table.sql'). Cannot apply '5_add-new-feature.sql'.

  cause Two or more migration files share the same sequential version number in the migration directory, leading to a conflict in the execution order.
  fix Rename one of the conflicting migration files to have a unique, higher sequential number. For example, if `5_add-table.sql` exists, rename `5_add-new-feature.sql` to `6_add-new-feature.sql`.

Warnings

• gotcha The library explicitly does not support 'down' migrations. Reversing a change requires a new 'forward' migration that undoes the previous effect. This is a deliberate design decision, not a bug.
  Fix: Plan for 'rolling forward' changes by writing new SQL migration files that negate or modify previous changes, rather than attempting to roll back.
• breaking Migration files are ordered numerically and are checked via a hash to ensure immutability. If a migration file is altered after being applied (e.g., to a production database), the migration system will detect the hash mismatch and refuse to apply further migrations, leading to an error.
  Fix: NEVER alter migration files that have already been applied to any environment. If a change is needed, create a new, subsequent migration file.
• gotcha Concurrent migration file creation by multiple developers can lead to conflicts (e.g., two files named `5_add-table.sql` and `5_add-column.sql`). The system will detect this and refuse to apply.
  Fix: Use the `loadMigrationFiles` function or the `pg-validate-migrations` bin script during CI/CD to detect conflicts early. Establish clear team guidelines for migration file naming and sequencing to avoid overlaps.
• gotcha The `migrate` function's `ensureDatabaseExists` option defaults to `false` for backwards compatibility. If this option is not explicitly set to `true`, the target database must exist prior to running migrations, otherwise, the connection will fail. The `defaultDatabase` option also defaults to 'postgres' for database creation purposes.
  Fix: Always explicitly set `ensureDatabaseExists: true` if you want the library to create the database if it doesn't exist. Ensure `defaultDatabase` is set correctly if not using 'postgres' as the default administrative database.
• gotcha Certain DDL operations, like `CREATE INDEX` without `CONCURRENTLY` or most `ALTER TABLE` statements, can acquire strong locks that block reads and/or writes, potentially causing significant downtime on active production databases.
  Fix: For large tables or high-traffic systems, use `CREATE INDEX CONCURRENTLY` and ensure your migration is *not* wrapped in a transaction if this is used (the library allows disabling transactions per-migration with `-- postgres-migrations disable-transaction`). For `ALTER TABLE` operations, follow best practices for zero-downtime migrations such as multi-step processes for adding `NOT NULL` constraints or dropping columns.

Install

• npm install postgres-schema-migrations npm
• yarn add postgres-schema-migrations yarn
• pnpm add postgres-schema-migrations pnpm

Imports

• migrate
  wrong:

  const migrate = require('postgres-schema-migrations').migrate

  correct:

  import { migrate } from 'postgres-schema-migrations'

  Primary function for running migrations. The library ships TypeScript types, so ESM imports are idiomatic.
• loadMigrationFiles
  wrong:

  import loadMigrationFiles from 'postgres-schema-migrations/loadMigrationFiles'

  correct:

  import { loadMigrationFiles } from 'postgres-schema-migrations'

  Used for validating migration file consistency before application. It's a named export.
• Client
  wrong:

  import pg from 'pg'; const client = new pg.Client(...)

  correct:

  import { Client } from 'pg'

  While not part of this library, `pg`'s Client/Pool is a common parameter for `migrate`. Ensure `pg` is installed separately.

Quickstart

This quickstart demonstrates how to apply database migrations using a `pg` client, showing both default (public) and namespaced schema migrations. It highlights connection management and error handling.

import { migrate } from "postgres-schema-migrations";
import { Client } from 'pg';
import path from 'path';

async function runMigrations() {
  const migrationFilesPath = path.resolve(__dirname, 'migrations');

  // Using a direct pg client allows more control over connection lifecycle
  const dbConfig = {
    database: process.env.DB_DATABASE ?? 'database-name',
    user: process.env.DB_USER ?? 'postgres',
    password: process.env.DB_PASSWORD ?? 'password',
    host: process.env.DB_HOST ?? 'localhost',
    port: parseInt(process.env.DB_PORT ?? '5432', 10),
  };

  const client = new Client(dbConfig);
  await client.connect();

  try {
    console.log('Running migrations for public schema...');
    await migrate({ client }, migrationFilesPath); // Defaults to 'public' schema
    console.log('Public schema migrations complete.');

    // Example of running migrations for a specific schema
    console.log('Running migrations for custom_schema...');
    const schemaMigrationsPath = path.resolve(__dirname, 'schema_migrations');
    await migrate({ client }, schemaMigrationsPath, { schema: 'custom_schema' });
    console.log('Custom schema migrations complete.');

  } catch (error) {
    console.error('Migration failed:', error);
    process.exit(1);
  } finally {
    await client.end();
  }
}

// Dummy migration files setup for example to be runnable
// In a real project, these would be actual .sql files.
// Make sure these directories exist for the quickstart to fully run.
// Example: migrations/1_create_users_table.sql
// CREATE TABLE users (id SERIAL PRIMARY KEY, name VARCHAR(255) NOT NULL);
// Example: schema_migrations/1_create_products_table.sql
// CREATE SCHEMA IF NOT EXISTS custom_schema;
// CREATE TABLE custom_schema.products (id SERIAL PRIMARY KEY, name VARCHAR(255) NOT NULL);

runMigrations();