Raw SQL Queries - Prisma ORM

Overview

While Prisma’s query builder handles most use cases, sometimes you need raw SQL for complex queries, database-specific features, or performance optimization. Prisma provides $queryRaw and $executeRaw for these scenarios.

Query vs Execute

$queryRaw

Use $queryRaw for SELECT queries that return data:

const users = await prisma.$queryRaw`
  SELECT * FROM "User" WHERE age >= ${25}
`

$executeRaw

Use $executeRaw for INSERT, UPDATE, DELETE operations:

const count = await prisma.$executeRaw`
  UPDATE "User" SET active = ${true} WHERE age >= ${25}
`
// Returns number of affected rows

Tagged Templates (Type-Safe)

The recommended approach uses tagged template literals for automatic parameterization:

const minAge = 45
const maxAge = 60

const users = await prisma.$queryRaw`
  SELECT * FROM "User" 
  WHERE age >= ${minAge} AND age <= ${maxAge}
`

Tagged templates automatically prevent SQL injection by parameterizing values.

Type Safety with Prisma.sql

import { Prisma } from '@prisma/client'

const query = Prisma.sql`
  SELECT * FROM "User" WHERE age >= ${45}
`

const users = await prisma.$queryRaw(query)

Unsafe Raw Queries

Use $queryRawUnsafe and $executeRawUnsafe when you need dynamic SQL:

$queryRawUnsafe

// With interpolation (careful!)
const tableName = 'User'
const users = await prisma.$queryRawUnsafe(`
  SELECT * FROM "${tableName}" WHERE age >= ${45}
`)

// With parameterization (safer)
const users = await prisma.$queryRawUnsafe(
  'SELECT * FROM "User" WHERE age >= $1 AND age <= $2',
  45,
  60
)

Be extremely careful with $queryRawUnsafe. Never interpolate user input directly:

// DANGEROUS - SQL injection vulnerability
const email = req.body.email
const user = await prisma.$queryRawUnsafe(`
  SELECT * FROM "User" WHERE email = '${email}'
`)

// SAFE - use parameterization
const user = await prisma.$queryRawUnsafe(
  'SELECT * FROM "User" WHERE email = $1',
  email
)

Database-Specific Placeholders

PostgreSQL / CockroachDB: $1, $2, $3

await prisma.$queryRawUnsafe(
  'SELECT * FROM "User" WHERE age >= $1 AND age <= $2',
  45, 60
)

MySQL / SQLite: ?

await prisma.$queryRawUnsafe(
  'SELECT * FROM User WHERE age >= ? AND age <= ?',
  45, 60
)

SQL Server: @P1, @P2, @P3

await prisma.$queryRawUnsafe(
  'SELECT * FROM "User" WHERE age >= @P1 AND age <= @P2',
  45, 60
)

Advanced Patterns

Prisma.join for Arrays

Safely insert arrays into SQL:

import { Prisma } from '@prisma/client'

const ages = [45, 50, 60]

const users = await prisma.$queryRaw`
  SELECT * FROM "User" 
  WHERE age IN (${Prisma.join(ages)})
`
// Generates: WHERE age IN ($1, $2, $3)

Prisma.raw for Identifiers

Safely include column names and SQL fragments:

import { Prisma } from '@prisma/client'

const columns = [
  Prisma.raw('id'),
  Prisma.raw('email'),
  Prisma.raw('age'),
]

const users = await prisma.$queryRaw`
  SELECT ${Prisma.join(columns)} FROM "User"
  WHERE age IN (${Prisma.join([45, 60])})
`

Use Prisma.raw() only for trusted identifiers (table names, column names). Never use it for user input.

Combining Helpers

const fieldSelection = [
  Prisma.raw('age'),
  Prisma.raw('email'),
  Prisma.raw('id'),
]

const ages = [45, 60]

const result = await prisma.$queryRaw`
  SELECT ${Prisma.join(fieldSelection)} 
  FROM "User" 
  WHERE age IN (${Prisma.join(ages)})
`

Type Annotations

Provide TypeScript types for query results:

type UserResult = {
  id: string
  email: string
  age: number
}

const users = await prisma.$queryRaw<UserResult[]>`
  SELECT id, email, age FROM "User" WHERE age >= ${45}
`

// Now users is typed as UserResult[]
users.forEach(user => {
  console.log(user.email) // TypeScript knows this exists
})

Transactions with Raw Queries

Batch Transactions

const [users, posts] = await prisma.$transaction([
  prisma.$queryRaw`SELECT * FROM "User"`,
  prisma.$queryRaw`SELECT * FROM "Post"`,
])

Interactive Transactions

const result = await prisma.$transaction(async (tx) => {
  const users = await tx.$queryRaw`SELECT * FROM "User"`
  
  const count = await tx.$executeRaw`
    UPDATE "User" SET active = ${true}
  `
  
  return { users, count }
})

Mixed Operations

await prisma.$transaction([
  prisma.user.create({ data: { email: 'user@example.com' } }),
  prisma.$executeRaw`UPDATE "User" SET verified = ${true}`,
  prisma.$queryRaw`SELECT * FROM "User"`,
])

Database-Specific Features

PostgreSQL Full-Text Search

const searchTerm = 'prisma'

const results = await prisma.$queryRaw`
  SELECT * FROM "Post"
  WHERE to_tsvector('english', title || ' ' || content) 
    @@ to_tsquery('english', ${searchTerm})
`

PostgreSQL JSON Operations

const users = await prisma.$queryRaw`
  SELECT * FROM "User"
  WHERE metadata->>'role' = ${'admin'}
`

MySQL Custom Functions

const distance = await prisma.$queryRaw`
  SELECT id, 
    ST_Distance_Sphere(
      point(longitude, latitude),
      point(${userLng}, ${userLat})
    ) as distance
  FROM Location
  ORDER BY distance
  LIMIT 10
`

Window Functions

type RankedUser = {
  id: string
  email: string
  rank: number
}

const rankedUsers = await prisma.$queryRaw<RankedUser[]>`
  SELECT 
    id,
    email,
    RANK() OVER (ORDER BY created_at DESC) as rank
  FROM "User"
`

Common Use Cases

Complex Aggregations

type AgeDistribution = {
  age_group: string
  count: bigint
}

const distribution = await prisma.$queryRaw<AgeDistribution[]>`
  SELECT 
    CASE 
      WHEN age < 18 THEN 'under_18'
      WHEN age BETWEEN 18 AND 30 THEN '18_30'
      WHEN age BETWEEN 31 AND 50 THEN '31_50'
      ELSE 'over_50'
    END as age_group,
    COUNT(*) as count
  FROM "User"
  GROUP BY age_group
  ORDER BY age_group
`

Bulk Inserts

const values = [
  { email: 'user1@example.com', age: 25 },
  { email: 'user2@example.com', age: 30 },
  { email: 'user3@example.com', age: 35 },
]

const count = await prisma.$executeRaw`
  INSERT INTO "User" (email, age)
  VALUES 
    ${Prisma.join(values.map(v => 
      Prisma.sql`(${v.email}, ${v.age})`
    ))}
`

Recursive CTEs

type OrgHierarchy = {
  id: string
  name: string
  level: number
}

const hierarchy = await prisma.$queryRaw<OrgHierarchy[]>`
  WITH RECURSIVE org_tree AS (
    SELECT id, name, parent_id, 0 as level
    FROM Organization
    WHERE parent_id IS NULL
    
    UNION ALL
    
    SELECT o.id, o.name, o.parent_id, ot.level + 1
    FROM Organization o
    JOIN org_tree ot ON o.parent_id = ot.id
  )
  SELECT id, name, level FROM org_tree
`

Performance Considerations

Prepared Statements

Tagged templates use prepared statements:

// This query is prepared and cached
for (const age of [20, 30, 40]) {
  await prisma.$queryRaw`
    SELECT * FROM "User" WHERE age = ${age}
  `
}

Large Result Sets

// For large results, consider pagination
const page = 1
const pageSize = 100

const users = await prisma.$queryRaw`
  SELECT * FROM "User"
  ORDER BY created_at DESC
  LIMIT ${pageSize}
  OFFSET ${(page - 1) * pageSize}
`

Raw queries bypass Prisma’s result batching and caching. Use them judiciously for performance-critical operations.

Error Handling

try {
  const result = await prisma.$queryRaw`
    SELECT * FROM "User" WHERE invalid_column = ${1}
  `
} catch (error) {
  if (error.code === 'P2010') {
    console.error('Raw query failed:', error.message)
  }
  throw error
}

Migration to Raw SQL

When Prisma’s query builder isn’t enough:

// Prisma query builder (simple)
const users = await prisma.user.findMany({
  where: {
    age: { gte: 45, lte: 60 },
  },
})

// Raw SQL (for complex logic)
const users = await prisma.$queryRaw`
  SELECT u.*, 
    (SELECT COUNT(*) FROM "Post" WHERE "userId" = u.id) as post_count
  FROM "User" u
  WHERE age BETWEEN ${45} AND ${60}
  HAVING post_count > 5
`

Best Practices

Use tagged templates for automatic parameterization
Never interpolate user input in $queryRawUnsafe
Provide TypeScript types for query results
Use Prisma.join for arrays
Use Prisma.raw only for identifiers
Prefer query builder when possible
Test raw queries thoroughly (less type safety)
Use transactions for multiple raw operations
Handle database-specific syntax with conditionals
Document complex raw queries for maintainability

Security Checklist

Connection Pooling

TypeScript Type Safety

⌘I

Overview
Query vs Execute
$queryRaw
$executeRaw
Tagged Templates (Type-Safe)
Type Safety with Prisma.sql
Unsafe Raw Queries
$queryRawUnsafe
Database-Specific Placeholders
Advanced Patterns
Prisma.join for Arrays
Prisma.raw for Identifiers
Combining Helpers
Type Annotations
Transactions with Raw Queries
Batch Transactions
Interactive Transactions
Mixed Operations
Database-Specific Features
PostgreSQL Full-Text Search
PostgreSQL JSON Operations
MySQL Custom Functions
Window Functions
Common Use Cases
Complex Aggregations
Bulk Inserts
Recursive CTEs
Performance Considerations
Prepared Statements
Large Result Sets
Error Handling
Migration to Raw SQL
Best Practices
Security Checklist

Get Started

Core Concepts

Prisma Client

Prisma Migrate

Driver Adapters

Guides

​Overview

​Query vs Execute

​$queryRaw

​$executeRaw

​Tagged Templates (Type-Safe)

​Type Safety with Prisma.sql

​Unsafe Raw Queries

​$queryRawUnsafe

​Database-Specific Placeholders

​Advanced Patterns

​Prisma.join for Arrays

​Prisma.raw for Identifiers

​Combining Helpers

​Type Annotations

​Transactions with Raw Queries

​Batch Transactions

​Interactive Transactions

​Mixed Operations

​Database-Specific Features

​PostgreSQL Full-Text Search

​PostgreSQL JSON Operations

​MySQL Custom Functions

​Window Functions

​Common Use Cases

​Complex Aggregations

​Bulk Inserts

​Recursive CTEs

​Performance Considerations

​Prepared Statements

​Large Result Sets

​Error Handling

​Migration to Raw SQL

​Best Practices

​Security Checklist

Overview

Query vs Execute

$queryRaw

$executeRaw

Tagged Templates (Type-Safe)

Type Safety with Prisma.sql

Unsafe Raw Queries

$queryRawUnsafe

Database-Specific Placeholders

Advanced Patterns

Prisma.join for Arrays

Prisma.raw for Identifiers

Combining Helpers

Type Annotations

Transactions with Raw Queries

Batch Transactions

Interactive Transactions

Mixed Operations

Database-Specific Features

PostgreSQL Full-Text Search

PostgreSQL JSON Operations

MySQL Custom Functions

Window Functions

Common Use Cases

Complex Aggregations

Bulk Inserts

Recursive CTEs

Performance Considerations

Prepared Statements

Large Result Sets

Error Handling

Migration to Raw SQL

Best Practices

Security Checklist