Prisma @@index to SQL CREATE INDEX

Index Conversion

Prisma's @@index attribute generates CREATE INDEX statements in SQL. Indexes are crucial for query performance, especially on columns used in WHERE clauses, JOIN conditions, and ORDER BY clauses.

Example Prisma Schema

model Order {
  id         Int      @id @default(autoincrement())
  customerId Int      @map("customer_id")
  status     String
  total      Decimal
  createdAt  DateTime @default(now()) @map("created_at")

  @@index([customerId])
  @@index([status])
  @@index([createdAt])
  @@map("orders")
}

Generated SQL

CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  status VARCHAR(255) NOT NULL,
  total DECIMAL(10,2) NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_orders_customer_id ON orders (customer_id);
CREATE INDEX idx_orders_status ON orders (status);
CREATE INDEX idx_orders_created_at ON orders (created_at);

Index Naming Convention

The converter generates index names in the format idx_{table}_{columns}, which is a common SQL naming convention. For example, an index on the customer_id column of the orders table becomes idx_orders_customer_id.

Composite Indexes

You can create multi-column indexes by listing multiple fields:

model SearchLog {
  id        Int      @id @default(autoincrement())
  userId    Int      @map("user_id")
  query     String
  createdAt DateTime @default(now()) @map("created_at")

  @@index([userId, createdAt])
  @@map("search_logs")
}

This generates: CREATE INDEX idx_search_logs_user_id_created_at ON search_logs (user_id, created_at);

Composite indexes are useful when you frequently query by multiple columns together, such as "all searches by user X in the last 7 days."

Indexes vs Unique Constraints

• @@index creates a non-unique index — duplicate values are allowed.
• @@unique creates a unique index — duplicate combinations are rejected.
• @unique on a single field creates a single-column unique index.

All three improve query performance, but unique constraints also enforce data integrity.