APIs with FastAPI · Chapter 9 of 26
Databases
By the end of this chapter you can describe a table as a SQLAlchemy model, create it in Postgres, save and read rows through a session, give every request its own session with a yield dependency, say what a commit does and what happens without one, and explain what the engine's connection pool is for. Build it in your browser: a real editor and terminal, AI that explains, and tests that tell you when it works.
- Part: Structure and data
- 4 tasks
- FastAPI
- In the all labs plan
The first 2 chapters of APIs with FastAPI are free. This one comes with the all labs plan.
Why you need this
Until now DakiyaGo's parcels have lived in a Python dictionary. Sunita found out what that means on Monday: Aman restarted the API to change a setting, and every parcel booked since the morning was gone. It gets worse in production, where the API runs as four processes, each with its own dictionary, so a parcel booked through one is unknown to the other three. And Sunita's own scripts, which mark parcels picked up from the hubs, cannot see the dictionary at all.
Parcels need to live outside the API, in one place every process and every script shares, that survives restarts and never half-saves a row. That is a database. DakiyaGo uses Postgres, and talks to it from Python with SQLAlchemy.
What you build: 4 tasks
Each task has tests. You write the code in the lab's editor and press Check.
- Task 1
db.py: the parcels table
Parcels must survive a restart. In db.py, make a SQLAlchemy engine from the environment variable DATABASE_URL, a session maker bound to it, a declarative Base, and a model Parcel for the table parcels with seven columns, all required (NOT NULL): awb (a string of up to 12, the primary key), merchant_id (a string of up to 3), to_pincode (a string of 6), weight_grams and cod_amount (whole numbers), status (a string of up to 20) and booked_at (a date and time with its time zone).
status and booked_at have defaults the database itself fills in, booked and the time of the insert, so a row any program inserts without them gets them. And a function get_session that yields a new session and closes it afterwards, even when the route fails. The check creates the table from your Base, inspects it in Postgres, inserts a row without status and booked_at, and runs get_session the way FastAPI does.
- Task 2
book.py: save a booking
Make an app that creates the tables when it starts (from db.py's Base) and saves bookings. POST /parcels takes a JSON body with awb (DKG and five digits), merchant_id, to_pincode (six digits), weight_grams (more than 0) and cod_amount (0 or more, 0 when left out), anything else answering 422. It saves the parcel through a session from get_session, commits, and answers 201 with the saved parcel: its seven columns, with status and booked_at as the database filled them.
The check starts your app on a database of its own, books parcels, and looks for them in Postgres from another connection.
- Task 3
track.py: read a parcel
Make an app that creates the tables when it starts and answers GET /parcels/{awb} with that parcel from the database: an object with its seven columns (booked_at with its time zone). An AWB the database does not have answers 404.
The check fills the table with the 40 parcels from /workspace/data/parcels.json from its own connection, reads some through your API, changes one in the database, and reads it again.
- Task 4 · interview question
Interview: status.py, a session per request
Sunita's scripts change statuses straight in the database, and her dashboard changes them through the API. Make an app (tables created at startup) with GET /parcels/{awb}/status answering {"awb": ..., "status": ...}, and PUT /parcels/{awb}/status taking {"status": ...} (one of booked, picked_up, in_transit, out_for_delivery, delivered, returned; anything else 422), saving it and answering the same shape. Both answer 404 for an AWB the database does not have.
The check reads a status, changes it from its own connection and reads it again, changes one through PUT and looks in Postgres, and asks Postgres whether any of your app's connections is left inside a transaction between requests. The interviewer will ask: why one session per request, and not one for the whole app?
What you will learn
- The engine and a model: how SQLAlchemy reaches Postgres, and a table written as a Python class.
- Defaults the database fills in, and why they belong there.
- The session: plan changes, then commit them.
- A session per request, as a yield dependency.
- The connection pool, and what one shared session does to it.
More in Structure and data
Build "Databases" yourself
DakiyaGo, a Jaipur courier startup, takes bookings on WhatsApp. Merchants want an API, and you build it. A real editor and terminal in your browser, AI that explains, and a Check that says when it works.
The first 2 chapters of APIs with FastAPI are free. This one comes with the all labs plan.