SQL Proper

SQL Proper is a lightweight and straightforward SQL migration tool for both MySQL/MariaDB (via mysql2) and SQLite, with:

• A simple CLI for application developers
• A focused programmatic API for Node.js
• A small, testable framework core for contributors and advanced users

This README is written primarily for developers integrating or extending SQL Proper. If you only need usage details, you can skim Quick Start (CLI) and Usage in Code (Import).

Installation

Add SQL Proper to your project:

# Globally (optional, for CLI-only usage)
npm install -g @noego/proper

# Or locally to your project (recommended)
npm install --save @noego/proper

For TypeScript projects, the emitted .d.ts files from the build are included in the published package; you don’t need to install separate type packages.

Quick Start (CLI)

Below is a fast-track on how to work with SQL Proper from the command line. Skip to Configuration to learn how to connect to your database or to Programmatic API for using it from code.

1. Initialize a config file (creates proper.json by default):

   proper init

   You can specify a custom file:

   proper init --config path/to/my-config.json

   This generates a proper.json with default settings. By default it targets MySQL ("database": "sql"). Switch to SQLite by setting "database": "sqlite" and filling the sqlite block. Ensure it contains at least:

   {
     "migration_folder": "migrations",
     "migration_table": "proper_migrations",
     "database": "sqlite",
     "sqlite": { "database": "db.sqlite" }
   }

   Also create a migrations/ directory in your project root to hold .up.sql and .down.sql files. File names follow <timestamp>_<name>.up.sql and <timestamp>_<name>.down.sql.

2. Create a new migration (generates timestamped .up.sql & .down.sql files):

   proper create --name "create_users_table"

   Edit those .sql files and add your migration scripts.

   Always use proper create to mint the timestamp — never type one by hand. Generated stamps never end in 0, and proper up refuses to apply a pending migration whose stamp ends in 000 (a tell-tale hand-typed value). Migrations that were already applied are never re-checked. A historical rounded key can be allowed only by listing that exact canonical key in legacy_migration_keys; the strict check remains active for every other key.

3. Apply migrations:

   proper up

   • Applies all pending migrations.
   • Use --increment <number> to limit the count, e.g. proper up --increment 1.

4. Roll back migrations:

   proper down

   • Rolls back the latest applied migrations.
   • Use --increment <number> to limit the count, e.g. proper down --increment 1.

5. Reset your database:

   proper reset

   • Rolls back all migrations, then re-applies them.

6. Check status:

   proper status

   • Prints a table of all migrations and their status (completed or pending).

All commands accept a --config <path> to specify a config file other than the default proper.json.

Configuration

Before you run migrations, SQL Proper needs to know how to connect to your DB and where to store the migration files.
Here’s a sample proper.json for SQLite (change "database": "sql" if using MySQL):

{
  "migration_folder": "migrations",
  "migration_table": "proper_migrations",
  "database": "sqlite",
  "sql": {
    "host": "localhost",
    "user": "root",
    "database": "proper",
    "password": "password123"
  },
  "sqlite": {
    "database": ":memory:"
  }
}

• migration_folder: The directory holding your .up.sql and .down.sql files.
• migration_table: Name of the DB table that keeps track of applied migrations.
• database: Either "sql" (for MySQL) or "sqlite".
  • If using "sql", fill out the sql object (host, user, password, database).
  • If using "sqlite", define "sqlite": { "database": "<path or :memory:>" }.
• legacy_migration_keys: Optional array of exact canonical keys for known historical migrations whose rounded timestamp ends in 000. Paths, migration filenames, wildcards, nonstrings, and noncanonical spellings are rejected; unlisted rounded migrations remain blocked.
• host / user / password / database: Standard MySQL connection details if "database": "sql".

Use proper init to quickly generate a default config, or manually create your own. When running the CLI, if your config isn’t in proper.json, specify --config:

proper up --config path/to/my-custom.json

Programmatic API

If you prefer integrating migrations directly in your Node.js services or test harnesses, import SQL Proper:

import { MigrationRunnerFactory } from "@noego/proper";

(async () => {
  // Suppose "myconfig.json" is your config file
  const runner = await MigrationRunnerFactory.create("myconfig.json");

  // Or pass an existing connection (useful for testing or connection pooling):
  // const runner = await MigrationRunnerFactory.create("myconfig.json", existingConnection);

  await runner.setup();

  // Apply pending migrations
  const pending = await runner.getPendingMigrations();
  await runner.migrate(pending, true);

  console.log("Migrations applied!");

  // Check completed migrations
  const completed = await runner.getCompletedMigrations();
  console.log("Completed:", completed);

  // Wrap up
  await runner.close();
})();

Public exports

From @noego/proper:

• MigrationRunnerFactory
  • create(configPath: string, connection?: any): create a runner backed by config, optionally reusing an existing DB connection (see examples below).
  • createEmpty(configPath: string): create a runner without connecting to a database (used by init, create).
• MigrationConfig: config model used by the framework.
• MigrationRunner (alias of MySQLMigrationRunner): convenience export for MySQL-specific runner.

From @noego/proper/runners:

• BaseSQLRunner, SQLRunner, SQLiteRunner: low-level database runners for advanced integrations and testing.

These are implemented in the framework/ directory and re-exported via framework/lib/runner.ts. Check the generated .d.ts files in lib/ and bin/ for the exact TypeScript shapes in a given release.

Core runner API

The MigrationRunner instances returned from MigrationRunnerFactory.create expose:

• setup(): Prepare DB environment (creates migration table if missing).
• getMigrations(): Load all migrations from the configured folder.
• getPendingMigrations() / getCompletedMigrations(): Filtered migration sets.
• migrate(nodes, forward): Execute migrations (forward = true for up, false for down).
• reset(): Roll back everything, then reapply them (used by CLI reset).
• createMigration(name): Scaffold <timestamp>_<name>.up.sql and .down.sql files.
• init(configPath): Create a default proper.json if missing.
• query(sql: string, params?): Execute read-only queries (used by CLI query).
• close(): Close the DB connection (no-op if you provided the connection).

Testing with In-Memory Databases

For test isolation and faster test execution, you can pass an existing database connection to Proper. This is particularly useful when working with SQLite :memory: databases where you want multiple tests to share the same in-memory instance.

Why This Matters: When using SQLite :memory: databases for testing, the database only exists as long as the connection is open. By creating the connection yourself and passing it to Proper, you can:

1. Create the :memory: connection once
2. Run migrations using Proper with that connection
3. Run your tests using the same connection with a fully migrated schema

Without this capability, you'd have to let Proper create the connection, but then you couldn't access that same in-memory database from your tests (it would be lost when Proper closes). Connection injection solves this by letting you control the lifecycle.

Example: Using a Shared :memory: Connection

import { MigrationRunnerFactory } from "@noego/proper";
import * as sqlite from "sqlite";
import * as sqlite3 from "sqlite3";

// Create a single :memory: connection that all tests will share
const memoryDb = await sqlite.open({
  filename: ':memory:',
  driver: sqlite3.Database
});

// Pass the connection to Proper
const runner = await MigrationRunnerFactory.create("test-config.json", memoryDb);

// Setup and run migrations on the shared connection
await runner.setup();
const pending = await runner.getPendingMigrations();
await runner.migrate(pending, true);

// Now your tests can use the same memoryDb instance
// All queries will run against the same in-memory database

Example: Test Setup with Connection Injection

import { describe, beforeAll, afterAll, test } from '@jest/globals';

describe('Database Tests', () => {
  let db;
  let runner;

  beforeAll(async () => {
    // Step 1: Create the :memory: database connection
    // This connection will persist for all tests in this suite
    db = await sqlite.open({
      filename: ':memory:',
      driver: sqlite3.Database
    });

    // Step 2: Pass the connection to Proper and run migrations
    // Proper will use YOUR connection instead of creating its own
    runner = await MigrationRunnerFactory.create("proper.json", db);
    await runner.setup();
    await runner.migrate(await runner.getPendingMigrations(), true);

    // Step 3: Now your tests can use the same 'db' connection
    // The schema is fully migrated and ready for testing
  });

  afterAll(async () => {
    await runner.close();
  });

  test('should query migrated database', async () => {
    // Use the same db connection that was migrated by Proper
    // This works because we created the connection ourselves
    const result = await db.get('SELECT * FROM users WHERE id = ?', [1]);
    expect(result).toBeDefined();
  });
});

Ledger Patches (proper patch)

Ledger patches are one-shot, versioned YAML files that repair proper_migrations history without executing schema SQL. Use them when applied migration files were renamed after installation, when a ledger row is missing for schema that already exists, or when a ledger row wrongly claims success.

Patches are discovered and applied automatically before Proper reads migration completion state (CLI and MigrationRunnerFactory.create() alike), are transactional, are applied at most once per configured migration ledger, and work identically for MySQL/MariaDB, SQLite, and PostgreSQL.

Creating a patch

proper patch fix_renamed_keys
proper patch --name fix_renamed_keys

This scaffolds <patch_folder>/<timestamp>_fix_renamed_keys.yaml without opening a database. The scaffold's operations: [] is intentionally not runnable — complete the file before running another database-backed command.

Configuration

{
  "patch_folder": "patches",       // default: a `patches` sibling of migration_folder
  "patch_table": "proper_patches"  // default
}

Patches may also be co-located with migrations by setting patch_folder to the same path as migration_folder — migration discovery only considers *.up/down.sql|js files and patch discovery only considers top-level *.yaml files, so the two never interfere.

Patch file format (version 1)

version: 1
description: fix hand-stamped migration names
operations:
  - rename_migration:
      from: 1787905000000_create_conversation_run_lifecycle
      to: 1787941600000_create_conversation_run_lifecycle
  - mark_applied:
      key: 1787941700000_existing_schema_baseline
  - unmark_applied:
      key: 1787941800000_incorrect_ledger_row

Operations run in file order, then operation order. Each is a conditional bookkeeping repair:

• rename_migration — moves a ledger row from from to to; a no-op when the database already converged; a hard conflict when both keys exist.
• mark_applied — inserts a ledger row without running up SQL (you are responsible for verifying the schema already matches).
• unmark_applied — deletes a ledger row without running down SQL, making the migration pending again.

Parsing is strict: unknown fields/verbs, duplicate keys, YAML anchors/aliases, merge keys, and custom tags are rejected. There is no raw SQL escape hatch.

Immutability

Patch files are permanent, append-only migration history. Once any database records a patch, the file must never be edited, renamed, or deleted — Proper stores a SHA-256 checksum at application time and refuses to run migrations if an applied patch's file is missing or changed. A correction is a new, later patch (A -> B -> C and A -> B -> A chains are supported and validated against the current migration files).

Deployment and rollback warning

A history rename must ship the renamed migration files, the patch file(s), and a patch-aware Proper version in the same application artifact. Do not run old and new application versions' migration startup concurrently during the first deployment of a rename. After a rename patch commits, rolling back to an artifact that only contains the old migration filenames is unsafe — that old runner can attempt the old migration again. Roll back only to artifacts consistent with the repaired ledger, or restore the database separately.

proper down and proper reset never touch patch history; destructive terminate() drops both the migration table and the patch table.

Programmatic API

const runner = await MigrationRunnerFactory.create("proper.json"); // patches already applied
const results: PatchApplyResult[] = await runner.applyPendingPatches(); // idempotent
const path: string = runner.createPatch("fix_renamed_keys"); // scaffold, no DB

Seeding

SQL Proper supports a powerful seeding feature that allows you to populate your database with initial data (reference data, test users, etc.). Seeds can be SQL files or JavaScript/TypeScript modules.

Configuration

Configure seeds in your proper.json:

{
  "seeds": {
    "migrationsDir": "database/seed/migrations",
    "dataDir": "database/seed/data",
    "list": ["muscles", "exercises", "equipments", "user"]
  }
}

• migrationsDir: Directory containing seed implementation files (.sql, .ts, or .js).
• dataDir: (Optional) Directory containing JSON data files for seeds.
• list: Default list of seed names to run when no names are provided to the CLI.

CLI Usage

Run seeds via the CLI:

# Run default seeds defined in config
proper seed

# Run specific seeds
proper seed muscles exercises

# Seed rollback (runs down migration for seeds)
proper seed --action down muscles

# Transactional mode
proper seed --transactional seed   # Wrap each seed in a transaction
proper seed --transactional runner # Wrap all seeds in one transaction

Seed Implementations

Seeds can be implemented in two ways:

1. SQL Pair (<name>.up.sql and <name>.down.sql) in migrationsDir.

• Simple raw SQL execution.
• Good for static reference data.

2. TypeScript/JavaScript Module (<name>.ts or <name>.js) in migrationsDir.

• Exports up and down functions.
• Receives a runner and a context object.
• context contains:
  • data: Loaded JSON data matching the seed name (from dataDir).
  • dialect: The current database dialect (sql, sqlite, etc.).
  • log: A logger function.

Example TS Seed (database/seed/migrations/user.ts):

import type { IMigrationRunner, SeedContext } from "@noego/proper";

export async function up(runner: IMigrationRunner, ctx: SeedContext) {
  const users = ctx.data as any[]; // Data loaded from database/seed/data/user.json
  
  for (const user of users) {
    await runner.query("INSERT INTO users (email, name) VALUES (?, ?)", [user.email, user.name]);
    ctx.log?.(`Created user ${user.email}`);
  }
}

export async function down(runner: IMigrationRunner, ctx: SeedContext) {
  const users = ctx.data as any[];
  for (const user of users) {
    await runner.query("DELETE FROM users WHERE email = ?", [user.email]);
  }
}

Programmatic API

Run seeds from your code (great for tests):

import { MigrationRunnerFactory, runSeedsWithRunner, loadMigrationConfig } from "@noego/proper";

// Load config
const config = loadMigrationConfig("proper.json");
const runner = await MigrationRunnerFactory.create("proper.json");

// Run seeds
await runSeedsWithRunner(runner, config, 'up', {
  names: ['muscles', 'exercises'], // Optional: override list
  validate: true, // Optional: validate JSON data against schema
});

Examples

An example/ directory is included with:

• A docker-compose setup for MySQL
• Sample migrations
• A sample proper.json
• A Makefile demonstrating typical commands:

# Start MySQL in Docker
make db_up

# Stop MySQL
make db_down

# Apply migrations
make up

# Rollback
make down

# Reset DB
make reset

# Create migration
make create name="create_posts"

Environment

Provide sensitive values via environment variables when possible:

• SQL_PASSWORD: Provide your MySQL password via environment variable instead of storing it in the config file. If a password is set in your config, that value is used.

Example:

SQL_PASSWORD=supersecret proper up --config proper.json

Project Layout (for contributors)

This is a small, framework-style project. The main pieces are:

• cli.ts: CLI entrypoint (compiled to bin/cli.js and published as the proper binary).
• index.ts: Library entrypoint, exporting MigrationRunnerFactory, MigrationConfig, and MigrationRunner.
• framework/: Core framework classes:
  • MigrationCLI.ts: CLI parser and command wiring.
  • MigrationRunner.ts: main migration runner + factory.
  • MigrationConfig.ts: config reading/validation.
  • MigrationDirectoryReader.ts: migration discovery from the filesystem.
  • MigrationStatus.ts, MigrationNode.ts, MigrationFilter.ts: migration model, status helpers, and filtering logic.
  • SQLRunner.ts: MySQL and SQLite runner implementations.
  • errors.ts: domain-specific error types (used heavily by CLI and tests).
• framework/lib/runner.ts: re-exports low-level SQL runners for consumers (@noego/proper/runners).
• helper/: small utilities (e.g. string helpers).
• tests/: Jest-based unit tests for the framework and CLI.
• example/: runnable MySQL + migrations example (with docker-compose and Makefile).

Build artifacts are written to:

• bin/: compiled CLI and public library entrypoints.
• lib/: compiled runner exports (runners subpath).

The package’s "files" array in package.json ensures only bin/, lib/, and readme.md are published.

Development & Contributing

Requirements:

• Node.js 16+ (LTS recommended)
• npm (or a compatible package manager)

Local workflow:

• Install dependencies: npm install
• Run tests: npm test
• Build the package: npm run build (emits to bin/ and lib/ via tsup)
• Run CLI from source (TypeScript): npx tsx cli.ts --config sqlite/proper.json up

Test tooling:

• Jest with ts-jest and jest-extended (see jest.config.ts).
• Core framework tests in tests/unit/framework.
• CLI tests in tests/unit/cli.test.ts.

Debugging in VS Code:

• Use the provided .vscode/launch.json:
  • “Jest: Current File” runs the open test file.
  • “Jest: All Tests” runs the full suite.
  • “Jest: CLI Test File” runs only tests/unit/cli.test.ts.

Contribution guidelines (informal for now):

• Keep the public API surface (index.ts, framework/lib/runner.ts, CLI flags) backward compatible where possible.
• Add or update tests in tests/unit when changing framework or CLI behavior.
• Prefer small, focused abstractions in framework/ over adding logic in the CLI.

Exit Codes

• 0: success (e.g., help, completed operations)
• Non‑zero: CLI error (e.g., unknown command, missing --name for create, config validation failures)

License

ISC — (c) 2023–2025 Shavauhn Gabay

Additional Notes

• Ensure your migration files follow the <timestamp>_<name>.up.sql / .down.sql pattern in the configured migration_folder.
• Back up your database before running migrations, especially in production.
• Use version control to track migration files and configurations.
• Test migrations in a staging environment before production.