Displaying Records

Connecting the live database to the HTML table from the Static Data lesson

Concepts

A global variable for a database connection is simpler to write but harder to test
- You cannot replace it with a test double without modifying the module
- Why is a global database connection harder to replace during testing than one passed as a function argument?
Dependency injection means that a handler declares what it needs as a function argument and the framework supplies it
- E.g., a database connection
- Injected dependencies can be swapped at the call site
- Decouples the handler from how the dependency is created, making it easier to test
- What kinds of things does Litestar use dependency injection for?
Pagination splits a large result set into pages using SQL limit (maximum rows to return) and offset (how many rows to skip)
- For page n with page size k, the offset is (n - 1) * k
- If a table has 150 rows and you show 20 per page, what are the limit and offset values for page 4?
Shareable URLs: if the current page number is in the URL, a user can bookmark or share a link that shows the same page of data
- Provided nothing has been deleted or moved
- How can the current page number be stored in a URL?

Dependency injection in Litestar

How Litestar passes a database connection to a handler
- Declare a database connection as a dependency in a Litestar application and pass it to a route handler

Querying on request

Replacing the hard-coded list with a live SELECT query
- Modify the snail handler to run a select query against the database and return the results instead of the hard-coded list
Mapping rows to dataclasses for the renderer
- Write a function that converts rows returned by a SQLite query into a list of snail measurement dataclass instances

Pagination

Add limit and offset to the SQL query
- Modify the select query to accept a page number and page size and return only the rows for that page
Page number as a query parameter
- Update the route handler to read the page number from the URL query string and pass it to the database query

Displaying page controls

Previous/next links in htpy
- Write an htpy function that generates Previous and Next pagination links given the current page number
Keeping the current page in the URL so links are shareable
- Update the pagination links so that each one includes the current page number as a query parameter in the URL

Accessibility (a11y)

Adding a <caption> and scope attributes so screen readers can announce column and row headings
- Add a <caption> element and scope attributes to the snail table so that screen readers can announce column and row headings correctly
Using aria-label on pagination links so "Previous" and "Next" make sense out of context
- Update the Previous and Next pagination links to include aria-label attributes that describe where each link goes
Marking the active page with aria-current="page"
- Modify the page controls to mark the currently displayed page number with aria-current="page"

Check for Understanding

What is the difference between a dependency and a global variable, and why does Litestar's approach make handlers easier to test?

A global variable is a single object shared by all code in the module; replacing it for testing requires monkey-patching or modifying the module. A dependency is declared as a function argument, so in tests you can create the handler's test client and pass a different dependency (e.g., a test database connection) without touching the handler code. This makes tests isolated and reproducible.

If you have 47 rows and show 10 per page, how many pages are there? Write the SQL for the third page.

5 pages (ceiling of 47 / 10). The SQL for page 3 is:

select * from snails order by id limit 10 offset 20

offset is (page - 1) * page_size, or (3 - 1) * 10 = 20.

What happens to the page number if a user bookmarks page 3 and then you insert 20 new rows?

The URL still says ?page=3, so the server applies offset 20 and returns rows 21-30 of the new total. Depending on how the data is sorted, different rows may now appear on page 3 than when the bookmark was created. This is expected behavior for most data tables, but matters for reproducibility if the sort order changes too.

Why is it bad practice to fetch all rows from the database and then slice the list in Python?

If the table has thousands or millions of rows, fetching all of them uses more memory and is slower than just getting a few. Pushing work into the database is almost always faster for large datasets.

A pagination link reads "Next" in the browser. Why might a screen reader user find this unhelpful, and how does aria-label fix it?

A screen reader can list all links on a page. If every page has a link whose text is just "Next", the list tells the user nothing about where each link goes. Adding aria-label="Next page of snail records" gives the link a descriptive name that makes sense on its own, without reading the surrounding context.

Exercises

Add a page size control

Let the user choose how many rows to display per page by adding a per_page query parameter that accepts the values 10, 25, or 50. Reject other values with a 400 response. Update the page controls so that the chosen page size is preserved when navigating between pages.

Show total counts

Display the total number of records in the database and the range of records shown on the current page (e.g., "Showing records 21-30 of 47"). You will need a second query that runs count(*) without limit or offset. Add this before asking the LLM, then compare your approach to what the LLM suggests.

Jump to a page

Add a small form with a text input that lets the user type a page number directly and jump to it. Submit the form using GET to an appropriate URL. Handle the case where the user types a number larger than the total number of pages.

Handle out-of-range pages

Decide what the server should do when ?page=0 or ?page=9999 is requested but those pages do not exist: return an empty page, redirect to the nearest valid page, or return a 404. Implement your chosen behavior.

Audit for accessibility

Install the WAVE browser extension and run it against your snail table page. Prompt the LLM to fix the three most severe errors it reports. For each fix, note whether you would have caught it without the tool and why.