MigrateDB 🚀

Powerful and secure database migration tool for Node.js with support for PostgreSQL, MySQL, MariaDB, and SQLite. MigrateDB provides a robust, feature-rich solution for managing database schema changes with rollback support, transactions, dependency management, and much more.

✨ Features

Core Features

• ✅ Multi-Database Support - PostgreSQL, MySQL/MariaDB, SQLite
• ✅ Rollback Support - Safe migration rollbacks with down scripts
• ✅ Transaction Support - Automatic transaction wrapping with rollback on failure
• ✅ Migration Locking - Prevents concurrent migration runs
• ✅ Checksum Verification - Ensures migration integrity
• ✅ CLI Tool - Full-featured command-line interface
• ✅ Status Tracking - View applied and pending migrations
• ✅ Dry-Run Mode - Preview migrations without executing

Advanced Features

• 🔄 Migration Dependencies - Declare and resolve migration dependencies
• 🌍 Environment-Specific Migrations - Filter migrations by environment (dev/staging/prod)
• 📝 Configuration Files - JSON/YAML configuration support
• ✅ Validation - Validate migration files for common issues
• 📊 Audit Trail - Track migration execution with timestamps
• 🎯 Type Safety - Full TypeScript support with no any types

📦 Installation

npm install @ujjwaljain16/migratedb

Peer Dependencies

Install the appropriate database driver:

# For PostgreSQL
npm install pg

# For MySQL/MariaDB
npm install mysql2

# For SQLite
npm install better-sqlite3

🚀 Quick Start

1. Create Migration Files

Create a migrations directory and add your SQL files:

mkdir migrations

-- migrations/001__create_users.sql
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- DOWN
DROP TABLE IF EXISTS users;

2. Run Migrations

Using CLI:

migratedb migrate --connection postgres://user:pass@localhost/dbname

Using Code:

import { migratedb } from '@ujjwaljain16/migratedb'

await migratedb.migrate('postgres://user:pass@localhost/dbname')

3. Check Status

migratedb status --connection postgres://user:pass@localhost/dbname

🔧 Configuration

Configuration File

Create migratedb.config.json:

{
  "migrationsDir": "./migrations",
  "verifyChecksum": true,
  "useTransactions": true,
  "enableLocking": true,
  "logLevel": "info",
  "environments": {
    "dev": {
      "connectionString": "postgres://user:pass@localhost/dev_db"
    },
    "prod": {
      "connectionString": "postgres://user:pass@localhost/prod_db",
      "verifyChecksum": true,
      "useTransactions": true
    }
  }
}

Or use YAML (migratedb.config.yaml):

migrationsDir: ./migrations
verifyChecksum: true
useTransactions: true
enableLocking: true
logLevel: info
environments:
  dev:
    connectionString: postgres://user:pass@localhost/dev_db
  prod:
    connectionString: postgres://user:pass@localhost/prod_db
    verifyChecksum: true

Environment Variables

export DATABASE_URL="postgres://user:pass@localhost/dbname"
export MIGRATEDB_ENV="production"

🎯 Advanced Features

Migration Dependencies

Declare dependencies in your migration files:

-- migrations/002__create_posts.sql
-- DEPENDS: 001__create_users.sql

CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id),
    title VARCHAR(255) NOT NULL,
    content TEXT
);

Environment-Specific Migrations

Tag migrations for specific environments:

-- migrations/003__dev_data.sql
-- ENV: dev

INSERT INTO users (name, email) VALUES 
    ('Dev User', '[email protected]');

Down Migrations

Always include rollback scripts:

-- migrations/001__create_users.sql
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL
);

-- DOWN
DROP TABLE IF EXISTS users;

🗄️ Database Support

PostgreSQL

const connectionString = 'postgres://user:pass@localhost/dbname'

MySQL/MariaDB

const connectionString = 'mysql://user:pass@localhost/dbname'
// or
const connectionString = 'mysql2://user:pass@localhost/dbname'

SQLite

const connectionString = 'sqlite://./database.db'
// or
const connectionString = './database.db'

📚 API Reference

MigrateDB Class

class MigrateDB {
  async migrate(
    connectionUrl: string,
    config?: MigrateDBConfig
  ): Promise<MigrationResult>
  
  async rollback(
    connectionUrl: string,
    targetMigration?: string,
    config?: MigrateDBConfig
  ): Promise<MigrationResult>
  
  async status(
    connectionUrl: string,
    config?: MigrateDBConfig
  ): Promise<StatusResult>
}

Configuration Options

interface MigrateDBConfig {
  migrationsDir?: string
  verifyChecksum?: boolean
  useTransactions?: boolean
  enableLocking?: boolean
  logLevel?: 'debug' | 'info' | 'warn' | 'error'
  dryRun?: boolean
  environment?: string
}

🧪 Testing

Run tests:

npm test

📝 Migration File Format

Naming Convention

• Use timestamp prefix: YYYYMMDDHHMMSS__description.sql
• Or: 001__description.sql, 002__description.sql, etc.

File Structure

-- Optional: Migration metadata
-- DEPENDS: 001__other_migration.sql
-- ENV: dev, staging

-- UP Migration
CREATE TABLE example (
    id SERIAL PRIMARY KEY
);

-- DOWN Migration
-- DOWN
DROP TABLE IF EXISTS example;

🔒 Security

• Checksum Verification: Prevents tampering with applied migrations
• Transaction Safety: Automatic rollback on failure
• Migration Locking: Prevents concurrent execution
• Validation: Checks for common SQL issues

📄 License

Apache-2.0 License

Made with ❤️ for the Node.js community