Until now, posts have been stored in a Python list in memory—acceptable for learning basic endpoint creation, but fundamentally flawed because every server restart erases all data, resetting back to the hard-coded list. This chapter addresses this by integrating a real database using SQLite and SQLAlchemy, establishing the three-layer architecture that will persist throughout the series, creating database models with proper relationships, and migrating all routes to use persistent storage.

Database Choice and Architecture

SQLite is used initially because it’s built into Python and requires no separate server installation. However, the library for database interaction is SQLAlchemy, Python’s most popular ORM (Object-Relational Mapper). The critical advantage of using an ORM: switching from SQLite to PostgreSQL or MySQL in production requires only a configuration change—the rest of the code remains identical. The series will make this switch to PostgreSQL before deployment.

The application will have three distinct layers:

Database models — SQLAlchemy classes defining what gets stored in the database
Pydantic schemas — Already created in the previous chapter, these define what the API accepts and returns
API routes — FastAPI endpoints handling actual requests

Why Separate Models and Schemas?

A library called SQLModel, created by FastAPI’s author, attempts to combine SQLAlchemy and Pydantic into single model definitions. However, this tutorial maintains separation for several important reasons.

Industry standard: SQLAlchemy is the overwhelming standard for database work in Python. Most production FastAPI applications use separate SQLAlchemy models and Pydantic schemas. This is what you’ll encounter in professional teams and open source projects.

Understanding through separation: SQLModel is built on top of both libraries. Learning them separately makes SQLModel easier to adopt later if desired.

Greater control: Different fields are often needed for creating versus returning data (as seen with user_id in PostCreate but not in PostBase). SQLModel can handle this but requires inheritance patterns that end up resembling the separated approach anyway.

Clear responsibilities: Each layer has a distinct job. Database models handle ORM-specific features like relationships and database column types. Pydantic schemas define the API contract—what’s accepted and what’s returned. Keeping these separate means changing one doesn’t affect the other.

Data flow: A request comes in → Pydantic validates it → SQLAlchemy stores or retrieves data → Pydantic formats the response → the response goes out. Clear boundaries exist between each layer.

Installing SQLAlchemy

For pip installations:

pip install sqlalchemy

Using UV:

uv add sqlalchemy

SQLite is built into Python, so no separate drivers are needed. Important note: SQLite is excellent for learning and development, but production applications typically use PostgreSQL. The patterns built here translate directly to PostgreSQL later—same SQLAlchemy code, only the connection URL changes.

Database Configuration

Create database.py to hold all database setup code, imported by both main.py and the models file:

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker, DeclarativeBase
 
# Database connection URL
DATABASE_URL = "sqlite:///./blog.db"
 
# Create engine
engine = create_engine(
    DATABASE_URL,
    connect_args={"check_same_thread": False}
)
 
# Create session factory
SessionLocal = sessionmaker(
    autocommit=False,
    autoflush=False,
    bind=engine
)
 
# Base class for models
class Base(DeclarativeBase):
    pass
 
# Dependency function for routes
def get_db():
    with SessionLocal() as db:
        yield db

DATABASE_URL tells SQLAlchemy where to connect. For SQLite, this is sqlite:///./blog.db where . is the current directory and blog.db is the filename. This file is created automatically. When switching to PostgreSQL, changing this connection string is one of the only required changes.

engine is the connection to the database. The check_same_thread=False argument is SQLite-specific—SQLite normally restricts access to one thread, but FastAPI handles multiple requests across threads, so this restriction must be disabled. This argument isn’t needed for PostgreSQL or MySQL.

SessionLocal is a factory creating database sessions. A session represents a transaction with the database—each request gets its own session. autocommit=False and autoflush=False mean we control when changes are committed, a standard FastAPI pattern.

Base uses the modern SQLAlchemy v2 approach. Older tutorials may show declarative_base() (the v1 method), but inheriting from DeclarativeBase is the current approach with better type-checking support.

get_db() is a dependency function providing sessions to routes. It’s a generator using yield db. The with statement makes the session work as a context manager (like opening a file), ensuring cleanup even if errors occur. FastAPI’s dependency injection calls this function for each request and handles cleanup automatically.

Creating Database Models

Create models.py to define database tables using SQLAlchemy’s ORM:

from __future__ import annotations  # For forward references on older Python  
  
from datetime import datetime, UTC  
  
from sqlalchemy import DateTime, ForeignKey, Integer, String, Text  
from sqlalchemy.orm import Mapped, mapped_column, relationship  
  
from database import Base  
  
  
class User(Base):  
    __tablename__ = "users"  
  
    id: Mapped[int] = mapped_column(Integer, primary_key=True, index=True)  
    username: Mapped[str] = mapped_column(String(50), unique=True, nullable=False)  
    email: Mapped[str] = mapped_column(String(120), unique=True, nullable=False)  
    image_file: Mapped[str | None] = mapped_column(String(200), nullable=True, default=None)  
  
    posts: Mapped[list[Post]] = relationship(back_populates="author")  
  
  
class Post(Base):  
    __tablename__ = "posts"  
  
    id: Mapped[int] = mapped_column(Integer, primary_key=True, index=True)  
    title: Mapped[str] = mapped_column(String(100), nullable=False)  
    content: Mapped[str] = mapped_column(Text, nullable=False)  
    user_id: Mapped[int] = mapped_column(ForeignKey("users.id"), nullable=False, index=True)  
    date_posted: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=lambda: datetime.now(UTC))  
    author: Mapped[User] = relationship(back_populates="posts")

Users Model Details

Why create a Users model now? Current posts just have an author string, but in real applications, posts should be associated with actual user accounts. Authentication will be added in future tutorials. Setting up proper relationships now avoids complex database schema changes later.

__tablename__ specifies the table name in the database.

Mapped[int] provides type hints for IDEs. mapped_column() defines the actual database column. For id, primary_key=True makes it auto-increment.

username and email are strings with unique=True (no duplicates allowed) and nullable=False (required field).

image_file can be either a string or None (Mapped[str | None]). This stores only the filename (like corey.jpg), not the entire path. Why? This decouples the database from file structure. If files are reorganized or directories renamed, the database doesn’t need updating—only the path construction logic changes. This separates data from presentation, a solid production pattern.

image_path property: This is Python code, not a database column. If a user has an uploaded custom image, return it from /media/profile_pics/. If they don’t, return the default from /static/profile_pics/default.jpg. This separates static files (shipped with the app) from media (user-uploaded content). This is best practice from Django and other frameworks—it makes deployments and backups easier. Static files are committed to version control; user-generated media is not.

posts relationship: Creates a one-to-many relationship—one user has many posts. This is a list of Post objects. back_populates="author" links to the author field on Post. This allows user.posts to retrieve all of that user’s posts.

Forward reference note: The posts field references Post before it’s defined. In Python 3.14 (the latest version), this works without extra steps. On older Python versions, add from __future__ import annotations as the very first import in the file (above everything else). This import is included in the code—on Python 3.14 it does nothing, but on earlier versions it makes forward references work correctly. This ensures compatibility across Python versions.

Post Model Details

user_id foreign key: Links posts to users. ForeignKey("users.id") means this must reference a valid user. index=True creates a database index on this column.

Why index user_id? An index is like the index in a textbook. Without it, the database scans every row to find matches. Primary keys get indexes automatically, but foreign keys don’t. Queries frequently filter posts by user ID, so indexing makes these queries much faster. The trade-off is slightly slower writes, but this is usually worthwhile.

date_posted: Uses datetime with timezone=True. This ensures timezone-aware storage. SQLite stores datetimes as text, but this ensures PostgreSQL will use TIMESTAMP WITH TIMEZONE when migrating later. It’s good practice to be explicit about timezones. The default=lambda: datetime.now(UTC) is called at post creation, setting it to the current UTC time.

author relationship: This is the many-to-one side. back_populates="posts" links back to user.posts. This allows post.author to retrieve the user, with SQLAlchemy automatically handling joins.

Updating Pydantic Schemas

The schemas created in the previous chapter must be updated to work with database models. Import datetime and email validation:

from datetime import datetime
from pydantic import BaseModel, ConfigDict, Field, EmailStr

User Schemas

Create user schemas before post schemas:

from pydantic import BaseModel, ConfigDict, Field, EmailStr  
from datetime import datetime  
  
  
class UserBase(BaseModel):  
    username: str = Field(min_length=1, max_length=50)  
    email: EmailStr = Field(max_length=120)  
  
  
class UserCreate(UserBase):  
    pass
 
  
class UserResponse(UserBase):  
    model_config = ConfigDict(from_attributes=True)  
  
    id: int  
    image_file: str | None  
    image_path: str

UserBase contains fields shared between creating and returning users. EmailStr from Pydantic automatically validates proper email format—no custom validation needed. No minimum length is required for email because EmailStr already validates non-empty strings.

UserCreate currently passes, making it identical to UserBase. When authentication is added later, a password field will be included here (something passed when creating a user but not returned in responses).

UserResponse includes from_attributes=True so Pydantic can read from SQLAlchemy models. It adds id, image_file, and image_path.

Key point about image_path: This is a property on the User model, not a database column. from_attributes=True lets Pydantic read properties as well. The path construction logic is defined once in the model and automatically available in the schema. Without this, a computed field decorator would be needed in the schema to calculate the image path and determine whether it’s default or user-uploaded.

Privacy concern: Since UserResponse inherits from UserBase, it includes the email field in responses. Returning user emails in public API responses is likely a privacy concern. This will be addressed in later tutorials by splitting into separate public and private user schemas.

Updated Post Schemas

class PostBase(BaseModel):
    title: str = Field(min_length=1, max_length=100)
    content: str = Field(min_length=1)
 
class PostCreate(PostBase):
    user_id: int  # Temporary for testing
 
class PostResponse(PostBase):
    model_config = ConfigDict(from_attributes=True)
    
    id: int
    user_id: int
    date_posted: datetime
    author: UserResponse

PostBase no longer includes the author field—this now comes from the relationship.

PostCreate adds user_id for now, marked temporary. When creating a post, the user ID is passed manually for testing. Once authentication is added, the current user will be retrieved from the session automatically.

PostResponse adds id, user_id, date_posted (now a datetime instead of string), and author as a UserResponse.

Powerful capability: When SQLAlchemy loads a post, it can also load the related user. Pydantic sees the author field, validates the user object against UserResponse, and includes full user data in the API response. This produces nested JSON with the author’s username, email, and image path automatically. The datetime field serializes to standard ISO 8601 format automatically—no manual serialization or custom conversions required.

Updating Main Application

Required Imports

from typing import Annotated
from fastapi import FastAPI, Request, HTTPException, status, Depends
from sqlalchemy import select
from sqlalchemy.orm import Session
import models
from database import Base, engine, get_db
from schemas import PostCreate, PostResponse, UserCreate, UserResponse

Annotated is the FastAPI pattern for typed dependencies. Depends enables dependency injection—how database sessions are injected into routes. select is SQLAlchemy v2 style for querying, replacing the older db.query() pattern seen in some tutorials. Session provides type hints so IDEs know the db parameter type. models gives access to Post and User models. Base, engine, get_db are for creating tables and providing database sessions.

Creating Tables and Mounting Media

Before creating the app, create database tables:

Base.metadata.create_all(bind=engine)
 
app = FastAPI()

This examines all models inheriting from Base and creates tables if they don’t exist. This is idempotent—safe to run multiple times. If tables exist, nothing happens. This runs on app startup.

Mount a media directory for user-uploaded content:

app.mount("/static", StaticFiles(directory="static"), name="static")
app.mount("/media", StaticFiles(directory="media"), name="media")

This follows the same pattern as static files, creating a /media URL prefix serving files from the media directory. Create this directory in the project root with a profile_pics subdirectory inside it. The media directory remains empty initially—users will upload images here later.

Removing In-Memory Data

Delete the hard-coded posts list. Data now comes from the database.

Creating a User Endpoint

@app.post(
    "/api/users",
    response_model=UserResponse,
    status_code=status.HTTP_201_CREATED
)
def create_user(
    user: UserCreate,
    db: Annotated[Session, Depends(get_db)]
):
    # Check if username exists
    result = db.execute(
        select(models.User).where(models.User.username == user.username),
    )
    existing_user = result.scalars().first()
    if existing_user:
        raise HTTPException(
            status_code=status.HTTP_400_BAD_REQUEST,
            detail="Username already exists"
        )
    
    # Check if email exists
    result = db.execute(
        select(models.User).where(models.User.email == user.email)
    )
    existing_email = result.scalars().first()
    if existing_email:
        raise HTTPException(
            status_code=status.HTTP_400_BAD_REQUEST,
            detail="Email already exists"
        )
    
    # Create new user
    new_user = models.User(
        username=user.username,
        email=user.email
    )
    db.add(new_user)
    db.commit()
    db.refresh(new_user)
    return new_user

Dependency injection explained: db: Annotated[Session, Depends(get_db)] tells FastAPI: “Before running this function, call get_db() and pass the result as the db parameter.” FastAPI handles everything—it calls get_db(), provides the session, and cleans up when the request finishes. Each request gets its own database session automatically. This is a standard FastAPI pattern used extensively.

Database query pattern: db.execute(select(models.User).where(...)) is SQLAlchemy v2 querying. result.scalars().first() gets the first user object or None if no match exists.

The database already has unique constraints, but checking first provides friendlier error messages and avoids attempting invalid inserts.

Database operations: db.add() stages the insert. db.commit() executes it and saves to the database. db.refresh() reloads the object from the database. The refresh isn’t strictly necessary here since SQLAlchemy already tracks the auto-generated ID after commit, but it’s good practice for when you have server-side defaults or database triggers.

When new_user is returned, Pydantic automatically converts it to a UserResponse as specified in the response_model.

Getting a User Endpoint

@app.get("/api/users/{user_id}", response_model=UserResponse)
def get_user(
    user_id: int,
    db: Annotated[Session, Depends(get_db)]
):
    result = db.execute(
        select(models.User).where(models.User.id == user_id),
    )
    user = result.scalars().first()
    if user:
        return user
    raise HTTPException(
        status_code=status.HTTP_404_NOT_FOUND,
        detail="User not found"
    )

This follows the same pattern: dependency injection for the database session, querying for a user matching the path parameter, returning the user if found, or raising a 404 if not.

Updating Remaining Routes

Every route touching data must be updated for the database. The patterns are consistent.

Get all posts (API):

@app.get("/api/posts", response_model=list[PostResponse])
def get_posts(db: Annotated[Session, Depends(get_db)]):
    result = db.execute(select(models.Post))
    posts = result.scalars().all()
    return posts

No where clause—retrieve all posts. Use .all() instead of .first(). Pydantic automatically serializes the author relationship as UserResponse.

Create post (API):

@app.post(
    "/api/posts",
    response_model=PostResponse,
    status_code=status.HTTP_201_CREATED
)
def create_post(
    post: PostCreate,
    db: Annotated[Session, Depends(get_db)]
):
    # Verify user exists
    result = db.execute(
        select(models.User).where(models.User.id == post.user_id)
    )
    user = result.scalars().first()
    if not user:
        raise HTTPException(
            status_code=status.HTTP_404_NOT_FOUND,
            detail="User not found"
        )
    
    # Create post
    new_post = models.Post(
        title=post.title,
        content=post.content,
        user_id=post.user_id
    )
    db.add(new_post)
    db.commit()
    db.refresh(new_post)
    return new_post

Verify the user exists first—this provides a better error message than the database constraint violation.

Get single post (API):

@app.get("/api/posts/{post_id}", response_model=PostResponse)
def get_post(
    post_id: int,
    db: Annotated[Session, Depends(get_db)]
):
    result = db.execute(
        select(models.Post).where(models.Post.id == post_id)
    )
    post = result.scalars().first()
    if post:
        return post
    raise HTTPException(
        status_code=status.HTTP_404_NOT_FOUND,
        detail="Post not found"
    )

Query by post ID, return if found, raise 404 if not. The author relationship is populated automatically.

Home route (template):

@app.get("/", include_in_schema=False, name="home")
@app.get("/posts", include_in_schema=False, name="posts")
def home(
    request: Request,
    db: Annotated[Session, Depends(get_db)]
):
    result = db.execute(select(models.Post))
    posts = result.scalars().all()
    return templates.TemplateResponse(
        request,
        "home.html",
        {"posts": posts, "title": "Home"}
    )

Template rendering stays the same, but posts come from the database instead of an in-memory list.

Single post page (template):

@app.get("/posts/{post_id}", include_in_schema=False)
def post_page(
    request: Request,
    post_id: int,
    db: Annotated[Session, Depends(get_db)]
):
    result = db.execute(
        select(models.Post).where(models.Post.id == post_id)
    )
    post = result.scalars().first()
    if post:
        title = post.title[:50]
        return templates.TemplateResponse(
            request,
            "post.html",
            {"post": post, "title": title}
        )
    raise HTTPException(
        status_code=status.HTTP_404_NOT_FOUND,
        detail="Post not found"
    )

Query by ID from the path parameter, return template if found, raise 404 if not.

Get posts by user (API):

@app.get("/api/users/{user_id}/posts", response_model=list[PostResponse])
def get_user_posts(
    user_id: int,
    db: Annotated[Session, Depends(get_db)]
):
    # Verify user exists
    result = db.execute(
        select(models.User).where(models.User.id == user_id)
    )
    user = result.scalars().first()
    if not user:
        raise HTTPException(
            status_code=status.HTTP_404_NOT_FOUND,
            detail="User not found"
        )
    
    # Get user's posts
    result = db.execute(
        select(models.Post).where(models.Post.user_id == user_id)
    )
    posts = result.scalars().all()
    return posts

Verify the user exists first. Without this check, an empty list could mean either “user has no posts” or “user doesn’t exist.” Checking first and returning 404 confirms an empty list only means no posts.

User posts page (template):

@app.get("/users/{user_id}/posts", include_in_schema=False, name="user_posts")
def user_posts_page(
    request: Request,
    user_id: int,
    db: Annotated[Session, Depends(get_db)]
):
    # Verify user exists
    result = db.execute(
        select(models.User).where(models.User.id == user_id)
    )
    user = result.scalars().first()
    if not user:
        raise HTTPException(
            status_code=status.HTTP_404_NOT_FOUND,
            detail="User not found"
        )
    
    # Get user's posts
    result = db.execute(
        select(models.Post).where(models.Post.user_id == user_id)
    )
    posts = result.scalars().all()
    return templates.TemplateResponse(
        request,
        "user_posts.html",
        {"posts": posts, "user": user, "title": f"Posts by {user.username}"}
    )

This template route displays posts by a specific user, referenced using url_for() in templates.

Updating Templates for Relationships

Templates must be updated for the new relationship data.

Home Template Updates

In home.html:

<!-- Profile image - now dynamic -->
<img src="{{ post.author.image_path }}" alt="{{ post.author.username }}">
 
<!-- Author username with link -->
<a href="{{ url_for('user_posts', user_id=post.author.id) }}">
    {{ post.author.username }}
</a>
 
<!-- Formatted date -->
{{ post.date_posted.strftime('%B %d, %Y') }}

Key changes: post.author was a string; now it’s an object with properties. Use post.author.username to display the name. post.author.image_path uses the dynamic property from the User model. Link to the user’s posts page using url_for('user_posts', user_id=post.author.id).

date_posted is now a datetime object, not a string. Use .strftime('%B %d, %Y') for formatting. Python’s standard datetime formatting works in Jinja2 templates: %B is the full month name, %d is the day, %Y is the four-digit year.

Why format dates in templates, not the API? The API returns standardized ISO 8601 format. The frontend handles human-readable formatting. The same data can be formatted differently in different contexts.

Post Template Updates

Apply the same changes in post.html:

<img src="{{ post.author.image_path }}" alt="{{ post.author.username }}">
<a href="{{ url_for('user_posts', user_id=post.author.id) }}">
    {{ post.author.username }}
</a>
{{ post.date_posted.strftime('%B %d, %Y') }}

User Posts Template

Create user_posts.html (similar to home.html but with a heading showing the username):

{% extends "layout.html" %}  
{% block content %}  
  <h1 class="mb-4">Posts by {{ user.username }}</h1>  
  {% for post in posts %}  
    <article class="content-section py-3 px-4 mb-4">  
      <div class="d-flex align-items-start gap-4">  
        <img class="rounded-circle article-img flex-shrink-0"  
             src="{{ post.author.image_path }}"  
             alt="{{ post.author.username }}'s profile picture"  
             width="64"  
             height="64"  
             loading="lazy">  
        <div class="flex-grow-1">  
          <div class="article-metadata mb-2">  
            <a class="me-2"  
               href="{{ url_for('user_posts', user_id=post.author.id) }}">{{ post.author.username }}</a>  
            <small class="text-body-secondary">{{ post.date_posted.strftime("%B %d, %Y") }}</small>  
          </div>          <h2>            <a class="article-title"  
               href="{{ url_for('post_page', post_id=post.id) }}">{{ post.title }}</a>  
          </h2>          <p class="article-content">{{ post.content }}</p>  
        </div>      </div>    </article>  {% else %}  
    <p class="text-body-secondary">No posts by this user yet.</p>  
  {% endfor %}  
{% endblock content %}

Loop through posts as on the homepage, with an empty state showing “No posts by this user yet” if the list is empty.

Testing the Database Integration

If you’ve been testing along the way and a blog.db file exists in your project, it’s safe to delete it. The setup is idempotent—each app startup creates tables from scratch if they don’t exist. For testing, starting fresh is usually desirable.

Start the FastAPI server:

uv run fastapi dev main.py

Check the project directory—the blog.db file is automatically created. This is the database being created from the models.

Reload the homepage. No posts display, but the page loads without errors. This is expected—the database is currently empty.

Navigate to /docs. New routes appear for user operations.

Creating Test Users

Click “Create User” → “Try it out”:

{
  "username": "coreyms",
  "email": "corey@example.com"
}

Execute. A 201 response returns with ID 1, image_file as null, and image_path pointing to the default image.

Create a second user:

{
  "username": "janedoe",
  "email": "jane@example.com"
}

This receives ID 2.

Test “Get User” with user ID 1—it returns the first user successfully.

Creating Test Posts

Click “Create Post” → “Try it out”:

{
  "title": "First Database Post",
  "content": "This is a sample DB post",
  "user_id": 1
}

Execute. The 201 response includes the generated post ID, title, content, user_id, and date_posted in ISO 8601 format.

Critical observation: The author field includes full user information—username, email, ID, image path, and image file. This is the database relationship in action. When Pydantic serialized the response, it accessed post.author and SQLAlchemy automatically loaded the related user data.

Create two more posts:

{
  "title": "Second Database Post",
  "content": "This is another sample DB post",
  "user_id": 1
}

{
  "title": "Third Database Post",
  "content": "This is yet another sample",
  "user_id": 2
}

Testing Error Handling

Request user 999 → “404 User not found”. Good—no such user exists.

Test “Get all posts” → All three posts return successfully with full author information.

Test “Get single post” with ID 1 → Returns the first post.

Test a non-existent post (ID 999) → “Post not found”.

Testing Templates

Reload the homepage. Posts now display with proper formatting. Click a post title—it navigates to the individual post page. Click the author username—it shows all posts by that user. Dates are nicely formatted (e.g., “January 15, 2024”) rather than the ISO format from the database.

The critical test: Restart the server. Reload the homepage. All posts persist—they’re stored in blog.db, not a temporary in-memory list.

Before and After Comparison

Before (in-memory list):

Data stored in a Python variable
Data lost on restart
Manual ID generation
Manual searches through loops
Not scalable

After (database):

Data stored in an actual database
Data persists across restarts
Automatic ID generation
SQL queries handled by SQLAlchemy
Scalable structure

The API surface mostly looks the same, but the internals have fundamentally changed.

Summary

This chapter integrated a database using SQLAlchemy. A three-layer architecture was established: database models defining storage, Pydantic schemas defining the API contract, and API routes handling requests. Database configuration was set up in database.py with connection engine, session factory, base class, and dependency injection. User and Post models were created with a one-to-many relationship—one user has many posts. The User model includes an image_path property decoupling file paths from database storage. Pydantic schemas were updated with UserBase, UserCreate, UserResponse, and modifications to post schemas including relationships. from_attributes=True enables reading from SQLAlchemy models and their properties. All routes were migrated to use database sessions via dependency injection. Templates were updated to handle relationship objects and format datetimes properly.

The current implementation focuses on reading from the database and creating records. The next chapter covers PUT and PATCH for update operations and DELETE for delete operations, completing full CRUD (Create, Read, Update, Delete) functionality for the database.

Quartz 4

Explorer

05. Adding a Database - SQLAlchemy Models and Relationships