Stage 2 of 7 · 10 lessons · ~3 months
Relational databases and SQL
Almost every backend is a careful wrapper around a relational database. Learn to model data, write SQL by hand, index it for real queries and understand what your ORM does on your behalf.
Start: Queries and joinsLessons· one lesson for each topic, in this order
- 1Queries and joinsLesson
- 2Grouping and aggregatesLesson
- 3Data modelling and normalisationLesson
- 4Keys, constraints and relationshipsLesson
- 5IndexesLesson
- 6Reading query plans with EXPLAINLesson
- 7Transactions and ACIDLesson
- 8Isolation levels and lockingLesson
- 9Prisma or Drizzle: schema and migrationsLesson
- 10The N+1 query problemLesson
Start with· follow one of these
- CoursePaidGrokking Relational Database Design and Modeling
Design Gurus · Intermediate · $98 one-time
The modelling and normalisation decisions that stay with a codebase for years
- CourseFreeCS50's Introduction to Databases with SQL
Harvard University · Beginner
Free course from querying through schema design, indexes, transactions and scaling
Practise
Also worth your time
- Database Indexing Explained: B-Tree, Hash, Bitmap, R-Tree, and When to Use Each (opens in a new tab)Read
B-tree and composite indexes, plus the leftmost-prefix rule that trips people up
What sits under your queries: storage engines, transactions and isolation levels
Before moving on: Design a PostgreSQL schema for a small booking or ordering system, write the ten queries it needs by hand, add indexes and show with EXPLAIN ANALYZE that they are used.
Write raw SQL before you touch an ORM, otherwise you will never notice when it fires a hundred queries to render one page. Learn on PostgreSQL; it is the safest default and what most new projects pick.