Transactions & Raw SQL — Series 3

Preview — 3 of 10 questions

Why is prisma.$queryRawUnsafe(\SELECT * FROM users WHERE email = ${userInput}\) dangerous, while prisma.$queryRaw\SELECT * FROM users WHERE email = ${userInput}\`` is safe?

AThey're equally safe; $queryRawUnsafe's name is just historical, with no real difference in behavior

B$queryRawUnsafe only works with SELECT statements; $queryRaw works with any statement type

C$queryRawUnsafe accepts a plain string built by the caller, with no automatic parameterization — directly interpolating userInput into that string is exactly the classic SQL injection pattern the codebase's own security rules explicitly forbid, since PostgreSQL never sees userInput as a separate, safely-escaped value. $queryRaw (used as a tagged template literal) instead sends the interpolated values as genuine, separately-bound query parameters — PostgreSQL treats them purely as data, never as SQL syntax, regardless of what characters they contain

DThe only difference is that $queryRawUnsafe doesn't return typed results

Given Post/author, does prisma.post.findMany({ include: { author: true } }) over 1,000 posts run 1 query for the posts plus 1,000 separate queries for each post's author (the classic N+1 pattern)?

AYes — every included relation always triggers one additional query per parent row, exactly reproducing N+1

Binclude always requires exactly 2 queries regardless of how many relations are included, no matter how deeply nested

CNo — Prisma's query engine compiles a nested include into either a single JOIN-based query, or (depending on the relation type and provider) a small, constant number of batched queries — one query fetching all matching related rows at once via WHERE authorId IN (...), not one query per post. The N+1 pattern from the earlier related question is specifically what happens when application code manually loops over results and issues a separate query for each one, which include is designed to avoid

DPrisma has no way to avoid N+1 at all; it must be solved with a separate library like DataLoader

A search feature needs to conditionally add filter clauses to a raw query based on which optional parameters were actually provided. How does Prisma.sql support building this safely?

javascript

const filters = [Prisma.sql`status = 'active'`];
if (minPrice) filters.push(Prisma.sql`price >= ${minPrice}`);
const query = Prisma.sql`SELECT * FROM products WHERE ${Prisma.join(filters, ' AND ')}`;

APrisma.sql fragments cannot be combined; each raw query must be written as one single, complete template literal

BThis pattern only works with $executeRaw, never $queryRaw

CPrisma.sql fragments are individually-safe pieces of parameterized SQL that can be composed together — Prisma.join(fragments, separator) concatenates an array of them with a given separator (here, ' AND ') into one larger Prisma.sql fragment, while still preserving each fragment's own safely-bound parameters throughout, letting a query's structure be built up conditionally without ever falling back to unsafe string concatenation

DPrisma.join converts its array into a JSON string, embedded as a single text parameter

Answer all 10 questions (7 more), see explanations for every answer, and track your score.