Migration builder

The migration builder describes a hand-written migration as tables and columns instead of SQL strings, and writes each engine’s own SQL, so one file runs on Postgres and SQLite alike. Its types (MigrationBuilder, TableBuilder, AlterTableBuilder, ColumnBuilder, ColumnFactory) are exported from uql-orm/migrate.

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

export default defineBuilderMigration({
  async up(m) {
    await m.createTable('articles', (t) => {
      t.id();
      t.string('title', { length: 200 });
      t.boolean('published', { defaultValue: false });
      t.timestamptz('created_at', { defaultValue: currentTimestamp });
      t.integer('author_id', {
        references: { table: 'users', onDelete: 'CASCADE' },
      });
      t.index(['published', 'created_at']);
    });
  },
  async down(m) {
    await m.dropTable('articles');
  },
});

On MongoDB the builder handles collections and indexes only; see MongoDB migrations.

Column types

Each column method takes the name, then the options. integer, smallint, float, double, text, boolean, date, time, json and blob map to the type they name. The others:

Method	Notes
id()	Auto-increment BIGINT primary key, named id unless you pass a name
bigint	Takes BigInt defaults (0n)
decimal	Takes precision and scale
string	VARCHAR; length defaults to 255
char	CHAR; length defaults to 1
timestamp	No zone: UQL reads and writes UTC, but the database’s clock uses the session’s zone
timestamptz	An instant
jsonb	Binary JSON on Postgres, json elsewhere
uuid	Pair with a uuid default
vector	Takes dimensions, for semantic search

Both timestamp types hold milliseconds. createdAt(), updatedAt() and timestamps() add timestamptz columns defaulting to currentTimestamp, named exactly createdAt and updatedAt: naming strategies do not apply to the builder.

Composite keys and constraints sit beside the columns:

await m.createTable('accounts', (t) => {
  t.string('tenant_id', { length: 40 });
  t.string('username', { length: 50 });
  t.primaryKey(['tenant_id', 'username']);
  t.foreignKey(['tenant_id']).references('tenants', ['id']).onDelete('CASCADE');
  t.unique(['username']);
  t.comment('Customer accounts');
});

Column options

Option	Default	Meaning
nullable	false	Allow NULL
defaultValue	none	A literal or an expression
unique	false	Unique index on the column
primaryKey	false	Primary key
autoIncrement	false	Auto-increment (integers)
index	false	true for an auto-named index, a string to name it
unsigned	false	Unsigned number, on MySQL and MariaDB
comment	none	Column comment
references	none	Foreign key: table, column (default id), onDelete, onUpdate

Builder columns are NOT NULL unless you say otherwise, the opposite of @Field.

The same settings also chain (t.text('bio').nullable()), and so does a foreign key: references('users').onDelete('CASCADE'). Two exist only as chained calls: enum(['draft', 'published']) and computed('...').

Default expressions

A literal defaultValue (string, number, boolean, null, Date, JSON) is quoted for you. For a value the database computes, use the same constants as entities (currentTimestamp, currentDate, currentTime, uuid, uuidv7) or sql SQL:

import { sql, uuid } from 'uql-orm';

t.uuid('id', { primaryKey: true, defaultValue: uuid });
t.bigint('seq', { defaultValue: sql`nextval('orders_seq')` });

Each engine gets its own spelling. On an engine without the function, the migration throws before any SQL is sent. UQL does not check server versions, so uuidv7 on Postgres older than 18 fails on the server.

Altering a table

await m.alterTable('users', (t) => {
  t.addColumn((c) => c.string('nickname', { length: 100, nullable: true }));
  t.renameColumn('full_name', 'name');
  t.alterColumn((c) => c.string('email', { length: 300 }));
  t.dropColumn('legacy_field');
  t.addIndex(['nickname']);
  t.dropIndex('users__old_name_idx');
  t.addForeignKey(['profile_id'], { table: 'profiles', columns: ['id'] });
  t.dropForeignKey('users__legacy_fk');
});

Each of these also exists on m with the table first, such as m.addColumn('users', (c) => c.text('bio')), alongside m.renameTable(from, to), m.dropTable(name, { ifExists, cascade }) and m.raw(sql) for anything the builder does not model.

Data backfills

up and down get the querier as a second argument, in the same transaction as the builder:

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

export default defineBuilderMigration({
  async up(m, querier) {
    await m.addColumn('users', (c) => c.string('slug', { nullable: true }));
    const users = await querier.all<{
      id: number;
      name: string;
    }>`SELECT "id", "name" FROM "users"`;
    for (const { id, name } of users) {
      await querier.run`UPDATE "users" SET "slug" = ${name.toLowerCase()} WHERE "id" = ${id}`;
    }
    await m.alterColumn('users', (c) => c.string('slug'));
  },
  async down(m) {
    await m.dropColumn('users', 'slug');
  },
});

Query tables by name, not through your entities: the entities will change after the migration is written, and the migration must keep working.

Index options

t.index, t.unique, t.addIndex and m.createIndex take the same entries and options as @Index: column names, sql`...` expressions, per-column modifiers, type, where, include and vector tuning. where must be sql, since there is no entity to check it against.

t.unique([sql`lower("email")`], {
  name: 'notes__email_uk',
  where: sql`"deleted_at" IS NULL`,
});
t.index([{ column: 'body', length: 64 }]); // MySQL and MariaDB need a prefix length on TEXT

An option the engine cannot express throws when the migration runs, naming the index.