{"id":17306,"library":"node-db-migration","title":"Node DB Migration","description":"node-db-migration is a focused Node.js library designed for managing database schema evolution through simple SQL-based migration scripts. Currently at version 1.4.0, it offers robust support for popular relational databases including SQLite3, MySQL (or its modern fork `mysql2`), and PostgreSQL. Its core philosophy emphasizes using bare SQL files, allowing developers to maintain direct control over their database schemas without being tied to an ORM's migration DSL. The package operates by maintaining a dedicated `migrations` table within the database to track which scripts have been applied. It scans a specified directory for `.sql` files, enforcing a strict `YYYYMMDDHHmm-name.sql` naming convention to ensure chronological execution. Key features include sequential script execution, robust tracking of successful and failed migrations, and the ability to prevent further migrations upon failure until manual intervention. This helps ensure data consistency and provides a clear audit trail of schema changes. While its release cadence isn't rapid, it offers a stable and reliable solution for teams preferring a 'SQL-first' approach to database version control.","status":"active","version":"1.4.0","language":"javascript","source_language":"en","source_url":"https://github.com/akoidan/node-db-migration","tags":["javascript","db","migration","typescript"],"install":[{"cmd":"npm install node-db-migration","lang":"bash","label":"npm"},{"cmd":"yarn add node-db-migration","lang":"bash","label":"yarn"},{"cmd":"pnpm add node-db-migration","lang":"bash","label":"pnpm"}],"dependencies":[{"reason":"Required for MySQL database connections. 'mysql2' can also be used.","package":"mysql","optional":true},{"reason":"Required for PostgreSQL database connections.","package":"pg","optional":true},{"reason":"Required for SQLite database connections.","package":"sqlite3","optional":true}],"imports":[{"note":"Primary class for orchestrating migrations. CommonJS users should destructure from `require`.","wrong":"const CommandsRunner = require('node-db-migration').CommandsRunner;","symbol":"CommandsRunner","correct":"import { CommandsRunner } from 'node-db-migration';"},{"note":"Specific driver for MySQL connections. Named import is required. Corresponding database client ('mysql' or 'mysql2') must be installed.","wrong":"import MysqlDriver from 'node-db-migration';","symbol":"MysqlDriver","correct":"import { MysqlDriver } from 'node-db-migration';"},{"note":"Specific driver for PostgreSQL connections. Named import is required. The 'pg' client must be installed.","wrong":"let PsqlDriver = require('node-db-migration');","symbol":"PsqlDriver","correct":"import { PsqlDriver } from 'node-db-migration';"},{"note":"Specific driver for SQLite3 connections. Named import is required. The 'sqlite3' client must be installed.","symbol":"SQLite3Driver","correct":"import { SQLite3Driver } from 'node-db-migration';"}],"quickstart":{"code":"import { CommandsRunner, PsqlDriver } from 'node-db-migration';\nimport { Client } from 'pg';\nimport path from 'path';\nimport { fileURLToPath } from 'url';\n\nconst __filename = fileURLToPath(import.meta.url);\nconst __dirname = path.dirname(__filename);\n\nasync function runMigrations() {\n    const client = new Client({\n        connectionString: process.env.DATABASE_URL ?? 'postgresql://postgres:@localhost:5432/test_db',\n    });\n\n    try {\n        await client.connect();\n        console.log('Connected to PostgreSQL database.');\n\n        const migrations = new CommandsRunner({\n            driver: new PsqlDriver(client),\n            directoryWithScripts: path.join(__dirname, 'migrations'), // Ensure 'migrations' directory exists with .sql files\n            migrationTable: 'my_app_migrations' // Optional: custom table name for migration tracking\n        });\n\n        // Example: Create a dummy migration directory and file for demonstration\n        // In a real app, these would be pre-existing.\n        const fs = await import('fs/promises');\n        const migrationsDir = path.join(__dirname, 'migrations');\n        await fs.mkdir(migrationsDir, { recursive: true });\n        const migrationFileName = `${new Date().toISOString().replace(/[-:.]/g, '').substring(0, 14)}-create-users-table.sql`;\n        await fs.writeFile(path.join(migrationsDir, migrationFileName),\n            'CREATE TABLE IF NOT EXISTS users (id SERIAL PRIMARY KEY, name VARCHAR(255) NOT NULL, email VARCHAR(255) UNIQUE);');\n\n        console.log('Running `migrate` command...');\n        await migrations.run('migrate');\n        console.log('Migrations completed successfully.');\n\n        // You can run other commands like 'list', 'getFailed', 'resolve'\n        // console.log('\\nListing unapplied migrations:');\n        // await migrations.run('list');\n\n    } catch (error) {\n        console.error('Migration failed:', error);\n        process.exit(1);\n    } finally {\n        await client.end();\n        console.log('Database connection closed.');\n    }\n}\n\nrunMigrations();","lang":"typescript","description":"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."},"warnings":[{"fix":"Ensure all `.sql` migration files in the `directoryWithScripts` follow the `date-name.sql` pattern, where the date is in `YYYYMMDDHHmm` format.","message":"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.","severity":"gotcha","affected_versions":">=1.0.0"},{"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.","message":"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.","severity":"breaking","affected_versions":">=1.0.0"},{"fix":"When creating your MySQL connection, add `{ multipleStatements: true }` to the connection options: `mysql.createConnection({ ..., multipleStatements: true });`","message":"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.","severity":"gotcha","affected_versions":">=1.0.0"},{"fix":"Install the appropriate database client: `npm install pg`, `npm install mysql` (or `mysql2`), or `npm install sqlite3`.","message":"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.","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":"Install the 'pg' package: `npm install pg`","cause":"The PostgreSQL client library ('pg') is not installed in your project.","error":"Error: Cannot find module 'pg'"},{"fix":"Carefully review the SQL code in the indicated migration file for syntax errors specific to your database (e.g., MySQL, PostgreSQL, SQLite).","cause":"One of your `.sql` migration files contains invalid SQL syntax.","error":"Error: ER_PARSE_ERROR: You have an error in your SQL syntax..."},{"fix":"Execute the `init` command to create the migration tracking table: `migrations.run('init');`","cause":"The `migrations` table (or your custom `migrationTable` name) does not exist in the database, meaning the `init` command was not run or failed.","error":"Error: SQLITE_ERROR: no such table: migrations"}],"ecosystem":"npm","meta_description":null}