APIs with FastAPI · Chapter 10 of 26

Queries and relationships

By the end of this chapter you can connect two tables with a foreign key and a relationship, build a query with filters, ordering, a count and paging, spot the N+1 problem and fix it with selectinload, and explain why offset paging skips or repeats rows and how a cursor does not. 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

DakiyaGo's parcels are in Postgres now, and the merchants use them. Imran has three complaints from their developers in one week. Gulabi Block Prints wants only its own parcels that are out for delivery, not all of them. Imran's dashboard of merchants and their parcels has become slow, and Aman's database log shows why: one query for the merchants and then one more for every merchant. And Thar Silver Works pulls every parcel page by page each night into its own system, and some nights the same parcel arrives twice.

Reading one row by its key (chapter 9) is not enough. An API asks the database questions: which rows, in what order, how many, which page, and with what connected to them. Asking them well decides whether the API answers in 5 milliseconds or 5 seconds, and whether it answers correctly.

What you build: 4 tasks

Each task has tests. You write the code in the lab's editor and press Check.

  1. Task 1

    models.py: merchants and their parcels

    In models.py, as in chapter 9's db.py: an engine from DATABASE_URL, SessionLocal, Base and get_session. Two models: Merchant (table merchants: id, a string of up to 3 and the primary key; name, city and plan, strings) and Parcel (table parcels: the seven columns of chapter 9, with merchant_id now a foreign key to merchants.id). Merchant.parcels is the list of a merchant's parcels and Parcel.merchant is a parcel's merchant, two sides of one relationship.

    And a function seed(session) that adds the 5 merchants from /workspace/data/merchants.json (without their keys) and the 40 parcels from parcels.json (booked_at as the file has it) and commits. The check creates the tables, tries a parcel for a merchant who does not exist, runs seed, and follows both relationships.

  2. Task 2

    listing.py: filter, order, count, page

    Make an app (tables created at startup, from models.py) with GET /parcels and optional query parameters status and merchant_id (each an exact match; both together means both), limit (1 to 50, default 10) and offset (0 or more, default 0). It answers {"total": ..., "items": [...]}: total is how many parcels match the filters, and items is one page of them, newest booking first (two parcels booked at the same moment: the higher AWB first), each with its seven columns.

    Do the work in SQL: the check watches the SQL your app sends, and looks for a WHERE when there are filters, a count, and a LIMIT. It fills the database from its own connection.

  3. Task 3

    merchant_parcels.py: no N+1

    Imran's merchant dashboard shows every merchant with its parcels. Make an app (tables created at startup) with GET /merchants answering a list of all merchants by id, each an object with exactly id, name, city, plan and parcels: its parcels by AWB, each with exactly awb, to_pincode and status.

    The check counts the SQL queries one request sends: at most two, however many merchants there are.

  4. Task 4 · interview question

    Interview: cursor.py, paging that survives new parcels

    Merchants' software pulls every parcel page by page while new bookings keep arriving. Make an app (tables created at startup) with GET /parcels?limit= (1 to 50, default 10) and after= (optional), newest booking first (ties: higher AWB first), answering {"items": [...], "next": ...}: one page of parcels with their seven columns, and next, a cursor (text) for the page after, or null on the last page. Following next from the first page must give every parcel exactly once, in order, even when new parcels are booked between pages and when several share a booking time. An after this API never gave out answers 400.

    The interviewer will ask: why does offset paging skip or repeat rows, and what does a cursor remember instead?

What you will learn

  • Foreign keys and relationships: rows that point at other rows.
  • Queries: select with where, order_by, limit, offset, and a count, built step by step.
  • The N+1 problem, and loading what you need in one more query.
  • Offset paging and keyset paging: why the first breaks when rows arrive and the second does not.

More in Structure and data

Build "Queries and relationships" 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.