PythonSQLSQLiteIntermediate~4h

ORMs

The queries you did not write

An object mapper turns a loop over eight rows into twenty-one statements and never mentions it. This is how to put the number on it first — then bring it down, change the schema underneath it without stopping, and say where the mapper stops and SQL starts.

Start this project See the modules

Modules: 6
Drills: 10
Build steps: 6
Time: ~4h

Walk out able to

Say how many statements a piece of ORM code will send before running it, turn an N plus one into two, and name the query that has outgrown the mapper.

What you drill

Class, table, row

What a class commits to, plus the join table nobody names.

Lazy, and what it costs

Which lines go to the database, and how many times.

Expand, migrate, contract

Change a live schema in three separate deployments.

A mapper of your own

Seventy lines of Python, then the query it cannot spell.

The pitch

What you practise, and what you leave with

A mapping layer of your own over two SQLite tables: a lazy relation, a counter, a batch that takes nine statements to two, and one query in raw SQL.

You will practise

Statements a block sends, before you run it
Lazy loads, caches, and what a call re-queries
Eager loads: one statement or two, rows or none
Migration graphs, and what a rollback takes too

Afterwards you can

Count the queries before the mapper sends them
Turn an N plus one into two statements
Change a live schema without stopping it
Say when a query has outgrown the mapper

Modules

6 modules, 27 items

Lessons explain one idea. Drills repeat it until it sticks. Build steps make something that exists afterwards.

1
Map the class onto the table
~38 min
Say what a declaration commits to, and why two lookups give one object.
- LessonWhy count the queries4 min
- LessonWhat the mapper keeps track of8 min
- LessonWhat a declaration commits to7 min
- DrillCount what a declaration commits to10 min
- DrillFollow one row through a session9 min
2
When the query actually runs
~25 min
Tell a line that describes work from a line that does it.
- LessonNothing happens until you look9 min
- LessonWhy calling it twice costs twice7 min
- DrillSpot the line that goes to the database9 min
3
Count the queries
~52 min
Put an exact number on a loop before you run it, then bring it down.
- LessonCount the statements before you run it9 min
- LessonBring the count down to two10 min
- DrillCount the statements a loop sends12 min
- DrillCount the rows a joined load returns11 min
- DrillPick the strategy that fits the shape10 min
4
Change a schema that is live
~35 min
Split one schema change into three deployments that each stand alone.
- LessonChange a schema nobody can stop using9 min
- LessonWhat a rollback takes with it7 min
- DrillWalk a migration graph9 min
- DrillDeploy a change without stopping10 min
5
Drop to SQL on purpose
~36 min
Name the query that has outgrown the mapper, and what leaving costs.
- LessonWhere the mapper stops7 min
- LessonWhat leaving the mapper costs9 min
- DrillName the query that outgrew it10 min
- DrillPrice the bulk statement10 min
6
Build the mapper
~1h
Write the layer that hides the queries, then count what it hid.
- Build stepStand up the shelf8 min
- Build stepWrap the rows in objects14 min
- Build stepLet the relation load itself14 min
- Build stepBatch the second query14 min
- Build stepSplit the name without stopping14 min
- Build stepAsk it what the mapper cannot16 min