Convert SQL CREATE INDEX to Prisma @@index

SQL Indexes to Prisma @@index

Indexes are critical for query performance. Prisma uses the @@index model attribute to declare indexes, and the converter maps SQL CREATE INDEX statements to the appropriate Prisma syntax.

Example SQL

CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  user_id INTEGER NOT NULL,
  status VARCHAR(20) NOT NULL,
  total DECIMAL(10, 2) NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  FOREIGN KEY (user_id) REFERENCES users(id)
);

CREATE INDEX idx_orders_user_id ON orders (user_id);
CREATE INDEX idx_orders_status ON orders (status);
CREATE INDEX idx_orders_user_status ON orders (user_id, status);
CREATE INDEX idx_orders_created_at ON orders (created_at DESC);

Generated Prisma Schema

model Order {
  id        Int      @id @default(autoincrement())
  userId    Int      @map("user_id")
  status    String   @db.VarChar(20)
  total     Decimal  @db.Decimal(10, 2)
  createdAt DateTime @default(now()) @map("created_at") @db.Timestamptz()
  user      User     @relation(fields: [userId], references: [id])

  @@index([userId], map: "idx_orders_user_id")
  @@index([status], map: "idx_orders_status")
  @@index([userId, status], map: "idx_orders_user_status")
  @@index([createdAt(sort: Desc)], map: "idx_orders_created_at")
  @@map("orders")
}

Index Types

SQL index type	Prisma syntax
Single column	@@index([fieldName])
Composite	@@index([field1, field2])
Descending	@@index([field(sort: Desc)])
Unique index	@@unique([...])
Full-text (MySQL)	@@fulltext([field1, field2])

Sort Order

SQL allows specifying sort direction per column:

CREATE INDEX idx_orders_date_desc ON orders (created_at DESC);

In Prisma, use sort: Desc in the index definition. This is particularly important for queries that sort by recent first.

Composite Index Column Order

The order of columns in a composite index matters for query optimization. The converter preserves the SQL column order:

// Good: supports queries filtering on userId, or userId + status
@@index([userId, status])

// Different: supports queries filtering on status, or status + userId
@@index([status, userId])

Index Naming

The converter preserves SQL index names via the map parameter:

@@index([userId], map: "idx_orders_user_id")

If no name is provided, Prisma generates one automatically. Keeping the original name makes it easier to correlate Prisma schema changes with existing database indexes.

When to Add Indexes

The converter generates indexes for:

1. Foreign key columns — essential for JOIN performance
2. Columns in WHERE clauses — based on common query patterns
3. Columns in ORDER BY — for sorted queries
4. Columns used in GROUP BY — for aggregate queries

Full-Text Search (MySQL)

CREATE FULLTEXT INDEX idx_posts_search ON posts (title, content);

@@fulltext([title, content], map: "idx_posts_search")

Note: @@fulltext is available only for MySQL in Prisma.