Composite Indexes in Drizzle ORM

Composite Indexes in Drizzle

Composite indexes span multiple columns and are essential for query performance. The converter maps SQL CREATE INDEX statements to Drizzle's index() function in the table's constraints callback.

Basic Composite Index

SQL:

CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  status VARCHAR(20) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_orders_customer_status ON orders(customer_id, status);

Drizzle:

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

export const orders = pgTable("orders", {
  id: serial("id").primaryKey(),
  customerId: integer("customer_id").notNull(),
  status: varchar("status", { length: 20 }).notNull(),
  createdAt: timestamp("created_at").default(sql`CURRENT_TIMESTAMP`),
}, (table) => [
  index("idx_orders_customer_status").on(table.customerId, table.status),
]);

Multiple Indexes on One Table

A table can have multiple indexes. They all go in the same constraints callback:

CREATE INDEX idx_orders_customer ON orders(customer_id);
CREATE INDEX idx_orders_status ON orders(status);
CREATE INDEX idx_orders_created ON orders(created_at);

export const orders = pgTable("orders", {
  // ... columns
}, (table) => [
  index("idx_orders_customer").on(table.customerId),
  index("idx_orders_status").on(table.status),
  index("idx_orders_created").on(table.createdAt),
]);

Mixing Constraints

The constraints callback can contain different constraint types together:

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

Index Naming Conventions

Good index naming helps with database maintenance. Common conventions:

• idx_{table}_{columns} — e.g., idx_orders_customer_status
• {table}_{columns}_idx — e.g., orders_customer_status_idx

The converter preserves the original index name from the SQL when available.

Partial and Expression Indexes

PostgreSQL supports partial indexes (WHERE clause) and expression indexes. These are not directly supported by Drizzle's schema DSL and require raw SQL migrations. The converter handles standard index definitions only.