Prisma Raw Queries

You are a database developer using Prisma ORM. The user wants to execute raw SQL queries directly within Prisma for complex operations that can't be built with the standard query builder.

What to check first

• Verify @prisma/client is installed: npm list @prisma/client
• Check your .env file contains a valid DATABASE_URL connection string
• Confirm your Prisma schema is synced with your database: npx prisma db push or npx prisma migrate dev

Steps

1. Import PrismaClient and instantiate it in your application file
2. Use prisma.$queryRaw for SELECT queries that return data rows
3. Use prisma.$executeRaw for INSERT, UPDATE, DELETE queries that modify data
4. Pass SQL as a template literal with backticks to enable parameter binding
5. Use Prisma.sql for proper SQL escaping and type safety in dynamic queries
6. Handle the returned data: $queryRaw returns an array of objects matching your query shape
7. Catch PrismaClientKnownRequestError for database-specific errors
8. Test parameter binding by logging the SQL before executing in production

Code

import { PrismaClient, Prisma } from '@prisma/client';

const prisma = new PrismaClient();

// SELECT query with $queryRaw — returns typed array of results
async function getComplexUserReport(minAge: number, countryCode: string) {
  const results = await prisma.$queryRaw<
    Array<{ id: number; name: string; totalOrders: bigint }>
  >`
    SELECT 
      u.id, 
      u.name, 
      COUNT(o.id) as "totalOrders"
    FROM "User" u
    LEFT JOIN "Order" o ON u.id = o.userId
    WHERE u.age > ${minAge}
    AND u.country = ${countryCode}
    GROUP BY u.id, u.name
    HAVING COUNT(o.id) > 0
    ORDER BY "totalOrders" DESC
  `;
  return results;
}

// UPDATE query with $executeRaw — returns number of rows affected
async function bulkUpdateUserStatus(userIds: number[], newStatus: string) {
  const rowsAffected = await prisma.$executeRaw`
    UPDATE "User"
    SET status = ${newStatus}, "updatedAt" = NOW()
    WHERE id IN (${Prisma.join(userIds)})
  `;
  return rowsAffected;
}

// Dynamic SQL with Prisma.sql for safe interpolation
async function dynamicSearch(tableName: string, searchTerm: string) {
  const identifier = Prisma.raw(`"${tableName}"`);
  const results = await prisma.$queryRaw`
    SELECT * FROM ${identifier}
    WHERE "name" ILIKE ${

Note: this example was truncated in the source. See the GitHub repo for the latest full version.

Common Pitfalls

• Treating this skill as a one-shot solution — most workflows need iteration and verification
• Skipping the verification steps — you don't know it worked until you measure
• Applying this skill without understanding the underlying problem — read the related docs first

When NOT to Use This Skill

• When a simpler manual approach would take less than 10 minutes
• On critical production systems without testing in staging first
• When you don't have permission or authorization to make these changes

How to Verify It Worked

• Run the verification steps documented above
• Compare the output against your expected baseline
• Check logs for any warnings or errors — silent failures are the worst kind

Production Considerations

• Test in staging before deploying to production
• Have a rollback plan — every change should be reversible
• Monitor the affected systems for at least 24 hours after the change