Drizzle ORM

Indexes and Constraints

Indexes, unique constraints, checks, foreign keys and composite keys, written as in Drizzle.

Constraints and indexes go in the table's third argument, a callback that receives the columns and returns an array, with the same names, parameters and chains as Drizzle. They shape the SQL, not the rows you read, so your models do not change.

import * as DZ from '@mionjs/drizzle-orm-pg-core';
import {sql, tableRef} from '@mionjs/drizzle-orm';

export const teams = DZ.pgTable('teams', {
  id: DZ.serial('id', {primaryKey: true}),
  slug: DZ.varchar('slug', {length: 40, notNull: true, unique: true}),
});

export const members = DZ.pgTable(
  'members',
  {
    teamId: DZ.integer('team_id', {notNull: true}),
    userId: DZ.uuid('user_id', {notNull: true}),
    email: DZ.varchar('email', {length: 200, notNull: true}),
    age: DZ.integer('age', {notNull: true}),
    joinedAt: DZ.timestamp('joined_at', {notNull: true, defaultNow: true}),
  },
  (t) => [
    DZ.primaryKey({name: 'members_pk', columns: [t.teamId, t.userId]}),

    DZ.foreignKey({
      name: 'members_team_fk',
      columns: [t.teamId],
      foreignColumns: [tableRef(teams, 'id')],
    })
      .onDelete('cascade')
      .onUpdate('restrict'),

    // ordered, and only over some rows
    DZ.index('members_joined_idx')
      .on(t.joinedAt.desc(), t.email)
      .where(sql`${t.age} >= 18`),

    DZ.uniqueIndex('members_email_uidx').on(t.email),
    DZ.unique('members_team_email_uq').on(t.teamId, t.email).nullsNotDistinct(), // nulls count as equal

    DZ.check('members_age_check', sql`${t.age} >= 0`),
  ]
);

// single-column constraints inline
export const invites = DZ.pgTable('invites', {
  id: DZ.uuid('id', {defaultRandom: true, primaryKey: true}),
  teamId: DZ.integer('team_id', {
    references: [
      () => tableRef(teams, 'id'),
      {
        onDelete: 'cascade',
      },
    ],
  }),
  code: DZ.varchar('code', {
    length: 12,
    notNull: true,
    unique: ['invites_code_uq'],
  }),
});

Index and Constraint Helpers

Helper	What it makes
`index(name)`	a plain index
`uniqueIndex(name)`	a unique index
`unique(name)`	a unique constraint
`primaryKey({name, columns})`	a composite primary key
`foreignKey({name, columns, foreignColumns})`	a foreign key
`check(name, sql)`	a check constraint
`pgPolicy(name, config)`	a row level security policy, PostgreSQL only

Index and Constraint Options

Indexes take the options PostgreSQL takes:

index('idx').on(t.name.desc(), t.age).where(sql`${t.age} >= 18`);
uniqueIndex('idx').using('hnsw', t.embedding.op('vector_l2_ops'));
index('idx').on(t.name).concurrently().with({fillfactor: '70'});

Column position modifiers are asc(), desc(), nullsFirst(), nullsLast() and op(). Foreign keys take onDelete() and onUpdate(). Unique constraints take nullsNotDistinct(). MySQL indexes also take algorithm() and lock().

Inline Column Constraints

A single-column constraint can sit on the column itself:

id: uuid('id', {defaultRandom: true, primaryKey: true}),
code: varchar('code', {length: 12, notNull: true, unique: ['invites_code_uq']}),
teamId: integer('team_id', {references: [() => tableRef(teams, 'id'), {onDelete: 'cascade'}]}),

Referencing Another Table

Name a column on another table with tableRef(table, 'column'), which checks the column name against the table. A table's type holds its shape (the name, the columns, the constraints), not the column objects, which keeps that type small across your app. A table type writes the reference as references: [TableRef<TeamsTable, 'id'>].

import {sql, tableRef} from '@mionjs/drizzle-orm';

integer('team_id', {references: [() => tableRef(teams, 'id')]});
// t.teamId: a column of the table you are declaring, from the callback argument
foreignKey({name: 'members_team_fk', columns: [t.teamId], foreignColumns: [tableRef(teams, 'id')]});
sql`select ${tableRef(teams, 'code')} from ${teams}`;

A column that references its own table needs the return type written out, as in drizzle: references: [(): TableRef<'emps', 'id'> => tableRef(emps, 'id')]. The type takes the table name for the same reason.