Migration builder

A migration from uql-migrate generate can define its schema with a fluent builder instead of SQL strings. The builder emits each engine’s own dialect, so one migration runs on Postgres and SQLite unchanged.

A typical migration

Add a table with a relation and a composite index, and modify an existing one:

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

export default defineBuilderMigration({
  async up(m) {
    await m.createTable('articles', (t) => {
      t.id(); // BIGINT auto-increment PK
      t.string('title', { length: 200 }); // VARCHAR(200) NOT NULL
      t.string('slug', { length: 200, unique: true });
      t.text('body');
      t.boolean('published', { defaultValue: false });
      t.timestamp('published_at', { nullable: true });
      t.timestamp('created_at', { defaultValue: expr.now() });
      t.integer('author_id', {
        references: { table: 'users', column: 'id', onDelete: 'CASCADE' },
      });
      t.index(['published', 'created_at']);
    });

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

  async down(m) {
    await m.alterTable('users', (t) => {
      t.dropIndex('users__email_idx');
      t.dropColumn('avatar_url');
      t.dropColumn('bio');
    });

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

Column types

Every method takes the column name first and the options object second.

Method	Notes
id()	Auto-incrementing BIGINT primary key; the name defaults to id
integer
smallint
bigint	Defaults take a BigInt literal (0n)
float
double
decimal	Takes precision and scale
string	VARCHAR, length defaults to 255
char	CHAR, length defaults to 1
text
boolean
date
time
timestamp	Pair with expr.now() as a default
timestamptz	Timestamp with time zone
json
jsonb	Binary JSON on Postgres, json elsewhere
uuid	Pair with a uuid default, which is per-engine
blob
vector	Takes dimensions, for semantic search

createdAt() and updatedAt() add a timestamp defaulting to expr.now(); timestamps() adds both. They name the columns literally, since the builder writes raw table columns and naming strategies only translate entity fields. Spell the names yourself if your tables are snake_case.

Table-level constraints sit alongside the columns. primaryKey and foreignKey take a list, so they declare composite keys; a column-level primaryKey: true or references covers a single column:

await m.createTable('accounts', (t) => {
  t.string('tenant_id', { length: 40 });
  t.string('username', { length: 50 });
  t.string('email');
  t.decimal('balance', { precision: 10, scale: 2 });
  t.uuid('external_id');

  t.primaryKey(['tenant_id', 'username']);
  t.foreignKey(['tenant_id']).references('tenants', ['id']).onDelete('CASCADE');
  t.unique(['username', 'email']);
  t.index(['email']);
  t.comment('Customer accounts');
});

Column methods also chain: t.text('bio').nullable().comment('...') says the same as the options object. Prefer the options object - it is what generated migrations use. Only references('users').onDelete('CASCADE') needs the chain.

Column options

Option	Type	Default	Description
nullable	boolean	false	Allow NULL values
defaultValue	unknown	undefined	Literal value, or an expression
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-names it, string names it)
unsigned	boolean	false	Unsigned numeric type, on MySQL and MariaDB
comment	string	-	Database comment for the column
references	object	-	Foreign key: table, column, onDelete, onUpdate

Note the inversion against @Field, where nullable defaults to true: a builder column is NOT NULL unless you say otherwise, an entity field is nullable unless you say otherwise.

Default expressions

defaultValue formats a literal for you - a string, number, boolean, null, a Date, a JSON object - so pass the value itself, parentheses and escaping included. expr is for the defaults the database evaluates rather than stores:

Helper	Emits	Available
expr.now()	CURRENT_TIMESTAMP	everywhere
expr.currentDate()	CURRENT_DATE	everywhere
expr.currentTime()	CURRENT_TIME	everywhere
expr.uuid()	gen_random_uuid(), UUID()	not SQLite
expr.uuidv7()	uuidv7(), UUID_v7()	Postgres 18+, MariaDB 11.7+
expr.onUpdateNow()	CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP	MySQL, MariaDB
expr.raw(sql)	whatever you write	everywhere

The dialect picks the spelling at generation time, so one migration works on every engine that has the function. Where an engine has none, generation throws instead of emitting SQL the server would reject. Use expr.raw() there.

UQL cannot see server versions, so a Postgres 17 server takes expr.uuidv7() and rejects it itself. Worth that floor if you key on UUIDs: v7 is time-ordered, so inserts stay at the end of the index.

Altering a table

alterTable exposes addColumn, dropColumn, renameColumn and alterColumn for columns, addIndex and dropIndex for indexes, and addForeignKey and dropForeignKey for constraints:

await m.alterTable('users', (t) => {
  t.addColumn((c) => c.string('nickname', { length: 100 }));
  t.dropColumn('legacy_field');
  t.renameColumn('full_name', 'name');
  t.alterColumn((c) => c.string('email', { length: 300 }));

  t.addIndex(['nickname']);
  t.dropIndex('users__old_name_idx');

  t.addForeignKey(['profile_id'], { table: 'profiles', columns: ['id'] });
  t.dropForeignKey('users__legacy_fk');
});

// Escape hatch for anything the builder does not model
await m.raw(
  'CREATE VIEW active_users AS SELECT * FROM users WHERE is_active = true',
);

Each also exists on m with the table named first, for a migration that changes one thing: m.addColumn('users', (c) => c.text('bio')). The remaining table operations live there too:

await m.renameTable('users', 'accounts');
await m.dropTable('legacy_sessions', { ifExists: true, cascade: true });

Index options

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', (t) => {
      t.id();
      t.string('email', { length: 200 });
      t.text('body');
      t.timestamp('deleted_at', { nullable: true });

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

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

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

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