Databases & SQL — Where Data Lives

What you'll learn

• What a database is and why apps need one
• Tables, rows, columns — the spreadsheet model
• SQL — the language to ask questions of data
• SQL vs NoSQL, and you'll write query logic yourself

A giant, smart spreadsheet 📊

When you close an app, your data doesn't vanish — a database stored it permanently. The most common type, a relational database (MySQL, PostgreSQL, SQLite), organizes data like linked spreadsheets:

• A table = one sheet (e.g. users)
• A column = a field (id, name, email)
• A row = one record (one actual user)

users table
┌────┬─────────┬──────────────────┐
│ id │ name    │ email            │
├────┼─────────┼──────────────────┤
│ 1  │ Sara    │ sara@mail.com    │
│ 2  │ Omar    │ omar@mail.com    │
└────┴─────────┴──────────────────┘

You talk to it with SQL (Structured Query Language) — remarkably close to English:

SELECT name FROM users WHERE id = 1;          -- get Sara's name
INSERT INTO users (name, email) VALUES ('Lina', 'lina@mail.com');
UPDATE users SET email = 'new@mail.com' WHERE id = 2;
DELETE FROM users WHERE id = 2;

These four — SELECT, INSERT, UPDATE, DELETE — are called CRUD (Create, Read, Update, Delete) and are 95% of what backends do all day.

SQL vs NoSQL: SQL databases have a strict structure (every user row has the same columns) — great when data is well-defined and relationships matter. NoSQL databases (MongoDB, etc.) store flexible documents — great when structure varies or you need massive scale. Most apps start with SQL; reach for NoSQL for specific needs.

Indexes and JOINs — speed and relationships 🔗

Two ideas turn a plain table of data into a real, fast, relational database.

Indexes — the book's table of contents. Without an index, finding WHERE email = 'sara@mail.com' in a million-row table means scanning every single row, one by one — a full table scan. Slow. An index is a separate, pre-sorted lookup structure (like a book's index at the back) that lets the database jump almost straight to the matching rows instead of reading everything:

CREATE INDEX idx_users_email ON users(email);
-- now WHERE email = '...' is fast even with millions of rows

The trade-off: indexes speed up reads but slightly slow down writes (every INSERT/UPDATE must also update the index) and use extra disk space. You index the columns you filter or sort by often — not every column.

JOINs — connecting tables. Real data is rarely one flat table. An e-commerce app has users and separately orders, linked by a user_id column in orders pointing back to a user's id. A JOIN stitches matching rows from two tables into one result:

SELECT users.name, orders.total
FROM orders
JOIN users ON orders.user_id = users.id;
-- "Sara" | 45.00
-- "Omar" | 120.00

This is the core idea behind "relational" in relational database: tables relate to each other through shared keys (a foreign key like user_id), instead of duplicating every user's name inside every single order row.

Real-world case study: this platform's own database 🔍

This is not hypothetical — this very learning platform runs on a real relational database. Its backend (a Cloudflare Worker) uses D1, Cloudflare's managed SQLite database, with a schema that starts like this:

CREATE TABLE IF NOT EXISTS users (
  id          INTEGER PRIMARY KEY AUTOINCREMENT,
  github_id   TEXT UNIQUE NOT NULL,
  username    TEXT NOT NULL,
  email       TEXT,
  created_at  DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX IF NOT EXISTS idx_users_username ON users(username);

Notice the exact concepts from this lesson: id is the primary key (uniquely identifies each row), github_id is marked UNIQUE so no two users can share one GitHub account, and there's a real index on username — because the app looks up users by username often enough that a full table scan would be wasteful as the user count grows.

Every query in the Worker's code uses parameterized queries like env.DB.prepare("SELECT * FROM users WHERE github_id = ?").bind(user.sub) — the ? placeholder plus .bind() is the standard, safe way to insert values into SQL without exposing the app to SQL injection (an attacker sneaking SQL commands through a text field). This lesson's abstractions aren't academic — they're exactly what runs in production for the app you're learning in right now.

Summary

• Databases store data permanently as tables (rows + columns)
• SQL does CRUD: SELECT, INSERT, UPDATE, DELETE
• SQL = structured + relational; NoSQL = flexible + scalable
• You simulated a WHERE query in Python
• Indexes make lookups fast at scale; JOINs connect related tables through keys — you built an index-style lookup by user_id
• This platform's own D1 database shows these ideas in production, including safe parameterized queries against SQL injection

Practice: try free interactive SQL at sqlbolt.com. Next: APIs & REST — how the frontend and other services talk to your database-backed server.