Module 3 of 9 · Lesson 7 of 26

Create and verify an Alembic migration

Work through create and verify an Alembic migration using a runnable reference, a focused regression check and a local extension.

Read in any order. All lessons stay open, including after an unanswered or incorrect check.

In this lesson you will create and verify an Alembic migration. Work with the Equipment Rental API in the downloadable lab. The reference is a complete solution with separate lesson checks, so you can inspect the answer, make a deliberate local change and verify its behavior.

A migration is a reviewable record of how a database changes. The chain here starts with users, equipment and rentals, adds condition, then adds check-in records. Alembic records the applied revision. The final model describes today's schema; importing it at application startup is not an adequate history of how existing data reaches that schema.

The condition migration expands the table with a nullable column, fills existing rows, and only then makes it required. Adding NOT NULL immediately would fail when old rows have no value. This small teaching migration fills rows inside one transaction. Lesson 11 separates bounded data batches for larger datasets; those batches must not be described as independent commits inside this migration transaction. A generated migration is a proposal: inspect constraints, defaults and destructive operations before applying it. The provided downgrade deliberately refuses to discard history. Use a reviewed forward repair or an isolated backup restore when a reversal would lose data. Never mark a migration applied merely to suppress an error.

Worked source: migrations/versions/0002_condition.py, upgrade.

The local runner applies alembic upgrade head to a newly created database. The lesson07 check compares the resulting PostgreSQL schema with Base.metadata and verifies revision 0003. Read the three revision files in order. The app factory starts only after migration; it never calls create_all.

pythonCopyable

def upgrade():
    op.add_column("equipment", sa.Column("condition", sa.String(20), nullable=True))
    # This small teaching dataset is filled within the migration transaction.
    op.execute("UPDATE equipment SET condition = 'ready' WHERE condition IS NULL")
    op.alter_column("equipment", "condition", nullable=False, server_default="ready")

TerminalPython 3.13 virtual environment; Docker running; extracted lab directory

python run_checks.py -k lesson07

Expected result The selected lesson test passes against a new temporary PostgreSQL database; the container is removed afterward.

Keep for reference

Equipment Rental lab and lesson checks

ZIP containing Python source, real Alembic migrations, 26 lesson checks, a dependency lock and text instructions. Extract it before following the local exercise.

Download Equipment Rental lab and lesson checks

Practise locally

On a separate disposable database, apply revision 0001, insert an equipment row, then upgrade to head. Verify that condition becomes ready and the old row survives. Save before/after row counts and the revision. Prepare a new migration for a nullable manufacturer field and inspect its generated SQL before applying it to your own local database.

The lesson check verifies the reference behavior. Add your own assertions for your change. Local practice is not uploaded or scored by this learning release.

Pause and reflect

What failure does this lesson prevent, and which assertion in lesson07 would expose it?

Use a concrete input, expected result and limitation from your local work. Saving a reflection does not certify the project.

Optional knowledge check

How should a new required field be introduced for existing rows?

Expand, fill valid values, then enforce the requirement.

Correct. Existing data must satisfy the new constraint before the database can enforce it.

Fake-apply the revision and leave the column absent.

Try another answer. The migration record would no longer describe the actual schema.

Add NOT NULL first, then fill the old missing values.

Try another answer. The constraint cannot be added successfully while existing rows violate it.

This practice does not assess your project or award a certificate.

Your reading progress

Progress is saved in this browser when storage is available.