Module Project: the versioned blog with migrations

Description

You are going to consolidate the blog with a complete migration history: from the initial one to several incremental changes that demonstrate every pattern you learned. The deliverable: an alembic/versions/ folder with 5+ migrations, a simplified db/setup.sql (bootstrap only), and a README documenting the migration workflow.

By the end of the module, you recreate your entire schema from scratch by running only alembic upgrade head. That is the promise of version control for your DB.

---

The plan: 5 migrations to generate

#	Migration	Type	Covers
01	initial schema	Autogenerate	Capsules 1-2 (the whole base schema)
02	add view_count to posts	Simple autogenerate	The basic pattern
03	add bookmarks table	Autogenerate	A new table
04	add view_count_default + populate	Manual	A data migration
05	add indexes for blog queries	Manual	Indexes + naming

---

Step 1: clean up and simplify db/setup.sql

Since Alembic now handles the schema, db/setup.sql only needs the minimal bootstrap:

-- db/setup.sql — POST-ALEMBIC VERSION
-- Bootstrap only. Alembic creates the tables.
-- Run as superuser: docker exec -i blog-postgres psql -U postgres -d blog_dev < db/setup.sql

-- The extension needed for gen_random_uuid() (the models use it)
CREATE EXTENSION IF NOT EXISTS "pgcrypto";

-- The application user
DO $$
BEGIN
  IF NOT EXISTS (SELECT FROM pg_catalog.pg_roles WHERE rolname = 'blog_user') THEN
    CREATE ROLE blog_user WITH LOGIN PASSWORD 'blog_user_pass';
  END IF;
END
$$;

-- Permissions
GRANT ALL PRIVILEGES ON DATABASE blog_dev TO blog_user;
GRANT ALL ON SCHEMA public TO blog_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO blog_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON SEQUENCES TO blog_user;

\echo 'Bootstrap complete. Now run: alembic upgrade head'

db/seed.sql and db/indexes.sql are still available but no longer necessary — we replace them with migrations + (optionally) data migrations for the fixtures.

---

Step 2: configure Alembic (a module recap)

# 1. Initialize (if you have not yet)
alembic init alembic

# 2. Edit alembic/env.py with:
#    - load_dotenv()
#    - target_metadata = Base.metadata
#    - compare_type=True, compare_server_default=True

# 3. Edit app/database.py to add the naming_convention to Base.metadata

(See capsule 02 for the details.)

---

Step 3: generate the 5 migrations

Migration 01: initial schema

# A completely empty DB
docker compose down -v && docker compose up -d
sleep 5
docker exec -i blog-postgres psql -U postgres -d blog_dev < db/setup.sql

# Generate
alembic revision --autogenerate -m "initial schema"

Review the alembic/versions/2026-04-25_*_initial_schema.py file. It must contain op.create_table() for categories, tags, users, posts, comments, post_tags.

alembic upgrade head

Migration 02: add view_count

Modify app/models.py:

class Post(Base):
    # ...
    view_count: Mapped[int] = mapped_column(default=0, server_default="0", nullable=False)

Generate and apply:

alembic revision --autogenerate -m "add view_count to posts"
# Review it
alembic upgrade head

Migration 03: add the bookmarks table

Add to app/models.py:

class Bookmark(Base):
    __tablename__ = "bookmarks"
    
    user_id: Mapped[UUID] = mapped_column(
        ForeignKey("users.id", ondelete="CASCADE"),
        primary_key=True,
    )
    post_id: Mapped[UUID] = mapped_column(
        ForeignKey("posts.id", ondelete="CASCADE"),
        primary_key=True,
    )
    created_at: Mapped[datetime] = timestamp_default()

alembic revision --autogenerate -m "add bookmarks table"
alembic upgrade head

Migration 04: a data migration — populate view_count realistically

Manual, not autogenerate:

alembic revision -m "populate view_count from analytics"

Edit the file:

"""populate view_count from analytics

Revision ID: ...
"""
from alembic import op
import sqlalchemy as sa

revision = "..."
down_revision = "..."  # auto-filled, leave it as is


def upgrade() -> None:
    # A simulation: we assign published posts a random view_count between 100-1000
    op.execute("""
        UPDATE posts 
        SET view_count = FLOOR(100 + RANDOM() * 900)::INTEGER
        WHERE published = TRUE
    """)


def downgrade() -> None:
    # Set them all back to 0 (we lose the values)
    op.execute("UPDATE posts SET view_count = 0")

alembic upgrade head

# Verify
docker exec -it blog-postgres psql -U blog_user -d blog_dev -c \
  "SELECT title, view_count FROM posts WHERE published = TRUE LIMIT 5"

Migration 05: add the module 3 indexes

Add the missing Index() entries to __table_args__ in app/models.py:

from sqlalchemy import Index

class Post(Base):
    # ...
    __table_args__ = (
        # existing CHECKs...
        Index("idx_posts_author_id", "author_id"),
        Index("idx_posts_category_id", "category_id"),
        Index(
            "idx_posts_published_at_desc_partial",
            "published_at",
            postgresql_where="published = true",
        ),
    )

class Comment(Base):
    # ...
    __table_args__ = (
        # existing CHECKs...
        Index("idx_comments_post_id", "post_id"),
        Index("idx_comments_author_id", "author_id"),
        Index(
            "idx_comments_parent_id",
            "parent_comment_id",
            postgresql_where="parent_comment_id IS NOT NULL",
        ),
    )

alembic revision --autogenerate -m "add indexes for blog queries"
# Review: an op.create_index(...) must appear for each one
alembic upgrade head

If autogenerate does not detect the partial indexes correctly (it happens sometimes), edit the migration manually to use postgresql_where=sa.text("...").

---

Step 4: load the fixtures with the seed

After the migrations, load the test data:

docker exec -i blog-postgres psql -U blog_user -d blog_dev < db/seed.sql

A professional alternative: turn seed.sql into a dedicated data migration (e.g. one that only runs in dev/staging):

alembic revision -m "seed initial data (dev only)"

# A manual migration
def upgrade():
    import os
    if os.environ.get("ENV", "dev") not in ("dev", "test"):
        return  # A no-op in production
    
    op.bulk_insert(
        sa.table("categories",
            sa.column("name"), sa.column("slug")),
        [
            {"name": "Technology", "slug": "technology"},
            # ...
        ],
    )
    # ... more bulk_insert for users, tags, posts, comments


def downgrade():
    op.execute("TRUNCATE post_tags, comments, posts, tags, categories, users CASCADE")

In the real blog, it is better to keep seed.sql separate: migrations must be identical in every environment. Fixture data is dev-specific.

---

Step 5: update README.md

# Blog — PostgreSQL & SQLAlchemy Guide

[...previous sections...]

## Setup from scratch

```bash
# 1. Bring up PostgreSQL
docker compose up -d

# 2. Bootstrap (extension + user)
docker exec -i blog-postgres psql -U postgres -d blog_dev < db/setup.sql

# 3. Apply the migrations (creates the tables)
alembic upgrade head

# 4. (Dev) Load the test data
docker exec -i blog-postgres psql -U blog_user -d blog_dev < db/seed.sql
```

## The workflow for schema changes

```bash
# 1. Edit app/models.py
# 2. Generate the migration
alembic revision --autogenerate -m "description of the change"

# 3. Review the generated file in alembic/versions/
# 4. Apply it
alembic upgrade head

# 5. Test reversibility
alembic downgrade -1
alembic upgrade head

# 6. Commit the migration to the repo
git add app/models.py alembic/versions/<file>
git commit -m "..."
```

## Useful commands

```bash
alembic current             # See the current revision
alembic history             # See every migration
alembic upgrade head        # Apply the pending ones
alembic upgrade +1          # Move forward one
alembic downgrade -1        # Move back one
alembic upgrade head --sql  # Dry run: print the SQL without running it
alembic check               # Verify the sync between the models and the DB
```

## The migration structure

```
alembic/versions/
├── 2026-04-25_1430_a1b2c3_initial_schema.py
├── 2026-04-25_1500_b2c3d4_add_view_count_to_posts.py
├── 2026-04-25_1530_c3d4e5_add_bookmarks_table.py
├── 2026-04-25_1600_d4e5f6_populate_view_count_from_analytics.py
└── 2026-04-25_1630_e5f6g7_add_indexes_for_blog_queries.py
```

[...the rest of the README...]

---

Final verification for the module

✅ Criterion 1: the complete schema from migrations

docker compose down -v && docker compose up -d
sleep 5
docker exec -i blog-postgres psql -U postgres -d blog_dev < db/setup.sql
alembic upgrade head

docker exec -it blog-postgres psql -U blog_user -d blog_dev -c "\dt"

Expected: 7 tables (alembic_version + the 6 + bookmarks).

✅ Criterion 2: the complete migration chain

alembic history

Expected: 5 migrations in chronological order, a single HEAD.

✅ Criterion 3: downgrade and upgrade work

alembic downgrade base
alembic upgrade head

No errors.

✅ Criterion 4: the indexes are there

docker exec -it blog-postgres psql -U blog_user -d blog_dev -c \
  "SELECT indexname FROM pg_indexes WHERE schemaname='public' ORDER BY indexname"

You will see the indexes created by migration 05 (plus the automatic PK/UNIQUE ones).

✅ Criterion 5: the data migration was applied

# After migration 04 (which populated view_count), include a seed with published posts
docker exec -i blog-postgres psql -U blog_user -d blog_dev < db/seed.sql

# Re-apply migration 04 (if you ran it before the seed):
# alembic downgrade <revision_03> && alembic upgrade head

# Verify
docker exec -it blog-postgres psql -U blog_user -d blog_dev -c \
  "SELECT title, view_count FROM posts WHERE published=TRUE LIMIT 3"

view_count must have non-zero values.

✅ Criterion 6: alembic check is clean

alembic check
# No new upgrade operations detected.

If it says anything else, there is a diff between the models and the DB.

✅ Criterion 7: the tests pass

pytest tests/

---

If everything passes: you are ready for Module 6

You have completed Module 5. You have:

• ✅ Alembic configured with env.py pointing at Base.metadata
• ✅ 5+ versioned migrations in alembic/versions/
• ✅ The blog's complete schema, reproducible with alembic upgrade head
• ✅ A data migration that demonstrates the pattern
• ✅ Versioned indexes (not just in db/indexes.sql)
• ✅ A simplified db/setup.sql (bootstrap only)
• ✅ Documentation of the workflow in the README

In Module 6 (FastAPI + Async SQLAlchemy) you are going to:

• Switch to AsyncSession and asyncpg
• Configure dependency injection with Depends(get_db)
• Expose the repository pattern's operations as HTTP endpoints
• Connection pooling for real concurrency
• Testing the complete app

---

Module summary

1. Capsule 01: context and map
2. Capsule 02: initializing Alembic
3. Capsule 03: the initial migration
4. Capsule 04: autogenerate (the day-to-day)
5. Capsule 05: manual migrations
6. Capsule 06: data migrations
7. Capsule 07: team workflow and CI/CD
8. Capsule 08: the complete versioned blog

---

The decisions made in this module

Decision	Reason
Alembic + Base.metadata as the source of truth	The schema versioned in Git
A global naming_convention	Deterministic names in autogenerate
compare_type=True and compare_server_default=True	Detecting more changes
db/setup.sql bootstrap only, no schema	Avoiding duplication with Alembic
db/seed.sql separate from the migrations	Fixture data only in dev
Indexes in __table_args__	Versioned along with the model

---

Additional Resources

1. Alembic Best Practices — The official cookbook
2. "Database Migrations Done Right" — Olivier Grisel
3. Real World SQLAlchemy 2 Project — The structure of a modern FastAPI project
4. Database CI/CD Patterns — It applies conceptually even though it is about another tool

---

🎉 You have completed Module 5: Alembic Migrations.

Next module (the last one): Module 6 — FastAPI + Async SQLAlchemy.