Alembic migrations in FastAPI - Step-by-Step Execution

Concept Flow - Alembic migrations

Create migration script

↓

Edit migration script

↓

Run alembic upgrade

↓

Database schema updated

↓

Check migration history

↓

Repeat for new changes

This flow shows how you create, edit, and apply database schema changes step-by-step using Alembic migrations.

Execution Sample

FastAPI

alembic revision -m "add users table"
# edit migration script
alembic upgrade head

Create a new migration script, edit it to define schema changes, then apply those changes to the database.

Execution Table

Step	Command/Action	Effect	Database State	Notes
1	alembic revision -m "add users table"	Creates new migration script file	Schema unchanged	Migration script ready for edits
2	Edit migration script	Defines new table 'users'	Schema unchanged	Script now contains SQL commands for new table
3	alembic upgrade head	Runs migration script	Users table added	Database schema updated
4	alembic history	Shows applied migrations	Users table present	Confirms migration applied
5	alembic revision -m "add email column"	Creates new migration script	Users table present	Prepare next schema change
6	Edit migration script	Adds 'email' column to users	Users table present	Script ready for next upgrade
7	alembic upgrade head	Runs migration script	Users table with email column	Schema updated again
8	alembic downgrade -1	Reverts last migration	Users table without email column	Schema rolled back one step
9	alembic upgrade head	Reapplies last migration	Users table with email column	Schema restored
10	Exit	No more commands	Final schema state	Migration process complete

💡 No more migration commands; database schema is up to date

Variable Tracker

Variable	Start	After Step 3	After Step 7	After Step 8	After Step 9
Database Schema	Empty	Users table added	Users table + email column	Users table without email column	Users table + email column

Key Moments - 3 Insights

Why does the database schema not change immediately after creating a migration script?

Creating a migration script only prepares the changes; the schema updates only happen after running 'alembic upgrade' as shown in steps 1-3.

What happens when you run 'alembic downgrade -1'?

It reverts the last applied migration, rolling back the schema to the previous state, as seen in step 8.

Why do we need to edit the migration script after creating it?

Alembic generates an empty script; you must add the specific schema changes manually before applying them, shown in steps 1-2 and 5-6.

Visual Quiz - 3 Questions

Look at the execution table, what is the database state after step 3?

A Users table with email column

B Empty schema

C Users table added

D Users table without email column

💡 Hint

Check the 'Database State' column in row for step 3

At which step does the 'email' column get removed from the users table?

A Step 8

B Step 9

C Step 7

D Step 5

💡 Hint

Look for the step where the schema rolls back the last migration

If you skip editing the migration script after creating it, what happens when you run 'alembic upgrade head'?

A Schema updates with default changes

B No schema changes applied

C Error stops migration

D Alembic creates a new migration script automatically

💡 Hint

Refer to steps 1-3 and the note about editing migration scripts

Concept Snapshot

Alembic migrations manage database schema changes.
Create a migration script with 'alembic revision -m'.
Edit the script to define schema updates.
Apply changes using 'alembic upgrade head'.
Use 'alembic downgrade' to revert changes.
Check migration history with 'alembic history'.

Full Transcript

Alembic migrations help update your database schema step-by-step. First, you create a migration script using 'alembic revision -m' with a message describing the change. This script is empty initially, so you edit it to add the specific schema changes like creating tables or adding columns. Then, you apply these changes to the database by running 'alembic upgrade head'. This updates the database schema. You can check which migrations have been applied with 'alembic history'. If needed, you can undo the last migration using 'alembic downgrade -1'. This process allows you to safely and clearly manage database changes over time.

Practice

1. What is the main purpose of Alembic migrations in a FastAPI project?

easy

A. To manage and apply database schema changes safely over time

B. To handle HTTP requests and responses

C. To serve static files like images and CSS

D. To create user interface components

Solution

1. Step 1: Understand Alembic's role

   Alembic is a tool designed to manage database schema changes, not web server tasks.

2. Step 2: Identify the correct purpose

   It helps developers apply, track, and revert database changes safely during development and deployment.

3. Final Answer:

   To manage and apply database schema changes safely over time -> Option A

4. Quick Check:

   Alembic = database migrations [OK]

Hint: Alembic is for database schema changes, not web or UI tasks [OK]

Common Mistakes:

• Confusing Alembic with FastAPI routing
• Thinking Alembic serves static files
• Assuming Alembic builds UI components

2. Which Alembic command creates a new migration script file?

easy

A. alembic upgrade head

B. alembic init migrations

C. alembic downgrade -1

D. alembic revision -m "message"

Solution

1. Step 1: Identify commands for migration scripts

   The command to create a new migration script is revision with a message describing the change.

2. Step 2: Match the command

   alembic revision -m "message" creates a new migration file with the given message.

3. Final Answer:

   alembic revision -m "message" -> Option D

4. Quick Check:

   revision = create migration script [OK]

Hint: Use 'revision' with -m to create migration scripts [OK]

Common Mistakes:

• Using 'upgrade' to create scripts instead of apply them
• Confusing 'downgrade' with script creation
• Using 'init' after project setup

3. Given this Alembic command sequence:
alembic revision -m "add users table"
alembic upgrade head
What happens after running these commands?

medium

A. The database schema is reset to the initial state

B. The migration script is deleted and no changes are applied

C. A new migration script is created and the database schema is updated to include the users table

D. The database schema is downgraded by one version

Solution

1. Step 1: Understand the revision command

   alembic revision -m "add users table" creates a new migration script file describing the addition of the users table.

2. Step 2: Understand the upgrade command

   alembic upgrade head applies all migrations up to the latest, updating the database schema accordingly.

3. Final Answer:

   A new migration script is created and the database schema is updated to include the users table -> Option C

4. Quick Check:

   revision + upgrade = new migration applied [OK]

Hint: Revision creates script; upgrade applies it to DB [OK]

Common Mistakes:

• Thinking upgrade resets or deletes migrations
• Confusing downgrade with upgrade
• Assuming revision applies changes immediately

4. You run alembic upgrade head but get an error about missing dependencies in your migration script. What is the best way to fix this?

medium

A. Edit the migration script to add missing imports or fix syntax errors

B. Delete all migration scripts and start over

C. Run alembic downgrade base without fixing scripts

D. Ignore the error and rerun the command

Solution

1. Step 1: Identify cause of error

   Missing dependencies or syntax errors in migration scripts cause upgrade failures.

2. Step 2: Fix the migration script

   Editing the script to add missing imports or correct syntax resolves the error and allows upgrade to succeed.

3. Final Answer:

   Edit the migration script to add missing imports or fix syntax errors -> Option A

4. Quick Check:

   Fix script errors before upgrading [OK]

Hint: Fix migration script errors before upgrading [OK]

Common Mistakes:

• Deleting scripts unnecessarily
• Ignoring errors and retrying blindly
• Downgrading without fixing root cause

5. You want to add a new column to an existing table using Alembic migrations. Which sequence correctly applies this change without losing existing data?

hard

A. Delete the database and recreate it with the new column

B. Create a new revision script adding the column, then run alembic upgrade head

C. Modify the existing migration script that created the table and rerun alembic upgrade head

D. Run alembic downgrade base then create a new revision script

Solution

1. Step 1: Create a new migration script

   Adding a new column requires a new migration script describing the change to preserve history and data.

2. Step 2: Apply the migration

   Running alembic upgrade head applies the new migration safely without deleting existing data.

3. Final Answer:

   Create a new revision script adding the column, then run alembic upgrade head -> Option B

4. Quick Check:

   New revision + upgrade = safe schema update [OK]

Hint: Always create new revision for schema changes, then upgrade [OK]

Common Mistakes:

• Modifying old migration scripts after applying
• Deleting database instead of migrating
• Downgrading unnecessarily before adding columns