Migrations

Entities and tables have to agree, and UQL can start from either one:

• You write the entities. UQL diffs your TypeScript classes against the live database and generates the migration SQL. No DDL by hand. See from entities to the database.
• The database already exists. UQL reads its tables, foreign keys and indexes and writes the @Entity classes for you. See from a database to entities.

Only the first step differs. Once the classes exist, they are the schema: every later change starts in an entity and reaches the database through a migration.

# 1. Edit an entity: add a field, change a type, add a relation
# 2. Generate the migration from the diff
npx uql-migrate generate:entities add_user_nickname

# 3. Review the file, then apply it
npx uql-migrate up

Because the SQL comes out of the diff, entities and migrations cannot drift apart. On an empty database you can skip the file and apply the diff directly, and hand-written SQL stays available for data backfills and anything a diff cannot express.

Configuration

One uql.config.ts serves both your application bootstrap and the CLI, so migrations run under the same settings as your queries, naming strategy included.

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({
    host: 'localhost',
    user: 'theUser',
    password: 'thePassword',
    database: 'theDatabase',
  }),
  entities: [User, Post],
  migrationsPath: './migrations',
} satisfies Config;

There is no top-level dialect field: the CLI infers the engine from the pool you export, reading pool.dialect.dialectName. It looks for uql.config.ts in the project root unless you pass --config / -c.

Under Bun, export a BunSqlQuerierPool and run the CLI with --bun, so Bun resolves the TypeScript and loads its native drivers:

bun run --bun uql-migrate up

CLI commands

Writing schema changes:

Command	Description
generate:entities <name>	Diffs your entities against the database and writes the migration.
generate <name>	Creates an empty timestamped file for SQL you write yourself, such as a data backfill. Aliased as create.
generate:from-db	Scaffolds @Entity classes from an existing database, relations included.

Applying and inspecting:

Command	Description
up	Applies all pending migrations.
down	Rolls back the last applied migration batch.
status	Shows which migrations have run and which are pending.
pending	Lists only the migrations still to be applied.
sync	Applies the entity schema directly to the database, with no file in between.
types	Writes a .d.ts for the registered entities, for a schema defined at runtime.
drift:check	Reports where the database and the entities disagree.

Each command takes flags (up --step, down --all, sync --dry-run, generate:from-db -o); run npx uql-migrate --help for the full list.

From entities to the database

This is the everyday direction. You change a class, generate:entities turns the diff into a migration file, and up applies it:

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

The file holds the CREATE TABLE and ALTER TABLE statements the diff produced, in the dialect your pool speaks. It lands in migrationsPath for you to read before it reaches a database, and down takes it back out.

Syncing without a migration file

While the schema is still moving and the data is disposable (a prototype, a test database, a local container), sync applies the same diff directly:

# Print the DDL your entities imply, without touching the database
npx uql-migrate sync --dry-run

# Apply it: creates the missing tables, columns, indexes, and foreign keys
npx uql-migrate sync

It is additive: it creates what the entities declare and is missing, and refuses the destructive half of the diff (column drops, type alterations, dropping or altering a foreign key), so it is safe to re-run as the entities grow. --unsafe allows those, and --force drops and recreates every table your entities map, for resetting a scratch database only.

Once there are rows you would miss, stop syncing in place and generate the migration instead: the same diff, but written to a file you review in the pull request and can roll back.

From code, without the CLI

Migrator.sync is the same engine the CLI calls, for a dev server or a test suite that sets its own schema up:

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

const migrator = new Migrator(config.pool, {
  entities: config.entities,
});

// Automatically add missing tables and columns
await migrator.sync({ logging: true });

sync({ entity }) applies one entity instead of every registered one, which is the path a runtime schema takes, and planSync(options) returns the statements without running them, as --dry-run prints them.

Pass entities explicitly ([User, Profile, Post]) if the migrator does not share your uql.config.ts. Either way the classes have to be imported for sync to see them: an entity nothing references is an entity it will not create.

From a database to entities

When the tables came first (an existing product, another ORM, a schema someone else owns), point the CLI at the database and it writes the @Entity classes, then checks them back against what it read:

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

That is a one-time step. From here the entities are the schema like any other, and everything above applies unchanged. If your columns are snake_case and your code is camelCase, set the naming strategy in uql.config.ts before scaffolding, so the mapping covers the generated classes and your queries alike. Replacing another ORM as you go is covered at length in switching to UQL.

Relations, when scaffolding

generate:from-db reads relations off the constraints the database reports. A foreign key becomes @ManyToOne on the owning side and @OneToMany on the other; a foreign key that also carries a unique constraint becomes one-to-one.

A table with no foreign key constraints therefore scaffolds without relations: junction tables come out as plain entities, and a column named like user_id comes out as a column.

How the diff works

One engine backs generate:entities, sync and drift:check, and it treats a schema as a graph rather than a list of tables: circular dependencies resolve, and tables are created and dropped in topological order. Types are compared per dialect, so equivalent spellings (INTEGER against INT) do not surface as phantom diffs.

Drift detection

drift:check compares the entities against the running database and reports two levels:

• Critical: missing tables or columns, and type mismatches that risk truncating data.
• Warning: missing indexes, unexpected columns, indexes that exist under the right name but no longer match what the entity declares, and foreign keys whose ON DELETE or ON UPDATE differs from it.

The migrations table is left out of the comparison, since it exists by design and has no entity. Default values are not compared unless asked for: an engine reports a default as it stored it (now(), CURRENT_TIMESTAMP, 'active'::text), which rarely matches the entity’s literal.

Indexes, both directions

Indexes travel each way:

• Entity -> DB: @Field({ index: true }) and @Index([...]) create indexes with everything the engine supports: expressions, prefix lengths, stored order, INCLUDE, operator classes. An index added to an existing table is created on the next sync; one the entity never declared is left alone.
• DB -> Entity: generate:from-db writes back what it reads: expressions, partial predicates, INCLUDE columns, access method and stored order. A plain single-column index becomes @Field({ index }); anything a field cannot express, a unique index included, becomes @Index([...]).
• Drift: drift:check compares an index structurally (columns and their stored order, uniqueness, access method, INCLUDE columns, operator class) as far as the engine reports them. Postgres reports all of it, CockroachDB everything but nulls order and operator class, MySQL, MariaDB, MSSQL and SQLite only columns and uniqueness. Expressions and partial predicates are never compared, since a database reprints them from its parse tree and matching the two spellings would need a SQL parser.

What the generated DDL guarantees

• Auto-increment primary keys are BIGINT on every dialect, so they stay compatible with TypeScript number.
• Tables for SQLite, LibSQL and Cloudflare D1 are created in STRICT mode.
• Primary keys are never altered automatically by sync.
• Foreign key columns inherit the exact SQL type of the primary key they reference.

Plain SQL migrations

A migration is a module exporting up/down, the same shape generate:entities produces, so you can write the SQL yourself:

migrations/20260731120000_add_articles.ts

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

export default {
  async up(querier: SqlQuerier): Promise<void> {
    await querier.run(
      `CREATE TABLE "articles" ("id" BIGSERIAL PRIMARY KEY, "title" VARCHAR(200) NOT NULL)`,
    );
    await querier.run(
      `CREATE INDEX "articles__title_idx" ON "articles" ("title")`,
    );
  },

  async down(querier: SqlQuerier): Promise<void> {
    await querier.run(`DROP TABLE "articles"`);
  },
};

One statement per run call, and the whole migration runs in a transaction on engines that support transactional DDL. A custom SchemaGenerator returns string[] from its create-table helpers for the same reason. Use this for anything the builder does not model (views, triggers, stored procedures, data backfills), or mix the two: m.raw('...') inside a builder migration takes plain SQL.

The trade-off is portability: SQL you write is yours to keep working on every engine you target, while the builder emits the right dialect for each. Files are loaded as modules, so they must be .ts, .js or .mjs; a bare .sql file is not picked up.

Migration builder

Instead of SQL strings, a hand-written migration can define its schema with a fluent, dialect-aware builder: createTable, alterTable, a method per column type, and m.raw() for the rest. See the migration builder reference.