Node DB Migration

Common errors

• Error: Cannot find module 'pg'

  cause The PostgreSQL client library ('pg') is not installed in your project.
  fix Install the 'pg' package: `npm install pg`

• Error: ER_PARSE_ERROR: You have an error in your SQL syntax...

  cause One of your `.sql` migration files contains invalid SQL syntax.
  fix Carefully review the SQL code in the indicated migration file for syntax errors specific to your database (e.g., MySQL, PostgreSQL, SQLite).

• Error: SQLITE_ERROR: no such table: migrations

  cause The `migrations` table (or your custom `migrationTable` name) does not exist in the database, meaning the `init` command was not run or failed.
  fix Execute the `init` command to create the migration tracking table: `migrations.run('init');`

Warnings

• gotcha Migration SQL files must strictly adhere to the `YYYYMMDDHHmm-name.sql` naming convention (e.g., `201705231245-add-pets-table.sql`). Incorrect naming will prevent files from being recognized and executed.
  Fix: Ensure all `.sql` migration files in the `directoryWithScripts` follow the `date-name.sql` pattern, where the date is in `YYYYMMDDHHmm` format.
• breaking If a migration script fails, `node-db-migration` will record the failure in the `migrations` table and *stop* further migrations until the failed entry is manually resolved. This is a design choice to ensure database consistency.
  Fix: Use the `resolve` command (`migrations.run('resolve')`) after fixing the SQL error, or manually update the `migrations` table to clear the failed status for the specific script, then rerun the `migrate` command.
• gotcha When using MySQL, if your migration scripts contain multiple SQL statements separated by semicolons, you *must* configure your `mysql` or `mysql2` connection with `multipleStatements: true`. Otherwise, only the first statement will execute.
  Fix: When creating your MySQL connection, add `{ multipleStatements: true }` to the connection options: `mysql.createConnection({ ..., multipleStatements: true });`
• gotcha The database client (e.g., `pg`, `mysql`, `sqlite3`) must be separately installed as a dependency in your project, as `node-db-migration` only provides the drivers, not the underlying database connectors.
  Fix: Install the appropriate database client: `npm install pg`, `npm install mysql` (or `mysql2`), or `npm install sqlite3`.

Install

• npm install node-db-migration npm
• yarn add node-db-migration yarn
• pnpm add node-db-migration pnpm

Imports

• CommandsRunner
  wrong:

  const CommandsRunner = require('node-db-migration').CommandsRunner;

  correct:

  import { CommandsRunner } from 'node-db-migration';

  Primary class for orchestrating migrations. CommonJS users should destructure from `require`.
• MysqlDriver
  wrong:

  import MysqlDriver from 'node-db-migration';

  correct:

  import { MysqlDriver } from 'node-db-migration';

  Specific driver for MySQL connections. Named import is required. Corresponding database client ('mysql' or 'mysql2') must be installed.
• PsqlDriver
  wrong:

  let PsqlDriver = require('node-db-migration');

  correct:

  import { PsqlDriver } from 'node-db-migration';

  Specific driver for PostgreSQL connections. Named import is required. The 'pg' client must be installed.
• SQLite3Driver

  import { SQLite3Driver } from 'node-db-migration';

  Specific driver for SQLite3 connections. Named import is required. The 'sqlite3' client must be installed.

Quickstart

This quickstart demonstrates how to set up and run database migrations using `node-db-migration` with a PostgreSQL database. It connects to a database, initializes the `CommandsRunner` with a `PsqlDriver` and a directory for SQL migration scripts, then executes the `migrate` command to apply pending changes.

import { CommandsRunner, PsqlDriver } from 'node-db-migration';
import { Client } from 'pg';
import path from 'path';
import { fileURLToPath } from 'url';

const __filename = fileURLToPath(import.meta.url);
const __dirname = path.dirname(__filename);

async function runMigrations() {
    const client = new Client({
        connectionString: process.env.DATABASE_URL ?? 'postgresql://postgres:@localhost:5432/test_db',
    });

    try {
        await client.connect();
        console.log('Connected to PostgreSQL database.');

        const migrations = new CommandsRunner({
            driver: new PsqlDriver(client),
            directoryWithScripts: path.join(__dirname, 'migrations'), // Ensure 'migrations' directory exists with .sql files
            migrationTable: 'my_app_migrations' // Optional: custom table name for migration tracking
        });

        // Example: Create a dummy migration directory and file for demonstration
        // In a real app, these would be pre-existing.
        const fs = await import('fs/promises');
        const migrationsDir = path.join(__dirname, 'migrations');
        await fs.mkdir(migrationsDir, { recursive: true });
        const migrationFileName = `${new Date().toISOString().replace(/[-:.]/g, '').substring(0, 14)}-create-users-table.sql`;
        await fs.writeFile(path.join(migrationsDir, migrationFileName),
            'CREATE TABLE IF NOT EXISTS users (id SERIAL PRIMARY KEY, name VARCHAR(255) NOT NULL, email VARCHAR(255) UNIQUE);');

        console.log('Running `migrate` command...');
        await migrations.run('migrate');
        console.log('Migrations completed successfully.');

        // You can run other commands like 'list', 'getFailed', 'resolve'
        // console.log('\nListing unapplied migrations:');
        // await migrations.run('list');

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

runMigrations();