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

Typorithm · medium

Schema bootstrap is not a migration history

pythonsqlalchemyalembicdatabasesindependent-practice

Saved on this device

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

Step 1 of 4 · intro card

Schema bootstrap is not a migration history

Concept and contract

Observe what create_all leaves unchanged and apply one explicit additive operation to an existing table.

Creating a fresh schema and evolving an existing schema are different operations. MetaData.create_all can create missing tables, but it does not compare every existing column with a changed model and automatically transform that table. Treating it as a migration mechanism can leave application code expecting a column that the database never received.

The fixture first creates the original roles table, then declares proposed metadata with an additional nullable label column. Calling create_all again leaves the existing table alone. A separate, explicit Alembic operation is what actually adds the column before the program reads it.

Mechanism and worked example

The program inserts one owned row, creates a MigrationContext from the active connection, and uses Operations.add_column for the reviewed nullable addition.

The program inserts one owned row, creates a MigrationContext from the active connection, and uses Operations.add_column for the reviewed nullable addition. The final report contains columns [id, score, label] and a row whose new label is None. The original score remains unchanged.

This low-level Operations object does not by itself establish a complete revision environment. The report’s revision remains None because this experiment has no version-table history. That distinction is deliberate: it isolates an actual schema change before the next lesson introduces named revision scripts and command-driven upgrades.

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

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))
        proposed.create_all(engine)  # existing tables are not altered
        with engine.begin() as connection:
            connection.execute(text("INSERT INTO roles VALUES (1, :score)"), {"score": score})
            context = MigrationContext.configure(connection)
            operation = Operations(context)
            operation.add_column("roles", Column("label", String(30), nullable=True))
            columns = [column["name"] for column in inspect(connection).get_columns("roles")]
            row = list(connection.execute(text("SELECT id, score, label FROM roles")).one())
            return {"columns": columns, "row": row, "revision": context.get_current_revision()}
    finally:
        engine.dispose()

Reasoning: The program inserts one owned row, creates a MigrationContext from the active connection, and uses Operations.add_column for the reviewed nullable addition. The final report contains columns [id, score, label] and a row whose new label is None. The original score remains unchanged. This low-level Operations object does not by itself establish a complete revision environment. The report’s revision remains None because this experiment has no version-table history. That distinction is deliberate: it isolates an actual schema change before the next lesson introduces named revision scripts and command-driven upgrades.

Download the SQLAlchemy and Alembic computer lab ↗

Focused decision: context is in the complete example

python

context = MigrationContext.configure(connection)
operation = Operations(context)
operation.add_column("roles", Column("label", String(30), nullable=True))

Database decision: The second bootstrap call does not alter this table. The explicit additive operation makes the new nullable column real, while revision history remains a separate concern.

Download the SQLAlchemy and Alembic computer lab ↗

Counterexamples and common mistakes

The controlled mistake substitutes another create_all call for the actual add-column operation.

The controlled mistake substitutes another create_all call for the actual add-column operation. The later SELECT then fails because label still does not exist in the real table. A correct-looking model definition is not evidence that a deployed schema matches it.

Do not manually stamp a revision as a substitute for executing required changes, and do not run create_all as an undocumented fix for an evolving production schema. Bootstrap can be appropriate for a new owned database, but existing tables need a reviewed transition that also considers their rows, constraints, clients and rollback requirements.

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

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))
        proposed.create_all(engine)  # existing tables are not altered
        with engine.begin() as connection:
            connection.execute(text("INSERT INTO roles VALUES (1, :score)"), {"score": score})
            context = MigrationContext.configure(connection)
            operation = Operations(context)
            proposed.create_all(connection)  # still does not migrate an existing table
            columns = [column["name"] for column in inspect(connection).get_columns("roles")]
            row = list(connection.execute(text("SELECT id, score, label FROM roles")).one())
            return {"columns": columns, "row": row, "revision": context.get_current_revision()}
    finally:
        engine.dispose()

Reasoning: The controlled mistake substitutes another create_all call for the actual add-column operation. The later SELECT then fails because label still does not exist in the real table. A correct-looking model definition is not evidence that a deployed schema matches it. Do not manually stamp a revision as a substitute for executing required changes, and do not run create_all as an undocumented fix for an evolving production schema. Bootstrap can be appropriate for a new owned database, but existing tables need a reviewed transition that also considers their rows, constraints, clients and rollback requirements.

Download the SQLAlchemy and Alembic computer lab ↗

Independent practice

First type the MigrationContext, Operations owner and explicit add_column call.

First type the MigrationContext, Operations owner and explicit add_column call. For independent practice add a nullable provenance column named source instead of label, then read that column in the report. Keep the original row, score and absence of revision history unchanged.

Update the proposed metadata, the additive operation and the SELECT consistently; changing only the displayed column name would be a false report. The fixture is deliberately disposable, so rerunning it always starts from the original schema instead of pretending that repeatedly adding the same column is an idempotent production deployment.

Step 1: Add a nullable provenance column named source instead of label and select that new column. Step 2: Keep the old score and row while renaming the new nullable field consistently.

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

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))
        proposed.create_all(engine)  # existing tables are not altered
        with engine.begin() as connection:
            connection.execute(text("INSERT INTO roles VALUES (1, :score)"), {"score": score})
            context = MigrationContext.configure(connection)
            operation = Operations(context)
            operation.add_column("roles", Column("label", String(30), nullable=True))
            columns = [column["name"] for column in inspect(connection).get_columns("roles")]
            row = list(connection.execute(text("SELECT id, score, label FROM roles")).one())
            return {"columns": columns, "row": row, "revision": context.get_current_revision()}
    finally:
        engine.dispose()

# Task: Add a nullable provenance column named source instead of label and select that new column.

Reasoning: Add a nullable provenance column named source instead of label and select that new column.

Download the SQLAlchemy and Alembic computer lab ↗

Hint 1: There are separate names in metadata, the operation and the SELECT.
Hint 2: Changing the report label alone does not change the database schema.

Solution, tests and trade-offs

The solution reports columns [id, score, source] and the same original row with a None provenance value.

The solution reports columns [id, score, source] and the same original row with a None provenance value. Its cases use positive, zero and negative scores to establish that the transition preserves existing data rather than rebuilding the report from a convenient constant.

The lab checks actual schema inspection and a real SELECT after the operation. A low-level schema addition passing here proves this bounded transition, not the completeness of a deployment procedure. Version tracking, command configuration, migration ordering and restoration are separate responsibilities that the following lessons make observable.

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

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("source", String(30), nullable=True))
        proposed.create_all(engine)  # existing tables are not altered
        with engine.begin() as connection:
            connection.execute(text("INSERT INTO roles VALUES (1, :score)"), {"score": score})
            context = MigrationContext.configure(connection)
            operation = Operations(context)
            operation.add_column("roles", Column("source", String(30), nullable=True))
            columns = [column["name"] for column in inspect(connection).get_columns("roles")]
            row = list(connection.execute(text("SELECT id, score, source FROM roles")).one())
            return {"columns": columns, "row": row, "revision": context.get_current_revision()}
    finally:
        engine.dispose()

Reasoning: The solution reports columns [id, score, source] and the same original row with a None provenance value. Its cases use positive, zero and negative scores to establish that the transition preserves existing data rather than rebuilding the report from a convenient constant. The lab checks actual schema inspection and a real SELECT after the operation. A low-level schema addition passing here proves this bounded transition, not the completeness of a deployment procedure. Version tracking, command configuration, migration ordering and restoration are separate responsibilities that the following lessons make observable.

Download the SQLAlchemy and Alembic computer lab ↗

Interview reasoning and next step

Explain the difference between declared metadata and inspected database state.

Explain the difference between declared metadata and inspected database state. State which call creates a missing table, which call changes the existing table here, and why get_current_revision still returns None after the additive operation. Do not confuse a successful DDL statement with a complete migration history.

Next, build an owned Alembic environment with two named revisions. Follow the revision table through upgrade and downgrade, then compare those operations with stamp. The goal is to connect schema changes to an auditable sequence without assuming that a version label alone proves the expected tables and rows exist.

Optional stretch — after the core task works: Inspect a fresh empty database separately from an existing table with a changed model.

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-bootstrap. 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.

Observe what create_all leaves unchanged and apply one explicit additive operation to an existing table.