Skip to content

Instantly share code, notes, and snippets.

@parris

parris/migrationTypes.ts

Created August 13, 2025 16:25
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save parris/39649caa403f5e629d30893d678f14e3 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save parris/39649caa403f5e629d30893d678f14e3 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
migrations.ts
import chalk from 'chalk';
import fs from 'fs/promises';
import path from 'path';
import { fileURLToPath } from 'url';
import { MigrationModule } from './migrationTypes.js';
import sql from './sql.js';
export const __filename = fileURLToPath(import.meta.url);
export const __dirname = path.dirname(__filename);
interface MigrationOptions {
verbose: boolean;
}
const defaultOptions = { verbose: true };
const migrationTemplate = `import { type MigrationRunner } from '@/utils/migrationTypes.js';
export const up: MigrationRunner = async (sql) => {
const [row] = await sql\`
\`;
};
export const down: MigrationRunner = async (sql) => {
const [row] = await sql\`
\`;
};
`;
export async function createMigration(name: string) {
const date = new Date();
const dt = `${date.getUTCFullYear()}_${
date.getUTCMonth() + 1
}_${date.getUTCDate()}_${date.getUTCHours()}:${date.getUTCMinutes()}:${date.getUTCSeconds()}`;
return fs.writeFile(
path.join(__dirname, '..', 'migrations', `${dt}_${name}.ts`),
migrationTemplate,
);
}
async function createTablesIfNonExistant() {
const [{ exists }] =
await sql`SELECT EXISTS (SELECT true FROM pg_tables WHERE tablename = 'migrations') and (SELECT true FROM pg_tables WHERE tablename = 'migration_lock') AS exists;`;
const willCreateTables = !exists;
if (!willCreateTables) {
return;
}
let startTime = Date.now();
await sql.begin(async (sql) => {
await sql`
CREATE TABLE IF NOT EXISTS migrations (
id SERIAL PRIMARY KEY,
name VARCHAR(255) UNIQUE NOT NULL,
batch INT NOT NULL,
created_at TIMESTAMP NOT NULL DEFAULT now()
);
`;
await sql`
CREATE TABLE IF NOT EXISTS migration_lock (
index SERIAL PRIMARY KEY,
is_locked BOOLEAN NOT NULL DEFAULT false
);
`;
const lock = await sql`SELECT * FROM migration_lock`;
if (lock.length === 0) {
await sql`
INSERT INTO migration_lock (is_locked) values (false) returning *
`;
}
});
let endTime = Date.now();
console.log(
`- Created migration tables "migrations", "migration_lock" ${chalk.green(
`[${endTime - startTime}ms]`,
)}`,
);
}
const isRejected = <T>(p: PromiseSettledResult<T>): p is PromiseRejectedResult =>
p.status === 'rejected';
export async function migrateTo(
migrationLimit = Number.POSITIVE_INFINITY,
options: MigrationOptions = defaultOptions,
) {
options.verbose && console.log(chalk.underline(chalk.bold('Running Migrations')));
await createTablesIfNonExistant();
let startTime = Date.now();
let migrationCount = 0;
const isLocked = await sql`SELECT * FROM migration_lock WHERE is_locked = true`;
if (isLocked.length > 0) {
console.error(
`${chalk.yellow('Migrations are locked')} - you need to wait or manually unlock them`,
);
return;
}
let thisRunInitiatedTheLock: boolean = false;
try {
const [migrationLock] = await sql`
UPDATE migration_lock SET is_locked = true returning *;
`;
if (migrationLock.isLocked) {
thisRunInitiatedTheLock = true;
}
const existing = await sql`SELECT * FROM migrations ORDER BY batch ASC`;
const existingNames = existing.map((row) => row.name);
const migrationFiles = (await fs.readdir(path.join(__dirname, '..', 'migrations'))).filter(
(filename) => !filename.endsWith('.map'),
);
const resolvedModules = await Promise.allSettled<MigrationModule>(
migrationFiles.map((filename) => import(path.join(__dirname, '..', 'migrations', filename))),
);
const nextBatchNumber = existing.length === 0 ? 1 : existing[existing.length - 1].batch + 1;
options.verbose && console.log(`- info: migrations will use batch number ${nextBatchNumber}`);
await sql.begin(async (sql) => {
const results = [];
let runCount = 0;
for (let i = 0; i < migrationFiles.length; i++) {
if (runCount >= migrationLimit) {
break;
}
if (!existingNames.includes(migrationFiles[i])) {
const mod = resolvedModules[i];
if (isRejected(mod)) {
throw new Error(`${chalk.red('[Module Load Error]')} ${mod.reason}`);
}
if (typeof mod.value.up !== 'function') {
throw new Error(
`${chalk.red('[Migration Malformed Error]')} ${
migrationFiles[i]
} does not have "up" method`,
);
}
const runStart = Date.now();
const run = await mod.value.up(sql);
results.push(run);
const recordMigration = await sql`
INSERT INTO migrations (name, batch) values (${migrationFiles[i]}, ${nextBatchNumber}) returning *
`;
const runEnd = Date.now();
results.push(recordMigration);
migrationCount += 1;
runCount += 1;
options.verbose &&
console.log(`- ▲ ${migrationFiles[i]} ${chalk.green(`[${runEnd - runStart}ms]`)}`);
}
}
return results;
});
} finally {
if (thisRunInitiatedTheLock) {
try {
await sql`
UPDATE migration_lock SET is_locked = false returning *;
`;
} catch (e: any) {
throw new Error(
`${chalk.red('[Migration Unlock Error]')} Could not unlock migrations - ${e.toString()}`,
);
}
}
const endTime = Date.now();
options.verbose &&
console.info(
`\nRan ${migrationCount} ${migrationCount === 1 ? 'migration' : 'migrations'} in ${
endTime - startTime
}ms`,
);
}
}
export async function migrateBack(
migrationLimit?: number,
options: MigrationOptions = defaultOptions,
) {
options.verbose && console.log(chalk.underline(chalk.bold('Rolling Migrations Backwards')));
await createTablesIfNonExistant();
let startTime = Date.now();
let rollbackCount = 0;
const isLocked = await sql`SELECT * FROM migration_lock WHERE is_locked = true`;
if (isLocked.length > 0) {
console.error(
`${chalk.yellow('Migrations are locked')} - you need to wait or manually unlock them`,
);
return;
}
let thisRunInitiatedTheLock: boolean = false;
try {
const [migrationLock] = await sql`
UPDATE migration_lock SET is_locked = true returning *;
`;
if (migrationLock.isLocked) {
thisRunInitiatedTheLock = true;
}
const existing = await sql`SELECT * FROM migrations ORDER BY id DESC`;
if (existing.length === 0) {
options.verbose && console.log('\nNo migrations found');
return;
}
const lastBatch = existing[0].batch;
const migrationsToRollback =
typeof migrationLimit === 'number'
? existing.slice(0, migrationLimit ?? existing.length)
: existing.filter((migration) => migration.batch === lastBatch);
const resolvedModules = await Promise.allSettled<MigrationModule>(
migrationsToRollback.map(
(migration) => import(path.join(__dirname, '..', 'migrations', migration.name)),
),
);
if (typeof migrationLimit === 'number') {
options.verbose && console.log(`- Mode: rolling back individual migrations`);
options.verbose && console.log(`- Rollback limit: ${migrationLimit}`);
} else {
options.verbose && console.log(`- Mode: rolling back by batch`);
options.verbose && console.log(`- Batch number: ${lastBatch}`);
}
await sql.begin(async (sql) => {
const results = [];
for (let i = 0; i < migrationsToRollback.length; i++) {
const mod = resolvedModules[i];
if (isRejected(mod)) {
throw new Error(`${chalk.red('[Module Load Error]')} ${mod.reason}`);
}
if (typeof mod.value.down !== 'function') {
throw new Error(
`${chalk.red('[Migration Malformed Error]')} ${
migrationsToRollback[i].name
} does not have "down" method`,
);
}
const runStart = Date.now();
results.push(await mod.value.down(sql));
const deleteMigration = await sql`
DELETE FROM migrations WHERE name=${migrationsToRollback[i].name} returning *
`;
const runEnd = Date.now();
results.push(deleteMigration);
rollbackCount += 1;
options.verbose &&
console.log(
`- ▼ ${migrationsToRollback[i].name} ${chalk.green(`[${runEnd - runStart}ms]`)}`,
);
}
return results;
});
} finally {
if (thisRunInitiatedTheLock) {
try {
await sql`
UPDATE migration_lock SET is_locked = false returning *;
`;
} catch (e: any) {
throw new Error(
`${chalk.red('[Migration Unlock Error]')} Could not unlock migrations - ${e.toString()}`,
);
}
}
const endTime = Date.now();
options.verbose &&
console.info(
`\nRan ${rollbackCount} ${rollbackCount === 1 ? 'migration' : 'migrations'} in ${
endTime - startTime
}ms`,
);
}
}
Raw
migrationTypes.ts
import postgres from 'postgres';
export type MigrationRunner = (sql: postgres.TransactionSql<{}>) => Promise<void>;
export interface MigrationModule {
up: MigrationRunner;
down: MigrationRunner;
}
Raw
scriptRunner.ts
import { program } from 'commander';
import './env.js';
program
.command('migrate:latest')
.description('Runs the migrations')
.action(async () => {
const { migrateTo } = await import('@/utils/migrations.js');
await migrateTo();
process.exit(0);
});
program
.command('migrate:up')
.description('Runs a specific number of migrations')
.argument('[numberOfMigrations]', 'The limit for the number of migrations to run, defaults to 1')
.action(async (numberOfMigrations: string = '1') => {
const { migrateTo } = await import('@/utils/migrations.js');
await migrateTo(numberOfMigrations ? parseInt(numberOfMigrations, 10) : undefined);
process.exit(0);
});
program
.command('migrate:rollback')
.description('Undoes the last batch of migrations')
.action(async () => {
const { migrateBack } = await import('@/utils/migrations.js');
await migrateBack();
process.exit(0);
});
program
.command('migrate:down')
.description('Reverts a certain number of migrations')
.argument('[numberOfMigrations]', 'The limit for the number of migrations to run, defaults to 1')
.action(async (numberOfMigrations: string = '1') => {
const { migrateBack } = await import('@/utils/migrations.js');
await migrateBack(numberOfMigrations ? parseInt(numberOfMigrations, 10) : undefined);
process.exit(0);
});
program
.command('migrate:create')
.description('Creates a new migrations')
.argument('<string>', 'Name of the migration')
.action(async (name: string) => {
const { createMigration } = await import('@/utils/migrations.js');
await createMigration(name);
});
program.parse();
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment