Skip to content
Chapter 7

Databases

Where your application's data lives

Here's a scary thought. You build an amazing app. Users sign up, create posts, upload photos. Then the server restarts. And everything is gone. Every user. Every post. Gone. That's life without a database. Your variables, your arrays, your beautiful in-memory objects -- they all evaporate the instant your server process stops. A database is the thing that makes data survive. It's a magical filing cabinet: organized, indexed, searchable, and it never loses anything -- even when the power goes out, even when a million people are rummaging through it at the same time. Remember the APIs we built in Chapter 4? They sent data to the frontend. But where did that data come from? This is where.

What Is a Database (and Why Not Just Use a File)?

You could save data to a JSON file. Seriously. Read the file, parse it, add some data, write it back. It works. Here's what that looks like in practice:

You write `fs.writeFileSync("users.json", JSON.stringify(users))`. A user signs up. You read the file, push the new user, write it back. Done. Ship it.

Now two users sign up at the exact same time. User A reads the file (10 users). User B reads the file (10 users). User A adds themselves and writes back (11 users). User B adds themselves and writes back... 11 users. Not 12. User A just vanished. This is called a race condition, and it will ruin your week.

That's just the start. How do you find one user out of a million? Read the entire file and loop through it? How do you make sure no two users have the same email? Read the whole file every time someone signs up? What if your server crashes mid-write and the file is now corrupted JSON? Half your data is gone. Forever.

Think of a database as a magical filing cabinet. You don't just throw papers inside -- every document has a labeled folder, every folder sits in the right drawer, and there's an index card system that lets you find any document in seconds, even if there are millions. Multiple people can open different drawers at the same time without interfering with each other. And if someone trips over the power cord, every document that was fully filed is still there when the lights come back on.

That's what a database management system (DBMS) gives you. Concurrent access so thousands of users can read and write at the same time. Indexes that make lookups lightning-fast. Constraints that enforce rules (like "emails must be unique"). Transactions that guarantee all-or-nothing operations -- either the whole thing succeeds or nothing changes. When people say "PostgreSQL" or "MongoDB," they're talking about specific DBMS software. Your app connects to it (usually over a network), sends queries, and gets results back. The DBMS handles all the hard stuff behind the scenes.

Before reading on, think about this: every app you use -- Instagram, Gmail, Spotify -- has a database underneath. What kind of data do you think each one stores?

Database Query Flow

From JavaScript to SQL and back -- how an ORM bridges the gap

Your Code

JS / TS

const users =
  await prisma
  .user
  .findMany({
    where: {
      active: true
    }
  })
ORM (Prisma)

Query Builder

prisma.user
  .findMany()

// Translates JS
// method calls
// into SQL
SQL Query

Generated SQL

SELECT *
  FROM "User"
  WHERE
    "active" = true
  ORDER BY
    "id" ASC
Database

PostgreSQL

┌────┬──────┐
│ id │ name │
├────┼──────┤
│  1 │ Ana  │
│  2 │ Bo   │
│  3 │ Cal  │
└────┴──────┘
Result

Rows returned

// Raw rows
// mapped back
// to objects
[
  { id: 1, ... },
  { id: 2, ... }
]
Your Code

Typed Objects

// User[] type
users.map(u =>
  u.name
)
// ["Ana",
//  "Bo", "Cal"]
Application code
SQL layer
Database layer

SQL vs NoSQL: Two Philosophies, One Question

Two philosophies. One question: how should you organize your data?

Imagine two people organizing a library. The first person creates a strict card catalog system. Every book has an entry. Every entry follows the same format: title, author, genre, shelf number. No exceptions. Want to add a book without an author listed? Too bad -- the system won't allow it. But because everything follows the same structure, you can instantly answer questions like "Show me all science fiction books published after 2020, sorted by author."

The second person takes a different approach. They put each book in a labeled box with whatever information seems relevant. Some boxes have author info, some don't. Some have reviews attached, some have reading notes. It's flexible -- you can store anything. But answering that same cross-referencing question is going to take a lot more work.

That's SQL versus NoSQL.

SQL databases (PostgreSQL, MySQL, SQLite) organize data into tables -- think spreadsheets. Each table has a fixed schema: specific columns with specific data types. A `users` table might have `id`, `name`, `email`, and `created_at`. Every single row follows this structure. No exceptions. The real power comes from relationships. Your `posts` table has a `user_id` column that points back to the `users` table. This lets you ask powerful questions: "Show me all posts by users who signed up this month." The database can answer that in milliseconds because the relationships are built into the structure itself.

NoSQL databases (MongoDB, Redis, DynamoDB) skip the rigid structure. MongoDB stores "documents" -- basically JSON objects. A user might look like `{ name: "Alice", email: "alice@example.com", posts: [...] }`. Different documents can have different shapes. That flexibility is great when your data doesn't fit neatly into rows and columns.

Here's a question: which one should you pick?

For most web applications, start with SQL -- specifically PostgreSQL. The strict structure feels limiting at first, but it catches bugs before they become disasters. Tried to save a user without an email? The database rejects it. Tried to reference a user that doesn't exist? Rejected. That strictness is a feature, not a bug. You can always add NoSQL later for specific use cases like caching (Redis) or storing flexible content (MongoDB).

schema.sqlsql
-- SQL: Creating tables with defined schemas
CREATE TABLE users (
  id        SERIAL PRIMARY KEY,
  name      VARCHAR(100) NOT NULL,
  email     VARCHAR(255) UNIQUE NOT NULL,
  role      VARCHAR(20) DEFAULT 'user',
  created_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE posts (
  id        SERIAL PRIMARY KEY,
  user_id   INTEGER REFERENCES users(id) ON DELETE CASCADE,
  title     VARCHAR(255) NOT NULL,
  content   TEXT,
  published BOOLEAN DEFAULT false,
  created_at TIMESTAMP DEFAULT NOW()
);

-- The REFERENCES keyword creates a foreign key relationship
-- ON DELETE CASCADE means: if a user is deleted, delete their posts too

CRUD: The Only Four Things Apps Do with Data

Every app you've ever used does four things with data. Only four. Create new data, Read existing data, Update data, and Delete data. That's it. Instagram, Gmail, Notion, TikTok -- strip away the UI and it's all CRUD. This is one of those ideas that seems too simple to be important, but it's actually the skeleton key that unlocks how every data-driven app works.

These four operations map directly to SQL statements. Create is INSERT -- you're adding a new row to a table. Read is SELECT -- the most powerful SQL statement, capable of filtering, sorting, joining tables together, and aggregating data. Update is UPDATE -- modifying existing rows. Delete is DELETE -- removing rows.

Here's something that will save you from a catastrophic mistake: always use a WHERE clause with UPDATE and DELETE. Writing `DELETE FROM posts` without a WHERE clause deletes every single post in your table. Not some. All of them. There's a reason this is a running joke among developers -- because it happens to almost everyone once.

Remember the HTTP methods from Chapter 4? They map directly to CRUD: POST creates, GET reads, PUT updates, DELETE deletes. That's not a coincidence. The entire web is built on this pattern. Your API routes receive HTTP requests and translate them into database operations. The frontend sends a POST request, your API runs an INSERT query. A GET request becomes a SELECT. It's the same four operations, all the way down.

Before reading on, think about your favorite app. Can you identify a Create, Read, Update, and Delete action in it?

crud-operations.sqlsql
-- CREATE: Insert new data
INSERT INTO users (name, email, role)
VALUES ('Alice Chen', 'alice@example.com', 'admin');

-- READ: Query data
SELECT * FROM users WHERE role = 'admin';

-- Read with JOIN: combine data from related tables
SELECT
  posts.title,
  posts.created_at,
  users.name AS author_name
FROM posts
JOIN users ON posts.user_id = users.id
WHERE posts.published = true
ORDER BY posts.created_at DESC
LIMIT 10;

-- UPDATE: Modify existing data
UPDATE users
SET role = 'admin'
WHERE email = 'alice@example.com';

-- DELETE: Remove data
DELETE FROM posts
WHERE published = false AND created_at < '2024-01-01';

-- Aggregate functions
SELECT
  users.name,
  COUNT(posts.id) AS post_count
FROM users
LEFT JOIN posts ON posts.user_id = users.id
GROUP BY users.id, users.name
ORDER BY post_count DESC;

ORMs: Because SQL Strings in JavaScript Feel Wrong

Writing SQL strings inside your JavaScript code feels wrong. Because it kind of is. You end up with something like `db.query("SELECT * FROM users WHERE id = " + userId)` -- and congratulations, you just created a SQL injection vulnerability. An attacker could pass `"1; DROP TABLE users"` as the userId, and your entire users table is gone. This is not theoretical. It's one of the most common security attacks on the web.

Even without the security nightmare, raw SQL in JavaScript has problems. Your editor can't autocomplete table names. TypeScript can't check if the column you're querying actually exists. If you rename a column in the database, nothing in your code shows an error until it blows up at runtime.

ORMs (Object-Relational Mappers) fix all of this. They let you interact with your database using JavaScript/TypeScript objects and methods instead of raw SQL strings. The most popular one in the TypeScript world is Prisma.

With Prisma, you define your data model in a `schema.prisma` file, and it generates a fully type-safe client. Instead of writing SQL, you write `prisma.user.create({ data: { name: "Alice" } })`. Your editor autocompletes every field name. TypeScript catches errors at compile time. SQL injection is impossible because queries are automatically parameterized. It feels like the database is just another TypeScript module.

Drizzle ORM is a newer alternative that takes a different approach -- its API feels closer to actual SQL, but with full TypeScript safety. Some developers prefer it because it's lighter and gives more control. Both are excellent choices.

In Chapter 8, we'll see how middleware protects the database access we're setting up here -- making sure only the right users can create, read, update, and delete data.

prisma-example.tstypescript
// First, define your data model in schema.prisma:
//
//   model User {
//     id        Int      @id @default(autoincrement())
//     name      String
//     email     String   @unique
//     posts     Post[]
//     createdAt DateTime @default(now())
//   }
//
//   model Post {
//     id        Int      @id @default(autoincrement())
//     title     String
//     content   String?
//     published Boolean  @default(false)
//     author    User     @relation(fields: [authorId], references: [id])
//     authorId  Int
//   }
//
// Then use the generated client in your TypeScript code:

import { PrismaClient } from "@prisma/client"

const prisma = new PrismaClient()

// CREATE -- fully type-safe, auto-completed
const newUser = await prisma.user.create({
  data: {
    name: "Alice Chen",
    email: "alice@example.com",
  },
})

// READ -- with filtering and relations
const publishedPosts = await prisma.post.findMany({
  where: { published: true },
  include: { author: true },  // JOIN is automatic
  orderBy: { createdAt: "desc" },
  take: 10,
})

// UPDATE
const updatedUser = await prisma.user.update({
  where: { email: "alice@example.com" },
  data: { name: "Alice M. Chen" },
})

// DELETE
await prisma.post.delete({
  where: { id: 42 },
})

// Complex query: users with their post count
const usersWithCounts = await prisma.user.findMany({
  include: {
    _count: { select: { posts: true } },
  },
})
Key Takeaways
  • A JSON file breaks the moment two users write at the same time. Databases solve concurrency, indexing, integrity, and durability -- the problems you can't solve with files.
  • SQL databases (like PostgreSQL) enforce structure with schemas and relationships. That strictness catches bugs before they become disasters. Start here for most web apps.
  • Every app does exactly four things with data: Create (INSERT), Read (SELECT), Update (UPDATE), Delete (DELETE). These map directly to the HTTP methods POST, GET, PUT, and DELETE from Chapter 4.
  • ORMs like Prisma let you talk to your database with type-safe TypeScript instead of raw SQL strings. You get autocomplete, compile-time error checking, and automatic SQL injection protection.
  • NoSQL databases like MongoDB offer flexibility for data that doesn't fit into neat tables, but SQL is the better starting point for most projects.
Quiz
Question 1 of 3

Two users sign up at the exact same time. With a JSON file, one user's data gets lost. Why?