Performance Optimization: Indexes and Raw SQL

Optimizing query execution speeds becomes critical as database sizes grow. Let us look at configuring database indexes and executing raw SQL queries in Prisma.

1. Configuring Database Indexes (@@index)

Database indexes speed up search queries on specific columns at the cost of slightly slower writes.

You can define indexes directly in your schema.prisma file:

model Post {
  id        Int      @id @default(autoincrement())
  title     String
  status    String
  createdAt DateTime @default(now())

  // Configure single-column index
  @@index([status])

  // Configure composite index for sorted searches
  @@index([status, createdAt])
}

• @@index([status]): Speeds up queries like where: { status: "published" }.
• @@index([status, createdAt]): Optimizes queries that filter by status and sort by date simultaneously.

2. Executing Raw SQL Queries ($queryRaw)

While Prisma Client query methods cover most use cases, you may occasionally need to write raw SQL queries for complex aggregations or database-specific features.

Prisma provides the $queryRaw method to run raw SQL queries safely:

import { Prisma } from "@prisma/client";

const activeStatus = "published";

// Execute raw SQL securely
const posts = await db.$queryRaw`
  SELECT id, title, views_count 
  FROM "Post" 
  WHERE status = ${activeStatus}
  ORDER BY views_count DESC
  LIMIT 5
`;

Prisma uses template literals to escape query inputs automatically, preventing SQL injection vulnerabilities.

3. Executing Raw SQL Commands ($executeRaw)

To execute database commands that do not return a result set (such as modifying columns or dropping temporary tables), use $executeRaw:

const affectedRows = await db.$executeRaw`
  UPDATE "User"
  SET points = points + 10
  WHERE active = true
`;