Postgres Migrations

Common errors

• ERROR: Migration file 'X_my-migration.sql' has been modified since it was applied. Aborting.

  cause A migration SQL file that was previously successfully applied to the database has been altered on the filesystem.
  fix Revert the changes to the migration file `X_my-migration.sql` to its original state, or ensure no applied migration files are modified. All subsequent changes should be in new migration files.

• ERROR: Found multiple migration files with the same sequence number: X_migration1.sql, X_migration2.sql. Please resolve this conflict.

  cause Two or more migration files in the specified directory share the same numerical prefix, leading to an ambiguous execution order.
  fix Rename one of the conflicting migration files (e.g., `X_migration2.sql` to `Y_migration2.sql` where `Y` is the next available sequence number) to ensure all migration files have unique, sequential prefixes.

• TypeError: client.connect is not a function

  cause An invalid `pg` client object (e.g., a raw configuration object or an uninstantiated `pg` class) was passed where a connected client or pool was expected.
  fix Ensure you are passing an instantiated `pg.Client`, `pg.Pool`, or `pg.PoolClient` instance that has already called `.connect()` (for `Client`) or is ready for use (for `Pool`). Alternatively, pass the database connection config directly.

Warnings

• breaking This library intentionally does not support 'down' migrations or rollbacks. The philosophy is to 'roll forward' by applying new migrations to correct any issues, which may be a significant paradigm shift for users accustomed to rollback mechanisms.
  Fix: Plan for all schema changes to be additive or corrective via new migration files, rather than relying on reversions.
• gotcha Modifying migration files that have already been applied to a database will cause the migration process to fail due to hash mismatches. The library performs integrity checks to ensure that previously run migrations remain unchanged.
  Fix: Never alter migration files after they have been committed and applied to any environment. Create a new migration file to introduce any necessary changes or corrections.
• gotcha If two migration files are created with the same sequential prefix (e.g., `5_add_column_a.sql` and `5_add_column_b.sql`), the migration process will detect this conflict and refuse to proceed. This is to prevent inconsistent migration ordering.
  Fix: Ensure all migration files have unique, sequential prefixes. Use `loadMigrationFiles` or the `pg-validate-migrations` bin script to check for conflicts early in development.
• gotcha The `ensureDatabaseExists` option, which defaults to `false`, might change its default behavior in future major versions. If `false`, the database must exist before migrations can run.
  Fix: Explicitly set `ensureDatabaseExists: true` if you want the library to create the target database for you, or ensure the database exists manually before running migrations.
• gotcha The library requires Node.js version 10.17.0 or higher and PostgreSQL version 9.4 or higher. Older environments are not supported and may lead to unexpected behavior or errors.
  Fix: Upgrade your Node.js runtime and PostgreSQL database to meet the minimum version requirements.

Install

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

Imports

• migrate
  wrong:

  const { migrate } = require('postgres-migrations')

  correct:

  import { migrate } from 'postgres-migrations'

  Primary function to apply migrations. Accepts either a `pg` client/pool instance or a database connection config object.
• loadMigrationFiles
  wrong:

  const { loadMigrationFiles } = require('postgres-migrations')

  correct:

  import { loadMigrationFiles } from 'postgres-migrations'

  Utility to programmatically validate migration files for conflicts before application.
• pg.Client
  wrong:

  const { Client } = require('pg')

  correct:

  import { Client } from 'pg'

  While not part of `postgres-migrations`, the `pg` client or pool is often used in conjunction with this library.

Quickstart

Demonstrates how to apply migrations using an existing `pg.Client` instance, ensuring proper connection handling and error reporting. It uses environment variables for database credentials.

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

async function runMigrations() {
  const dbConfig = {
    database: process.env.DB_NAME ?? 'your_database',
    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),
    // ensureDatabaseExists: true // Uncomment if you want the library to create the database if it doesn't exist
  };

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

  try {
    console.log('Starting database migrations...');
    const migrationsPath = path.resolve(__dirname, 'migrations');
    await migrate({ client }, migrationsPath);
    console.log('Database migrations completed successfully.');
  } catch (error) {
    console.error('Migration failed:', error);
    process.exit(1);
  } finally {
    await client.end();
  }
}

// Example migrations directory structure:
// my-project/
// ├── src/
// │   └── index.ts (or .js)
// └── migrations/
//     ├── 1_create_users_table.sql
//     └── 2_add_posts_table.sql

runMigrations();