> Every UQL docs page, as Markdown: https://uql-orm.dev/llms.txt
> The same docs over MCP: https://uql-orm.dev/mcp
> Before writing UQL code, read the skill: https://uql-orm.dev/.well-known/agent-skills/uql-orm/SKILL.md

# Migrations

> Keep your entities and your database schema in sync with the uql-migrate CLI.

Source: https://uql-orm.dev/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:

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

If the database came first, UQL can [write the entities from it](#from-a-database-to-entities).

## 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](https://uql-orm.dev/naming-strategy.md) applies to migrations too.

```typescript title="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`](https://uql-orm.dev/bun-sql.md), `bun:sqlite`) work. A `uql.config.js` needs neither.

```bash
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](#syncing-without-a-migration-file). |
| `drift:check` | Reports where [the database and the entities disagree](#drift-detection). |
| `types` | Writes a `.d.ts` for an [entity defined at runtime](https://uql-orm.dev/entities/runtime.md#typing). |

`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](https://uql-orm.dev/mongodb.md#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:

```bash
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](https://uql-orm.dev/cloudflare-d1.md#schema-changes).

From code, for a dev server or test suite:

```ts
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](https://uql-orm.dev/entities/runtime.md).

## 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](https://uql-orm.dev/migrations/builder.md), which runs on every engine and supports [backfills](https://uql-orm.dev/migrations/builder.md#data-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`:

```typescript title="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](https://marketplace.visualstudio.com/items?itemName=Tobermory.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:

```ts
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

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

Set the [naming strategy](https://uql-orm.dev/naming-strategy.md) first if your columns are `snake_case`. Fields follow [the entity docs](https://uql-orm.dev/entities/basic.md#required-fields): `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](https://uql-orm.dev/switching-to-uql.md).

### 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](https://uql-orm.dev/entities/indexes.md#synchronization).

### 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.