node-checkpoint

A lightweight, Rails-like SQL migration runner for PostgreSQL and MySQL.

Features

• Simple, timestamp-based migration files
• Support for PostgreSQL and MySQL
• Rails-like CLI commands
• Transaction support per migration
• Zero configuration required (with sensible defaults)
• ES modules support

Installation

npm install node-checkpoint

Quick Start

1. Initialize checkpoint in your project

npx checkpoint init

This creates:

• A migration.config.js configuration file
• A migrations/ directory
• The migrations tracking table in your database

2. Create your first migration

npx checkpoint create create_users_table

This generates a timestamped migration file like 20250130120000_create_users_table.js:

/**
 * Migration: create_users_table
 * Created: 2025-01-30T12:00:00.000Z
 */

export async function up(adapter) {
  // Write your migration here
  await adapter.execute(`
    CREATE TABLE users (
      id SERIAL PRIMARY KEY,
      name VARCHAR(255) NOT NULL,
      email VARCHAR(255) UNIQUE NOT NULL,
      created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    )
  `);
}

export async function down(adapter) {
  // Write your rollback here
  await adapter.execute('DROP TABLE users');
}

3. Run migrations

npx checkpoint up

4. Check migration status

npx checkpoint status

5. Rollback the last migration

npx checkpoint down

Configuration

The migration.config.js file supports environment variables:

export default {
  database: {
    type: process.env.DB_TYPE || 'postgres', // 'postgres' or 'mysql'
    host: process.env.DB_HOST || 'localhost',
    port: process.env.DB_PORT || 5432,
    user: process.env.DB_USER || 'root',
    password: process.env.DB_PASSWORD || '',
    database: process.env.DB_NAME || 'mydb',
  },
  migrationsDir: process.env.MIGRATIONS_DIR || './migrations',
  tableName: 'schema_migrations',
};

You can use a .env file with the dotenv package:

DB_TYPE=postgres
DB_HOST=localhost
DB_PORT=5432
DB_USER=myuser
DB_PASSWORD=mypassword
DB_NAME=mydb
MIGRATIONS_DIR=./migrations

CLI Commands

Command	Description
checkpoint init	Initialize checkpoint in the current directory
checkpoint up	Run all pending migrations
checkpoint down	Rollback the last migration
checkpoint status	Show migration status (executed and pending)
checkpoint create <name>	Create a new migration file

Migration Files

Migration files must export two functions:

• up(adapter): Applies the migration
• down(adapter): Reverts the migration

The adapter parameter provides an execute(sql) method to run SQL commands.

PostgreSQL Example

export async function up(adapter) {
  await adapter.execute(`
    CREATE TABLE products (
      id SERIAL PRIMARY KEY,
      name VARCHAR(255) NOT NULL,
      price DECIMAL(10, 2) NOT NULL,
      created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    )
  `);

  await adapter.execute(`
    CREATE INDEX idx_products_name ON products(name)
  `);
}

export async function down(adapter) {
  await adapter.execute('DROP TABLE products');
}

MySQL Example

export async function up(adapter) {
  await adapter.execute(`
    CREATE TABLE products (
      id INT AUTO_INCREMENT PRIMARY KEY,
      name VARCHAR(255) NOT NULL,
      price DECIMAL(10, 2) NOT NULL,
      created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    )
  `);

  await adapter.execute(`
    CREATE INDEX idx_products_name ON products(name)
  `);
}

export async function down(adapter) {
  await adapter.execute('DROP TABLE products');
}

Programmatic Usage

You can also use checkpoint programmatically in your Node.js code:

import { Migrator } from 'node-checkpoint';

const config = {
  database: {
    type: 'postgres',
    host: 'localhost',
    port: 5432,
    user: 'myuser',
    password: 'mypassword',
    database: 'mydb',
  },
  migrationsDir: './migrations',
  tableName: 'schema_migrations',
};

const migrator = new Migrator(config);

// Initialize
await migrator.init();

// Run migrations
await migrator.up();

// Check status
await migrator.status();

// Rollback
await migrator.down();

// Create new migration
await migrator.create('add_users_table');

Database Support

PostgreSQL

• Uses connection pooling
• Supports all PostgreSQL data types and features
• Tested with PostgreSQL 12+

MySQL

• Uses mysql2/promise for async/await support
• Supports all MySQL data types and features
• Tested with MySQL 8+

Security

node-checkpoint includes several security measures:

1. Table name validation: The migrations table name is validated to prevent SQL injection
2. Parameterized queries: All migration tracking uses parameterized queries
3. Isolated migrations: Each migration runs independently with proper error handling

Important: The adapter.execute() method in migrations runs raw SQL. Always validate and sanitize any dynamic values before including them in SQL statements.

Best Practices

1. Always include a down migration: This allows you to rollback changes if needed.

2. One logical change per migration: Keep migrations focused and atomic.

3. Test migrations in development first: Always test both up and down migrations before applying to production.

4. Use descriptive migration names: Names like create_users_table or add_email_to_users are clear and self-documenting.

5. Don't modify existing migrations: Once a migration is committed and deployed, create a new migration instead of modifying the old one.

6. Backup your database: Always backup before running migrations in production.

Migration Naming Convention

Migration files follow the format: {timestamp}_{name}.js

Example: 20250130120000_create_users_table.js

The timestamp ensures migrations run in chronological order.

Error Handling

If a migration fails:

• The error is displayed in the console
• The migration is not recorded as executed
• You can fix the migration and run checkpoint up again

Testing

node-checkpoint includes comprehensive unit and integration tests.

Quick Test (with Docker)

# One command to run all tests
./test-quick-start.sh

# Or use npm script
npm run test:docker

This will:

1. Start PostgreSQL and MySQL test databases in Docker
2. Run all tests
3. Clean up automatically

Manual Testing

# Start test databases
docker-compose -f docker-compose.test.yml up -d

# Run all tests
npm test

# Or run specific test suites
npm run test:unit         # Unit tests only
npm run test:integration  # Integration tests (requires databases)

# Stop databases
docker-compose -f docker-compose.test.yml down

See test/README.md for detailed testing documentation.

Requirements

• Node.js >= 18.0.0
• PostgreSQL or MySQL database
• Docker (optional, for testing)

License

MIT

Contributing

Contributions are welcome! Please feel free to submit a Pull Request.