The Data Code in One Place
The Data Code in One Place: Keeping the Data Code in One Place, A Database Connection for Every Request, Creating and Changing Tables: Migrations.
Updated
- Keeping the Data Code in One Place (Journeyman): A data module is the one file in an API that holds every SQL statement, with functions that take a connection and give back plain values (an id, a dictionary, a list, or None for a row that isn't there), so the routes call it and never write SQL themselves.
- A Database Connection for Every Request (Journeyman): A get_db dependency opens a SQLite connection for each request, hands it to the route with yield, commits when the route succeeds, rolls back when it raises, and always closes it, so plain def routes ask for a connection with Depends instead of opening and closing their own.
- Creating and Changing Tables: Migrations (Journeyman): Migrations are numbered SQL scripts that create and change an API's tables, run in order when the app starts, with SQLite's PRAGMA user_version remembering how many a database has had, so every copy of the database gets each change exactly once.