Skip to main content

U04-01.TodoAPI-Migrate-DB

Exercise: Migrations (Alembic), Concurrency & Transactions​

  • Refer to U03-01.TodoAPI in Unit 03

Goal​

Use Alembic for schema management and add transaction-safe operations and indexes.

Note: Alembic typically runs with a sync driver (e.g., psycopg/psycopg2) even if the app uses asyncpg. Keep migrations separate from runtime DB access.

Tasks​

  1. Add fields: due_date (nullable), priority (enum or int).

  2. Initialize Alembic

    pip install alembic psycopg[binary]  # psycopg3
    alembic init alembic
    • Set sqlalchemy.url in alembic.ini to sync URL: postgresql+psycopg://todo_user:todo_pass@localhost:5432/todo_db

  3. Create revision & upgrade

    • Autogenerate revision or handwrite upgrade()/downgrade().
    • Run: 
      alembic upgrade head

  4. Indexes

    • Index on completed, created_at.

  5. Transaction-safe toggle

    • Use SELECT ... FOR UPDATE or session transactions to avoid race conditions when toggling completed in high concurrency.

Acceptance Criteria​

  • Alembic revision adds new columns/indexes.
  • Toggle endpoint guarantees consistency under concurrent requests (explain your approach).

Deliverables​

  • Alembic migration files.
  • Brief concurrency note in README.

Rubric​

  • Alembic set up & run
  • Correct schema changes & indexes
  • Concurrency-safe operation