> 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

# Migration builder

> Write hand-made migrations once, as tables and columns, and run them on every engine.

Source: https://uql-orm.dev/migrations/builder

The migration builder describes a hand-written migration as tables and columns instead of [SQL strings](https://uql-orm.dev/migrations.md#plain-sql), 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`.

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

## Column types

Each column method takes the name, then the [options](#column-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](#default-expressions) |
| `vector` | Takes `dimensions`, for [semantic search](https://uql-orm.dev/querying/semantic-search.md) |

Both timestamp types hold milliseconds. `createdAt()`, `updatedAt()` and `timestamps()` add `timestamptz` columns defaulting to `currentTimestamp`, named exactly `createdAt` and `updatedAt`: [naming strategies](https://uql-orm.dev/naming-strategy.md) do not apply to the builder.

Composite keys and constraints sit beside the columns:

```typescript
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](#default-expressions) |
| `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`](https://uql-orm.dev/entities/basic.md#field-options).

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`](https://uql-orm.dev/entities/basic.md#values-the-database-computes)) or [`sql`](https://uql-orm.dev/querying/raw-sql.md) SQL:

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

```typescript
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:

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

```typescript
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.