Migrations & Schema Sync

import { Prorm, DataTypes } from 'ts-prorm-orm';

const prorm = new Prorm({
  dialect: 'sqlite',
  storage: ':memory:',
});

const User = prorm.define('User', {
  name: { type: DataTypes.STRING, allowNull: false },
  email: { type: DataTypes.STRING, unique: true },
});

// Default mode: create any missing tables, leave existing ones untouched.
await prorm.sync();

// Default: only CREATE TABLE for tables that don't exist yet. Never drops data.
await prorm.sync();

// force: DROP every table (in reverse order, CASCADE) and recreate from scratch.
// Destroys all data — great for tests, never for production.
await prorm.sync({ force: true });

// alter: keep existing tables and data, but add/remove/modify columns to match models.
await prorm.sync({ alter: true });

// The same as alter: true — add and change columns, warn about unknown ones.
await prorm.sync({ alter: { drop: false } });

// Really drop columns that are no longer in the model.
await prorm.sync({ alter: { drop: true } });

await prorm.sync({ alter: { drop: 'soft' } });

const parked = await prorm.listSoftDroppedColumns('users');
// [{ table: 'users', archivedName: '_prorm_dropped_nickname_20260926',
//    originalName: 'nickname', droppedAt: Date, dialect: 'sqlite' }]

await prorm.restoreSoftDroppedColumn('users', 'nickname'); // rename it back
await prorm.purgeSoftDroppedColumns('users');              // DROP the parked columns for good

{ id: 1, email: 'a@x.test', _prorm_dropped_nickname_20260926: 'ace', createdAt: …, updatedAt: … }

// Drop only what was parked more than 30 days ago; returns what it dropped.
const purged = await prorm.purgeSoftDroppedColumns(undefined, { olderThan: '30d' });
for (const c of purged) console.log(`dropped ${c.table}.${c.archivedName}`);

// Only ever wipe databases whose name contains "test".
await prorm.sync({ force: true, match: /test/ });

// migrations/20240315120000-create-users-table.js
module.exports = {
  up: async (queryInterface, prorm) => {
    await queryInterface.createTable('users', {
      id: { type: 'INTEGER', primaryKey: true, autoIncrement: true },
      name: { type: 'VARCHAR(255)', allowNull: false },
      email: { type: 'VARCHAR(255)', allowNull: false },
      createdAt: { type: 'DATETIME', allowNull: false },
    });
    await queryInterface.addIndex('users', 'users_email_idx', ['email'], { unique: true });
  },

  down: async (queryInterface, prorm) => {
    await queryInterface.dropTable('users');
  },
};

import { DataTypes, type Prorm, type QueryInterface } from 'ts-prorm-orm';

export async function up(queryInterface: QueryInterface, prorm: Prorm): Promise<void> {
  await queryInterface.createTable('orders', {
    id: { type: DataTypes.INTEGER, primaryKey: true, autoIncrement: true },
    userId: {
      type: DataTypes.INTEGER,
      allowNull: false,
      references: { model: 'users', key: 'id', onDelete: 'CASCADE' },
    },
    totalCents: { type: DataTypes.INTEGER, allowNull: false, defaultValue: 0 },
    placedAt: { type: DataTypes.DATE, allowNull: false },
  });
  await queryInterface.addIndex('orders', 'orders_user_placed_idx', ['userId', 'placedAt']);
}

export async function down(queryInterface: QueryInterface): Promise<void> {
  await queryInterface.dropTable('orders');
}

my-app/
  .env                                       # DATABASE_URL for the CLI
  config/
    config.ts                                # scaffolded by prorm init
  migrations/
    20260926175708-create-users-table.js
    20260926175719-add-users-display-name.js
    20260926120500-create-orders.ts          # sorts first: its timestamp is earlier
  models/
    index.ts
  seeders/
    1790460065308-roles.js

DATABASE_URL=postgres://app:secret@localhost:5432/app_dev

DATABASE_URL=mysql://app:secret@localhost:3306/app_dev

DB_DIALECT=sqlite
DB_NAME=./app.db

$ npx prorm migration:create -- --name create-users-table
Created migrations directory: /srv/my-app/migrations
Migration created: /srv/my-app/migrations/20260926175708-create-users-table.js
Migration created successfully: 20260926175708-create-users-table.js

$ npx prorm migration:create -- --name add-users-display-name
Migration created: /srv/my-app/migrations/20260926175719-add-users-display-name.js
Migration created successfully: 20260926175719-add-users-display-name.js

$ npx prorm migration:status
Database connection established

Migration Status:
================

Pending Migrations:
  [DOWN] 20260926175708-create-users-table
  [DOWN] 20260926175719-add-users-display-name

----------------
Total: 2 (0 executed, 2 pending)

$ npx prorm migration:run --verbose
Database connection established
Running 2 pending migration(s)...
Running migration: 20260926175708-create-users-table
Migration 20260926175708-create-users-table completed
Running migration: 20260926175719-add-users-display-name
Migration 20260926175719-add-users-display-name completed
Executed 2 migration(s)
  - 20260926175708-create-users-table
  - 20260926175719-add-users-display-name

$ DATABASE_URL="$PROD_DATABASE_URL" npx prorm migration:run
Database connection established
Running 2 pending migration(s)...
Running migration: 20260926175708-create-users-table
Migration 20260926175708-create-users-table completed
Running migration: 20260926175719-add-users-display-name
Migration 20260926175719-add-users-display-name completed
Executed 2 migration(s)

$ npx prorm migration:run
Database connection established
No pending migrations to run

$ npx prorm migration:revert
Database connection established
Reverting last migration...
Reverting migration: 20260926175719-add-users-display-name
Migration 20260926175719-add-users-display-name reverted
Reverted 1 migration(s)

$ npx prorm migration:status
Database connection established

Migration Status:
================

Executed Migrations:
  [UP] 20260926175708-create-users-table [Batch 1]

Pending Migrations:
  [DOWN] 20260926175719-add-users-display-name

----------------
Total: 2 (1 executed, 1 pending)

$ npx prorm migration:revert -- --steps 2 --verbose
Database connection established
Reverting 2 migration(s)...
Reverting migration: 20260926175719-add-users-display-name
Migration 20260926175719-add-users-display-name reverted
Reverting migration: 20260926175708-create-users-table
Migration 20260926175708-create-users-table reverted
Reverted 2 migration(s)
  - 20260926175719-add-users-display-name
  - 20260926175708-create-users-table

$ npx prorm migration:run
Database connection established
Running 1 pending migration(s)...
Running migration: 20260926120000-create-orders
Migration 20260926120000-create-orders failed: SqliteError: no such column: "customerId" - should this be a string literal in single-quotes?
Error: no such column: "customerId" - should this be a string literal in single-quotes?

// In the migration file:
export const transaction = false;

// Or for every migration the migrator runs:
const migrator = new Migrator({ prorm, path: './migrations', transaction: false });

import type { CreateQueryInterface, Prorm, QueryInterface } from 'ts-prorm-orm';

export async function up(queryInterface: QueryInterface, prorm: Prorm) {
  const qi: CreateQueryInterface = prorm.getQueryInterface();
  await qi.addEnumValue('order_status', 'refunded', { after: 'delivered' });
}

await qi.addEnumValue('order_status', 'refunded', { after: 'delivered', ifNotExists: true });

await qi.createExtension('uuid-ossp');
await qi.createExtension('pg_trgm');

await qi.bulkInsert('users', rowsWithExplicitIds);
// Without this, the next ordinary insert collides on the primary key.
await qi.setSequenceValue('users_id_seq', 10_000);

await qi.addIndex('events', ['occurred_at']);
await qi.analyze('events');

await qi.commentOnTable('events', 'Append-only; see the retention job');
await qi.commentOnColumn('events', 'payload', 'Raw webhook body, unvalidated');

import { Prorm } from 'ts-prorm-orm';
import { Migrator } from 'ts-prorm-orm'; // also: createMigrator(options)

const prorm = new Prorm({ dialect: 'sqlite', storage: './app.db' });
await prorm.connect();

const migrator = new Migrator({
  prorm,
  path: './migrations',   // default './migrations'
  tableName: 'PrormMeta', // tracking table, default 'PrormMeta'
});

// Discover files on disk before inspecting/running.
await migrator.findMigrations();

// Run every pending migration.
const result = await migrator.up();
console.log(`Ran ${result.executed}:`, result.migrations);

// Run just one, by name.
await migrator.up({ migration: '20240315120000-create-users-table' });

// Revert the most recently executed migration.
await migrator.down();

// Revert the last N migrations (reverse order).
await migrator.down({ steps: 3 });

// Revert one specific migration.
await migrator.down({ migration: '20240315120000-create-users-table' });

// Revert every migration in a batch above the target batch number.
await migrator.downToBatch(2);

const pending = await migrator.pending();   // Migration[] not yet run
const done    = await migrator.executed();  // Migration[] already run
const status  = await migrator.status();    // MigrationStatus[]

for (const s of status) {
  console.log(s.name, s.status, s.batch); // status: 'up' | 'pending' | 'down'
}

name                                    batch  timestamp
20260926175708-create-users-table       1      1790485935
20260926175719-add-users-display-name   1      1790485935

const before = await migrator.status();
const lastGood = Math.max(0, ...before.filter((s) => s.status === 'up').map((s) => s.batch ?? 0));

const result = await migrator.up();
try {
  await runSmokeTests();
} catch (err) {
  // Undo exactly what this deploy applied, then fail the deploy.
  await migrator.downToBatch(lastGood);
  throw err;
}

$ npx prorm init
$ npx prorm migration:create -- --name create-users-table
$ npx prorm migration:run
$ npx prorm migration:run -- --name 20240315120000-create-users-table
$ npx prorm migration:revert
$ npx prorm migration:revert -- --steps 3
$ npx prorm migration:revert -- --name 20240315120000-create-users-table
$ npx prorm migration:status
$ npx prorm migration:run -- --path ./db/migrations

const qi = migrator.getQueryInterface();

// Introspection
const tables = await qi.showTables();                 // string[]
const exists = await qi.tableExists('users');         // boolean
const hasCol = await qi.columnExists('users', 'email');
const columns = await qi.describeTable('users');      // { [col]: ColumnDescription }
const indexes = await qi.showIndexes('users');        // IndexDescription[]
const fks     = await qi.getForeignKeysForTable('users');
const version = await qi.getDatabaseVersion();

// A data-migration example using the bulk + raw helpers.
export const up = async (queryInterface, prorm) => {
  await queryInterface.addColumn('users', 'status', {
    type: 'VARCHAR(20)',
    allowNull: false,
    defaultValue: 'active',
  });

  await queryInterface.bulkUpdate('users', { status: 'legacy' }, { createdAt: null });

  const { rows } = await queryInterface.prormQuery('SELECT COUNT(*) AS n FROM users');
  console.log('backfilled', rows[0].n, 'rows');
};