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.

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:>" }.
• 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();
  });
});

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.