Data & where it lives
A builder's view of databases: SQL vs NoSQL at decision level, schemas and relationships, what an ORM buys you, and where the DB sits — all pointing into Module 3 for depth.
Every app’s data has to live somewhere, survive a restart, and come back fast. You don’t need to hand-tune queries to direct an AI well, but you do need the builder’s view: what an ORM buys you, how data relates, when SQL beats NoSQL, and the one performance trap (N+1) AI walks into constantly. (The deep treatment — query planning, indexes, transactions — is Module 3; this is the map that points there.)
Why an ORM (and why you still learn SQL)
You can write "SELECT * FROM todos WHERE id = " + id — and you’ve just opened the door
to SQL injection and a typo-prone mess. An ORM lets you write
db.todo.findUnique({ where: { id } }): it generates safe, parameterized SQL, maps rows to
typed objects, and gives the AI a schema it can autocomplete against. It doesn’t excuse you
from understanding SQL — when something’s slow or a query is subtle, you need to read what
the ORM generated — but it stops you hand-gluing strings. (SQL depth: Module 3.)
Model the nouns: relationships
Data modeling at the builder level is mostly “name the nouns and how they relate.” A user
has many todos — that’s a one-to-many, expressed as a foreign key (userId) on the
todo. A todo can have many tags and a tag many todos — that’s a many-to-many, expressed
through a join table. Getting these relationships right up front is what lets you tell the
AI “a Project has many Tasks, each Task belongs to one User” and get a sane schema.
SQL or NoSQL? And the trap called N+1
For most apps, SQL (Postgres) is the right default — structured data with relationships and real joins. Reach for NoSQL when the data is naturally document-shaped, the schema must flex, or you’re at a scale where denormalization pays off. The decision is about shape and access pattern, not fashion. The performance trap to know by name is N+1: fetch a list of 100 todos, then loop and fetch each one’s user separately — that’s 101 queries instead of 1. AI-written ORM code falls into this constantly; the fix is to fetch the related data in one go.
| SQL (relational) | NoSQL (document) | |
|---|---|---|
| Data shape | structured, related rows | flexible, nested documents |
| Joins | first-class | awkward — you denormalize instead |
| Schema | defined, migrated | flexible per document |
| Reach for it | most apps — the safe default | huge scale, document data, flexible schema |
Migrations, and where the DB sits
Schema changes are migrations — versioned, applied in order, repeatable across dev / staging / prod — never a hand-edit of the live database (that’s how environments drift and data gets lost). And the database sits behind the API: reachable by your handlers and background workers, never by the browser, with its connection string held as a secret in an env var. It’s the one component whose data must survive everything else restarting.
01 Learning objectives
0 / 6 done02 Curated reading
03 Knowledge check
- 01easy
The main reason to use an ORM over hand-built SQL strings is…
- 02medium
What is an N+1 query problem?
- 03medium
For an app with related, structured data and joins, the safe default is…
04 Interview questions
browse all ↗What gets asked on this topic — tap a card for how to approach it, the follow-ups, and the trap. Company tags are best-effort & sourced.
-
What is an N+1 query, how would you spot it, and how do you fix it?
An N+1 happens when you fetch a list (1 query) and then loop over it, firing one more query per item (N more) — 100 orders becomes 101 queries. You spot it by seeing a database call inside a loop, or by watching the query log explode with one row's-worth of queries per item. You fix it by fetching the related data in a single query — a JOIN, or the ORM's
include/with/eager-load — so it's one or two queries regardless of list size. It's the classic ORM (and AI-generated) performance bug because the naive code reads so naturally.What a strong answer covers1 query for the list + N queries (one per item) in a loop.
Spot it: a DB call inside a loop, or the query log exploding per item.
Fix: fetch the relation in one query (JOIN / include / eager-load).
Fine on 10 rows, a meltdown on 10,000 — it hides until scale.
Quick self-checkYour page lists 200 posts and shows each author's name; it fires 201 DB queries. The fix?
-
Wrong — it scales the symptom, not the cause; the query count is still linear in posts.
-
Correct — that collapses 201 queries to one or two regardless of list size.
-
Wrong — masks it and serves stale data; the underlying N+1 remains.
-
Wrong — the access pattern, not the database type, is the problem.
Follow-ups they push on- Why does an N+1 pass code review and the demo, then fail in production?
- How do indexes relate to query performance? (see 3.4)
Red flag Accepting AI code with a `db.x.find()` inside a `.map()`/`for` loop — it's almost always an N+1.
source: Prisma — Solving the n+1 problem ↗