Learn the database every backend actually runs on
A foundations-first relational database path — start with SELECT, WHERE and the relational model, move through joins, subqueries and window functions, then schema design, indexes, transactions and connecting from real application code, all the way to a deployed, production-shaped capstone API. All 10 weeks are live, start to finish.
Prerequisites & tooling
What you should already know
- Comfortable opening a terminal and running a command
- No prior database or SQL experience required — this course starts from zero
- Some exposure to any programming language helps for Week 8 onward, but isn't required to start
- Pairs well with any of this site's backend courses (Node.js, Spring Boot, PHP, Python, Go), but stands entirely on its own
Stack you'll work with
PostgreSQL specifically — the most standards-compliant, widely-used open-source relational database.
The full 10-week roadmap
Ten modules, from SELECT and the relational model through joins, subqueries and window functions, to schema design, indexes, transactions and connecting from real application code — capped by a deployed capstone API. Every week below is live.
SQL Fundamentals — SELECT, WHERE, ORDER BY & the Relational Model
Install PostgreSQL, connect for the first time, and write the queries that make up most of what anyone does in SQL.
- Tables, rows, columns & primary keys — the relational model
- Installing PostgreSQL & connecting with
psql SELECT,WHERE,ORDER BY,LIMIT& core data types
Filtering, Aggregation & GROUP BY
Turn SQL from a lookup tool into a real analysis tool.
- Richer
WHEREwithAND/OR/IN/BETWEEN/LIKE COUNT,SUM,AVG&GROUP BY/HAVING- NULL-safe comparisons &
COALESCE
Joins — INNER, LEFT, RIGHT, FULL & Self Joins
Combine data that lives across multiple tables — the whole point of "relational."
INNER JOINvs.LEFT JOINvs.RIGHT JOINFULL OUTER JOIN& self joins- Joining three or more tables correctly
Subqueries, CTEs & Window Functions
Answer questions a single SELECT can't, without leaving SQL.
- Subqueries in
WHEREandFROM - Common Table Expressions with
WITH - Window functions:
ROW_NUMBER,RANK& running totals
Schema Design & Normalization
Design a schema that doesn't fight you six months later.
- Normal forms: 1NF, 2NF & 3NF, with real examples
- Foreign keys & referential integrity
- Modeling one-to-many & many-to-many relationships
Constraints, Indexes & Query Performance
Stop guessing why a query is slow, and prove it instead.
NOT NULL,UNIQUE&CHECKconstraints- B-tree indexes & when they actually help
- Reading a real query plan with
EXPLAIN ANALYZE
Transactions & ACID Guarantees
What actually keeps your data correct when things go wrong mid-write.
BEGIN/COMMIT/ROLLBACKin practice- Isolation levels & the race conditions they prevent
- What atomicity actually guarantees, with a real failure scenario
Connecting from Application Code
Go from a query typed by hand to a query a real app runs.
- Connecting from Node.js with
node-postgres - Parameterized queries & preventing SQL injection
- Where a query builder or ORM fits in, and where it doesn't
Views, Stored Procedures, Functions & Triggers
Push some logic into the database itself, deliberately and sparingly.
CREATE VIEWfor a reusable, named query- Functions & the basics of PL/pgSQL
- Triggers for automatic, database-enforced behavior
Capstone: Design, Build & Deploy a Real Postgres-Backed API
Apply every module in one project — a properly designed, tested, deployed database behind a real API.
- A normalized schema designed from a real domain
- A REST API backed by it, with proper transactions
- Deployed on a real, managed PostgreSQL host