prisma-client-api-raw-queries

by prisma

Raw Queries. Reference when using this Prisma feature. npx skills add https://github.com/prisma/cursor-plugin --skill prisma-client-api-raw-queries Download ZIPGitHub8

Raw Queries

Execute raw SQL when Prisma's query API isn't sufficient.

$queryRaw

Execute SELECT queries and get typed results:

const users = await prisma.$queryRaw`
 SELECT * FROM "User" WHERE email LIKE ${'%@prisma.io'}
`

With type

type User = { id: number; email: string; name: string | null }

const users = await prisma.$queryRaw `
 SELECT id, email, name FROM "User" WHERE role = ${'ADMIN'}
`

Dynamic table/column names

Use Prisma.raw() for identifiers (not safe for user input):

import { Prisma } from '../generated/client'

const column = 'email'
const users = await prisma.$queryRaw`
 SELECT ${Prisma.raw(column)} FROM "User"
`

With Prisma.sql

Build queries dynamically:

import { Prisma } from '../generated/client'

const email = '[email protected]'
const query = Prisma.sql`SELECT * FROM "User" WHERE email = ${email}`
const users = await prisma.$queryRaw(query)

Join multiple SQL fragments

import { Prisma } from '../generated/client'

const conditions = [
 Prisma.sql`role = ${'ADMIN'}`,
 Prisma.sql`verified = ${true}`
]

const users = await prisma.$queryRaw`
 SELECT * FROM "User" 
 WHERE ${Prisma.join(conditions, ' AND ')}
`

$executeRaw

Execute INSERT, UPDATE, DELETE (returns affected count):

const count = await prisma.$executeRaw`
 UPDATE "User" SET verified = true WHERE email LIKE ${'%@prisma.io'}
`
console.log(`Updated ${count} users`)

Delete example

const deleted = await prisma.$executeRaw`
 DELETE FROM "User" WHERE "deletedAt" For fully dynamic queries (use with caution!):

// ⚠️ SQL injection risk - only use with trusted input const table = 'User' const users = await prisma.$queryRawUnsafe( SELECT * FROM "${table}" WHERE id = $1, userId )

### Parameterized unsafe query

const result = await prisma.$executeRawUnsafe( 'UPDATE "User" SET name = $1 WHERE id = $2', 'Alice', 1 )

## SQL Injection Prevention

### Safe (parameterized)

// ✅ User input is parameterized const email = userInput const users = await prisma.$queryRaw SELECT * FROM "User" WHERE email = ${email}

### Unsafe (concatenation)

// ❌ SQL injection vulnerability! const email = userInput const users = await prisma.$queryRawUnsafe( SELECT * FROM "User" WHERE email = '${email}' )

## Database-Specific Features

### PostgreSQL

// Array operations const users = await prisma.$queryRaw SELECT * FROM "User" WHERE 'admin' = ANY(roles)

// JSON operations const users = await prisma.$queryRaw SELECT * FROM "User" WHERE metadata->>'theme' = 'dark'

### MySQL

// Full-text search const posts = await prisma.$queryRaw SELECT * FROM Post WHERE MATCH(title, content) AGAINST(${searchTerm})

## Transactions with Raw Queries

await prisma.$transaction(async (tx) => { await tx.$executeRawUPDATE "Account" SET balance = balance - ${amount} WHERE id = ${senderId} await tx.$executeRawUPDATE "Account" SET balance = balance + ${amount} WHERE id = ${recipientId} })

## Handling Results

### BigInt handling

 PostgreSQL returns BigInt for COUNT:

const result = await prisma.$queryRaw SELECT COUNT(*) as count FROM "User" const count = Number(result[0].count)

### Date handling

type Result = { createdAt: Date } const users = await prisma.$queryRaw SELECT "createdAt" FROM "User" // createdAt is already a Date object