{"id":16493,"library":"postgres-migrations","title":"Postgres Migrations","description":"The `postgres-migrations` package provides a robust database migration solution for PostgreSQL, directly inspired by Stack Overflow's deployment methodology. Currently at version 5.3.0, it offers a stable and opinionated approach to managing database schema changes. Key differentiators include its strict sequential SQL file-based ordering, which explicitly avoids timestamp-based naming to ensure consistent execution order across all environments. The library deliberately eschews 'down' migrations, advocating for a 'roll forward' philosophy where issues are addressed by applying new incremental migrations. It enforces database integrity by performing hash checks on previously applied migration files, preventing accidental or unauthorized modifications. The package maintains a hidden `migrations` table to track applied scripts and requires Node.js 10.17.0+ and PostgreSQL 9.4+.","status":"active","version":"5.3.0","language":"javascript","source_language":"en","source_url":"ssh://git@github.com/thomwright/postgres-migrations","tags":["javascript","postgres","postgresql","migration","migrations","sql","database","db","typescript"],"install":[{"cmd":"npm install postgres-migrations","lang":"bash","label":"npm"},{"cmd":"yarn add postgres-migrations","lang":"bash","label":"yarn"},{"cmd":"pnpm add postgres-migrations","lang":"bash","label":"pnpm"}],"dependencies":[{"reason":"Used for database connectivity; can be passed directly to the `migrate` function.","package":"pg","optional":false}],"imports":[{"note":"Primary function to apply migrations. Accepts either a `pg` client/pool instance or a database connection config object.","wrong":"const { migrate } = require('postgres-migrations')","symbol":"migrate","correct":"import { migrate } from 'postgres-migrations'"},{"note":"Utility to programmatically validate migration files for conflicts before application.","wrong":"const { loadMigrationFiles } = require('postgres-migrations')","symbol":"loadMigrationFiles","correct":"import { loadMigrationFiles } from 'postgres-migrations'"},{"note":"While not part of `postgres-migrations`, the `pg` client or pool is often used in conjunction with this library.","wrong":"const { Client } = require('pg')","symbol":"pg.Client","correct":"import { Client } from 'pg'"}],"quickstart":{"code":"import { migrate } from 'postgres-migrations';\nimport { Client } from 'pg';\nimport path from 'path';\n\nasync function runMigrations() {\n  const dbConfig = {\n    database: process.env.DB_NAME ?? 'your_database',\n    user: process.env.DB_USER ?? 'postgres',\n    password: process.env.DB_PASSWORD ?? 'password',\n    host: process.env.DB_HOST ?? 'localhost',\n    port: parseInt(process.env.DB_PORT ?? '5432', 10),\n    // ensureDatabaseExists: true // Uncomment if you want the library to create the database if it doesn't exist\n  };\n\n  const client = new Client(dbConfig);\n  await client.connect();\n\n  try {\n    console.log('Starting database migrations...');\n    const migrationsPath = path.resolve(__dirname, 'migrations');\n    await migrate({ client }, migrationsPath);\n    console.log('Database migrations completed successfully.');\n  } catch (error) {\n    console.error('Migration failed:', error);\n    process.exit(1);\n  } finally {\n    await client.end();\n  }\n}\n\n// Example migrations directory structure:\n// my-project/\n// ├── src/\n// │   └── index.ts (or .js)\n// └── migrations/\n//     ├── 1_create_users_table.sql\n//     └── 2_add_posts_table.sql\n\nrunMigrations();","lang":"typescript","description":"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."},"warnings":[{"fix":"Plan for all schema changes to be additive or corrective via new migration files, rather than relying on reversions.","message":"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.","severity":"breaking","affected_versions":">=1.0.0"},{"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.","message":"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.","severity":"gotcha","affected_versions":">=1.0.0"},{"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.","message":"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.","severity":"gotcha","affected_versions":">=1.0.0"},{"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.","message":"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.","severity":"gotcha","affected_versions":">=1.0.0"},{"fix":"Upgrade your Node.js runtime and PostgreSQL database to meet the minimum version requirements.","message":"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.","severity":"gotcha","affected_versions":">=1.0.0"}],"env_vars":null,"last_verified":"2026-04-22T00:00:00.000Z","next_check":"2026-07-21T00:00:00.000Z","problems":[{"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.","cause":"A migration SQL file that was previously successfully applied to the database has been altered on the filesystem.","error":"ERROR: Migration file 'X_my-migration.sql' has been modified since it was applied. Aborting."},{"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.","cause":"Two or more migration files in the specified directory share the same numerical prefix, leading to an ambiguous execution order.","error":"ERROR: Found multiple migration files with the same sequence number: X_migration1.sql, X_migration2.sql. Please resolve this conflict."},{"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.","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.","error":"TypeError: client.connect is not a function"}],"ecosystem":"npm"}