Up Next: Data with SQLite

Look back at what Module 2 taught you, see a project that puts all of it to work, an event sign-up API with a test for every rule, and see the gap Module 3 closes, by keeping an API's data in a SQLite database with SQL you write yourself.

Journeyman · FastAPI · REST API · Updated

By

After this lesson you can

  • Describe what Module 3's SQLite database, SQL and data module will add to an API built in Module 2

Key takeaways

  • After Module 2 you can check every request with Pydantic models and field rules, choose what goes out with response models, give every error one shape, and prove it all with TestClient tests.
  • An event sign-up API, with field rules, response models, a 409 when an event is full and a test for every rule, uses everything this module taught.
  • Data kept in a Python list lives only as long as the app runs. Stop the app and start it again, and every ticket sold is gone.
  • Module 3 keeps the data in a SQLite database, with the SQL in one data module, a connection for every request, and tests on a real, throwaway database.

At the start of this module, your API believed every request it got. Now it trusts nothing: it checks every field, refuses bad input with a clear answer, keeps private fields to itself, and has tests that prove each of those things in a second. That's the difference between an API that works in a demo and one other people can build an app on.

This lesson looks back at what you can do, shows you a project that puts it all together, and gives you a first look at Module 3, where the API's data stops disappearing.

What you can do now

After Module 2, you can:

  • check input by hand, and know why an API never trusts a request body;
  • describe a request body with a Pydantic model, with field rules for lengths, ranges and choices, and read FastAPI's 422 answer field by field;
  • choose what goes back out with response models, so a field such as a staff note never leaves the API;
  • design create, update and delete the REST way: POST and 201, PUT and PATCH, DELETE and 204, and a 404 for each when the item isn't there;
  • raise your own exceptions for the problems your API knows about, such as a 409 when a ticket's status can't change, and give every error one shape;
  • test an API end to end with pytest and TestClient: the status code, the body and what changed, every error, one test for each bad value, and a fresh start for every test.

Put it all together: an event sign-up API

Here's a project that uses all of it, one you can build on your own now. A community center wants an API for its events, kept in memory:

  • events, each with a title, a date, a room and a capacity;
  • sign-ups, each with a name, an email and how many seats, checked by field rules: a valid email, one to four seats, an event date in the future;
  • response models that never show other people's emails;
  • a 409 when an event is full, 404s for events and sign-ups that aren't there, and one error shape for every error;
  • PATCH to change a sign-up;
  • a test for every rule, written by you with pytest and TestClient.

When it's done, you have an API that refuses bad input politely and proves it, one you can show a friend.

Where it falls short

Every API in this module keeps its data in memory, in a Python list. Buy 2 tickets from Starlight Stage's API, then ask for the show. Each answer below is a request, its status line and the body that came back:

POST /shows/2/tickets  {"quantity": 2}
HTTP/1.1 201 Created
{"id":1,"show_id":2,"quantity":2,"total":66}

GET /shows/2
HTTP/1.1 200 OK
{"id":2,"title":"Midnight Brass Band","date":"2026-12-05","price":32,"seats_left":2}

Now stop the app with Ctrl+C, start it again, and ask once more:

GET /shows/2
HTTP/1.1 200 OK
{"id":2,"title":"Midnight Brass Band","date":"2026-12-05","price":32,"seats_left":4}

The 4 seats are back, and the ticket is gone. The list lived in the app's memory, and memory is cleared when the program stops. A real venue's app restarts every time its code changes, and a fan's ticket can't vanish with it. The tests liked that clean start, but the fans won't.

What Module 3 unlocks

Module 3 keeps an API's data where a restart can't reach it:

  • A database: a program built to keep data safely on disk and find it again fast. You'll use SQLite, a whole database in one file, which comes with Python in the sqlite3 module, so there's nothing extra to install.
  • SQL from zero: the language you ask a database with, to make tables, add rows, find them, change them and remove them, with placeholders that keep an attacker's text out of your queries.
  • Related tables: a show's tickets in their own table, linked to the show, and joins that bring them back together.
  • One data module that holds every query, a database connection for every request, and numbered changes to the tables as the API grows.
  • Tests against a real, throwaway database, made fresh for every test, the same fresh start this chapter's fixture gave the in-memory data.

Check yourself

2 questions to try on the lesson page.

So what

Module 2 taught your API to say no to bad input and to prove it; Module 3 teaches it to remember what it said yes to.

Key terms

Related