[WIP] SQL Migrations Core

Database framework-agnostic package for DIY migrations solutions with zero dependencies, written in TypeScript.

With sql-migrations-core you can write sql migrations as you want. Use ANY database framework and prepare CLI as you feel comfortable.

Table of Contents

• SQL Migrations Core
  • Installation
  • Initialization
  • Usage
  • Migration files
  • CLI
• API
  • Creating migrationsCore object
    • Using create(config)
    • Using new MigrationsCore()
  • Methods of migrationsCore object
  • Methods of localMigrations object
  • Methods of storedMigrations object

Installation

Install with npm:

npm i sql-migrations-core

Or with yarn:

yarn add sql-migrations-core

...Or with pnpm:

pnpm add sql-migrations-core

Initialization

Minimal configuration looks like this:

const migrationsCore = MigrationsCore.create({
  path: path.join('..', 'path/to/migrations/dir'),
  sqlActions: {
    async createMigrationTable() {
      // TODO implement
    },
    async getMigrationsNames() {
      // TODO implement
      return [];
    },
    async migrateDown(migrations) {
      // TODO implement
    },
    async migrateUp(migrations) {
      // TODO implement
    },
  },
});

There is a trade-off - you can use any db framework, but you need to implement SQL queries by yourself. For example, with kysely:

import { db } from './database';

// Probably you'll want to move SQL logic to repository.
// In this example I'm just showing a minimal SQL implementation.

const migrationsCore = MigrationsCore.create({
  path: path.join("/migrations"),
  sqlActions: {
    async createMigrationTable(): Promise<void> {
      await db.schema
        .createTable("__migration")
        .ifNotExists()
        .addColumn("name", "varchar", (cb) => cb.notNull().unique())
        .addColumn("migrated_at", "timestamp", (cb) =>
          cb.notNull().defaultTo(sql`current_timestamp`)
        )
        .execute();
    },

    async getMigrationsNames(): Promise<string[]> {
      const records = await db
        .selectFrom("__migration")
        .select("name")
        .execute();
      return records.map((r) => r.name);
    },

    async migrateDown(migrations: Migration[]): Promise<void> {
      await db.transaction().execute(async (trx) => {
        for (const migration of migrations) {
          await trx
            .deleteFrom("__migration")
            .where("name", "=", migration.name)
            .execute();
          await sql.raw(migration.sql).execute(trx);
        }
      });
    },

    async migrateUp(migrations: Migration[]): Promise<void> {
      await db.transaction().execute(async (trx) => {
        for (const migration of migrations) {
          await trx
            .insertInto("__migration")
            .values({ name: migration.name })
            .execute();
          await sql.raw(migration.sql).execute(trx);
        }
      });
    },
  },
});

See other examples here.

Usage

After initializing migrationsCore you can use methods:

await migrationsCore.createFiles('example'); // creates blank sql files

await migrationsCore.up(); // one migration up
await migrationsCore.down(); // one migration down
await migrationsCore.sync(); // all pending up migrations
await migrationsCore.drop(); // all down migrations of executed migrations
await migrationsCore.toLatest(); // all pending up migrations from last executed
await migrationsCore.to('123-example'); // all up/down migrations between last executed and provided

await migrationsCore.status(); // get statuses of migrations (name and is synced)

Also, you can pass chunkSize in some methods to run migrations by chunks in synchronous way:

await migrationsCore.sync(100); // runs migrations in chunks limit by 100 

See API for more info.

Migration files

Migrations core creates 2 migration files - up and down. It uses {timestamp}-${title}${postfix} format (name of migration is {timestamp}-{title}):

await migrationsCore.createFile('example');

// Will create something like this
// ./migrations/1718394484921-example.down.sql
// ./migrations/1718394484921-example.up.sql

CLI

There is no out-of-box CLI solution, so you need implement one by yourself. For example, create migration file:

import { migrationsCore } from './migrations-core';
import path from 'node:path';

const migrationsPath = path.join('./migrations');
migrationsCore.createFile(process.args[2]);

Usage:

node ./scripts/migrations-create.js example

See other CLI examples here.

API

There are several classes available to use:

1. MigrationsCore - main class that gives access to all needed migration actions.
2. LocalMigrations - class used by MigrationsCore. Hides all filesystem operations to access local migrations. For advanced usage.
3. StoredMigrations - class used by MigrationsCore. Hides all database operations to run migrations and access metadata of executed migrations. For advanced usage.

Creating migrationsCore object

There are two ways to create instance of MigrationsCore:

1. Using static MigrationsCore.create() method
2. Using new MigrationsCore() constructor

Using create(config)

create() method receives config object and returns instance of MigrationsCore. Actually it's constructor wrapper, which creates LocalMigrations and StoredMigrations by itself. Config options are passed to these objects. Config options:

• path - string, path to migrations directory. Used by LocalMigrations.
• postfix - object, optional, custom postfix for migrations files. Used by LocalMigrations.
• postfix.up - string, postfix for up migrations (default: .up.sql).
• postfix.down - string, postfix for down migrations (default: .down.sql).
• sqlActions - object, postfix for down migrations (default: .down.sql). Used by StoredMigrations.
• sqlActions.createMigrationTable - function, used to create table before every StoredMigrations action. Recommended to use with IF NOT EXISTS statement.
• sqlActions.migrateUp - function, used to run up migrations. Receives array of migration objects.
• sqlActions.migrateDown - function, used to run down migrations. Receives array of migration objects.
• sqlActions.getMigrationsNames - function, used to get executed migrations names. Should return array of migration names.
• sqlActions.getLastMigrationName - function, optional, used to get last executed migrations name. Should return name or null. If function not provided - getMigrationsNames used instead.

Example:

const migrationsCore = MigrationsCore.create({
  path: path.join(__dirname, '../migrations'),
  postfix: {
    up: '.custom.up.sql',
    down: '.custom.down.sql',
  },
  sqlActions: {
    createMigrationTable() { /* ... */ },
    migrateUp(migrations) { /* ... */ },
    migrateDown(migrations) { /* ... */ },
    getMigrationsNames() { /* ... */ },
    getLastMigrationName() { /* ... */ },
  }
});

Using new MigrationsCore()

For options refer to Using create() method section.

Example:

const localMigrations = new LocalMigrations({
  dirPath: path.join(__dirname, '../migrations'),
  postfix: {
    up: '.up.sql',
    down: '.down.sql',
  },
});

const storedMigrations = new StoredMigrations({
  sqlActions: {
    createMigrationTable() { /* ... */ },
    migrateUp(migrations) { /* ... */ },
    migrateDown(migrations) { /* ... */ },
    getMigrationsNames() { /* ... */ },
    getLastMigrationName() { /* ... */ },
  }
});

const migrationsCore = new MigrationsCore(localMigrations, storedMigrations);

Methods of migrationsCore object

See migrations-core.interface.ts

Methods of localMigrations object

See local-migrations.interface.ts

Methods of storedMigrations object

See stored-migrations.interface.ts