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).

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.

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.

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)

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 WHERE clauses 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.

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.

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 $1 and the separate array argument? That’s a parameterized query. 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.