Egon ORM

Appearance

Migrations ​

Migrations change the database's structure in versioned, ordered steps. Each one is a TypeScript file in a migrations directory, written by hand with a small set of operations, and the runner applies the pending ones and records what it applied.

ts
// migrations/20261012093000_create_users.ts
export const createUsers = defineMigration({
  up(m) {
    m.createTable("users", (table) => {
      table.id(); //                                       "id" bigserial primary key
      table.text("email").notNull();
      table.text("name"); //                               columns allow null unless notNull()
      table.integer("age");
      table.enumText("role", ["admin", "member"]).notNull().default("member"); // text + check
      table.jsonb("settings");
      table.references("org_id", "orgs", { type: "uuid", onDelete: "cascade" });
      table.timestamps(); //                               created_at, updated_at
    });
    m.createIndex("users", ["email"], { unique: true }); // users_email_key
  },
  down(m) {
    m.dropTable("users"); //                           its indexes go with it
  },
});

The file name holds the version and the name: 20261012093000_create_users.ts is version 20261012093000 (a UTC timestamp, YYYYMMDDHHMMSS) named create_users. egon migrate create create_users writes such a file, with an empty up and down for you to fill in (CLI reference).

If you come from Prisma Migrate or drizzle-kit: there is no schema diffing. You write the change you want, as with Sequelize's or Knex's migrations; the schema classes and the migrations are kept in step by you, not inferred from each other.

Running migrations ​

From the command line, with @egon-orm/cli installed (npm install -D @egon-orm/cli, then npx egon …) and the configuration in egon.config.ts:

sh
egon migrate create add_age  # write migrations/<version>_add_age.ts
egon migrate                 # apply every pending migration
egon migrate --dry-run       # print the SQL, run nothing
egon rollback                # roll back the latest one
egon migrations              # list them: up, down or missing (claimed: see SQLite below)

From code, with createMigrator from egon-orm/migration:

ts
const adapter = postgres({ connectionString: process.env.DATABASE_URL });
const migrator = createMigrator({
  adapter,
  directory: "./migrations",
  log: (event: MigrationEvent) => {
    if (event.kind === "migrated")
      process.stdout.write(`${event.version} ${event.name}: ${event.durationMs} ms\n`);
  },
});

await migrator.up(); //                       every pending migration
await migrator.up({ to: 20261013080000 }); // up to and including a version
await migrator.down(); //                     the latest one
await migrator.down({ step: 2 });
const plan = await migrator.up({ dryRun: true }); // the SQL, nothing run
const rows = await migrator.status(); //      [{ version, name, state: "up" | "down" | "missing" }]

await adapter.close();

The migrator takes the adapter, the same one you give createRepo (or one of its own).

Up and down ​

Every migration has two functions, and both are required:

  • up(m) makes the change;
  • down(m) undoes it. Rolling back always runs down.

When there is nothing to undo, as for a backfill, down is an empty function. Write a // noop comment in it, so a reader knows it is empty on purpose:

ts
// migrations/20261013080000_backfill_roles.ts
export const backfillRoles = defineMigration({
  up(m) {
    m.execute(fragment`update users set role = ${"member"} where role is null`);
  },
  down() {
    // noop: the old rows had no role, so there is nothing to put back
  },
});

A migration without up or without down is a type error. Loading a file that lacks one (for example a JavaScript file, or a cast) fails with a MigrationError that names the file and the missing function. Nothing runs then: not egon migrate, egon rollback or egon migrations.

down lists the undoing operations in the reverse order of up: drop what up created, and recreate what it dropped. m.dropTable, table.remove and m.dropConstraint take only names. So to bring back what up removed, down defines it again: the column's type, the table's columns or the constraint's check.

Operations ​

The createTable and alterTable callbacks receive a table builder. These pages call it table rather than t, so it does not shadow the t imported from egon-orm.

OperationOptions
m.createTable(name, (table) => …, options?)schema, ifNotExists, primaryKey: false
m.alterTable(name, (table) => …, options?)schema
m.dropTable(name, options?)schema, ifExists, cascade
m.renameTable(from, to, options?)schema
m.createIndex(table, columns, options?)unique, name, where, using, include, concurrently, schema, ifNotExists
m.dropIndex(table, columns, options?)unique, name (these and the columns name the index, as in createIndex), concurrently, schema, ifExists
m.createConstraint(table, name, options)check or exclude (a fragment), using, schema
m.dropConstraint(table, name, options?)schema, ifExists
m.execute(sql)a fragment; its interpolations are bound parameters
m.createSchema(name) / m.dropSchema(name)ifNotExists / ifExists, cascade (a database schema)
m.createExtension(name) / m.dropExtension(name)ifNotExists, schema / ifExists

A table must have a primary key: table.id(), or .primaryKey() on its key columns (several make a composite key). Pass { primaryKey: false } for a table that has none on purpose.

Inside alterTable, the column methods add columns, and modify(name, type, { notNull, default }), remove(name) and rename(from, to) change existing ones:

ts
export const profileFields = defineMigration({
  up(m) {
    m.alterTable("users", (table) => {
      table.text("nickname"); //                  add
      table.modify("age", "bigint"); //           change the type
      table.remove("legacy_flag"); //             drop
      table.rename("name", "full_name");
    });
  },
  down(m) {
    m.alterTable("users", (table) => {
      table.rename("full_name", "name");
      table.boolean("legacy_flag"); //            down says what the dropped column was
      table.modify("age", "integer");
      table.remove("nickname");
    });
  },
});

Columns ​

The column methods are named after the column types (DbType): integer, bigint, float, decimal, boolean, text (also string), uuid, binary, date, time, timestamp, timestamptz, json, jsonb, array(name, of) and custom(name, sql). table.add(name, type) takes any type, such as { kind: "decimal", precision: 10, scale: 2 }.

The helpers:

HelperColumn
table.id()"id" bigserial primary key; table.id("id", { type: "integer" }) for serial (the first argument is the column name)
table.references(column, target, options?)a bigint (or type) with a foreign key to the table target (column, onDelete, onUpdate, schema, name)
table.enumText(column, values)text with a check constraint allowing only values
table.timestamps()created_at and updated_at, timestamptz not null default now()

Each returns a column builder: .notNull(), .nullable(), .default(value), .primaryKey(), .unique().

A column default is a constant (a string, number, bigint, boolean, null, or for json and jsonb an object or array) or an expression as a fragment, .default(fragment`now()`). DDL takes no bound parameters, so a constant is written into the statement as a SQL literal: strings with their quotes doubled (on Postgres as an E'…' string with doubled backslashes when they hold a backslash), numbers only when finite. Fragments in DDL (a default, an index predicate, a constraint) may interpolate identifiers with fragment.ident(), but not values.

Index and constraint names ​

Migrations name what they create the way the changeset constraint functions expect, so a database error becomes a changeset error without either side naming anything:

Created byDefault nameMatched by default by
m.createIndex(table, cols, { unique: true }), .unique()<table>_<columns>_keyuniqueConstraint(fields)
table.references(column, …)<table>_<column>_fkeyforeignKeyConstraint, assocConstraint, noAssocConstraint
table.enumText(column, …)<table>_<column>_checkcheckConstraint(field, { name })
m.createIndex(table, cols)<table>_<columns>_index(not a constraint)
ts
class User extends Schema("users", {
  id: t.bigint({ mode: "number" }).primaryKey().dbGenerated(),
  email: t.string(),
}) {
  static changeset(user: User | typeof User, params: unknown) {
    // Looks for users_email_key: the name createIndex("users", ["email"], { unique: true }) gave.
    return cast(user, params, ["email"]).uniqueConstraint("email");
  }
}

export async function signUp(repo: ReturnType<typeof createRepo>, email: string) {
  const result = await repo.insert(User.changeset(User, { email }));
  if (!result.success) return result.error.errorMessages(); // { email: ["has already been taken"] }
  return result.data;
}

A name longer than the database keeps (63 bytes on Postgres) is shortened the way Postgres shortens the names it makes itself, and the changeset defaults match the shortened name. Pass name to choose your own; then give the same name to the changeset function.

ts
export const searchIndexes = defineMigration({
  transaction: false, // CONCURRENTLY cannot run inside a transaction
  up(m) {
    m.createIndex("posts", ["author_id", "created_at"]); // posts_author_id_created_at_index
    m.createIndex("posts", ["tags"], { using: "gin", concurrently: true });
    m.createIndex("users", ["email"], {
      unique: true,
      where: fragment`deleted_at is null`,
      name: "users_live_email_key",
    });
    m.createIndex("users", [fragment`lower(${fragment.ident("email")})`], {
      name: "users_lower_email_index",
    });
  },
  down(m) {
    // dropIndex takes the table, the columns and unique or name, which name the index.
    m.dropIndex("users", [fragment`lower(${fragment.ident("email")})`], { name: "users_lower_email_index" });
    m.dropIndex("users", ["email"], { name: "users_live_email_key" });
    m.dropIndex("posts", ["tags"], { concurrently: true });
    m.dropIndex("posts", ["author_id", "created_at"]);
  },
});
ts
export const bookingRules = defineMigration({
  up(m) {
    m.createExtension("btree_gist");
    m.createConstraint("bookings", "bookings_nights_check", { check: fragment`nights > 0` });
    m.createConstraint("bookings", "bookings_room_during_excl", {
      exclude: fragment`room with =, during with &&`,
      using: "gist",
    });
  },
  down(m) {
    m.dropConstraint("bookings", "bookings_room_during_excl"); // by name
    m.dropConstraint("bookings", "bookings_nights_check");
    m.dropExtension("btree_gist");
  },
});

How the runner works ​

  • Order. Pending migrations run oldest version first. A pending migration older than the latest applied one (merged from another branch) is applied too, with a warning.

  • Transactions. Each migration runs in its own transaction where the database has transactional DDL (Postgres and SQLite do); if it fails, it is rolled back and the run stops. With transaction: false it runs without one, and a failure can leave it partly applied. MySQL commits after every DDL statement, so there a migration that fails midway is always left partly applied and is not recorded: fix the database by hand (or write migrations of one DDL statement each) before running again.

  • The lock. The runner holds a lock for the whole run (on Postgres a session-level advisory lock keyed by the tracking table, on MySQL GET_LOCK with a name derived from it), so two deploys that start at once take turns: the second waits, then finds nothing left to do. lockTimeoutMs makes it give up instead.

  • The lock on SQLite. SQLite has no lock outside a transaction, so the runner holds a write transaction (begin immediate) for the whole run, and each migration is a savepoint inside it. While it runs, every other writer to the file waits until the run ends: another runner, in this process or another, tries again every 25 ms until lockTimeoutMs, and other writes to the file fail with a retryable SQLITE_BUSY once their busy_timeout runs out. Keep migration runs out of busy periods.

  • transaction: false on SQLite. Some statements cannot run in a transaction at all (vacuum), and pragma foreign_keys = off is ignored inside one, which the table rebuild needs (copy, drop, rename: the way to change a column on SQLite). Such a migration runs outside the lock:

    1. the runner records a claim (a row with version -1 in the tracking table) and commits;
    2. it runs the migration with no transaction;
    3. it takes the lock again, removes the claim and records the version.

    Another runner that gets the lock while the claim is there gives it back and waits, so two runners never both run it.

    If the process dies in step 2, the claim stays behind:

    • egon migrations (status()) lists it as claimed, version -1;
    • a later run warns once (a warning event, which egon migrate prints), then waits for the claim to go;
    • with lockTimeoutMs, it gives up instead.

    Check what the migration did, then delete the row with version -1; a waiting run then carries on.

    After such a migration, whether it succeeded or failed, the runner:

    • rolls back a transaction the migration left open (a begin without a commit is a failure);
    • puts foreign_keys back as it was before, since the adapter's one handle is shared with the application.

    A failure is reported as "failed inside a transaction of its own, which was rolled back" when the migration's own transaction was undone, and otherwise as "ran without a transaction": statements before the failing one stay applied.

    A table rebuild follows SQLite's own order:

    1. foreign keys off, outside any transaction;
    2. the rebuild and its check inside begin … commit;
    3. foreign keys on.

    A row left without its parent fails the check, and the whole rebuild is rolled back:

    ts
    // migrations/20261101090000_require_post_title.ts (SQLite)
export const requirePostTitle = defineMigration({
  // SQLite's own order: foreign keys off outside a transaction (the pragma does nothing
  // inside one), then the rebuild and its check in a transaction, so that a row left
  // without its parent rolls the whole rebuild back.
  transaction: false,
  up(m) {
    m.execute(fragment`pragma foreign_keys = off`);
    m.execute(fragment`begin`);
    m.execute(fragment`create table posts_new (id integer primary key, title text not null default '')`);
    m.execute(fragment`insert into posts_new (id, title) select id, coalesce(title, '') from posts`);
    m.execute(fragment`drop table posts`);
    m.execute(fragment`alter table posts_new rename to posts`);
    // pragma foreign_key_check lists the rows that break a foreign key: counting them into a
    // column that must be 0 fails the statement, and the runner rolls the transaction back.
    m.execute(fragment`create temp table fk_violations (n integer check (n = 0))`);
    m.execute(fragment`insert into fk_violations select count(*) from pragma_foreign_key_check`);
    m.execute(fragment`drop table fk_violations`);
    m.execute(fragment`commit`);
    // The adapter's one handle keeps the pragma: the runner also puts it back, even on failure.
    m.execute(fragment`pragma foreign_keys = on`);
  },
  down() {
    // noop: the stricter column can stay when this migration is rolled back
  },
});

  • Tracking. Applied versions are rows of schema_migrations (version bigint primary key, created_at timestamp(0)), created by the first up or down that is not a dry run (status and dry runs never create it), with created_at in UTC.

  • Missing files. A recorded version with no migration file shows as missing. Rolling back past it is refused until the file is restored.

  • Dry run. up({ dryRun: true }) and down({ dryRun: true }) return the SQL of each migration and change nothing, not even the tracking table.

Zero-downtime changes ​

  • Build indexes on large tables with concurrently: true. It cannot run inside a transaction, so the migration needs transaction: false (the runner refuses otherwise). Keep such a migration to that one operation: without a transaction, a failure leaves the earlier statements applied.
  • Add a column as nullable (or with a default), backfill it in a migration of its own (its down a // noop), and add notNull in a later one.
  • Rename in steps: add the new column, write to both, then drop the old one.

Running in CI and deploys ​

Run egon migrate before the new code starts. It exits with 0 on success and 1 on a failure, prints a line per migration with its duration, and with --json prints one JSON object instead. Several instances may run it at once; the lock serialises them.

Loading migration files ​

Migration files are loaded with import(). TypeScript files need a process that can load TypeScript: Node 22.18 or later strips types by itself; on 22.12 to 22.17 turn it on with NODE_OPTIONS=--experimental-strip-types npx egon … (or node --experimental-strip-types for a script of your own). Node's type stripping does not map ./db.js to ./db.ts, so a migration that imports your own files the way TypeScript writes them needs tsx instead (NODE_OPTIONS="--import tsx" npx egon …, or node --import tsx), or Bun. Without a loader, the error says what to install. A file exports its migration as migration (export const migration = defineMigration(…), as the file that egon migrate create writes does); the default export works too, and a file that exports both runs migration.

For bundlers and deployments without a file system, pass the migrations as a list:

ts
const migrator = createMigrator({
  adapter: postgres({ connectionString: process.env.DATABASE_URL }),
  migrations: [
    { version: 20261012093000, name: "create_users", migration: createUsers },
    { version: 20261013080000, name: "backfill_roles", migration: backfillRoles },
  ],
});

Adopting an existing database ​

To start using migrations on a database that already has its tables, write a first migration that describes them (egon migrate create initial gives you the empty file), and record it as applied without running it. On a database that has never been migrated the tracking table does not exist yet, so create it first, as the runner would:

ts
// Once, by hand or in a script: record 20261001000000_initial as applied without running it.
await adapter.execute(
  "create table if not exists schema_migrations (version bigint primary key, created_at timestamp(0))",
  [],
);
await adapter.execute(
  "insert into schema_migrations (version, created_at) values ($1, now() at time zone 'utc')",
  ["20261001000000"],
);

If the database already has a schema_migrations table from another tool, with a version bigint column, the runner reads it as it is. Its versions count as applied; name its timestamp column if it is not created_at:

ts
const migrator = createMigrator({
  adapter: postgres({ connectionString: process.env.DATABASE_URL }),
  directory: "./migrations",
  table: "schema_migrations", // the existing tracking table
  timestampColumn: "inserted_at", // its timestamp column, if not created_at
});

Good to know

  • Columns allow NULL unless you call .notNull(), as in SQL. (Schema fields are the other way round: non-null unless .nullable().)
  • The column method timestamp is a timestamp without a time zone and timestamptz one with it; the schema field t.timestamp() is stored as timestamptz, and t.json() as jsonb.
  • Nothing is inferred from your schema classes when migrations run: a migration describes the database, and the schema classes describe your code.
  • Rolling back runs down as you wrote it: a down that drops a table drops its rows too.