Week 7 · Season 2 · Face and hands
Remembering things
You can design tables for FRIDAY's tasks and chats, ask them questions in SQL, change them with migrations, use the database safely from Python, and keep data right when two things happen at once.
This week
- Tables, rows, keys and SELECT
- WHERE, JOIN and counting with GROUP BY
- Modelling tasks and chats; numbered migrations
- SQLite from Python; SQL injection and placeholders
- Transactions, indexes and speed
- Go deeper: query plans, row-level security, backups
How you learn
Write every query by hand and predict its result before you run it. Use a placeholder for every value a user sends, and wrap writes that belong together in one transaction.
What you build
FRIDAY remembers: its tasks and chats live in a SQLite database, so they survive a restart. Draw the tables before you create them, change them only with numbered migrations, and prove it with a restart test.
Stretch: Read a query plan and add the index it needs, try row-level security in Postgres, then take a backup and practise restoring it.
What you ship
Project 1: FRIDAY goes online. A chat page, your own server and database, real tools and sign-in, reachable from your phone.
- PLAN.md Part 5, a schema drawing and numbered migrations for tasks, chats and messages
- FRIDAY keeps its tasks and chats after a restart, with restart, sneaky-name and transaction tests
Lessons
- Spreadsheets with superpowers
After this deck you can explain why a Python list forgets everything when a program stops, say what a database is, describe a table of rows and columns with a primary key, and read a simple
SELECTquery in SQL. - Asking good questions
After this deck you can filter rows with WHERE, match rows from two tables with a JOIN, and count them per group with
COUNTand GROUP BY, in short queries you can read out loud. - Shape your data
After this deck you can sketch tables for FRIDAY's tasks, notes and chats, choose a one-to-many design over a list crammed into one cell, and change a live database safely with numbered migrations.
- Python meets the database
After this deck you can use SQLite from Python (
connect,execute,commit), say when people reach for Postgres instead, spot SQL injection in code that glues text into SQL, and stop it with placeholders. - Many hands, one notebook
After this deck you can explain how two requests at once can corrupt data, use a transaction so a group of changes happens all together or not at all, and add an index to speed up a slow search, knowing what it costs.
- Go deeper: plans, locks and backups
After this deck you can read a query plan (a SCAN versus a SEARCH using an index) and use it to fix a slow query, explain how Postgres row-level security hides other people's rows even from buggy code, and plan backups you have actually restored.
Labs
Python in the browser, next to the lessons.
Weekend build
- FRIDAY remembers: tasks and chats that survive a restart
Your server gets a notebook: tasks and chats live in a SQLite database, so a restart no longer wipes FRIDAY's memory.
Skills you prove
- Databases & SQL
- Store data in a database and query it with SQL, safely under concurrent use
Free links for this week
Hand-picked official docs, papers and talks.
- SQLite documentation · docs
- Python: sqlite3 · docs
- PostgreSQL tutorial · docs
- OWASP: SQL injection prevention cheat sheet · docs
- PostgreSQL: Using EXPLAIN · docs
- PostgreSQL: Row security policies · docs
- PostgreSQL: Backup and restore · docs
- SQLite: the query planner · docs
- SQLite: atomic commit · docs