Optimize query access paths
Speed up lookups or sorting on frequently queried columns.
Drizzle ORM · Function
index
Drizzle’s index function declares a database index on one or more table columns or expressions. Configure it with methods such as .on(), .using(), .where(), and .concurrently() where supported.
How it works
The index function creates a table-index declaration that Drizzle can use when generating database schema changes. Give it a name when needed, then configure indexed columns with .on(); the declaration is returned from the table callback alongside other indexes or constraints.
Syntax
index(name).on(...columns)
index(name).onOnly(...columns)
index(name).using(method, ...columns)Parameters
| Name | Type | Description |
|---|---|---|
name | string | The index name; optional for simple column indexes but recommended for expression-based indexes. |
columns / expressions | columns or SQL expressions | One or more table columns or SQL expressions passed to .on(), .onOnly(), or .using(). |
Returns
An index declaration used in the table callback and by Drizzle schema and migration tooling.
Key points
- Use
indexfor ordinary indexes anduniqueIndexwhen indexed values must be unique. - The basic declaration supplies a name and passes one or more table columns to
.on(). .using()selects an index method and accepts ordered columns or SQL expressions as indexed items.- A partial index predicate can be supplied with
.where()using a SQL expression.
Common mistakes
- Name expression-based indexes explicitly; relying on automatic naming can fail for indexes built from SQL expressions.
- When using
push, changes to index expressions, predicates, or operator classes may not produce migration statements; use the documented remove-and-readd workflow.
Example: Declare a table index
import { index, pgTable, serial, text } from "drizzle-orm/pg-core";
export const users = pgTable("users", {
id: serial().primaryKey(),
name: text().notNull(),
}, (table) => [
index("users_name_idx").on(table.name),
]);Defines a PostgreSQL table and adds a named B-tree index on the name column.
When to use it
Support composite queries
Create a multicolumn index for queries filtering or ordering by a column combination.
Support expression-based search
Index a computed SQL expression, such as a lowercased text value.
Use PostgreSQL-specific options
Create a PostgreSQL partial or concurrently built index.
Interview questions
QHow do you declare an index in Drizzle?
Use index(name).on(columns...) inside the table callback. The declaration describes an ordinary database index, while uniqueIndex(name).on(...) declares a unique one.
Follow-up: How would you index an SQL expression? Use .on(sql...) and provide the index name explicitly, because expression-based indexes should not depend on generated names.
QWhich PostgreSQL index options does the documented API expose?
The index API supports configuration beyond columns, including .using(), .where(), and .concurrently() for PostgreSQL declarations. .onOnly() is available when the PostgreSQL index should be created with ON ONLY.
Follow-up: How can an index use a PostgreSQL operator class? Pass the column expression through .op('operator_class'), together with the relevant index method if required.
QWhen should an index be given an explicit name?
For a normal column index, Drizzle can generate an index name when one is omitted. The documentation specifically recommends supplying a name whenever at least one indexed item is an expression.
Follow-up: What happens with an existing index changed through push? Changes to expressions, where clauses, and operator classes may require temporarily removing the index, pushing, then adding the changed declaration and pushing again.
Related
uniqueIndexpgTablesql.on().onOnly().using().where().concurrently()