DB Migrator

2.4.0 · active · verified Wed Apr 22

db-migrator is a complete and easy-to-use database migration tool for Node.js projects, offering robust support for both PostgreSQL and MySQL databases. The current stable version is 2.4.0, and based on its active GitHub repository, it appears to maintain an ongoing, though not explicitly defined, release cadence. A key differentiator is its comprehensive feature set, including automatic migration from scratch to the latest version, granular step-by-step forward and backward migration capabilities, and the ability to migrate to a specific database version. It supports deep searching for migration scripts within subfolders and ensures data integrity through transactional "all or nothing" execution, where failures result in a full rollback. This tool builds upon its `pg-migrator` predecessor by introducing timestamp-based version IDs, storing execution times, allowing descriptive migration file names, favoring `npm` scripts for execution, and crucially, adding support for MySQL. It mandates Node.js v7.6.0 or higher due to its reliance on `async/await` syntax in its codebase.

Common errors

```
db-migrate: command not found
```
cause The `db-migrate` command-line utility is not directly available in your shell's PATH, or the npm scripts are not correctly set up.

fix Ensure you have `db-migrator` installed locally (`npm install db-migrator`) and are running the commands through your `package.json` scripts, e.g., `npm run db-migrate`.
```
Error: Cannot find module 'pg' (or 'promise-mysql')
```
cause The required database driver (PostgreSQL's `pg` or MySQL's `promise-mysql`) was not installed.

fix Install the appropriate database driver for your chosen database: `npm install pg --save` for PostgreSQL, or `npm install promise-mysql --save` for MySQL.
```
error: database "your_database_name" does not exist
```
cause The database specified in the `db_migrator_db_url` configuration does not exist on the database server.

fix Manually create the database (e.g., `createdb your_database_name` for PostgreSQL, or `CREATE DATABASE your_database_name;` for MySQL) before attempting to run migrations.
```
ERROR: permission denied for relation some_table (PostgreSQL) / Access denied for user 'user'@'host' to database 'db' (MySQL)
```
cause The database user configured in your `db_migrator_db_url` lacks the necessary permissions to perform an operation defined in one of your migration scripts.

fix Review and update the permissions granted to the database user. Ensure the user has sufficient privileges for schema modifications and data operations on the target database.

Warnings

breaking db-migrator requires Node.js v7.6.0 or higher due to its use of `async/await` features. Older Node.js versions are not supported.
Fix: Upgrade your Node.js runtime environment to version 7.6.0 or later.
gotcha All migration scripts are executed within a single transaction scope. Explicit `BEGIN`, `COMMIT`, or `ROLLBACK` statements within your SQL migration files will interfere with `db-migrator`'s transactional behavior and should be avoided.
Fix: Remove all transaction control statements (e.g., `BEGIN`, `COMMIT`, `ROLLBACK`) from your migration SQL scripts.
gotcha PostgreSQL ENUM types cannot be altered directly within a transaction, which is how `db-migrator` executes scripts. Attempting to add values to an existing ENUM type will typically result in a transaction-related error.
Fix: To alter a PostgreSQL ENUM type, a common workaround involves changing the column's type to `VARCHAR`, recreating the ENUM with the new values, and then changing the column's type back to use the updated ENUM. This sequence must be handled carefully within your migration script.
gotcha The database user specified in your `db_migrator_db_url` connection string must have sufficient permissions to execute all operations defined in your migration scripts. Insufficient privileges will lead to migration failures.
Fix: Grant the necessary database privileges (e.g., `CREATE`, `ALTER`, `DROP` for schema changes, `SELECT`, `INSERT`, `UPDATE`, `DELETE` for data manipulation) to the database user configured for `db-migrator`.

Install

npm install db-migrator npm
yarn add db-migrator yarn
pnpm add db-migrator pnpm

Quickstart

This quickstart demonstrates how to integrate db-migrator into your project using npm scripts, configure your database connection via .npmrc, and use the primary CLI commands for creating, migrating, rolling back, and checking migration status.

{
  "scripts": {
    "db-migrate": "db-migrate",
    "db-rollback": "db-rollback",
    "db-create": "db-create",
    "db-status": "db-status"
  }
}

// .npmrc configuration for database connection
// For PostgreSQL:
db_migrator_db_url=postgresql://mydatabase@localhost?ssl=false
// For MySQL:
db_migrator_db_url=mysql://user:pass@host/db

// Create a directory for your migration files (default is 'migrations')
// mkdir migrations

// Example usage via npm scripts:
// To generate new migration files:
// npm run db-create "add_users_table"

// To apply all pending migrations:
// npm run db-migrate

// To rollback the most recent migration:
// npm run db-rollback

// To view the current migration status:
// npm run db-status

view raw JSON →