Guide to Alembic Setup for FastAPI Projects

27 Mar, 2025

What is Alembic and Guide to Migrating using Alembic

• Starter point: I have just setup my FastAPI Backend API with /health endpoint working.
• Next thing I want to do is setup alembic. But before jumping to how to setup alembic, let's understand why alembic?

Why Alembic?

Alembic is a database migration tool for SQLAlchemy. It allows you to:

• Track database schema changes
• Version control your database schema
• Upgrade/downgrade your database schema
• Create migration scripts automatically based on model changes

Time to go one level deeper

1. Why track database schema changes?

• Databases evolve alongside applications (new tables, columns, constraints, etc.)
• Without tracking, you lose history of how your database reached its current state
• Critical for debugging (e.g., "When did we add this column? Why does it exist?")

2. Why version control your database?

• Same reason you version control application code:
  • Roll back to previous versions if something breaks
  • See who made changes and when
• Collaborate with team members consistently
• Prevents "it works on my machine" problems where developers have different DB structures

3. Why upgrade/downgrade?

• Upgrades: Deploy new schema changes to production/staging (e.g., adding indexes for performance)
• Downgrades: Revert bad changes without restoring backups (critical for bug fixes)
• Enables continuous deployment by automating DB changes alongside code deployment

4. Why automatic migration scripts from models?

• Saves time by generating boilerplate SQL
• Reduces human error in writing complex schema changes
• Keeps application models (Python classes) and database schema in sync

Real-World Scenarios Where Alembic Shines:

• Team Collaboration: Multiple developers working on features that each require DB changes

• CI/CD Pipelines: Automated testing needs to create/teardown test databases

• Zero-Downtime Deployments: Carefully sequenced migrations for high-availability systems

• Data Consistency: Migrations often include data transformations (e.g., splitting a column into two)

With intro over now, let's move towards how to bit.

Setting up Alembic

1. First, install Alembic if you haven't already: pip install alembic
2. Initialize Alembic in your project:

cd backend  # Go to your backend directory
alembic init alembic

• This creates an alembic directory with the necessary files.

3. Configure alembic.ini - edit the file to set the database URL:

# Find and edit this line:
sqlalchemy.url = postgresql+psycopg2://postgres:postgres@localhost:5432/kollect_db

4. Update alembic/env.py to use your SQLAlchemy models: env.py file here

Creating and Running Migrations

1. Generate an initial migration:

• The initial migration in Alembic using alembic revision --autogenerate -m "Initial migration" serves several important purposes.
  1. Baseline Establishment: Creates the first versioned state of your database schema. Serves as a starting point for all future migrations.
  2. Sync Between Models and Database: The --autogenerate flag compares your SQLAlchemy models against the actual database. Generates migration scripts to make the database match your models
  3. Version Control Foundation: Creates the alembic_version table in your DB (to track which migrations have been applied). Enables the upgrade/downgrade workflow

2. What Happens Specifically:

• When you run: alembic revision --autogenerate -m "Initial migration"

• Alembic:

  1. Scans your SQLAlchemy model definitions
  2. Compares them with the current database state (if any exists)
  3. Generates a migration script in versions/ folder containing:
     1. upgrade(): Commands to create all tables/columns from your models
     2. downgrade(): Commands to completely revert the changes

• You then run: alembic upgrade head - To actually apply these changes to your database.

Key Reasons for This Approach:

• Reproducibility: Anyone can bootstrap the database from scratch by running migrations
• Consistency: Ensures your declarative models (Python classes) match the database schema
• Safety: The initial migration becomes a known good state you can always return to

Common Initial Migration Scenarios:

1. Brand New Project (Empty Database):

   • Autogenerate will produce SQL to create all tables
   • Example output might include create_table for every model

2. Existing Database:

• Alembic detects differences between models and DB
• Generates only the necessary changes to align them

3. First Migration in Legacy Project:

• Often requires manual tweaking of the autogenerated script
• May need to set include_object in env.py to handle special cases