Transactions & Raw SQL — Series 2

Preview — 3 of 10 questions

What's the key difference between $executeRaw and $queryRaw in Prisma?

javascript

// $executeRaw — for statements with no result rows, returns affected row count
const count = await prisma.$executeRaw`UPDATE users SET active = false WHERE last_login < ${cutoff}`;
// count: number, e.g. 42

// $queryRaw — for statements that produce rows, returns them as an array
const users = await prisma.$queryRaw<User[]>`SELECT * FROM users WHERE active = true`;
// users: User[]

A$executeRaw is for statements that don't return rows (INSERT, UPDATE, DELETE without RETURNING) and resolves to the number of affected rows; $queryRaw is for statements that DO return rows (SELECT, or INSERT ... RETURNING) and resolves to an array of result rows

B$executeRaw and $queryRaw are exact synonyms; either works for any raw SQL statement

C$executeRaw only works with $queryRawUnsafe-style unparameterized strings; $queryRaw only works with Prisma.sql

D$executeRaw runs the SQL asynchronously in the background without waiting for completion, while $queryRaw waits

How do you safely build a WHERE id IN (...) clause with a dynamic array of IDs using $queryRaw?

javascript

const ids = [1, 5, 9];
const users = await prisma.$queryRaw`SELECT * FROM users WHERE id IN (${Prisma.join(ids)})`;

APrisma.join(ids) converts the array into a single JSON string that PostgreSQL parses at query time

BPrisma.join(ids) expands the array into a comma-separated list of properly parameterized placeholders (e.g., $1, $2, $3) inside the Prisma.sql template, avoiding both SQL injection and the need to manually build placeholder strings

CArrays cannot be interpolated into $queryRaw templates at all; each id must be a separate $queryRaw call

DPrisma.join() is only usable outside of tagged template literals, as a standalone function call

What do maxWait and timeout control for prisma.$transaction(async (tx) => {...}, { maxWait, timeout })?

javascript

await prisma.$transaction(
  async (tx) => {
    await tx.order.create({ data: { /* ... */ } });
    await tx.inventory.update({ /* ... */ });
  },
  {
    maxWait: 5000,  // wait up to 5s to get a connection from the pool
    timeout: 10000, // once running, the transaction has up to 10s to complete
  }
);

AmaxWait and timeout are aliases for the same setting; only one needs to be specified

BThese options only apply to sequential transactions ($transaction([...])), never to interactive transactions

CmaxWait is how long Prisma will wait to acquire a database connection for the transaction before giving up; timeout is how long the transaction itself is allowed to run before Prisma automatically rolls it back — both exist specifically to prevent a stuck or overly long interactive transaction from holding a connection indefinitely

Dtimeout controls how long the client waits for a network response; it has no effect on the actual database transaction

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