Python FastAPI Tutorial (Part 5): Adding a Database - SQLAlchemy Models and Relationships

TL;DR

Persisting data requires replacing in-memory lists with SQLAlchemy ORM models backed by a real database file (blog.db).

Briefing Cornell Notes

Briefing

The core shift is replacing FastAPI’s in-memory “posts” list with a real SQLAlchemy-backed database so data persists across server restarts—and then wiring that database into the API, schemas, templates, and relationships. Using SQLite for development (no extra server needed), the setup creates a durable blog.db file and establishes proper user–post relationships, so posts can be tied to real user records instead of an author string. This matters because it turns a learning prototype into an architecture that can scale and later migrate to production databases like PostgreSQL with minimal code changes.

The implementation is built around three layers: SQLAlchemy database models, Pydantic schemas for the API contract, and FastAPI route handlers for request/response logic. Incoming requests get validated by Pydantic, SQLAlchemy stores or retrieves data via ORM models, and Pydantic formats the response back to JSON. That separation is emphasized as a practical design choice: database models handle ORM-specific features like foreign keys and relationships, while schemas define what the API accepts and returns.

A dedicated database configuration file defines the SQLAlchemy engine, session factory, and a FastAPI dependency that yields a per-request database session. For SQLite, the connection URL points to blog.db in the project directory, and the tutorial sets check_same_thread=False to accommodate FastAPI’s multi-threaded request handling. Sessions are configured with auto-commit and auto-flush disabled so commits happen explicitly. On startup, SQLAlchemy creates tables automatically using base.metadata.create_all(bind=engine), making the setup repeatable.

Two ORM models drive the data structure. The User model includes unique username and email fields, optional image_file storage, and a computed image_path property that selects between default static images and uploaded media under media/profile_pictures. A one-to-many relationship links users to posts: one user has many posts, and each post belongs to one author. The Post model includes title, content, a foreign key user_id referencing users.id (with an index for faster lookups), and a timezone-aware date_posted defaulted to the current UTC time. Time zone awareness is treated as a migration-friendly habit.

Pydantic schemas are updated to match the ORM models and relationships. User schemas validate username length and email format, and user response schemas enable reading ORM attributes (including the computed image_path property). Post schemas change from storing author as a string to returning a nested author object (user response) and include date_posted as a datetime that serializes to ISO 8601 automatically.

Routes are refactored to use dependency-injected database sessions and SQLAlchemy select queries. Creating a user checks for existing username and email before inserting, returning friendly HTTP 400 errors instead of relying only on database constraints. Fetching a user or posts uses 404 errors when records don’t exist, and the “get posts by user” endpoint distinguishes between “user not found” and “user exists but has no posts.” Templates are updated to reflect the new data shapes: post.author becomes an object (post.author.username, post.author.image_path), and date_posted is formatted for display in Jinja2.

Testing confirms the end-to-end behavior: blog.db is created, API endpoints return nested author data, error handling works for missing users/posts, and—crucially—data persists after restarting the server. The tutorial closes by noting that the next step is completing CRUD with update (PUT/PATCH) and delete operations.

Cornell Notes

FastAPI’s earlier in-memory posts list is replaced with SQLAlchemy ORM models backed by a persistent SQLite database (blog.db). The design uses three layers—SQLAlchemy models, Pydantic schemas, and FastAPI routes—so requests validate via Pydantic, data is stored/retrieved via SQLAlchemy, and responses are serialized back through Pydantic. User and Post models are linked with a one-to-many relationship: posts reference users via a foreign key (user_id), and API responses return nested author data automatically. The database session is injected per request using a FastAPI dependency, and tables are created on startup. Templates are updated to display author.username and format date_posted for readability, while the API keeps ISO 8601 timestamps.

Why does the tutorial insist on separating SQLAlchemy models from Pydantic schemas instead of using one combined model definition?

SQLAlchemy models are responsible for ORM concerns like relationships, foreign keys, and column types. Pydantic schemas define the API contract—what fields are accepted on input and what fields are returned on output. Keeping them separate makes it easier to change one layer without breaking the other. The tutorial also notes SQLModel as an alternative that merges both concepts, but it’s not used here because SQLAlchemy + Pydantic separation is the common production pattern and helps learners understand what each library contributes.

How does the database session get into each route, and why is that important?

A dependency function (get_db) creates a SQLAlchemy session and yields it to routes. FastAPI calls this dependency before the route runs and cleans up afterward, ensuring each request gets its own transaction context. That avoids creating sessions inside route functions repeatedly and keeps resource management consistent across endpoints.

What concrete changes happen when posts move from an in-memory list to ORM tables?

Manual ID generation and list-based lookups disappear. Instead, SQLAlchemy handles primary keys (auto-incremented user and post IDs) and queries use select statements. Routes now query the database for users and posts, return 404 errors when records don’t exist, and commit new records with db.add(), db.commit(), and db.refresh(). Data persists in blog.db across restarts, unlike the earlier in-memory approach.

How are user–post relationships represented, and how does that affect API responses?

The User model defines a relationship to posts (one user has many posts) and the Post model defines a foreign key user_id pointing to users.id. With relationship wiring (including back_populates), SQLAlchemy can load related objects so post.author becomes a full User object. Because Pydantic schemas are configured with from_attributes=True, nested JSON for author details (username, email, image_path, etc.) is produced automatically.

Why is date_posted stored as a timezone-aware datetime, and how is it displayed differently in the UI?

The Post model uses a DateTime column with time zone awareness (time zone=True) and defaults date_posted to the current UTC time. SQLAlchemy serializes datetime fields to ISO 8601 in API responses. Templates then format date_posted into a human-readable string using Jinja2’s strftime-style formatting, keeping machine-friendly timestamps in the API while presenting readable dates in HTML.

What’s the purpose of checking for existing username/email before inserting a new user?

The database enforces uniqueness on username and email, but pre-checking enables friendlier, deterministic errors. The create-user route runs select queries to see whether a username or email already exists and raises HTTP 400 with clear messages like “username already exists” or “email already exists,” rather than relying on a constraint violation error.

Review Questions

What components make up the tutorial’s three-layer architecture, and what job does each layer perform?
How do foreign keys and relationships change what the API returns for a post compared with the earlier author-as-string approach?
Where does the per-request SQLAlchemy session come from, and what does FastAPI do with it after the request finishes?

Key Points

1
Persisting data requires replacing in-memory lists with SQLAlchemy ORM models backed by a real database file (blog.db).
2
A per-request SQLAlchemy session is injected into routes via a FastAPI dependency (get_db), ensuring consistent transaction handling and cleanup.
3
User and Post are linked with a one-to-many relationship using a foreign key (Post.user_id → User.id), enabling nested author data in responses.
4
Pydantic schemas are updated to match ORM models, including from_attributes=True so computed properties like image_path and relationship objects serialize correctly.
5
Creating records now uses db.add(), db.commit(), and db.refresh(), while reads use SQLAlchemy select queries instead of list searches.
6
Routes return clear HTTP errors (400 for duplicate user fields, 404 for missing users/posts) to distinguish “not found” from “empty but valid.”
7
Templates must be updated to reflect new data shapes: post.author is an object (e.g., post.author.username) and date_posted is formatted for display.

Highlights

The switch from an in-memory list to SQLAlchemy + SQLite turns a restart-reset prototype into a persistent application backed by blog.db.

Nested author objects appear automatically in post responses once the ORM relationship and Pydantic from_attributes=True are in place.

A single computed property (User.image_path) centralizes default-vs-uploaded image logic and flows into API responses without extra schema computation.

Timezone-aware date_posted is stored in the database and then formatted into human-readable strings in Jinja2 templates.

Topics

FastAPI
SQLAlchemy
SQLite
ORM Relationships
Pydantic Schemas

Mentioned

Corey Schafer
OM