postgrator

Postgrator uses a directory of SQL or JavaScript files to manage database changes. Files must follow a specific naming convention:

[version].[action].[optional-description].[extension]

Components:

• Version: A number (e.g., 001, 202310271200). You can use any incrementing scheme.
• Action: Must be either do (to apply the migration) or undo (to revert it). Writing undo scripts is optional.
• Optional-description: A label or tag to describe the script. Do not use periods in the description.
• Extension: .sql, .js, .mjs, or .cjs.

Supported File Types:

• SQL: Plain SQL scripts.
• JavaScript: Modules that export a generateSql() function. This is useful for using environment variables or performing asynchronous tasks (e.g., fetching data from an API) to generate the SQL string. Note that JS migrations are not checksum validated.

migrations/
  |- 001.do.sql
  |- 001.undo.sql
  |- 002.do.optional-description.sql
  |- 002.undo.optional-description.sql
  |- 004.do.js
  |- 004.undo.js

To run migrations, instantiate Postgrator with a configuration object and call the .migrate() method. Postgrator automatically determines whether to go 'up' (apply) or 'down' (undo) based on the current database version.

Basic Usage Example

import Postgrator from "postgrator";
import pg from "pg";

const client = new pg.Client({
  host: "localhost",
  database: "postgrator",
  user: "postgrator",
  password: "postgrator",
});

async function main() {
  await client.connect();

  const postgrator = new Postgrator({
    migrationPattern: "./migrations/*",
    driver: "pg",
    database: "postgrator",
    schemaTable: "schemaversion",
    execQuery: (query) => client.query(query),
    execSqlScript: (sqlScript) => client.sqlScript(sqlScript),
  });

  try {
    // Migrate to a specific version
    const appliedMigrations = await postgrator.migrate("002");
    console.log(appliedMigrations);

    // Or migrate to the maximum available version
    await postgrator.migrate();
  } catch (error) {
    // If an error occurs, error.appliedMigrations contains the successful migrations
    console.error(error.appliedMigrations);
  } finally {
    await client.end();
  }
}

main();

Postgrator is an EventEmitter. You can hook into its lifecycle to implement custom logging. Note that there are no specific events for error or finish.

Available events:

• validation-started
• validation-finished
• migration-started
• migration-finished

const postgrator = new Postgrator(options);

postgrator.on("validation-started", (migration) => console.log("Validation started:", migration));
postgrator.on("validation-finished", (migration) => console.log("Validation finished:", migration));
postgrator.on("migration-started", (migration) => console.log("Migration started:", migration));
postgrator.on("migration-finished", (migration) => console.log("Migration finished:", migration));

const postgrator = new Postgrator(options);
postgrator.on("validation-started", (migration) => console.log(migration));
postgrator.on("validation-finished", (migration) => console.log(migration));
postgrator.on("migration-started", (migration) => console.log(migration));
postgrator.on("migration-finished", (migration) => console.log(migration));

When instantiating a Client, the config object controls how queries are executed and how the migration table is identified.

Required/Supported keys:

• driver: The database driver being used (e.g., 'pg' for PostgreSQL, 'sqlite3' for SQLite). This affects how table names are quoted and how schemas are handled.
• schemaTable: The name (or fully qualified name) of the table used to track migration versions. For the pg driver, if this contains a dot (e.g., "my_schema"."migrations"), the client will attempt to create the schema if it doesn't exist.
• currentSchema: (PostgreSQL only) If provided, the client executes SET search_path = <currentSchema> before running queries.
• execQuery: A function used to execute single SQL queries. It is the primary way the client communicates with the database.
• execSqlScript: (Optional) A function used to execute multi-line SQL scripts. If not provided, the client falls back to using execQuery for scripts.

If postgrator.migrate() fails while running a batch of migrations, Postgrator stops immediately. Any migrations that were successfully completed before the failure will remain applied in the database.

Accessing applied migrations on error

When an error occurs, the error object is decorated with an appliedMigrations property, which is an array of the migration objects that succeeded before the failure.

try {
  await postgrator.migrate();
} catch (error) {
  // error.appliedMigrations contains the successful migrations
  console.error("Successfully applied before failure:", error.appliedMigrations);
}

Best Practices for Preventing Partial Migrations

• Transactions: Wrap your SQL in a transaction or BEGIN/END block.
  • Postgres/SQL Server: Multiple statements in one execution are often treated as a single implicit transaction.
  • MySQL: Does not automatically wrap multiple statements in a transaction; it may implement up to the point of failure.
• SQL Server specific: Do not use the GO keyword in a single migration file. Instead, split statements separated by GO into separate migration files to avoid partial implementations without a record.",

When creating a new Postgrator instance, you must provide several core options.

The Client class is the base class used to interact with the database for managing migrations. It requires a config object in its constructor. The config object must provide a driver (e.g., 'pg' or 'sqlite3'), a schemaTable string representing the migration tracking table, and an execQuery function to execute SQL queries.

Key capabilities include:

• ensureTable(): Checks for the existence of the migration tracking table and creates/updates it with necessary columns (version, name, md5, run_at) if they are missing.
• runQuery(query): Executes a single SQL query. If using the pg driver and a currentSchema is configured, it automatically sets the search_path before running the query.
• runSqlScript(sqlScript): Executes a multi-line SQL script. If config.execSqlScript is provided, it uses that; otherwise, it falls back to runQuery.
• hasVersionTable(): Returns a boolean indicating if the migration tracking table exists.

import Client from './lib/Client.js';

const config = {
  driver: 'pg',
  schemaTable: 'migrations',
  currentSchema: 'public',
  execQuery: async (sql) => { /* implementation to run query */ },
  execSqlScript: async (sql) => { /* implementation to run script */ }
};

const client = new Client(config);
await client.ensureTable();
await client.runQuery('SELECT 1;');