Migrations

Database Migrations

UQL takes an Entity-First approach: you modify your TypeScript entity classes, and UQL auto-generates the migration files for you.

Your entities are the single source of truth

No need to write DDL manually. UQL diffs your entities against the live database and generates the exact SQL needed. The only thing you maintain is your entity classes; UQL handles everything else.

# 1. Update your entity (add a field, change a type, add a relation...)
# 2. Auto-generate the migration
npx uql-migrate generate:entities add_user_nickname

# 3. Review and apply
npx uql-migrate up

The generated migration contains the ALTER TABLE statements derived from the diff, so entities and migrations cannot drift apart. Manual migrations for data backfills or custom SQL are also supported (see below).

Generated files and custom generators

generate:entities writes plain SQL migrations, one querier.run(...) per statement. If you provide a custom SchemaGenerator, its create-table helpers return string[] for the same reason: one string per statement.

1. Unified Configuration

Reuse the same uql.config.ts for both your application bootstrap and the CLI. This ensures your app and migrations share the same settings (like Naming Strategies).

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 in Config: migrations and uql-migrate infer the database kind from pool.dialect.dialectName. On QuerierPool, the dialect instance is exposed as dialect (older releases used dialectInstance). The CLI validates that the default export looks like a real pool (getQuerier, transaction, withQuerier, and a dialect) via assertCliConfig from uql-orm/migrate.

By default, the CLI looks for uql.config.ts in the project root, but you can specify a custom path using the --config / -c flag.

2. Manage via CLI

Use the CLI to manage your database schema evolution.

Command	Description
generate <name>	Creates an empty timestamped file for manual SQL migrations (e.g., data backfills).
generate:entities <name>	Auto-generates a migration by diffing your entities against the current DB schema.
generate:from-db	Scaffolds Entities from an existing database. Includes Smart Relation Detection.
drift:check	Drift Detection: Compares your defined entities against the actual database schema and reports discrepancies.
up	Applies all pending migrations.
down	Rolls back the last applied migration batch.
status	Shows which migrations have been executed and which are pending.
pending	Lists only the migrations still to be applied.
sync	Applies the entity schema to the database (--dry-run to print it, --unsafe to allow drops, --pull to go the other way).

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

Common Workflows

Start a new project from scratch:

# 1. Define your entities in TypeScript
# 2. Auto-generate the initial migration
npx uql-migrate generate:entities initial_schema

# 3. Apply it
npx uql-migrate up

Evolve an existing schema (add a field to your entity, then generate the diff):

# You added @Field({ type: String }) nickname?: string to User entity
npx uql-migrate generate:entities add_user_nickname

# Review the generated migration, then apply
npx uql-migrate up

Adopt UQL on an existing database:

# Scaffold entities from your live DB
npx uql-migrate generate:from-db --output ./src/entities

# Check if entities match the DB
npx uql-migrate drift:check

Manual migration for data backfills or custom SQL:

npx uql-migrate generate seed_default_roles
# Edit the generated file, then apply
npx uql-migrate up

Day-to-day commands:

# Check migration status
npx uql-migrate status

# Rollback the last batch
npx uql-migrate down

# Use a custom config path
npx uql-migrate up --config ./configs/uql.config.ts

Bun Users

If you are using Bun’s native SQL drivers, use BunSqlQuerierPool in your configuration to avoid installing external driver packages like pg or mysql2:

uql.config.ts

import type { Config } from 'uql-orm';
import { BunSqlQuerierPool } from 'uql-orm/bunSql';

export default {
  pool: new BunSqlQuerierPool({ url: process.env.DATABASE_URL! }),
  // entities, migrationsPath, ...
} satisfies Config;

The pool constructor takes Bun’s SQL.Options (e.g. { url }, { adapter, hostname, ... }, or SQLite filename). The migrator reads the dialect id from pool.dialect.dialectName.

When running migrations, use the --bun flag so Bun’s runtime handles TypeScript resolution and native driver loading:

bun run --bun uql-migrate up

Or add a script to your package.json: "uql": "bun run --bun uql-migrate status".

3. Entity-First Synchronization (Development)

In development, you can use autoSync to automatically keep your database in sync with your entities without manual migrations. It uses the Schema AST engine to perform graph-based comparison and is safe by default, meaning it only adds missing tables and columns while blocking any destructive operations (column drops or type alterations) to prevent data loss.

Entities must be imported first

autoSync only sees entities that have been loaded, so import them before calling it.

Using Your Config (Recommended)

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.autoSync({ logging: true });

Explicit Entities

import { Migrator } from 'uql-orm/migrate';
import { User, Profile, Post } from './entities/index.js';
import { pool } from './uql.config.js';

const migrator = new Migrator(pool, {
  entities: [User, Profile, Post],
});
await migrator.autoSync({ logging: true });

Advanced Capabilities

The synchronization engine is built on a Schema AST (Abstract Syntax Tree) that treats your database schema as a graph, not just a list of tables.

1. Schema AST Engine

• Graph-Based Diffing: Handles circular dependencies and ensures correct topological sort order when creating or dropping tables.
• Dialect-Aware Comparison: Avoids “phantom diffs” by understanding equivalences between dialect-specific types (e.g., INTEGER vs INT).

2. Smart Relation Detection

When scaffolding entities from an existing database (generate:from-db), UQL automatically detects relationships by analyzing your schema:

• Explicit Foreign Keys: Standard foreign keys are mapped to @ManyToOne on the owning side and @OneToMany on the other.
• One-to-One Relations: Detected when a foreign key column also has a unique constraint.

Relations are read from the foreign keys the database reports, so a table with no foreign key constraints scaffolds without relations - junction tables come out as plain entities, and columns named like user_id are scaffolded as columns.

3. Drift Detection

Ensure production safety with drift:check. It compares your TypeScript entity definitions against the actual running database and reports:

• Critical: Missing tables or columns, type mismatches that risk data truncation.
• Warning: Missing indexes, unexpected columns, and indexes that exist under the right name but no longer match what the entity declares.

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.

4. Bidirectional Index Sync

Indexes travel in both directions:

• 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 entity whose table already exists is created on the next sync; one the entity never declared is left alone, since it may well have been created deliberately outside the ORM.
• DB -> Entity: generate:from-db writes back the index name, its columns and whether it is unique - single-column ones as @Field({ index }), multi-column as @Index([...]). An expression or a partial predicate has no @Index written for it yet.
• Drift: drift:check compares as much of an index as the engine will report. Postgres and CockroachDB report expressions, partial predicates, access method, INCLUDE columns and operator classes, so editing an expression in place or adding a where shows up. MySQL, MariaDB and SQLite report only columns and uniqueness, and nothing more is compared there: drift no migration can fix is noise, not a warning.

Other Features

• 64-bit Primary Keys: Auto-increment primary keys use BIGINT across all dialects for TypeScript number compatibility.
• SQLite STRICT Mode: Tables generated for SQLite, LibSQL, and Cloudflare D1 use STRICT mode by default.
• Safe Primary Keys: Primary keys are immune to automated alterations during autoSync.
• Foreign Key Inheritance: Foreign key columns automatically inherit the exact SQL type of their referenced primary keys.

---

Plain SQL Migrations

You do not have to use the builder. A migration is a module exporting up/down, so writing the SQL yourself is a first-class option - and it is exactly what generate:entities produces:

migrations/20260731120000_add_articles.ts

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

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

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

One statement per run call, and the whole migration runs in a transaction on engines that support transactional DDL. 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 yourself 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 API

When writing manual migrations (via generate), you have access to a fluent, type-safe API for defining your schema.

Real-World Example

A typical migration that adds a new table with relationships and modifies an existing one:

import { defineBuilderMigration, t } from 'uql-orm/migrate';

export default defineBuilderMigration({
  async up(m) {
    // Create a new table
    await m.createTable('articles', (table) => {
      table.id();                                         // BIGINT auto-increment PK
      table.string('title', { length: 200 });             // VARCHAR(200) NOT NULL
      table.string('slug', { length: 200, unique: true });
      table.text('body');                                  // TEXT NOT NULL
      table.boolean('published', { defaultValue: false });
      table.timestamp('published_at', { nullable: true });
      table.timestamp('created_at', { defaultValue: t.now() });

      // Foreign key to users table
      table.integer('author_id', {
        references: { table: 'users', column: 'id', onDelete: 'CASCADE' },
      });

      // Composite index for common queries
      table.index(['published', 'created_at']);
    });

    // Modify an existing table
    await m.alterTable('users', (table) => {
      table.addColumn((c) => c.text('bio'));
      table.addColumn((c) => c.string('avatar_url', { length: 500, nullable: true }));
      table.addIndex(['email']);
    });
  },

  async down(m) {
    // Reverse in opposite order
    await m.alterTable('users', (table) => {
      table.dropIndex('idx_users_email');
      table.dropColumn('avatar_url');
      table.dropColumn('bio');
    });

    await m.dropTable('articles');
  },
});

Comprehensive Column Types

import { defineBuilderMigration, t } from 'uql-orm/migrate';

export default defineBuilderMigration({
  async up(m) {
    await m.createTable('all_types_demo', (table) => {
      // --- Numeric Types ---
      table.id();                                              // Auto-incrementing PK (BigInt)
      table.integer('user_age', { nullable: true });
      table.smallint('status_id', { defaultValue: 0 });
      table.bigint('view_count', { defaultValue: 0n });
      table.float('rating');
      table.double('precise_score');
      table.decimal('price', { precision: 10, scale: 2 });

      // --- String Types ---
      table.string('username', { length: 50, unique: true }); // VARCHAR(50)
      table.string('email');                                   // VARCHAR(255) by default
      table.char('country_code', { length: 2 });
      table.text('bio');

      // --- Boolean ---
      table.boolean('is_active', { defaultValue: true });

      // --- Date & Time ---
      table.date('birth_date');
      table.time('daily_alarm');
      table.timestamp('created_at', { defaultValue: t.now() });
      table.timestamptz('updated_at');

      // --- JSON & Advanced ---
      table.json('settings');
      table.jsonb('metadata');                                 // Binary JSON (Postgres)
      table.uuid('external_id', { defaultValue: t.uuid() });
      table.blob('file_data');
      table.vector('embedding', { dimensions: 1536 });        // Vector for AI/ML

      // --- Relationships ---
      table.integer('author_id', {
        references: {
          table: 'users',
          column: 'id',
          onDelete: 'CASCADE',
          onUpdate: 'NO ACTION',
        },
      });

      // --- Composite Constraints ---
      table.unique(['username', 'email']);
      table.index(['is_active', 'created_at']);
      table.comment('A comprehensive demo table');
    });
  },

  async down(m) {
    await m.dropTable('all_types_demo');
  },
});

Alter Table Operations

await m.alterTable('users', (t) => {
  // Add columns
  t.addColumn((c) => c.string('nickname', { length: 100 }));

  // Drop columns
  t.dropColumn('legacy_field');

  // Rename columns
  t.renameColumn('full_name', 'name');

  // Alter column type
  t.alterColumn((c) => c.string('email', { length: 300 }));

  // Indexes
  t.addIndex(['nickname']);
  t.dropIndex('idx_users_old_name');

  // Foreign keys
  t.addForeignKey(['profile_id'], {
    table: 'profiles',
    columns: ['id'],
  });
  t.dropForeignKey('fk_users_legacy');
});

// Raw SQL (escape hatch)
await m.raw('CREATE VIEW active_users AS SELECT * FROM users WHERE is_active = true');

Index Options Reference

t.index(...), t.unique(...), t.addIndex(...) and m.createIndex(...) take the same entries and options as the @Index decorator: a column name, raw('...') for an expression, or an object with per-column modifiers, plus type, where, include and the vector tuning.

import { raw } from 'uql-orm';
import { defineBuilderMigration } from 'uql-orm/migrate';

export default defineBuilderMigration({
  async up(m) {
    await m.createTable('notes', (table) => {
      table.id();
      table.string('email', { length: 200 });
      table.text('body');
      table.timestamp('deleted_at', { nullable: true });

      // Case-insensitive uniqueness over live rows only
      table.unique([raw('lower("email")')], { name: 'uq_notes_email', where: '"deleted_at" IS NULL' });
      // MySQL and MariaDB require a prefix length to index TEXT at all
      table.index([{ column: 'body', length: 64 }]);
    });

    await m.createIndex('notes', ['deleted_at'], { name: 'idx_notes_deleted', type: 'btree' });
  },

  async down(m) {
    await m.dropTable('notes');
  },
});

Options an engine cannot express throw when the migration runs, naming the index, exactly as they do for entity-defined indexes.

Column Options Reference

All column methods accept an optional settings object:

Option	Type	Default	Description
nullable	boolean	false	Allow NULL values? (Default is NOT NULL)
defaultValue	any	undefined	Default value (use t.now(), t.uuid() for expressions)
unique	boolean	false	Add a unique constraint
primaryKey	boolean	false	Mark as primary key
autoIncrement	boolean	false	Enable auto-increment (integers only)
index	boolean | string	false	Create an index (bool=auto-name, string=custom-name)
comment	string	-	Database comment for the column
references	object	-	Define Foreign Key (see examples above)

---

Check out the getting started guide for more details on setting up your project.