PostgreSQL for SaaS Developers
Learn to use PostgreSQL as the persistent data layer behind a modern SaaS backend. Covers connecting and running SQL, schema design (tables, keys, constraints), migrations with Claude Code, transactions and concurrency control, and the query-performance tools (EXPLAIN, indexes, N+1 detection) you need to keep an app fast under load.
About This Course
Learn to use PostgreSQL the database that powers most modern SaaS products as the persistent storage behind an API you can build on top of. By the end of this course you will know how a relational database thinks (tables, rows, keys, constraints), how to design a schema for a real SaaS feature with users, items, and orders, how to evolve that schema safely with migrations, how transactions keep concurrent requests from stepping on each other, and how to spot and fix the slow queries that break your product under load. Every lesson pairs a short teaching segment with a hands-on lab in which Claude Code writes the SQL and the glue code for you you learn what to ask for and how to read what comes back.
Course Curriculum
10 Lessons
What a relational database is — tables, rows, and why we don't just use a file
Learn what a relational database actually is in plain English — a shape made of tables, rows, and columns, with specific rules about what each piece can hold. You will understand why Lumen Trail's catalog can't live in a JSON file forever, what a database connection looks like, and what SELECT really does under the covers. By the end you will have the mental model to open the hands-on lab after this and run your first real SQL against a running Postgres server.
Connect to Postgres and run your first SELECT - Lab Exercises
Open the Lumen Trail Postgres starter, connect to a running Postgres server with the psql command-line tool, create a single trail_items table by hand, INSERT a few rows, and read them back with SELECT. Then connect from a tiny Node script so you've seen both of the two ways real applications talk to Postgres — a terminal client for exploration and ad-hoc work, and a programmatic client for everything your server does.
Schema design — primary keys, foreign keys, and the constraints that keep data honest
Learn how to shape a real SaaS schema: which column is the stable identity (the primary key), how one table points at another (the foreign key), and the constraints (NOT NULL, UNIQUE, CHECK) that keep bad data from ever reaching storage. You will see why putting the schema's rules in the database — not in your handler code — is the single highest-leverage habit a SaaS developer can build.
Design the Lumen Trail SaaS schema - Lab Exercises
Design and create the Postgres schema for the Lumen Trail SaaS backend: a users table, a trail_items table owned by a seller, and an orders table that references both. Along the way you will add primary keys, foreign keys, NOT NULL and CHECK constraints, and unique indexes — and query the data with a real JOIN. Claude Code writes the SQL; you learn what each piece is for.
What migrations are and why your schema can't live in your head
Learn why "running SQL by hand on production" doesn't work and what the industry's answer is: a numbered chain of migrations, each a small, reversible change to the schema that every environment — your laptop, staging, production — runs through in the same order. You will understand what a migration file looks like, how a migration tool tracks which ones have run, and why the two most-expensive database bugs in a startup's life are "forgot to migrate" and "migrated the wrong environment."
Add migrations to the Lumen Trail schema with Claude Code - Lab Exercises
Wire up node-pg-migrate as the Lumen Trail backend's migration tool, convert the hand-run schema from the previous lab into a numbered chain of migrations, and add a new feature (an orders.notes column + a trail_items.category column) via a reversible migration. By the end you will have a schema that can be rebuilt from zero on any machine with a single command.
Transactions and concurrency — ACID in plain English, and the two kinds of locking
Learn what a database transaction actually is (ACID in plain English), why running two write operations without wrapping them in one is the single most common source of inconsistent data in a SaaS backend, and how to pick between the two concurrency-control patterns that solve it: pessimistic row-level locking (SELECT … FOR UPDATE) and optimistic concurrency control (a version column + a conditional UPDATE). You will also see the deadlock pattern that catches every beginner the first time they lock two rows in different orders.
Make the Lumen Trail checkout transactional and race-safe - Lab Exercises
Convert the Lumen Trail checkout flow — "decrement inventory and create an order" — from a pair of plain SQL statements that can race each other into a single transactional block, and add optimistic concurrency control via a version column so two users clicking "buy the last one" at the same instant can't both succeed. You will simulate the race with two concurrent connections and watch the second one correctly refuse.
Query performance — EXPLAIN, indexes, and the N+1 problem
Learn the three tools every SaaS developer needs for keeping an app fast as data grows: EXPLAIN (the query plan — Postgres telling you exactly how it's going to run your SELECT), indexes (the data structure that turns a 10,000-row scan into a one-step lookup), and the N+1 problem (the single most common performance bug in production — fixed with a JOIN or an IN clause). By the end you will recognize a bad query plan when you see one and know the two fixes that cover 90% of real cases.
Diagnose and fix slow Lumen Trail queries - Lab Exercises
Load the Lumen Trail database with 10,000 items and 50,000 orders, run a slow query, read its EXPLAIN output, add the one missing index that fixes it, and watch the query drop from seconds to milliseconds. Then spot and fix a classic N+1 pattern in the API code — one query becomes 51 — and replace it with a single JOIN. By the end you will have the muscle memory to catch both bugs on your own future code.