Prisma & SQL Interview Questions

This lesson does not repeat Backend, Lesson 07 (indexes, EXPLAIN, ACID, isolation levels) or Backend, Lesson 08 (the N+1 problem, DataLoader, connection pool basics). Read those first if you need them. Here we go to the things interviewers ask next: the Prisma 7 setup, the migration workflow, modeling, extensions, safe raw SQL, and the SQL features that separate a junior from a senior.

Words used in this lesson

• An ORM (object-relational mapper) is a library that lets you read and write database rows as objects in your language. Prisma is one. Think of it as a translator between TypeScript and SQL.
• A schema file (schema.prisma) is the text file where you describe your models (tables), fields (columns) and relations. Prisma reads it to build the database changes and the typed client.
• A migration is a saved, ordered SQL file that changes the database structure, such as "add a column". Together the files form the migration history.
• A driver adapter is a small package that connects Prisma Client to a database driver, such as pg for PostgreSQL. In Prisma 7 you always pass one.
• A transaction is a group of database steps that either all succeed or all roll back.
• Schema drift means the real database no longer matches what your migration files say it should be.
• A shadow database is a temporary empty database that Prisma creates and deletes to check your migrations.
• A window function is an SQL function that calculates over a set of related rows without collapsing them into one row (like a running total next to each row).
• A CTE (common table expression) is a named sub-query written with WITH name AS (...). Think of it as a temporary variable for a query.

Setting up Prisma 7

Prisma 7 changed the setup. Three things surprise people who learned Prisma 5 or 6.

1. The database URL moved to prisma.config.ts. The datasource block in schema.prisma now only names the provider.

generator client {  provider = "prisma-client"  output   = "../src/generated/prisma"}datasource db {  provider = "postgresql"}

// prisma.config.tsimport "dotenv/config";import { defineConfig, env } from "prisma/config";export default defineConfig({  schema: "prisma/schema.prisma",  migrations: { path: "prisma/migrations", seed: "tsx prisma/seed.ts" },  datasource: { url: env("DATABASE_URL") },});

2. .env is not loaded for you. I removed the dotenv line and the CLI stopped with Cannot resolve environment variable: DATABASE_URL. Load it yourself.

3. The client needs a driver adapter and a generated folder. The prisma-client generator requires an output path. It writes plain TypeScript into your project, not into node_modules. You import from that folder.

import { PrismaClient } from "./generated/prisma/client";import { PrismaPg } from "@prisma/adapter-pg";const adapter = new PrismaPg({ connectionString: process.env.DATABASE_URL });export const prisma = new PrismaClient({ adapter });

For SQLite use PrismaBetterSqlite3 from @prisma/adapter-better-sqlite3 with { url: "file:./dev.db" }. I ran both. The CLI reported Query Compiler: enabled: Prisma 7 plans queries in TypeScript/WASM and the driver runs them, instead of the old Rust query-engine binary.

Modeling: relations, enums, JSON, indexes

A relation links two models. Prisma stores it as a foreign key (a column that points to another table's row).

model Profile {  id     Int  @id @default(autoincrement())  userId Int  @unique  user   User @relation(fields: [userId], references: [id], onDelete: Cascade)}model PostCategory {  postId     Int  categoryId Int  assignedAt DateTime @default(now())  post       Post     @relation(fields: [postId], references: [id])  category   Category @relation(fields: [categoryId], references: [id])  @@id([postId, categoryId])}

Implicit or explicit? I use implicit when the link has no data of its own (a post and its tags). I use explicit as soon as the link needs a column (assignedAt, role, quantity), or when I want to query the link table directly. Moving from implicit to explicit later needs a data migration, so I decide early.

Referential actions say what the database does when a parent row is deleted. If you write nothing, the defaults are onDelete: Restrict for required relations (SetNull for optional ones) and onUpdate: Cascade. I checked the generated SQL: Post_authorId_fkey ... ON DELETE RESTRICT ON UPDATE CASCADE. Deleting a user who still has posts fails with Prisma error P2003. The profile above uses Cascade, so it disappears with its user.

Enums, JSON and indexes:

enum Role { USER ADMIN }model User {  id       Int   @id @default(autoincrement())  email    String @unique  role     Role  @default(USER)  settings Json?  @@index([settings], type: Gin)}model Post {  authorId  Int  createdAt DateTime @default(now())  @@index([authorId, createdAt])}

• An enum becomes a native PostgreSQL enum type (CREATE TYPE "Role" AS ENUM ...). Adding a value later is easy; removing one is not.
• Json becomes jsonb. You can filter inside it with where: { settings: { path: ["theme"], equals: "dark" } } (I ran this on PostgreSQL). A Gin index (Generalized Inverted Index, built for values with many parts) helps such searches.
• Prisma does not index foreign keys in PostgreSQL. Add @@index([authorId]) yourself (Backend, Lesson 07).
• Full-text search on PostgreSQL still needs previewFeatures = ["fullTextSearchPostgres"] in the generator. Then where: { body: { search: "fox & quick" } } works. For serious search (typo tolerance, ranking) I use a search engine such as Meilisearch rather than the database.

The migration workflow

Prisma Migrate compares your schema file with your migration history and writes SQL. The commands differ by where you run them.

The shadow database and drift

When you run migrate dev, Prisma creates a temporary shadow database, replays all your migration files in it, and compares the result with your real development database. If they differ, you have drift. After it finishes, the shadow database is deleted. The user in your connection string therefore needs the right to create databases locally. Hosted databases often need a manual shadowDatabaseUrl.

I created drift on purpose: I ran ALTER TABLE "Post" ADD COLUMN views ... by hand. Then:

• prisma migrate status said "Database schema is up to date!". It only compares migration files with the history table. It does not look at the real tables.
• prisma migrate dev said "Drift detected ... Added column views" and offered a reset.

I fixed it without losing data. I added views to the schema, wrote the matching ALTER TABLE into a new migration folder by hand, and ran prisma migrate resolve --applied <folder>. Then migrate dev said "Already in sync". In production I never want a reset, so this "write it down, then mark as applied" path matters.

Baselining an existing database

Baselining means giving a database that already exists, and holds real data, a starting migration that is marked as "already applied". I ran this flow on a database made with plain SQL:

npx prisma db pull                      # write models from the existing tablesmkdir -p prisma/migrations/0_initnpx prisma migrate diff --from-empty --to-schema prisma/schema.prisma \  --script > prisma/migrations/0_init/migration.sqlnpx prisma migrate resolve --applied 0_init   # do NOT run it, just record itnpx prisma migrate deploy               # "No pending migrations to apply."

New developers get the real 0_init SQL when they build a fresh local database. Production skips it. Later, migrate dev generated only the new ALTER TABLE.

Seeding

Put the command in prisma.config.ts (migrations.seed) and run npx prisma db seed. Write seeds with upsert so you can run them many times. I ran mine twice and got no duplicates.

Reading and writing data

select, include, omit

select lists the fields you want. include adds relations to all default fields. omit removes fields from the default set. You cannot use select and include together at the same level (I got: "Please either use include or select, but not both"). You can nest them: select a relation, and select inside it.

const user = await prisma.user.findFirst({  select: { id: true, email: true, posts: { select: { title: true }, take: 1 } },});const safe = await prisma.user.findFirst({ omit: { settings: true, deletedAt: true } });

omit is the clean way to keep a passwordHash out of API responses.

relationLoadStrategy

A load strategy says how Prisma fetches relations. With include: { posts: true } I counted the SQL statements:

Without the flag, passing relationLoadStrategy is a validation error ("Unknown argument"). Some articles say join is the default. That is only true after you turn the feature on. The docs still call it a preview feature in 7.x. One join query means fewer round trips but repeated parent data inside JSON. Two queries are often better for wide parents with big lists. I measure on real data.

Nested writes

A nested write creates or links related rows in one call, and Prisma wraps it in a transaction.

await prisma.user.create({  data: {    email: "a@x.com",    profile: { create: { bio: "hi" } },    posts: { create: [{      title: "P1",      tags: { connectOrCreate: [{ where: { name: "js" }, create: { name: "js" } }] },    }] },  },  include: { posts: { include: { tags: true } } },});

connectOrCreate links to a tag if it exists, or creates it. For an implicit many-to-many, tags: { set: [] } removes every link (I ran it).

Pagination

Offset pagination uses skip and take. Cursor pagination (also called keyset) remembers the last row you saw:

const page1 = await prisma.post.findMany({ take: 3, orderBy: { id: "asc" } });const page2 = await prisma.post.findMany({  take: 3, skip: 1, cursor: { id: page1.at(-1)!.id }, orderBy: { id: "asc" },});

skip: 1 skips the cursor row itself. The generated SQL uses WHERE id >= (SELECT id ... = $1) ... OFFSET 1. On a 300,000-row table in PostgreSQL I measured the same idea in plain SQL: OFFSET 250000 read 250,020 index entries in about 24 ms, while WHERE id > 250000 LIMIT 20 read 20 entries in 0.05 ms. The cursor needs a unique, ordered column. See Practical Design Problems, Lesson 03.

Transactions

Prisma has two forms.

// 1. Sequential: an array of queries, run in order in one transactionconst [count, posts] = await prisma.$transaction([  prisma.user.count(),  prisma.post.findMany({ take: 1 }),]);// 2. Interactive: a function; you can read, decide, then writeawait prisma.$transaction(async (tx) => {  const u = await tx.user.findUniqueOrThrow({ where: { email: "a@x.com" } });  await tx.post.create({ data: { title: "in-tx", authorId: u.id } });}, { maxWait: 3000, timeout: 8000, isolationLevel: "Serializable" });

I forced a duplicate email as the second item of a sequential transaction. It threw P2002 and the first user was not saved. I held an interactive transaction open for 5.5 seconds and got P2028 after about 5.5 seconds. Inside tx you must use tx, not the outer prisma; the outer client runs outside the transaction. Many simple cases need no transaction at all: update ... data: { views: { increment: 1 } } is already atomic (it compiles to SET views = views + $1).

Raw queries, safely

Use $queryRaw (returns rows) and $executeRaw (returns the number of changed rows) when Prisma cannot express the query, such as window functions.

const evil = "x' OR '1'='1";await prisma.$queryRaw`SELECT id FROM "User" WHERE email = ${evil}`;               // 0 rows: safeawait prisma.$queryRawUnsafe(`SELECT id FROM "User" WHERE email = '${evil}'`);       // ALL rows: injected!await prisma.$queryRawUnsafe(`SELECT id FROM "User" WHERE email = $1`, evil);        // 0 rows: safe

I ran all three on 5 users: the second returned 5 rows. The tagged template ($queryRaw`...`) turns each ${value} into a bound parameter ($1, $2). The Unsafe version is safe only when you pass values as parameters and never join strings. More facts I saw:

• Calling $queryRaw("SELECT 1") like a normal function fails: it is a tag function.
• A table or column name cannot be a parameter. SELECT * FROM ${"User"} failed with P2010 (syntax error near $1). Check names against an allow-list in code.
• Lists: WHERE id IN (${Prisma.join(ids)}) with Prisma.sql works.
• count(*) comes back as a bigint. Cast it (::int) or convert it, or JSON serialization will fail.

Extensions replace middleware

Middleware (prisma.$use) is gone in Prisma 7: typeof prisma.$use is undefined. Its replacement is client extensions, added with $extends. An extension can change queries (query), add model methods (model), add computed fields (result) or add client methods (client).

Soft delete, and why it leaks

Soft delete means marking a row as deleted (deletedAt) instead of removing it. This is the classic extension:

const xprisma = prisma.$extends({  query: {    user: {      async findMany({ args, query }) {        args.where = { deletedAt: null, ...args.where };        return query(args);      },    },  },});

I tested it. findMany and findFirst hid the deleted user. But:

• findUnique still returned the deleted user.
• count() still counted them.
• include: { author: true } on posts still loaded the deleted author.

The extension only covers the operations you list, on the model you list. Soft delete is easy to start and hard to finish. Alternatives I weigh: a database view that shows only live rows, row-level security, or hard deletes plus an audit table. Also remember unique columns: a soft-deleted email still blocks a new signup unless you use a partial unique index (WHERE deleted_at IS NULL, written in a hand-edited migration).

Errors, types, testing

Error codes to know: P2002 unique constraint failed, P2003 foreign key failed, P2025 record not found (also thrown by update and findUniqueOrThrow), P2028 transaction API error (such as a timeout), P2010 raw query failed. Check them with error.code, or instanceof Prisma.PrismaClientKnownRequestError. Map them to HTTP codes in one place (Backend, Lesson 04): P2002 to 409, P2025 to 404.

Type safety. The client types depend on what you ask for. If you select only id and email, then user.name is a compile error. To name a result type, use satisfies and GetPayload:

import { Prisma } from "./generated/prisma/client";const postWithAuthor = {  include: { author: { select: { id: true, name: true } } },} satisfies Prisma.PostDefaultArgs;type PostWithAuthor = Prisma.PostGetPayload<typeof postWithAuthor>;

I compiled this with tsc. The older Prisma.validator helper is not present in the Prisma 7 generated client, so satisfies is the way now.

Testing. I test against a real database, not a mock, because the behaviours above (constraints, cascades, error codes) live in the database. A mock would hide them. My pattern with the built-in Node test runner:

beforeEach(async () => {  await prisma.$executeRaw`TRUNCATE "User" RESTART IDENTITY CASCADE`;});test("unique email is enforced", async () => {  await prisma.user.create({ data: { email: "t@x.com" } });  await assert.rejects(    prisma.user.create({ data: { email: "t@x.com" } }),    (e: any) => e.code === "P2002",  );});

Use a separate test database and run migrate deploy once before the suite. Mock Prisma only for pure unit tests of business logic, and keep that logic away from the client with a small repository layer.

Pooling, serverless and Accelerate

In Prisma 7 the pool (the set of reusable database connections) belongs to the driver. For pg through PrismaPg, the defaults are max: 10 connections and connectionTimeoutMillis: 0, which means wait forever. I tested with max: 1 while one transaction held the only connection. A second query was still waiting after 1.5 seconds. With connectionTimeoutMillis: 500 it failed after about 540 ms with "timeout exceeded when trying to connect". A request that hangs forever is worse than an error, so I always set a timeout.

For serverless, keep the pool small per instance, reuse one client per process, and put a pooler (PgBouncer or your provider's pooler) in front. Use a direct URL for migrations. Details are in Backend, Lesson 08.

Core SQL that Prisma will not write for you

Run these in psql. I ran every one on PostgreSQL 16 with small tables: users(id, name, manager_id) and orders(id, user_id, total, status, created_at).

Join traps

• Anti-join (find rows with no match). LEFT JOIN ... WHERE o.id IS NULL and WHERE NOT EXISTS (...) both returned the 2 users with no orders.
• NOT IN and NULL. One order had user_id = NULL. WHERE id NOT IN (SELECT user_id FROM orders) returned 0 rows, because comparing with NULL is "unknown". Use NOT EXISTS.
• A WHERE on the right table undoes a LEFT JOIN. ... LEFT JOIN orders o ON o.user_id = u.id WHERE o.status = 'paid' returned 3 users (the unmatched ones vanished). Moving the test into ON ... AND o.status = 'paid' returned all 5.
• Fan-out. Join an order with 3 item rows and sum(total) gives 300 for an order worth 100. Aggregate first, then join, or use count(DISTINCT ...).
• count(*) counts rows (6); count(user_id) skips NULL (5).

Window functions

SELECT user_id, created_at, total,  sum(total)  OVER (PARTITION BY user_id ORDER BY created_at) AS running,  row_number() OVER (PARTITION BY user_id ORDER BY total DESC) AS rn,  lag(total)  OVER (PARTITION BY user_id ORDER BY created_at) AS prevFROM orders WHERE user_id IS NOT NULL;

PARTITION BY splits rows into groups. ORDER BY inside OVER orders inside each group. With ties on values 10, 10, 20: row_number gave 1,2,3; rank gave 1,1,3 (it skips); dense_rank gave 1,1,2 (no gaps). "Top order per user" is row_number() = 1, or in PostgreSQL SELECT DISTINCT ON (user_id) ... ORDER BY user_id, total DESC, which gave the same rows.

CTEs, recursion and LATERAL

A recursive CTE walks a tree such as an org chart:

WITH RECURSIVE tree AS (  SELECT id, name, 0 AS depth, name::text AS path FROM users WHERE manager_id IS NULL  UNION ALL  SELECT u.id, u.name, t.depth + 1, t.path || ' > ' || u.name  FROM users u JOIN tree t ON u.manager_id = t.id) SELECT * FROM tree ORDER BY path;   -- Ann > Bob > Di

In PostgreSQL 12 and later, a simple CTE is inlined: EXPLAIN showed one Seq Scan with both filters. Write WITH c AS MATERIALIZED (...) and it became a separate CTE Scan. So "CTEs are an optimization fence" is old advice. LATERAL lets a sub-query use columns of the row to its left. CROSS JOIN LATERAL (SELECT ... WHERE o.user_id = u.id ORDER BY created_at DESC LIMIT 1) gives the latest order for each user, which is a "top N per group" tool.

Locking in practice

SELECT ... FOR UPDATE locks rows (Backend, Lesson 07). Two more options matter:

• FOR UPDATE SKIP LOCKED skips rows another transaction holds. It is how you build a job queue. I held jobs 1 and 2 in one session. A second session ran the same query and got jobs 3 and 4, with no waiting.
• NOWAIT fails at once instead of waiting: ERROR: could not obtain lock on row.

One EXPLAIN note

On 300,000 rows, WHERE created_at::date = current_date used a Parallel Seq Scan, because the function on the column hides the index. Compare the column to a range instead (created_at >= today AND created_at < tomorrow).

Prisma 7 and the Prisma 8 release candidate

The Prisma docs say Prisma 8 is a release candidate, with general availability expected in October 2026. In it the schema file is called a contract (contract.prisma), and the command that makes types is contract emit. The docs also list what is not available yet, including $extends, most nested writes and transaction isolation levels. Prisma 7 keeps getting fixes for 18 months after Prisma 8 is final. So I stay on 7 for production, and I check the version of every article I read.

Common mistakes

1. Letting npm install prisma float to a new major version.
2. Creating a PrismaClient per request, or never setting a pool timeout.
3. Using $queryRawUnsafe with string concatenation.
4. Trusting migrate status as proof of no drift, or running migrate dev or db push on production.
5. Calling the outer prisma inside an interactive transaction.
6. Assuming a soft-delete extension covers findUnique, count and relations.

Interview questions

Key takeaways

• Pin Prisma to 7 for now. Check the version of every tutorial: migrate dev no longer generates or seeds, $use and Prisma.validator are gone, and the URL lives in prisma.config.ts.
• migrate dev is local and uses a shadow database; migrate deploy is for production. migrate status does not detect drift.
• Baseline existing databases with migrate diff --from-empty, then migrate resolve --applied.
• Use the tagged $queryRaw. $queryRawUnsafe with string joins is an injection hole.
• Extensions replace middleware, but a soft-delete extension must cover every read path.
• Set a pool timeout. In Prisma 7 with pg, the default is to wait forever.
• Know the SQL traps: NOT IN with NULL, LEFT JOIN plus WHERE, join fan-out, window frames, SKIP LOCKED.

What's next

Lesson 09: Hasura & GraphQL. Another way to expose the same database: a GraphQL API that compiles queries to SQL, with permissions built in.