CodingNic
Sign In Book Session

CRUD and Querying with Prisma

Comparing to Raw SQL

CRUD and Querying with Prisma 10 min read

Comparing to Raw SQL

Objectives

By the end of this lesson, you should be able to:

  • Match every Prisma Client method covered in this module to its raw SQL equivalent from Modules 1 and 2
  • Explain what Prisma is actually generating underneath a given query
  • Decide, for a given task, whether raw SQL or Prisma is the more direct tool

💡 Why this matters: This module covered a lot of new syntax. Laying it side by side with the raw SQL from Modules 1 and 2 is the fastest way to confirm it’s genuinely understood, not just memorized.

Reading

Task Raw SQL (Module 2) Prisma Client (this module)
Get all rows SELECT * FROM students prisma.student.findMany()
Filter SELECT * FROM students WHERE grade = $1 prisma.student.findMany({ where: { grade } })
One row by ID SELECT * FROM students WHERE id = $1 prisma.student.findUnique({ where: { id } })
Sort SELECT * FROM students ORDER BY grade DESC prisma.student.findMany({ orderBy: { grade: 'desc' } })
Paginate SELECT * FROM students LIMIT $1 OFFSET $2 prisma.student.findMany({ take, skip })

Writing

Task Raw SQL (Module 2) Prisma Client (this module)
Insert INSERT INTO students (name, grade) VALUES ($1, $2) RETURNING * prisma.student.create({ data: { name, grade } })
Update one UPDATE students SET grade = $1 WHERE id = $2 RETURNING * prisma.student.update({ where: { id }, data: { grade } })
Delete one DELETE FROM students WHERE id = $1 RETURNING * prisma.student.delete({ where: { id } })
Update many UPDATE students SET grade = $1 WHERE grade = $2 prisma.student.updateMany({ where: { grade: old }, data: { grade: new } })

Relationships

Task Raw SQL Prisma Client (Module 5)
Load with a join SELECT * FROM books JOIN authors ON books.author_id = authors.id prisma.book.findMany({ include: { author: true } })
Insert related rows Two separate INSERT statements, manually passing the new ID prisma.author.create({ data: { name, books: { create: [...] } } })

What Hasn’t Changed

Every Prisma Client call in this module ultimately generates parameterized SQL, values are still never concatenated into a query string, $1-style placeholders are still exactly how Prisma sends data to PostgreSQL underneath. Module 2’s SQL injection lesson still fully applies, Prisma just writes the parameterized query for you.

When Raw SQL Is Still the Right Tool

Prisma covers the vast majority of real application queries, but not every one. Complex aggregations, database-specific features, or a query that’s simpler to just write directly are all valid reasons to reach for raw SQL. Prisma Client exposes an escape hatch for exactly this:

javascript
const result = await prisma.$queryRaw`SELECT * FROM students WHERE grade = ${10}`;

prisma.$queryRaw still parameterizes the ${10} safely (it isn’t string interpolation into SQL, despite looking similar), it’s Prisma’s own tagged-template syntax for exactly this purpose. This is a genuine escape hatch, not something to reach for by default, everything covered in Lessons 1 through 3 should be the first choice for a normal query.

Try It

  1. Pick three raw SQL queries from your Module 2 exercises, and rewrite each one as a Prisma Client call.
  2. Explain, in your own words, why prisma.$queryRaw is still safe from SQL injection despite its template-literal syntax.
  3. Describe one situation where writing raw SQL directly might still be the better choice over Prisma Client.

Recap

  • Every Prisma Client method covered in this module has a direct raw SQL equivalent, findMany to SELECT, create to INSERT, and so on.
  • Prisma still generates parameterized SQL underneath, injection safety (Module 2) is preserved automatically.
  • prisma.$queryRaw is a safe escape hatch to raw SQL when Prisma’s query API genuinely isn’t the right fit.

This is the final lesson of this module before exercises. Next module: MongoDB, a genuinely different way of modeling and querying data.