Unique Constraints and Indexes in Drizzle

Unique Constraints in Drizzle

Unique constraints ensure that no two rows have the same value for a column or combination of columns. Drizzle provides two ways to define them: column-level .unique() chains and table-level unique() constraints.

Single-Column Unique

SQL:

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  email VARCHAR(255) NOT NULL UNIQUE,
  username VARCHAR(50) NOT NULL UNIQUE
);

Drizzle:

import { pgTable, serial, varchar } from "drizzle-orm/pg-core";

export const users = pgTable("users", {
  id: serial("id").primaryKey(),
  email: varchar("email", { length: 255 }).notNull().unique(),
  username: varchar("username", { length: 50 }).notNull().unique(),
});

The .unique() chain on a column builder creates a single-column unique constraint. This is the simplest form and maps directly from the SQL UNIQUE keyword.

Composite Unique Constraint

SQL:

CREATE TABLE team_members (
  team_id INTEGER NOT NULL,
  user_id INTEGER NOT NULL,
  role VARCHAR(50) NOT NULL,
  UNIQUE (team_id, user_id)
);

Drizzle:

import { integer, pgTable, unique, varchar } from "drizzle-orm/pg-core";

export const teamMembers = pgTable("team_members", {
  teamId: integer("team_id").notNull(),
  userId: integer("user_id").notNull(),
  role: varchar("role", { length: 50 }).notNull(),
}, (table) => [
  unique().on(table.teamId, table.userId),
]);

CREATE UNIQUE INDEX

SQL CREATE UNIQUE INDEX statements are converted the same way as composite unique constraints when they involve multiple columns, or to column-level .unique() for single columns:

CREATE UNIQUE INDEX idx_user_email ON users(email);
CREATE UNIQUE INDEX idx_team_member ON team_members(team_id, user_id);

Non-Unique Indexes

Regular (non-unique) indexes use the index() function:

import { index, integer, pgTable, varchar } from "drizzle-orm/pg-core";

export const posts = pgTable("posts", {
  id: serial("id").primaryKey(),
  authorId: integer("author_id").notNull(),
  category: varchar("category", { length: 50 }),
}, (table) => [
  index("idx_posts_author").on(table.authorId),
]);

Named vs Unnamed Constraints

Drizzle's unique() and index() accept an optional name string. The converter generates names from the original SQL when available, or derives them from the table name.