Alembic migrations in FastAPI - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ component_behavior

intermediate

What happens when you run alembic upgrade head?

In a FastAPI project using Alembic, what is the effect of running the command alembic upgrade head?

A It applies all pending migrations to update the database schema to the latest version.

B It deletes all migration history but keeps the database schema unchanged.

C It rolls back the last applied migration.

D It creates a new migration file with the current database schema.

💡 Hint

Think about what 'upgrade' means in database migrations.

The command alembic upgrade head applies all migrations that have not yet been applied, bringing the database schema up to the latest version defined by the migration scripts.

📝 Syntax

intermediate

Identify the correct Alembic migration script snippet to add a new column

Which snippet correctly adds a new column email of type String(255) to the users table in an Alembic migration?

A op.add_column('users', sa.Column('email', sa.String(255), nullable=False))

B op.create_column('users', 'email', sa.String(255))

C op.add('email', sa.String(255), table='users')

D op.alter_table('users').add_column('email', sa.String(255))

💡 Hint

Look for the Alembic operation that adds a column to a table.

The correct Alembic command to add a column is op.add_column with the table name and a sa.Column object describing the new column.

🔧 Debug

advanced

Why does this Alembic migration raise an error?

Given this migration snippet, why does running it cause an error?

def upgrade():
    op.add_column('items', sa.Column('price', sa.Float))

def downgrade():
    op.drop_column('items', 'cost')

A The 'sa.Float' type is invalid in Alembic migrations.

B The upgrade is missing a nullable argument for the new column.

C The downgrade tries to drop a column named 'cost' which does not exist; it should drop 'price'.

D The migration is missing a revision ID and down_revision identifiers.

💡 Hint

Check the column names used in upgrade and downgrade functions.

The downgrade function tries to drop a column named 'cost' which was never added. The correct column to drop is 'price'. This mismatch causes an error when downgrading.

❓ state_output

advanced

What is the state of the database after this migration?

Consider this Alembic migration upgrade function:

def upgrade():
    op.create_table(
        'orders',
        sa.Column('id', sa.Integer, primary_key=True),
        sa.Column('user_id', sa.Integer, nullable=False),
        sa.Column('total', sa.Float, nullable=False)
    )

What will be the state of the database after running this migration?

A The database schema remains unchanged.

B The 'orders' table is dropped from the database.

C The migration will fail because the 'total' column type is invalid.

D A new table named 'orders' exists with columns 'id', 'user_id', and 'total'.

💡 Hint

Think about what op.create_table does.

The migration creates a new table called 'orders' with the specified columns and constraints. After running, the database will have this new table.

🧠 Conceptual

expert

Why use Alembic migrations in a FastAPI project?

Which is the best explanation for why Alembic migrations are important in a FastAPI project?

A They automatically generate API endpoints based on database tables.

B They allow you to track and apply incremental database schema changes safely over time.

C They replace the need for an ORM by managing raw SQL queries.

D They provide a graphical interface to edit database tables directly.

💡 Hint

Think about managing database changes as your app evolves.

Alembic migrations help developers keep track of database schema changes and apply them incrementally, ensuring the database matches the code expectations safely and consistently.

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