📄️ Relational Databases: How Structured Data Works
Understand what relational databases are, why structure and relationships matter, and when to choose SQL over NoSQL. The foundation for everything in this module.
📄️ SQL Basics: Reading Data with SELECT
Write your first SQL queries using SELECT, FROM, WHERE, ORDER BY, and LIMIT. Learn declarative thinking and start pulling real data from the ShopFlow tables.
📄️ SQL Aggregations: Summarizing Data
Use COUNT, SUM, AVG, MIN, and MAX to summarize data. Learn GROUP BY to split results into categories and HAVING to filter aggregated groups — and understand why WHERE can't do that job.
📄️ SQL Joins: Combining Tables
Learn how to query across multiple tables using INNER JOIN, LEFT JOIN, and self-joins. Understand why joins work, how the cartesian product danger appears, and how to build the ShopFlow order summary query.
📄️ SQL Writes: INSERT, UPDATE, DELETE
Add, modify, and remove data in PostgreSQL using INSERT, UPDATE, and DELETE. Learn why UPDATE without WHERE is dangerous, how to preview destructive operations, and how BEGIN/COMMIT/ROLLBACK keep your data safe.
📄️ Schema Design: Tables, Types, and Constraints
Create PostgreSQL tables from scratch using CREATE TABLE. Choose the right data types, add constraints that enforce business rules, and build the complete ShopFlow schema yourself.
📄️ Foreign Keys and Relationships: Linking Tables
Understand how foreign key constraints enforce referential integrity in PostgreSQL. Learn ON DELETE CASCADE, RESTRICT, and SET NULL, and design one-to-many and many-to-many relationships correctly.
📄️ Indexes: Making Queries Fast
Understand what indexes are, how PostgreSQL decides to use them, and how to create the right indexes for your queries. Learn to read EXPLAIN ANALYZE output and avoid the common pitfall of over-indexing.
📄️ Connecting Node.js to PostgreSQL: node-postgres (pg)
Connect a Node.js application to PostgreSQL using the pg driver. Learn connection pooling, parameterized queries, TypeScript types, and why parameterized queries are the only safe way to handle user input.
📄️ Query Builders: Knex.js
Use Knex.js to build type-safe SQL queries in JavaScript, manage database migrations, and seed development data. Understand when a query builder is the right abstraction — and when it isn't.