Flask: additive schema changes with Alembic

pythonflaskindependent-practice

Saved on this device

Full lesson · 4 learning steps Open any exercise for focused practice

Step 1 of 4 · intro card

Flask: additive schema changes with Alembic

Concept and contract

Preserve an existing row while extending its schema through a migration operation.

A migration changes an existing schema while preserving the intended data and application compatibility. Initialization helpers such as create_all do not provide that versioned evolution.

Alembic expresses operations against a migration context and normally records a revision graph. This focused example exercises a real additive operation on a disposable database without pretending to be a complete production migration runner.

Mechanism and worked example

The fixture first creates an old table and inserts a saved row.

The fixture first creates an old table and inserts a saved row. It then adds a non-null country column with an explicit server default and reads back the original title beside its new value.

The default is required for the existing row under this schema change. The connection and engine are owned by the helper and closed after the selected result is copied.

Complete worked example

python

from sqlalchemy import create_engine, text, Column, String
from alembic.migration import MigrationContext
from alembic.operations import Operations

def migrate_country(country):
    if country not in ('US', 'IL', 'UA'):
        raise ValueError('unsupported fixture country')
    engine = create_engine('sqlite:///:memory:')
    try:
        with engine.begin() as connection:
            connection.execute(text('CREATE TABLE role (id INTEGER PRIMARY KEY, title TEXT NOT NULL)'))
            connection.execute(text("INSERT INTO role VALUES (1, 'Saved')"))
            operations = Operations(MigrationContext.configure(connection))
            operations.add_column('role', Column('country', String(2), nullable=False, server_default=country))
            row = connection.execute(text('SELECT title, country FROM role WHERE id=1')).one()
            return list(row)
    finally:
        engine.dispose()

Reasoning: The fixture first creates an old table and inserts a saved row. It then adds a non-null country column with an explicit server default and reads back the original title beside its new value.

Counterexamples and common mistakes

The counterexample uses a fixed US default regardless of the requested country, silently corrupting the intended backfill for other cases..

The counterexample uses a fixed US default regardless of the requested country, silently corrupting the intended backfill for other cases.

Deleting and recreating the table would be a worse shortcut because it loses historical rows. Defaults also have continuing effects: a server default can apply to later inserts until a follow-up migration deliberately changes or removes it.

Counterexample: a runnable framework mistake

python

from sqlalchemy import create_engine, text, Column, String
from alembic.migration import MigrationContext
from alembic.operations import Operations

def migrate_country(country):
    if country not in ('US', 'IL', 'UA'):
        raise ValueError('unsupported fixture country')
    engine = create_engine('sqlite:///:memory:')
    try:
        with engine.begin() as connection:
            connection.execute(text('CREATE TABLE role (id INTEGER PRIMARY KEY, title TEXT NOT NULL)'))
            connection.execute(text("INSERT INTO role VALUES (1, 'Saved')"))
            operations = Operations(MigrationContext.configure(connection))
            operations.add_column('role', Column('country', String(2), nullable=False, server_default="US"))
            row = connection.execute(text('SELECT title, country FROM role WHERE id=1')).one()
            return list(row)
    finally:
        engine.dispose()

Reasoning: The counterexample uses a fixed US default regardless of the requested country, silently corrupting the intended backfill for other cases.

Independent practice: one bounded framework change

Add a second non-null status column with a draft server default, then include it in the readback.

Add a second non-null status column with a draft server default, then include it in the readback. Preserve the original row and country-specific backfill.

This introduces one additive operation, not a destructive rewrite. Explain whether draft should remain a default for future inserts; the exercise keeps it intentionally, while a different domain might require a later explicit alteration.

Step 1: Add a draft status column while preserving the old row. Step 2: Use another additive Column operation.

Practice starter: solve before reading the solution

python

from sqlalchemy import create_engine, text, Column, String
from alembic.migration import MigrationContext
from alembic.operations import Operations

def migrate_country(country):
    # Remove this placeholder, then adapt the retained baseline:
    # Add a draft status column while preserving the old row.
    raise NotImplementedError
    if country not in ('US', 'IL', 'UA'):
        raise ValueError('unsupported fixture country')
    engine = create_engine('sqlite:///:memory:')
    try:
        with engine.begin() as connection:
            connection.execute(text('CREATE TABLE role (id INTEGER PRIMARY KEY, title TEXT NOT NULL)'))
            connection.execute(text("INSERT INTO role VALUES (1, 'Saved')"))
            operations = Operations(MigrationContext.configure(connection))
            operations.add_column('role', Column('country', String(2), nullable=False, server_default=country))
            row = connection.execute(text('SELECT title, country FROM role WHERE id=1')).one()
            return list(row)
    finally:
        engine.dispose()

Reasoning: Add a draft status column while preserving the old row. Use another additive Column operation.

Hint 1: Select status beside the existing fields.
Hint 2: Keep country validation and engine cleanup.

Solution, tests and trade-offs

The cases verify two country backfills and rejection of an unsupported country before opening the fixture database.

The cases verify two country backfills and rejection of an unsupported country before opening the fixture database. Practice checks the status alongside the preserved title and country.

Reading the existing row is essential evidence: an empty newly created table could have the correct columns while losing everything that mattered. No working ApplyDjinn database or migration history is accessed.

Practice solution and acceptance checks

python

from sqlalchemy import create_engine, text, Column, String
from alembic.migration import MigrationContext
from alembic.operations import Operations

def migrate_country(country):
    if country not in ('US', 'IL', 'UA'):
        raise ValueError('unsupported fixture country')
    engine = create_engine('sqlite:///:memory:')
    try:
        with engine.begin() as connection:
            connection.execute(text('CREATE TABLE role (id INTEGER PRIMARY KEY, title TEXT NOT NULL)'))
            connection.execute(text("INSERT INTO role VALUES (1, 'Saved')"))
            operations = Operations(MigrationContext.configure(connection))
            operations.add_column('role', Column('country', String(2), nullable=False, server_default=country))
            operations.add_column('role', Column('status', String(10), nullable=False, server_default='draft'))
            row = connection.execute(text('SELECT title, country, status FROM role WHERE id=1')).one()
            return list(row)
    finally:
        engine.dispose()

Reasoning: The cases verify two country backfills and rejection of an unsupported country before opening the fixture database. Practice checks the status alongside the preserved title and country.

Interview reasoning and next step

Before applying a production migration, review lock duration, database-specific DDL behavior, backups and tested rollback or forward-repair procedures.

Before applying a production migration, review lock duration, database-specific DDL behavior, backups and tested rollback or forward-repair procedures. SQLite often requires batch operations for more complex alterations, while other engines have different constraints.

Keep large data backfills bounded and resumable. The lesson’s owned in-memory operation teaches the preservation invariant without authorizing an automatic live schema rewrite.

Optional stretch — after the core task works: Write a revision and rehearse it against a copied realistic schema.

Computer practice: https://applydjinn.com/typorithm/python-frameworks-lab-20261105.zip. Install the pinned dependencies in an isolated environment, run verify.py --track flask, then edit the retained exercise baseline and check it with --practice fl-migrations. The lab uses owned in-memory databases, local framework clients and tiny CPU tensors. It never reads the working ApplyDjinn database, contacts production feeds or downloads models.

Preserve an existing row while extending its schema through a migration operation.