Migrations

Migrations carry schema changes from your entities to the database. Edit an entity, let UQL diff it against the database, review the file it writes, then apply it:

npx uql-migrate generate:entities add_user_nickname
npx uql-migrate up

If the database came first, UQL can write the entities from it.

Configuration

The CLI reads the same uql.config.ts as your app (or the file given with -c). The engine comes from the pool, and the naming strategy applies to migrations too.

uql.config.ts

import type { Config } from 'uql-orm';
import { PgQuerierPool } from 'uql-orm/postgres';
import { User, Post } from './entities';

export default {
  pool: new PgQuerierPool({ connectionString: process.env.DATABASE_URL }),
  entities: [User, Post],
  migrationsPath: './migrations',
} satisfies Config;

Running the CLI

On Node, install tsx so the CLI can load a TypeScript config with decorators. On Bun, pass --bun so Bun’s own drivers (BunSqlQuerierPool, bun:sqlite) work. A uql.config.js needs neither.

npm install -D tsx && npx uql-migrate up   # Node
bun run --bun uql-migrate up                # Bun

Command	What it does
generate:entities <name>	Writes a migration from the diff between your entities and the database.
generate <name>	Creates an empty migration (alias create).
generate:from-db	Writes @Entity classes from the database.
up / down	Applies all pending migrations / reverts the last one.
status / pending	Lists run and pending migrations / only pending ones.
sync	Applies the diff with no migration file.
drift:check	Reports where the database and the entities disagree.
types	Writes a .d.ts for an entity defined at runtime.

npx uql-migrate --help lists the flags (up --step, down --to, down --all, generate:from-db -o). A failed migration exits with code 1.

From entities to the database

generate:entities lists every column it drops or retypes, so read the file before up. On MongoDB it writes collections, indexes and validators (from checks and enums); see MongoDB migrations.

Renames, retypes and what loses data

A column is renamed only when the names differ by case and underscores (firstName and first_name), as a change of naming strategy makes them; data, indexes and foreign keys survive. Any other rename is a drop and an add. When the two columns match but for their names, generate:entities warns and prints the line to write instead, renameColumn('posts', 'title', 'headline'). A new table matching one no entity names gets a renameTable('articles', 'posts') hint the same way. A property whose column is pinned (@Field(f.string().name('title'))) can be renamed freely.

On Postgres-family engines a retyped column converts its values (USING "code"::INTEGER).

Making a column required fills its nulls with its default. With no default and rows present, generate:entities and sync stop and report how many rows lack a value: add a default, or add the column as nullable, fill it, then require it. The rollback of a drop can’t count rows, so it adds a required column with no default back as nullable, and generate:entities warns: fill it and require it in down if the rollback must restore it.

Syncing without a migration file

For a database whose data you can lose (a prototype, a test database), sync applies the diff directly:

npx uql-migrate sync --dry-run   # print the DDL only
npx uql-migrate sync

sync only adds, so it is safe to rerun. --unsafe also drops and alters, and --force drops and recreates every mapped table. --dry-run writes only SQL to stdout, so you can append it to a migration file another tool applies, as on Cloudflare D1.

From code, for a dev server or test suite:

import { Migrator } from 'uql-orm/migrate';

await new Migrator(pool, { entities: [User, Post] }).sync({ logging: true });

planSync() returns the statements instead, and sync({ entity }) applies one entity, for a runtime entity.

Hand-written migrations

generate <name> creates an empty migration for backfills, views and anything else a diff cannot express. Write it with the migration builder, which runs on every engine and supports backfills, or in plain SQL.

Every .ts, .js or .mjs file in migrationsPath is a migration named after its file. Keep shared helpers outside that directory and import them with their extension.

Plain SQL

A migration exports up and down, one statement per run:

migrations/20260731120000_add_articles.ts

import type { SqlQuerier } from 'uql-orm';

export default {
  async up(querier: SqlQuerier): Promise<void> {
    await querier.run /*sql*/ `
      CREATE TABLE "articles" (
        "id" BIGSERIAL PRIMARY KEY,
        "title" VARCHAR(200)
      )
    `;
  },
  async down(querier: SqlQuerier): Promise<void> {
    await querier.run /*sql*/ `DROP TABLE "articles"`;
  },
};

A ${value} in the template is bound, as anywhere else. The /*sql*/ comment is what es6-string-html highlights as SQL, and generated migrations carry it too; sql`...` is highlighted without it. Add transaction: false beside up and down for a statement that cannot run in a transaction, such as CREATE INDEX CONCURRENTLY. On MongoDB the module takes a MongoQuerier.

Applying migrations from code

An app can migrate before it starts serving:

import { Migrator } from 'uql-orm/migrate';

await new Migrator(pool, { migrationsPath: './migrations' }).up();

up() resolves with what it ran ({ name, direction, duration }) and takes { step } or { to: name }. down() reverts the last migration and takes the same options; { step: Infinity } reverts all. status() resolves with { pending, executed }.

Every file a run needs loads before any of them runs, so an import error leaves the database untouched, and down refuses a recorded migration whose file is gone. Each migration runs in its own transaction with its journal record. If one fails, the call rejects: earlier migrations stay applied, and the rest stay pending.

Some engines keep half a migration

MySQL, MariaDB and CockroachDB commit schema changes as they run, and MongoDB and transaction: false migrations run outside a transaction. A failure there keeps the earlier statements but records nothing. Keep a data change and the schema change it needs in separate migrations, or make them safe to rerun.

Several instances at once

up and down hold a lock named after the journal table, so when several instances start together one migrates and the rest wait, then find nothing to do. status never waits. The wait is capped by lockTimeout (milliseconds, 5 minutes by default).

Postgres, MySQL, MariaDB and SQL Server lock natively, and a dropped connection releases the lock. The other engines write a uql/lock record into the journal instead. A process killed mid-run leaves that record behind and later runs time out on it; delete it once you are sure nothing is migrating.

From a database to entities

npx uql-migrate generate:from-db --output ./src/entities
npx uql-migrate drift:check

Set the naming strategy first if your columns are snake_case. Fields follow the entity docs: id! for the key, title! for NOT NULL, status? for a column with a default, note?: string | null for a nullable one. A default the database computes becomes currentTimestamp or sql. To move off another ORM gradually, see switching to UQL.

Relations, when scaffolding

A foreign key becomes a @ManyToOne and its @OneToMany, or a one-to-one when it is unique. Tables without foreign key constraints get no relations.

Drift detection

drift:check reports missing tables, columns, checks and triggers, and type mismatches that could truncate data, as critical. Missing or changed indexes, extra columns, stale checks or triggers, and differing ON DELETE/ON UPDATE actions are warnings. It does not run on MongoDB; use sync --dry-run there.

How the diff works

The diff touches only the tables your entities name, so other apps sharing the database are safe. It treats equivalent types (INT, INTEGER) as equal. f.number().autoIncrement() is a BIGINT, a foreign key column takes its target key’s exact type, and sync never alters a primary key.

Indexes

Indexes are diffed like columns. A removed index is dropped only if UQL named it or the entity still claims its name, so hand-made indexes survive. See Indexes.

SQLite rebuilds the table

SQLite, libSQL, Turso and D1 cannot alter a column’s type, nullability or default, a key, a foreign key or a check, so the migration rebuilds the table, keeping its rows, indexes, triggers and checks. Where foreign keys cannot be turned off (D1, remote libSQL or Turso), rebuilding a referenced table fails with turn foreign keys off to rebuild <table> instead of losing rows.