An API Backed by SQLite

Put it together: an API whose routes never touch SQL, because all of it lives in one data module. You'll give every request its own database connection with a FastAPI dependency, create and change tables with numbered migration scripts, and test against a real, throwaway database. You'll also learn to read the ORMs many projects use. Then you'll move a workout log API onto SQLite.

Updated

The Data Code in One Place

  1. 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.
  2. 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.
  3. 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.

Testing with a Real Database

  1. Testing with a Real, Throwaway Database (Journeyman): API tests that touch a database get a fresh SQLite file in pytest's tmp_path for every test, with the migrations run and a few rows added in a fixture, and app.dependency_overrides swaps get_db for one that opens that file, so the tests run real SQL without ever touching the real data.

When a Project Uses an ORM

  1. SQLAlchemy and SQLModel: When a Project Uses an ORM (Journeyman): An ORM such as SQLAlchemy or SQLModel lets a project describe each table as a Python class and ask for rows with Python methods that write the SQL for it; this lesson reads the same workout query written with sqlite3, SQLAlchemy and SQLModel, what each costs and gives, and the signs that show which one a project uses.

Exercise: Workout Log API

  1. Exercise: Workout Log API (Journeyman): Move a gym's in-memory FastAPI workouts API onto SQLite by writing its two migration scripts, a data module that holds every SQL statement, a get_db dependency that gives each request its own connection, and routes that call them, tested on a throwaway database through app.dependency_overrides.

Up Next: Users and Security

  1. Up Next: Users and Security (Journeyman): Look back at what Module 3 taught you, see a project that puts all of it to work, a book club's API moved onto SQLite, and see the gap Module 4 closes, by giving an API accounts, sign-in and rules about who may do what.