← SQLAlchemy and Alembic: reliable database work Progress & review ↗

Typorithm · medium

Autogenerate candidates require a review

pythonsqlalchemyalembicdatabasesindependent-practice

Saved on this device

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

Step 1 of 4 · intro card

Autogenerate candidates require a review

Concept and contract

Compare proposed metadata with the real schema, apply a reviewed change and check for a remaining diff.

Autogenerate compares a database schema with supplied target metadata and produces candidate operations. It is a starting point for review, not an omniscient interpretation of application intent. A proposed column rename can appear as removing one column and adding another, and schema comparison does not invent the data transformation needed to preserve business meaning.

The fixture uses produce_migrations to inspect a structured candidate plan. It defines the complete target metadata for its tiny owned roles table and deliberately proposes one nullable additive column, a transition small enough to inspect and apply explicitly.

Mechanism and worked example

The first comparison produces an add_column candidate.

The first comparison produces an add_column candidate. The program then explicitly calls Operations.add_column for that reviewed field and compares again. The final plan is empty, the actual inspected columns include label, and the original score still exists.

An empty remaining schema diff is evidence for the compared metadata and comparison settings. It is not proof of application correctness, data quality or an automatically reviewed migration file. The complete fixture exposes candidate operation names so you can connect the proposed schema difference to the specific operation that was actually executed.

Complete worked example

python

from sqlalchemy import create_engine, MetaData, Table, Column, Integer, String, inspect, text
from alembic.migration import MigrationContext
from alembic.operations import Operations
from alembic.autogenerate import produce_migrations

def inspect_example(score):
    engine = create_engine("sqlite+pysqlite://", connect_args={"autocommit": False})
    try:
        original = MetaData()
        Table("roles", original, Column("id", Integer, primary_key=True), Column("score", Integer))
        original.create_all(engine)
        proposed = MetaData()
        Table("roles", proposed, Column("id", Integer, primary_key=True),
              Column("score", Integer), Column("label", String(30), nullable=True))
        with engine.begin() as connection:
            connection.execute(text("INSERT INTO roles VALUES (1, :score)"), {"score": score})
            context = MigrationContext.configure(connection)
            candidate = produce_migrations(context, proposed)
            changes = [item.to_diff_tuple()[0] for table in candidate.upgrade_ops.ops for item in table.ops]
            # Review this specific additive change; do not blindly run arbitrary candidates.
            operation = Operations(context)
            operation.add_column("roles", Column("label", String(30), nullable=True))
            clean_after = produce_migrations(context, proposed).upgrade_ops.is_empty()
            columns = [column["name"] for column in inspect(connection).get_columns("roles")]
            return {"candidates": changes, "cleanAfter": clean_after, "columns": columns,
                    "score": connection.scalar(text("SELECT score FROM roles"))}
    finally:
        engine.dispose()

Reasoning: The first comparison produces an add_column candidate. The program then explicitly calls Operations.add_column for that reviewed field and compares again. The final plan is empty, the actual inspected columns include label, and the original score still exists. An empty remaining schema diff is evidence for the compared metadata and comparison settings. It is not proof of application correctness, data quality or an automatically reviewed migration file. The complete fixture exposes candidate operation names so you can connect the proposed schema difference to the specific operation that was actually executed.

Download the SQLAlchemy and Alembic computer lab ↗

Focused decision: context is in the complete example

python

candidate = produce_migrations(context, proposed)
operation.add_column("roles", Column("label", String(30), nullable=True))
clean_after = produce_migrations(context, proposed).upgrade_ops.is_empty()

Database decision: The initial plan is advisory. The explicit reviewed operation changes the table, and a new comparison checks that the requested additive target has been reached.

Download the SQLAlchemy and Alembic computer lab ↗

Counterexamples and common mistakes

The controlled mistake omits the actual add-column operation.

The controlled mistake omits the actual add-column operation. Candidate generation still succeeds, but the second comparison is not clean and the physical column list remains old. Generating a plan did not execute it. A program that reports success merely because no exception occurred during comparison would miss this difference.

Review target_metadata coverage, comparison options, names and destructive changes. A missing model import can make a real table look unwanted, while a rename can look like a destructive drop/add pair. Revision-head checks and schema-diff checks answer different questions; neither one should be described as a complete backup or a test of every stored row.

Controlled bug: observe the rejected database contract

python

from sqlalchemy import create_engine, MetaData, Table, Column, Integer, String, inspect, text
from alembic.migration import MigrationContext
from alembic.operations import Operations
from alembic.autogenerate import produce_migrations

def inspect_example(score):
    engine = create_engine("sqlite+pysqlite://", connect_args={"autocommit": False})
    try:
        original = MetaData()
        Table("roles", original, Column("id", Integer, primary_key=True), Column("score", Integer))
        original.create_all(engine)
        proposed = MetaData()
        Table("roles", proposed, Column("id", Integer, primary_key=True),
              Column("score", Integer), Column("label", String(30), nullable=True))
        with engine.begin() as connection:
            connection.execute(text("INSERT INTO roles VALUES (1, :score)"), {"score": score})
            context = MigrationContext.configure(connection)
            candidate = produce_migrations(context, proposed)
            changes = [item.to_diff_tuple()[0] for table in candidate.upgrade_ops.ops for item in table.ops]
            # Review this specific additive change; do not blindly run arbitrary candidates.
            operation = Operations(context)
            # Missing the reviewed migration operation
            clean_after = produce_migrations(context, proposed).upgrade_ops.is_empty()
            columns = [column["name"] for column in inspect(connection).get_columns("roles")]
            return {"candidates": changes, "cleanAfter": clean_after, "columns": columns,
                    "score": connection.scalar(text("SELECT score FROM roles"))}
    finally:
        engine.dispose()

Reasoning: The controlled mistake omits the actual add-column operation. Candidate generation still succeeds, but the second comparison is not clean and the physical column list remains old. Generating a plan did not execute it. A program that reports success merely because no exception occurred during comparison would miss this difference. Review target_metadata coverage, comparison options, names and destructive changes. A missing model import can make a real table look unwanted, while a rename can look like a destructive drop/add pair. Revision-head checks and schema-diff checks answer different questions; neither one should be described as a complete backup or a test of every stored row.

Download the SQLAlchemy and Alembic computer lab ↗

Independent practice

Type the candidate comparison, explicit operation and final empty-plan check.

Type the candidate comparison, explicit operation and final empty-plan check. For independent practice propose a second nullable column named origin and explicitly apply it as well. Preserve the first additive field and original data, then inspect the remaining schema differences.

Make the target metadata and executed operations agree instead of suppressing unwanted candidates in the report. The expected plan now contains two add-column operations. Run the final comparison against the same owned connection after both changes so the result reflects current schema state rather than a previously captured candidate object.

Step 1: Propose and explicitly apply a second nullable column named origin; verify there is no remaining schema diff. Step 2: Keep label, the original score and the final comparison.

Practice starter: solve before reading the solution

python

from sqlalchemy import create_engine, MetaData, Table, Column, Integer, String, inspect, text
from alembic.migration import MigrationContext
from alembic.operations import Operations
from alembic.autogenerate import produce_migrations

def inspect_example(score):
    engine = create_engine("sqlite+pysqlite://", connect_args={"autocommit": False})
    try:
        original = MetaData()
        Table("roles", original, Column("id", Integer, primary_key=True), Column("score", Integer))
        original.create_all(engine)
        proposed = MetaData()
        Table("roles", proposed, Column("id", Integer, primary_key=True),
              Column("score", Integer), Column("label", String(30), nullable=True))
        with engine.begin() as connection:
            connection.execute(text("INSERT INTO roles VALUES (1, :score)"), {"score": score})
            context = MigrationContext.configure(connection)
            candidate = produce_migrations(context, proposed)
            changes = [item.to_diff_tuple()[0] for table in candidate.upgrade_ops.ops for item in table.ops]
            # Review this specific additive change; do not blindly run arbitrary candidates.
            operation = Operations(context)
            operation.add_column("roles", Column("label", String(30), nullable=True))
            clean_after = produce_migrations(context, proposed).upgrade_ops.is_empty()
            columns = [column["name"] for column in inspect(connection).get_columns("roles")]
            return {"candidates": changes, "cleanAfter": clean_after, "columns": columns,
                    "score": connection.scalar(text("SELECT score FROM roles"))}
    finally:
        engine.dispose()

# Task: Propose and explicitly apply a second nullable column named origin; verify there is no remaining schema diff.

Reasoning: Propose and explicitly apply a second nullable column named origin; verify there is no remaining schema diff.

Download the SQLAlchemy and Alembic computer lab ↗

Hint 1: Add origin to both proposed metadata and the explicit operations.
Hint 2: The final column list must come from the database inspector.

Solution, tests and trade-offs

The solution declares and applies both label and origin.

The solution declares and applies both label and origin. It reports two additive candidates, no remaining changes, the actual four-column table and the unchanged original score. Cases with positive, zero and negative scores confirm that this schema-only transition does not invent a data rewrite.

The lab checks the real autogenerate API and schema inspection, not an independently hardcoded candidate list returned by the function. In a project using the command workflow, supply reviewed target_metadata in env.py and inspect the generated upgrade and downgrade bodies. A check command can detect pending differences, but it does not replace that review.

Practice solution and acceptance checks

python

from sqlalchemy import create_engine, MetaData, Table, Column, Integer, String, inspect, text
from alembic.migration import MigrationContext
from alembic.operations import Operations
from alembic.autogenerate import produce_migrations

def inspect_example(score):
    engine = create_engine("sqlite+pysqlite://", connect_args={"autocommit": False})
    try:
        original = MetaData()
        Table("roles", original, Column("id", Integer, primary_key=True), Column("score", Integer))
        original.create_all(engine)
        proposed = MetaData()
        Table("roles", proposed, Column("id", Integer, primary_key=True),
              Column("score", Integer), Column("label", String(30), nullable=True),
              Column("origin", String(30), nullable=True))
        with engine.begin() as connection:
            connection.execute(text("INSERT INTO roles VALUES (1, :score)"), {"score": score})
            context = MigrationContext.configure(connection)
            candidate = produce_migrations(context, proposed)
            changes = [item.to_diff_tuple()[0] for table in candidate.upgrade_ops.ops for item in table.ops]
            # Review this specific additive change; do not blindly run arbitrary candidates.
            operation = Operations(context)
            operation.add_column("roles", Column("label", String(30), nullable=True))
            operation.add_column("roles", Column("origin", String(30), nullable=True))
            clean_after = produce_migrations(context, proposed).upgrade_ops.is_empty()
            columns = [column["name"] for column in inspect(connection).get_columns("roles")]
            return {"candidates": changes, "cleanAfter": clean_after, "columns": columns,
                    "score": connection.scalar(text("SELECT score FROM roles"))}
    finally:
        engine.dispose()

Reasoning: The solution declares and applies both label and origin. It reports two additive candidates, no remaining changes, the actual four-column table and the unchanged original score. Cases with positive, zero and negative scores confirm that this schema-only transition does not invent a data rewrite. The lab checks the real autogenerate API and schema inspection, not an independently hardcoded candidate list returned by the function. In a project using the command workflow, supply reviewed target_metadata in env.py and inspect the generated upgrade and downgrade bodies. A check command can detect pending differences, but it does not replace that review.

Download the SQLAlchemy and Alembic computer lab ↗

Interview reasoning and next step

Explain the difference between discovering a candidate, authoring a revision and applying a migration.

Explain the difference between discovering a candidate, authoring a revision and applying a migration. Give an example of a business intent, such as renaming a field without losing its values, that cannot safely be inferred from a simple drop/add diff. State what the final empty plan does and does not prove.

Next, apply a change SQLite cannot express through a straightforward portable ALTER sequence. Batch operations may recreate a table, so retained rows, nullability, named constraints and indexes need explicit observations. A generated candidate is only useful when its execution strategy and data effects are understood.

Optional stretch — after the core task works: Model a rename in another owned schema and inspect the destructive candidate before writing a preserving operation.

Computer practice: https://applydjinn.com/typorithm/sqlalchemy-lab-20261107.zip. Install the pinned requirements in an isolated Python 3.13 environment and run verify.py. Edit the working exercise baseline, then run --practice sa-autogenerate. The lab uses disposable SQLite databases and actual SQLAlchemy/Alembic calls; it does not connect to application accounts, production databases or paid services. The app includes prepared results and explained questions without an editor or Python runtime.

Compare proposed metadata with the real schema, apply a reviewed change and check for a remaining diff.