Databases & ORM

Your data lives here now. Everything you built in Module 15, the waitlist signups, the blog post drafts, the comment threads, was in memory. Refresh the browser, gone. Real products need real persistence.

A database is not magic storage. It's a precise, reliable engine for organizing information into tables and relationships. You'll think differently once you understand it: not "where do I save this?" but "what shape should this data have?" That shape, your schema, is the contract between your code and your data. Get it right once, and the database takes care of the rest.

This module teaches you to think in tables and relationships. We use Drizzle, a TypeScript ORM, to write queries without leaving your code. Then we peek underneath to see the actual SQL, because understanding what the ORM generates is what separates "I know how to use the tool" from "I know what's happening."

A relational database organizes data into tables. Each table is a collection of rows (records), and each row has columns (fields). That's it. Everything else follows from that simple shape.

Primary key: A unique identifier for each row. Usually id. Every row has one.

Foreign key: A column that references another table's primary key. posts.user_id points to users.id. This is how you model relationships. One user can have many posts. Many posts can have many comments.

Your TypeScript types (User, Post, Comment) are semantic. Your database schema is structural. A good schema makes your types obvious. A bad schema makes your types confused. Drizzle bridges this gap: your schema generates your types, so they stay in sync forever.

A schema is the blueprint. It says: "A users table has these columns with these types. A posts table connects to users like this."

Here's a real schema using Drizzle:

What's happening:

• pgTable creates a PostgreSQL table definition.
• Each field has a type (text, uuid, timestamp, etc.) and constraints (notNull, unique, default, etc.).
• .references() creates a foreign key relationship. onDelete: 'cascade' means if a user is deleted, their posts and comments are too.
• The type exports (User, NewUser) are automatically inferred from the schema. No manual type duplication.

Migrations. A schema doesn't magically appear in your database. You create a migration, a file that says "create this table" or "add this column." Drizzle's migration system tracks them, so you can version your schema like you version your code.

The generated SQL looks like:

The migration is your code. The SQL is the platform's language. Drizzle generates the SQL, but you're writing the schema in TypeScript. You stay in your language; the ORM handles the translation. This is the core value of an ORM.

An ORM (Object-Relational Mapper) is a bridge. On one side: your JavaScript objects. On the other: SQL queries. The ORM writes the SQL so you don't have to (most of the time).

Without an ORM, you'd write:

With Drizzle:

Both do the same thing. The ORM version is type-safe (if userEmail is wrong, TypeScript yells before you run it) and readable (the intent is clear).

Important: An ORM is not magic. It's a thin layer. The SQL is still there. Understanding what it generates is crucial. A query that looks simple in Drizzle can be slow if you don't know what SQL it's creating.

CRUD = Create, Read, Update, Delete. The four operations that cover 90% of database work.

Create

The ORM generates INSERT statements. .returning() tells the database to send back the inserted rows (with generated IDs, timestamps, etc.).

Read

The .with syntax is key. It tells Drizzle to fetch related data (the post's comments). Without it, you'd only get the post, then you'd need a separate query to get the comments. That's the N+1 problem, which we'll cover next.

Update

Delete

Note: You don't usually delete users or posts directly. Cascading deletes handle it (defined in the schema). If a user is deleted, all their posts and comments go with them.

To understand your ORM, you need to see the SQL underneath. Let's take a read operation and trace it.

Drizzle query:

The SQL Drizzle generates:

Notice: two queries. First gets the user, then gets their posts. Drizzle orchestrates these for you and stitches the result back together. In your code, it's one object. In the database, it's two round trips.

Better SQL (a JOIN):

If you're in the database directly (or need maximum control), you'd write:

This is one query. It's more efficient if the data is small. It's overkill if the post count is huge (you'd be sending megabytes of JSON). SQL's power is that you can express exactly what you want, but that power comes with complexity.

When to write raw SQL:

Use raw SQL when:

• You need maximum performance and the ORM's generated SQL is too slow.
• You're doing something the ORM can't express (complex window functions, recursive CTEs, etc.).
• You're debugging and need to see exactly what's running.

In Drizzle, raw SQL falls back to:

The backtick syntax prevents SQL injection. The ${} placeholders are parameterized.

The N+1 problem is the most common performance trap. Here's what it looks like:

The Problem:

If there are 100 posts, you just made 101 queries. One for all posts, then one for each post's author. That's the "N+1": one query, then N queries in a loop.

The reason this ships is that it is invisible until the database is far away. Drag the round trip from a local database up to a real one and watch the gap open.

const posts = await db.posts.findMany();

for (const post of posts) {
  post.author = await db.users.find(
    post.authorId
  );
}

const posts = await db.posts.findMany({
  include: { author: true },
});

The Fix:

Use .with to fetch related data in one shot:

But wait, Drizzle still makes two queries under the hood (we saw this in Part 5). Why isn't that N+1?

Because Drizzle is smart. It fetches all posts in one query, collects all the user_ids, then fetches all those users in one query. Two queries total, not 101. This is called a "batch fetch."

When N+1 happens in real code:

The key: always think about how many queries you're making. One query is fast. N+1 queries is slow. Drizzle's .with syntax prevents it most of the time.

Your assignment: take the waitlist and blog platform from Module 15 and give it real persistence.

Step 1: Define Your Schema

Start with the data you already have. A user who signed up, a blog post they wrote, the comments people left.

Questions to ask yourself:

• What shape is the data right now? (Waitlist emails, blog posts, comments.)
• What relationships exist? (A post can have many comments. A waitlist entry is standalone.)
• What constraints matter? (Email must be unique. A comment needs a post. A post needs a title.)

Step 2: Generate and Run Migrations

Verify the tables exist:

You should see waitlist_members, blog_posts, post_comments.

Step 3: Write CRUD Helpers

Create a file for your database operations:

Step 4: Wire It Up

In your Module 15 API routes, replace in-memory storage with database calls:

Step 5: See the SQL

Before your code ships, peek at what SQL Drizzle generates. In development, enable query logging:

Run a query. You'll see the SQL printed. Is it what you expected? That's the "peek under the hood" moment.

Step 6: Spot an N+1 Bug

In your code, intentionally introduce an N+1 query:

With query logging enabled, you'll see dozens of queries. Then fix it:

Log it again. Now you see two queries, not dozens.

Before you move on, verify:

Schema:

Persistence:

CRUD:

SQL:

Reflection questions:

• What happens when you delete a user whose posts are still in the database? (Cascading delete should handle it.)
• If you have 1000 blog posts and you want to show each post with its comment count on a homepage, how many queries do you make? (2: one for posts with .with: { comments: true }, then you count on the client. Not 1001.)
• When would you write raw SQL instead of using Drizzle? (When the ORM can't express what you need, or for maximum performance on a slow query.)

Your schema and your types, one source of truth

Your TypeScript code and your database schema started as separate worlds. You wrote types in your code, defined columns in migrations, and hoped they stayed in sync. They didn't.

Drizzle closes this gap. Your schema generates your types. One source of truth. TypeScript checks at compile time that you're not trying to insert a string into a number column. This is the value of the bridge: your language and your platform align.

The cost: you're learning two systems (TypeScript and SQL) simultaneously. But the symmetry helps. eq(posts.id, postId) looks like TypeScript because it is. Underneath, it's WHERE "posts"."id" = $1 in SQL. The syntax changed, but the meaning is identical.

The database is doing the actual work

Your CRUD functions are your code. The database engine is the platform. The ORM is the conversation between them.

A tempting trap: thinking the ORM is enough. It's not. You still need to understand queries. An ORM that generates SELECT * FROM posts, users WHERE posts.user_id = users.id is a Cartesian product (every post matched with every user). The ORM made a mistake, and you need to spot it.

This is why Part 5 matters. Peek at the SQL. Learn to read it. The platform (Postgres) is doing the actual work. The ORM is translating your intent into its language. Knowing both languages makes you fluent.

Once your data persists, you can:

• Add authentication (which user owns which post?).
• Add authorization (which user can delete which comment?).
• Optimize slow queries (add indexes to frequently-searched columns).
• Back up and restore (your data is valuable now).
• Scale (read replicas, caching, etc.).

For now: make the data reliable, make the queries correct, and make the schema match your code. Everything else follows.