Section · 01

Relational vs document — pick relational by default

Relational (SQL):
  PostgreSQL    — modern default; powerful, free, great
  MySQL / MariaDB — older default; still everywhere
  SQLite        — single-file; great for dev, embedded, small projects

Document (NoSQL):
  MongoDB       — JSON-shaped documents; flexible schema
  Firestore     — Google's hosted NoSQL with realtime sync
  DynamoDB      — AWS's serverless NoSQL

Which to pick

For a new web app, default to PostgreSQL. Relational databases have stronger guarantees, a clearer query language, and the best tooling. Document databases shine when your data is naturally hierarchical (rare) or when you genuinely have no schema (rarer than people think).

Section · 02

Tables, rows, and columns

A table is like a spreadsheet sheet. Each row is one record. Each column is one field with a defined type.

users
┌────┬─────────────────────┬─────────────┬─────────────────────┐
│ id │ email               │ name        │ created_at          │
├────┼─────────────────────┼─────────────┼─────────────────────┤
│  1 │ york@yorksims.com   │ York Sims   │ 2026-01-15 10:23:00 │
│  2 │ bob@example.com     │ Bob Smith   │ 2026-02-03 14:17:09 │
└────┴─────────────────────┴─────────────┴─────────────────────┘

One column is the primary key — usually an id that uniquely identifies each row. Other tables refer to it via foreign keys.

Section · 03

SQL CRUD — the four operations

-- CREATE
INSERT INTO users (email, name) VALUES ('york@yorksims.com', 'York');

-- READ
SELECT id, email FROM users WHERE name = 'York';
SELECT * FROM users ORDER BY created_at DESC LIMIT 10;

-- UPDATE
UPDATE users SET name = 'York S.' WHERE id = 1;

-- DELETE
DELETE FROM users WHERE id = 1;

That’s 95% of SQL. The other 5% is joins, indexes, aggregates, and views — which you learn as you need them.

Section · 04

Joins — connecting tables

Most data has relationships. Users have orders, orders have line items, line items reference products. Joins combine related rows.

-- Show each order with its user's name
SELECT
  orders.id,
  orders.total,
  users.name
FROM orders
JOIN users ON users.id = orders.user_id
WHERE orders.status = 'paid';

Four kinds of join you’ll see:

INNER JOIN  — only rows that match in both tables (default; the common case)
LEFT JOIN   — all rows from the left, matched rows from the right (NULL if no match)
RIGHT JOIN  — opposite of LEFT (rare in practice — just flip the tables)
FULL JOIN   — everything from both (rare)

Section · 05

Indexes — the difference between fast and slow

Without an index, finding a row means scanning every row in the table. Fine at 100 rows; lethal at 10M. An index is a sorted lookup structure that lets the database find rows quickly.

-- Probably your most-used column for lookups
CREATE INDEX idx_users_email ON users(email);

-- Composite — useful when you filter on both
CREATE INDEX idx_orders_user_status ON orders(user_id, status);

Rule of thumb: index the columns you put in WHEREclauses and the foreign-key columns you JOIN on. Indexes speed up reads but slow down writes (the DB has to update them too), so don’t index everything.

Section · 06

ORMs and query builders

An ORM (Object-Relational Mapper) lets you write database queries in your application language instead of raw SQL.

// Prisma — currently the popular Node ORM
const user = await prisma.user.findUnique({
  where: { email: "york@yorksims.com" },
  include: { orders: true },
});

// Drizzle — lighter alternative, closer to raw SQL
const user = await db
  .select()
  .from(users)
  .where(eq(users.email, "york@yorksims.com"));

// SQLAlchemy (Python)
user = session.query(User).filter_by(email="york@yorksims.com").first()

ORM or raw SQL?

ORMs are great for type safety and 80% of CRUD. They’re a leaky abstraction for complex queries — at some point you’ll write a 7-table join the ORM can’t express, and you’ll drop down to SQL. That’s fine. Most modern ORMs let you escape to raw SQL when needed.

Section · 07

Connecting from your app

// A typical Node.js + Postgres setup

import { Pool } from 'pg';
const pool = new Pool({ connectionString: process.env.DATABASE_URL });

// Run a query
const result = await pool.query(
  'SELECT * FROM users WHERE id = $1',
  [userId]   // <-- parameterized, NOT string-concatenated!
);
console.log(result.rows[0]);

Parameterized queries — non-negotiable

See $1and the separate array argument? That’s a parameterizedquery. The user input is sent to the database separately from the SQL — it can’t change the structure of the query. This is the only thing standing between your app and SQL injection. We’ll cover that — and a few of its friends — in the final lesson.

Database basics, the place state actually lives.