Skip to content

Exercise: Database Setup

With Alembic configured, generate the projects table and populate it with seed data.

Before You Start

Warning

Checking out the exercise branch will wipe any changes you made.

If you've made any changes since chapter 4, run git status first and commit or stash them before checking out the chapter branch:

shell
git status

# if there are uncommitted changes, either:
git stash
# or:
git add -A && git commit -m "chapter 4 work"

Then set up the starting state for this exercise:

shell
git fetch
git checkout -b my-ch05 --no-track origin/ch05
uv sync
docker compose up -d
Files that changed in this chapter:
  • compose.yaml (new, runs PostgreSQL in a container)
  • alembic/ (new, alembic's config and template; you'll generate the migration)
  • alembic.ini (new)
  • src/release_tracker/models.py (new, defines the Project model)
  • src/release_tracker/config.py (new, pydantic-settings config)
  • src/release_tracker/database.py (new, engine and session factory)
  • scripts/seed.py (new, you'll fill in TODOs)
  • pyproject.toml (modified, sqlmodel, alembic, psycopg[binary], pydantic-settings added)

Step 1: Generate and Run the Migration

Generate the initial migration script:

shell
uv run alembic revision --autogenerate -m "initial"

If you look in the alembic/versions/ directory, you'll see a new Python file. Open it up. You'll see upgrade() and downgrade() functions containing the SQLAlchemy commands to create and drop your projects table.

Your migration filename will differ

Alembic prefixes the filename with a unique revision ID (e.g., 70c28f0a2070_initial.py). Yours will be different from anyone else's. The exercise repo doesn't track these files (they're listed in .gitignore), so they stay local to your machine. If you want to see what a reference migration looks like, browse the solutions repo on GitHub.

Apply the migration to the database:

shell
uv run alembic upgrade head

Step 2: Verify in PostgreSQL

Let's ensure the table was actually created. We can connect directly to our PostgreSQL container using the psql command-line tool.

Run this command from the directory containing your compose.yaml:

shell
docker compose exec db psql -U release_tracker -d release_tracker

docker compose exec looks up the db service from compose.yaml, so you don't have to know the auto-generated container name.

Once inside the psql prompt, run the \dt command to list the tables:

Text
release_tracker=# \dt
             List of relations
 Schema |      Name       | Type  |      Owner      
--------+-----------------+-------+-----------------
 public | alembic_version | table | release_tracker
 public | projects        | table | release_tracker
(2 rows)

You should see both projects and alembic_version (which Alembic uses to track which migrations have been applied). Type \q to exit.

Step 3: Fill in the Seed Script

A database isn't very useful without data. We'll use SQLModel's Session to insert a few mock projects.

Your src/release_tracker/database.py is already in place. It exports get_engine() (cached with @lru_cache so the connection pool is created once) and a get_session() generator the API will use later.

Why use @lru_cache?

The @lru_cache decorator on get_engine() ensures that Python only ever executes this function once. On subsequent calls, it returns the exact same cached Engine object. The engine manages our database connection pool, so it's critical we reuse a single instance across the entire application rather than creating a new connection pool every time we need to talk to the database.

Open scripts/seed.py and fill in the TODOs to insert three projects:

scripts/seed.py
from sqlmodel import Session

from release_tracker.database import get_engine
from release_tracker.models import Project


def seed() -> None:
    # 1. Create a Session using the engine
    with Session(get_engine()) as session:
        # 2. Instantiate three Project objects
        frontend = Project(name="Frontend Redesign", slug="frontend-redesign")
        # TODO: Create two more projects

        # 3. Add them to the session
        session.add(frontend)
        # TODO: Add the other two projects

        # 4. Commit the transaction to save them to the database
        session.commit()

        print("Loaded sample data for 3 projects.")


if __name__ == "__main__":
    seed()

Step 4: Execute the Seed Script

Run your script from the terminal:

shell
uv run python scripts/seed.py

Step 5: Verify the Data

Confirm the records landed by running a one-line query through psql. The -c flag executes a single SQL statement and exits, so we don't have to drop into the interactive prompt:

shell
docker compose exec db psql -U release_tracker -d release_tracker -c "SELECT id, name, slug FROM projects;"

Success

If the query returns three rows (Frontend Redesign, API v2, Database Migration), the migration ran, the seed script committed, and the database round-trip works end-to-end.

Compare with the Solution

shell
git diff solutions/ch05 -- src/ scripts/ tests/