Skip to content
Tutorials Center
My learning

Topics

Lesson 36 of 50 ยท Databases

CRUD, filtering and pagination

Build a FastAPI data layer: a service layer for CRUD, filtering and sorting, offset and keyset pagination, the N+1 problem, and 409 on unique violations.

Almost every API is, at its core, CRUD: create, read, update, delete. The first version is easy. The real work starts when the table grows: clients want to search, sort and page through thousands of rows, two users try to register the same email, and a list endpoint that looked fine with 10 rows takes seconds with 10,000.

This lesson turns the session dependency from SQLAlchemy with FastAPI into a proper data layer. You will separate database code from HTTP code, turn a unique-constraint violation into a clean 409, add filtering and sorting parameters that cannot be abused, compare offset and keyset pagination, and meet the N+1 query problem with a counter that proves it.

A service layer

When endpoints contain session.add, select, commit and error handling, they get long and hard to test, and the same query is copied from endpoint to endpoint. A service layer is simply a set of plain functions that take a session and do the database work. Endpoints stay thin: parse the request, call a service function, shape the response.

The service functions do not know about HTTP. When something goes wrong they raise their own exceptions (DuplicateIsbn), and the app translates them to status codes in one exception handler, using the mechanism from Status codes and error handling.

Who does what

Endpoint Parse request, build response.
Service Queries and rules. No HTTP.
Model Tables and relationships.

Swapping the database or testing the logic no longer touches HTTP code.

1 / 2

Unique violations become 409

A column with unique=True makes the database itself refuse duplicates. That is the only reliable guard: checking "does this ISBN exist?" first and inserting afterwards has a race, because another request can slip in between. So insert, and handle the failure. SQLAlchemy raises IntegrityError, you roll back the session and raise your own exception, and the handler answers 409 Conflict: the request is valid, but it clashes with the current state.

Python CRUD with a service layer and a 409

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The duplicate gets 409 with a readable message, and the session is usable again because of the rollback(); forgetting that call leaves the session in a failed state. book_or_404 is a dependency, so read, update and delete share one lookup and one 404 (the pattern from the ownership example in Roles, scopes and permissions). A successful delete answers 204 No Content, which has no body by definition.

Filtering and sorting

List endpoints take query parameters (see Query parameters) and turn them into where and order_by clauses. Two rules keep this safe:

  • Add a where only for the filters the client actually sent. Optional parameters default to None and are skipped.
  • Never put a client-supplied column name into a query directly. Offer a whitelist of sort keys, ideally a Literal, so anything else is a 422 before it gets near the database.

Offset pagination

The classic design uses page and size (or offset and limit): skip (page - 1) * size rows and take size. Always cap the size with le=..., or a client will ask for a million rows. Return the total count too, so interfaces can draw page links. Counting needs a second query; use select(func.count()).select_from(...) with the same filters.

Offset pagination is simple and lets you jump to page 50, but it has two weaknesses. The database still has to walk past all the skipped rows, so deep pages get slower. And if rows are inserted or deleted while a client is paging, items can be repeated or missed.

Keyset pagination

Keyset (or cursor) pagination avoids both problems. The client sends the key of the last row it saw, and the query asks for rows after it: where id > :after order by id limit :n. With an index on the key, the cost does not grow with depth, and a new row can never shift earlier pages. You trade away random access (no "jump to page 50") and you must sort by a unique, stable column, or by a pair such as (created_at, id).

A handy trick is to fetch limit + 1 rows: if you got the extra one, there is a next page, and you drop it before responding.

Python Filtering, sorting, offset and keyset pagination

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Look at the output: the first query keeps only titles containing "alpha" (case-insensitive), sorted newest first, three per page. A sort key outside the whitelist or a page size over 20 is rejected with 422 automatically. The feed walks the 24 rows in cursors of 10 and ends when next_after is None, never skipping a row.

ilike("%text%") with a leading wildcard cannot use an ordinary index, so it is fine for small tables but slow for big ones. For real search use full-text features of the database, or a search engine such as Meilisearch.

Relationships and the N+1 problem

Tables relate to each other: an author has many books. In SQLAlchemy a relationship() lets you write author.books. By default it is lazy: the books are fetched with a new query the first time you touch the attribute. Loop over 50 authors and print their books, and you run 1 query for the authors plus 50 for the books: that is the N+1 problem, and it is the number one cause of slow list endpoints. Every query also costs a network round trip to the database.

The cure is to tell SQLAlchemy what you will need, up front: selectinload(Author.books) loads all the books for the selected authors in one extra query (2 queries in total, however many authors). joinedload does it with a JOIN instead, which suits to-one relationships.

To see the problem you need to count statements. A SQLAlchemy event listener on the engine does that.

Python Counting queries: lazy loading versus selectinload

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

For five authors the lazy endpoint runs 6 statements, and the eager one only 2. With five authors you will not notice, but with 500 you would. Whenever a response includes related rows, decide the loading strategy on purpose, and count statements in your tests (see Testing FastAPI apps) so a regression shows up as a failing assertion. Production monitoring tools also flag endpoints that run many near-identical queries.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Two requests try to create a user with the same unique email at nearly the same time. What is the most reliable protection?

    A check-then-insert has a race window. Only the database can enforce uniqueness atomically; catch the error, roll back and answer 409.
  2. 2. Why use a whitelist (such as a Literal) for the sort parameter?

    Only known keys map to order_by clauses; anything else is rejected with 422 before it reaches the database.
  3. 3. What is an advantage of keyset pagination over offset pagination?

    Asking for rows after the last seen key uses an index and is unaffected by inserts before that key; the price is losing random page access.
  4. 4. A list endpoint loads 100 authors and then touches author.books for each one with default lazy loading. How many queries run?

    One query for the authors plus one per author is the N+1 problem. selectinload brings it down to two queries.

Summary

  • Keep database code in service functions and let thin endpoints call them; translate domain exceptions to HTTP in one handler.
  • Let the database enforce uniqueness; roll back after IntegrityError and answer 409.
  • Add filters only when sent, sort through a whitelist, and cap page sizes with le.
  • Offset pagination is simple but slows down and can shift; keyset pagination (id > after, fetch limit + 1) scales.
  • Lazy relationships cause N+1 queries; load what you need with selectinload or joinedload and count statements to prove it.

Next: Migrations with Alembic shows how to change a live database schema safely.