Raw Query API

Prisma Client provides methods for executing raw SQL queries when you need to perform operations that are not supported by the Prisma Client API.

$queryRaw

Execute a raw SQL query that returns data (SELECT statements).

Signature

$queryRaw<T>(query: TemplateStringsArray | Sql, ...values: any[]): Promise<T>

Tagged Template Usage

The recommended way to use $queryRaw is with tagged template literals for automatic SQL injection protection.

import { PrismaClient } from '@prisma/client'

const prisma = new PrismaClient()

// Basic query
const users = await prisma.$queryRaw`SELECT * FROM User WHERE age > ${18}`

// With multiple parameters
const email = 'alice@prisma.io'
const minAge = 21
const result = await prisma.$queryRaw`
  SELECT * FROM User 
  WHERE email = ${email} AND age >= ${minAge}
`

Values in tagged templates are automatically parameterized to prevent SQL injection.

Using Prisma.sql

For dynamic query building, use Prisma.sql helper:

import { Prisma } from '@prisma/client'

const email = 'alice@prisma.io'
const query = Prisma.sql`SELECT * FROM User WHERE email = ${email}`

const users = await prisma.$queryRaw(query)

Type Safety

Specify return type:

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

const users = await prisma.$queryRaw<UserResult[]>`
  SELECT id, email, name FROM User
`
// users is now typed as UserResult[]

Return Values

return

Promise<T>

Returns an array of objects representing the query results.The structure depends on your database:

PostgreSQL, MySQL, SQLite: Array of objects with column names as keys
Field names are case-sensitive and match your database schema

Complete Examples

Simple SELECT:

const users = await prisma.$queryRaw`
  SELECT * FROM "User" WHERE "role" = 'ADMIN'
`

JOIN queries:

const results = await prisma.$queryRaw`
  SELECT u.name, COUNT(p.id) as postCount
  FROM "User" u
  LEFT JOIN "Post" p ON u.id = p."authorId"
  GROUP BY u.id, u.name
  HAVING COUNT(p.id) > ${5}
`

WITH clauses (CTEs):

const results = await prisma.$queryRaw`
  WITH active_users AS (
    SELECT * FROM "User" WHERE active = true
  )
  SELECT * FROM active_users WHERE age > ${25}
`

$queryRawUnsafe

Execute a raw SQL query using string concatenation (unsafe, use with caution).

Signature

$queryRawUnsafe<T>(query: string, ...values: any[]): Promise<T>

SQL Injection Risk: $queryRawUnsafe does not provide automatic SQL injection protection. Only use with trusted input or when you need to dynamically construct SQL that cannot be parameterized.

Usage

// Positional parameters (database-specific syntax)
const users = await prisma.$queryRawUnsafe(
  'SELECT * FROM User WHERE age > $1 AND role = $2',
  18,
  'USER'
)

// Dynamic table names (cannot be parameterized)
const tableName = 'User'  // Ensure this is validated!
const results = await prisma.$queryRawUnsafe(
  `SELECT * FROM "${tableName}" WHERE active = true`
)

Parameter Syntax by Database

PostgreSQL:

await prisma.$queryRawUnsafe(
  'SELECT * FROM "User" WHERE age > $1',
  18
)

MySQL:

await prisma.$queryRawUnsafe(
  'SELECT * FROM User WHERE age > ?',
  18
)

SQL Server:

await prisma.$queryRawUnsafe(
  'SELECT * FROM User WHERE age > @p1',
  18
)

SQLite:

await prisma.$queryRawUnsafe(
  'SELECT * FROM User WHERE age > ?',
  18
)

$executeRaw

Execute a raw SQL query that modifies data (INSERT, UPDATE, DELETE, DDL) and returns a count.

Signature

$executeRaw(query: TemplateStringsArray | Sql, ...values: any[]): Promise<number>

Usage

// INSERT
const count = await prisma.$executeRaw`
  INSERT INTO "User" (email, name) 
  VALUES (${'alice@prisma.io'}, ${'Alice'})
`
console.log(`Inserted ${count} row(s)`)

// UPDATE
const updated = await prisma.$executeRaw`
  UPDATE "User" 
  SET active = true 
  WHERE "createdAt" < ${new Date('2024-01-01')}
`
console.log(`Updated ${updated} row(s)`)

// DELETE
const deleted = await prisma.$executeRaw`
  DELETE FROM "User" 
  WHERE active = false
`
console.log(`Deleted ${deleted} row(s)`)

return

Promise<number>

Returns the number of rows affected by the operation.

DDL Operations

// Create index
await prisma.$executeRaw`
  CREATE INDEX idx_user_email ON "User"(email)
`

// Add column
await prisma.$executeRaw`
  ALTER TABLE "User" ADD COLUMN "lastLogin" TIMESTAMP
`

// Create table
await prisma.$executeRaw`
  CREATE TABLE IF NOT EXISTS "TempData" (
    id SERIAL PRIMARY KEY,
    data TEXT
  )
`

Prisma detects DDL statements (ALTER, CREATE, DROP, etc.) and shows a warning. Use Prisma Migrate for schema changes instead of raw DDL when possible.

$executeRawUnsafe

Execute a raw SQL query using string concatenation that modifies data.

Signature

$executeRawUnsafe(query: string, ...values: any[]): Promise<number>

SQL Injection Risk: Same security concerns as $queryRawUnsafe.

Usage

// With parameters
const count = await prisma.$executeRawUnsafe(
  'UPDATE "User" SET active = $1 WHERE age > $2',
  true,
  18
)

// Dynamic column names
const columnName = 'active'  // Validate this!
const result = await prisma.$executeRawUnsafe(
  `UPDATE "User" SET "${columnName}" = true`
)

$queryRawTyped

Execute a type-safe raw SQL query using generated TypedSQL.

Signature

$queryRawTyped<T>(typedSql: TypedSql<T>): Promise<T>

Requires the typedSql preview feature to be enabled in your Prisma schema.

Usage

First, enable the preview feature in your schema:

generator client {
  provider        = "prisma-client-js"
  previewFeatures = ["typedSql"]
}

Create a SQL file in prisma/sql/:

-- prisma/sql/getUsersByAge.sql
SELECT id, email, name 
FROM "User" 
WHERE age > :minAge

Generate the client:

prisma generate

Use the typed query:

import { getUsersByAge } from '@prisma/client/sql'

const users = await prisma.$queryRawTyped(
  getUsersByAge({ minAge: 18 })
)
// users is fully typed based on the SQL query!

Benefits

Full TypeScript type safety
Validation at generation time
IDE autocomplete for parameters
Compile-time error checking

$runCommandRaw (MongoDB Only)

Execute a raw MongoDB command.

Signature

$runCommandRaw(command: Record<string, any>): Promise<any>

Only available when using the MongoDB provider.

Usage

// Find with MongoDB query syntax
const result = await prisma.$runCommandRaw({
  find: 'User',
  filter: { age: { $gt: 18 } },
  sort: { name: 1 },
  limit: 10
})

// Aggregation pipeline
const stats = await prisma.$runCommandRaw({
  aggregate: 'User',
  pipeline: [
    { $match: { active: true } },
    { $group: { _id: '$role', count: { $sum: 1 } } }
  ]
})

// Database commands
const dbStats = await prisma.$runCommandRaw({
  dbStats: 1
})

Type Mappings

Prisma automatically maps between TypeScript and database types:

JavaScript → Database

JavaScript Type	PostgreSQL	MySQL	SQLite
`string`	TEXT	VARCHAR	TEXT
`number`	INTEGER	INT	INTEGER
`bigint`	BIGINT	BIGINT	INTEGER
`boolean`	BOOLEAN	BOOLEAN	INTEGER
`Date`	TIMESTAMP	DATETIME	TEXT
`Buffer`	BYTEA	BLOB	BLOB
`Decimal`	DECIMAL	DECIMAL	TEXT

Database → JavaScript

// Dates are returned as Date objects
const result = await prisma.$queryRaw`
  SELECT "createdAt" FROM "User"
`
// result[0].createdAt is a Date object

// BigInt for large numbers
const bigNumbers = await prisma.$queryRaw`
  SELECT "bigIntColumn" FROM "Data"
`
// result[0].bigIntColumn is a bigint

Using in Transactions

Raw queries can be used inside transactions:

const result = await prisma.$transaction(async (tx) => {
  // Regular Prisma query
  const user = await tx.user.create({
    data: { email: 'alice@prisma.io' }
  })
  
  // Raw query in the same transaction
  await tx.$executeRaw`
    INSERT INTO "AuditLog" ("userId", action) 
    VALUES (${user.id}, 'USER_CREATED')
  `
  
  return user
})

Best Practices

Prefer Prisma Client API: Use raw queries only when necessary.
Use tagged templates: Always use $queryRaw (not $queryRawUnsafe) unless you absolutely need dynamic SQL.
Validate dynamic inputs: If you must use $queryRawUnsafe, carefully validate all inputs.
Quote identifiers: Use proper quoting for table and column names:
- PostgreSQL: "User", "createdAt"
- MySQL: `User`, `createdAt`
- SQLite: "User", "createdAt"
Type your results: Always specify return types for better type safety.
Handle errors: Raw queries can fail in unexpected ways.

// ✅ Good: Type-safe with tagged template
const users = await prisma.$queryRaw<User[]>`
  SELECT * FROM "User" WHERE age > ${18}
`

// ❌ Bad: No type safety, SQL injection risk
const age = req.query.age
const users = await prisma.$queryRawUnsafe(
  `SELECT * FROM User WHERE age > ${age}`
)

Common Patterns

Pagination with COUNT

type Result = { total: bigint }

const [data, countResult] = await Promise.all([
  prisma.$queryRaw<User[]>`
    SELECT * FROM "User" 
    ORDER BY "createdAt" DESC 
    LIMIT ${pageSize} OFFSET ${offset}
  `,
  prisma.$queryRaw<Result[]>`
    SELECT COUNT(*)::int as total FROM "User"
  `
])

const total = Number(countResult[0].total)

Full-Text Search

PostgreSQL:

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

MySQL:

const searchTerm = 'prisma'
const results = await prisma.$queryRaw`
  SELECT * FROM Post
  WHERE MATCH(title, content) AGAINST (${searchTerm} IN NATURAL LANGUAGE MODE)
`

JSON Operations

PostgreSQL:

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

MySQL:

const results = await prisma.$queryRaw`
  SELECT * FROM User
  WHERE JSON_EXTRACT(metadata, '$.role') = ${'admin'}
`

Client API

CLI Commands

Schema Reference

Configuration

Raw Query API

Raw Query API

$queryRaw

Signature

Tagged Template Usage

Using Prisma.sql

Type Safety

Return Values

Complete Examples

$queryRawUnsafe

Signature

Usage

Parameter Syntax by Database

$executeRaw

Signature

Usage

DDL Operations

$executeRawUnsafe

Signature

Usage

$queryRawTyped

Signature

Usage

Benefits

$runCommandRaw (MongoDB Only)

Signature

Usage

Type Mappings

JavaScript → Database

Database → JavaScript

Using in Transactions

Best Practices

Common Patterns

Full-Text Search

JSON Operations

See Also

Client API

CLI Commands

Schema Reference

Configuration

​Raw Query API

​$queryRaw

​Signature

​Tagged Template Usage

​Using Prisma.sql

​Type Safety

​Return Values

​Complete Examples

​$queryRawUnsafe

​Signature

​Usage

​Parameter Syntax by Database

​$executeRaw

​Signature

​Usage

​DDL Operations

​$executeRawUnsafe

​Signature

​Usage

​$queryRawTyped

​Signature

​Usage

​Benefits

​$runCommandRaw (MongoDB Only)

​Signature

​Usage

​Type Mappings

​JavaScript → Database

​Database → JavaScript

​Using in Transactions

​Best Practices

​Common Patterns

​Pagination with COUNT

​Full-Text Search

​JSON Operations

​See Also

Raw Query API

$queryRaw

Signature

Tagged Template Usage

Using Prisma.sql

Type Safety

Return Values

Complete Examples

$queryRawUnsafe

Signature

Usage

Parameter Syntax by Database

$executeRaw

Signature

Usage

DDL Operations

$executeRawUnsafe

Signature

Usage

$queryRawTyped

Signature

Usage

Benefits

$runCommandRaw (MongoDB Only)

Signature

Usage

Type Mappings

JavaScript → Database

Database → JavaScript

Using in Transactions

Best Practices

Common Patterns

Pagination with COUNT

Full-Text Search

JSON Operations

See Also