Versioning Your Database with SQLAlchemy and Alembic: From Models to Safe Migrations

SQLAlchemy and Alembic give you a safer, more controlled way to evolve your schema over time. In the FastOpp Country Store project, that shows up when you do something simple but dangerous in real life: add a new column to an existing table in SQLite. Can you migrate your data without blowing everything up?

This tutorial follows the flow of the “FastOpp Country Store 05 – Add New Column to SQL Table” video. You will:

• Change a SQLModel database model in FastOpp (SQLModel runs on top of SQLAlchemy)
• Create and run database migrations (via Alembic under the hood)
• Fix a real migration error from SQLite

My Ah-ha! Moment

I’ve been working with Django for a number of years on multiple projects for clients. I just got a breakthrough understanding working on a project called FastOpp. The big realization was this:

When using SQLAlchemy and Alembic, you first create a Python migration file, and only later, when you run alembic upgrade, does that file actually change your database schema.

At first, that feels like extra work. Why not just apply the changes directly?

Because that two-step process is the safety net.

• alembic revision --autogenerate looks at your SQLAlchemy models versus the current database and writes a Python migration script.
• At this point, nothing has happened to your database.

• You get a chance to:

  • Review and edit the migration.
  • Commit it to git.
  • Have others review it in a PR.
  • Test it on dev/staging.

Only when you explicitly run something like:

alembic upgrade head

(or the FastOpp wrapper around that command) does Alembic connect to the database and apply those changes.

That separation—generate now, apply later—is what keeps accidental, destructive schema changes from landing silently in production. It is dramatically safer than systems that auto-migrate whenever the app starts and hope nothing catastrophic is in the diff.

Once this clicks, Alembic stops feeling like extra steps and starts looking like exactly what it is: version control for your schema.

What the FastOpp Video Actually Does

FastOpp is a FastAPI-based starter that gives you a Django-like experience for AI apps: admin panel, authentication, SQL database models and migrations, and Jinja2 templates with Tailwind/DaisyUI, all wired together in an opinionated structure.

In Part 5 of the Country Store series, the flow is roughly:

1. Start from a working FastOpp Country Store app using SQLite.
2. Add a new column to an existing SQLModel table (Food).
3. Use the FastOpp migration command (a thin wrapper around Alembic) to:

• Create a migration.
• Upgrade the database.
  1. Hit a SQLite migration issue around nullability/defaults.
  2. Edit the Alembic-generated migration file to fix the problem.

Step 1 – Add a New Column to the Food Model

The video works with a Food model representing items in the Country Store. The key step is adding a new price column to that table.

The resulting model looks like this:

import uuid
from sqlmodel import SQLModel, Field

class Food(SQLModel, table=True):
    __tablename__ = "foods"  # type: ignore

    id: uuid.UUID | None = Field(default_factory=uuid.uuid4, primary_key=True)
    name: str = Field(max_length=100, nullable=False)
    description: str | None = Field(default=None, nullable=True)
    image_url: str | None = Field(default=None, nullable=True)
    price: float = Field(default=0.0, nullable=False)

Important points:

• table=True and __tablename__ = "foods" define the underlying table name.
• Existing columns (id, name, description, image_url) are already in the database.
• The new column is price: float = Field(default=0.0, nullable=False).

At this moment:

• The Python model knows about price.
• The SQLite database does not yet have a price column on foods.

That gap between “model” and “database” is exactly what the migration will fix.

Step 2 – Create a Migration (Alembic Revision)

FastOpp exposes a migration command that calls Alembic under the hood. Conceptually, it’s equivalent to:

alembic revision --autogenerate -m "add price to foods"

What this does:

• Imports your SQLModel metadata (including Food).
• Compares it to the existing SQLite schema.
• Writes a new migration script in your migrations/alembic/versions directory.

A simplified version of the generated migration might look like this:

from alembic import op
import sqlalchemy as sa

def upgrade() -> None:
    op.add_column(
        "foods",
        sa.Column("price", sa.Float(), nullable=False),
    )


def downgrade() -> None:
    op.drop_column("foods", "price")

This is only a plan. The database still does not have the price column. You are in the safe stage where you can:

• Inspect the migration.
• Adjust nullability or defaults.
• Commit it to git.

Step 3 – Run the Migration and Handle SQLite Constraints

Next, run the upgrade. In raw Alembic:

alembic upgrade head

In FastOpp, we use a wrapper command via oppman.py, but the end result is the same: Alembic executes the upgrade() function against your SQLite database.

Here is where SQLite can cause trouble. Adding a non-nullable column (nullable=False) to a table that already has rows is a classic problem:

• SQLite needs a value for price on every existing row.
• If the migration doesn’t specify a default at the DB level (server_default), SQLite tries to use NULL.
• NULL contradicts NOT NULL, and you get an error similar to:

sqlite3.OperationalError: Cannot add a NOT NULL column with default value NULL

Even though the SQLModel field has default=0.0, Alembic’s autogenerate may not automatically turn that into a proper server_default on the database column. So you end up with:

sa.Column("price", sa.Float(), nullable=False)

and no default on the DB side.

This is exactly the kind of mismatch the video walks through: the application-level default and the database-level constraints are not fully aligned, and SQLite forces you to resolve it.

Step 4 – Fix the Migration Script for SQLite

The correct place to fix this is the migration file, not manual SQL pokes to your database.

Option A – Allow NULL Temporarily

For a demo app like Country Store, the easiest fix is to relax the constraint:

def upgrade() -> None:
    op.add_column(
        "foods",
        sa.Column("price", sa.Float(), nullable=True),
    )

SQLite can now add the price column and set NULL on all existing rows.

Your application code can treat None as “unknown price” or fall back to 0.0 at the Python layer. This keeps the migration simple and safe for a tutorial.

Option B – Set a Database Default

If you really want NOT NULL at the database level, then you need to explicitly tell SQLite what to put into price for old rows:

def upgrade() -> None:
    op.add_column(
        "foods",
        sa.Column(
            "price",
            sa.Float(),
            nullable=False,
            server_default="0.0",
        ),
    )

Now SQLite:

• Adds the price column.
• Fills 0.0 for every existing row.
• Enforces NOT NULL going forward.

The core idea:

• SQLModel’s default=0.0 is not enough by itself.
• You must encode your intention into the migration so SQLite knows what to do with existing data.

Step 5 – Re-run the Migration

After editing the migration file:

alembic upgrade head

(or the FastOpp wrapper)

This time the migration should succeed:

• The foods table gains a price column.
• Existing rows either have NULL or 0.0, depending on your choice.
• Alembic records that the migration has been applied.

Anyone else working on the FastOpp project just has to pull the code and run the same upgrade command to get the same schema.

Why This Flow Matters

This one change to the Food model (price added to foods) quietly demonstrates several important habits:

• Change the model first.
  The SQLModel definition is your desired end state.

• Generate and review a migration.
  Let Alembic autogenerate the migration, then edit it so SQLite’s constraints and your data model actually match.

• Fix problems in code, not with ad-hoc SQL.
  When SQLite complains about nullability or defaults, the answer is to adjust the migration script and run it again.

• Wire the change through to the UI.
  Jinja2 templates, with variable interpolation and simple helpers like .split(), give you a clean way to expose new columns.

Once you internalize this pattern, adding a new column like price to foods stops being a risk and becomes a predictable, repeatable process—exactly what you want in a real project.