Skip to content

10 · Alembic and Database Schema History

In the previous tutorial, a request finally reached a real SQLite database:

Browser
→ FastAPI
→ dependencies
→ route
→ service
→ SQLAlchemy Session
→ SQLite
→ HTTP response
→ browser

Today’s layer sits slightly outside that request lifecycle.

Alembic changes the database’s structure before ordinary requests use it:

Change SQLAlchemy model
→ create Alembic revision
→ review migration code
→ apply migration to SQLite
→ start the new application code
→ serve browser requests

During a normal request, Alembic does nothing. The browser never communicates with Alembic, and FastAPI does not ordinarily run a migration for each request.

Alembic’s job is to make sure the physical database structure evolves alongside the Python application.

Our documents table currently contains:

id
firm_id
created_by_user_id
title
normalized_title
document_type
priority
status

The application now needs an optional review deadline:

review_due_date

A document might have:

September Website Review
Due: October 1, 2026

Older documents have no deadline, so the new column should initially allow NULL.

This is a deliberately boring migration—and that is good. Adding a nullable column is one of the safest ways to introduce a new field.


We first update the SQLAlchemy model:

from datetime import date
from sqlalchemy import Date, String, UniqueConstraint
from sqlalchemy.orm import Mapped, mapped_column
from ..database import Base
class Document(Base):
__tablename__ = "documents"
__table_args__ = (
UniqueConstraint(
"firm_id",
"normalized_title",
name="uq_documents_firm_normalized_title",
),
)
id: Mapped[int] = mapped_column(
primary_key=True,
)
firm_id: Mapped[int] = mapped_column(
index=True,
)
created_by_user_id: Mapped[int]
title: Mapped[str] = mapped_column(
String(200),
)
normalized_title: Mapped[str] = mapped_column(
String(200),
)
document_type: Mapped[str] = mapped_column(
String(30),
)
priority: Mapped[int]
status: Mapped[str] = mapped_column(
String(30),
default="not_started",
)
review_due_date: Mapped[date | None] = mapped_column(
Date,
nullable=True,
)

The important new declaration is:

review_due_date: Mapped[date | None]

The Python type is a union:

date | None

That means a Document object may contain either:

date(2026, 10, 1)

or:

None

The database configuration agrees:

mapped_column(
Date,
nullable=True,
)

Date describes the SQL column type. nullable=True permits rows without a value.

But changing this Python class does not alter review.db.

We now have two different realities:

SQLAlchemy metadata
→ Python believes review_due_date exists
SQLite schema
→ The documents table does not contain it yet

If the new application code runs a query immediately, SQLAlchemy might generate:

SELECT
documents.id,
documents.title,
documents.review_due_date
FROM documents;

SQLite would respond with an error resembling:

no such column: documents.review_due_date

The model is a description of the desired schema. It is not a command sent automatically to every existing database.

That gap is what the migration closes.


A typical project now contains:

project/
├── alembic.ini
├── alembic/
│ ├── env.py
│ ├── script.py.mako
│ └── versions/
│ └── ...
└── app/
├── database.py
├── models/
├── schemas/
├── services/
└── routers/

These files are normally created initially with:

Terminal window
alembic init alembic

That is a project-setup command, not something run for each model change.

The pieces have different responsibilities:

Component Responsibility
alembic.ini General Alembic configuration
alembic/env.py Connect Alembic to the application and model metadata
alembic/versions/ Store migration history
Migration revision Describe one forward and reverse schema change
alembic_version table Record which revision a database has reached

Alembic’s official tutorial describes the migration environment and revision-script structure in more detail. Alembic tutorial


For our example, alembic.ini contains:

sqlalchemy.url = sqlite:///./review.db

This must point to the same database used by the application:

DATABASE_URL = "sqlite:///./review.db"

In a real application, duplicating the database URL in two files is risky. One value might change while the other remains stale.

A production project will commonly load the URL from shared settings or an environment variable. For now, using the same explicit value keeps the connection visible.

Alembic needs two things:

  1. A connection to the existing database
  2. SQLAlchemy metadata describing the desired database

The second part is configured in alembic/env.py.

A simplified section looks like this:

from app.database import Base
from app.models.documents import Document
target_metadata = Base.metadata

The import of Document may appear unused:

from app.models.documents import Document

But it performs an important startup action.

When Python imports the Document class, SQLAlchemy registers its documents table with:

Base.metadata

Alembic can then inspect:

target_metadata

and see the complete desired table definition.

Without importing the application’s model modules, Base.metadata may be incomplete. Alembic cannot compare a model it does not know exists.


After changing the model, a developer creates a revision:

Terminal window
alembic revision --autogenerate -m "add review due date"

This command does not alter the database.

Instead, Alembic performs a comparison:

Current SQLite schema
versus
SQLAlchemy target_metadata

It notices:

Metadata contains documents.review_due_date
SQLite does not

Alembic then writes a candidate Python file in:

alembic/versions/

It might look like this:

"""add review due date"""
from alembic import op
import sqlalchemy as sa
revision = "7c2a9e91b414"
down_revision = "51f77b62d2d1"
branch_labels = None
depends_on = None
def upgrade() -> None:
op.add_column(
"documents",
sa.Column(
"review_due_date",
sa.Date(),
nullable=True,
),
)
def downgrade() -> None:
op.drop_column(
"documents",
"review_due_date",
)

This file is executable Python. It should be committed to source control alongside the application code.

Every migration has an identifier:

revision = "7c2a9e91b414"

It also points to its immediate predecessor:

down_revision = "51f77b62d2d1"

Together, migration files form a history:

initial schema
→ document uniqueness constraint
→ add review due date
→ next future change

Alembic uses this chain to determine which migrations a particular database still needs.

The forward operation is:

def upgrade() -> None:
op.add_column(...)

It describes how to move an older database into the new state:

Before
→ documents has no review_due_date
After
→ documents has nullable review_due_date

op is Alembic’s operations interface. It expresses schema changes without requiring us to manually assemble every dialect-specific SQL statement.

The reverse operation is:

def downgrade() -> None:
op.drop_column(...)

It describes how to move backward one revision.

But a downgrade is not automatically safe.

If deadlines have already been stored, dropping the column destroys those values. A syntactically valid reverse migration can still cause irreversible data loss.

For that reason, production recovery often relies on forward fixes and database backups—not casual downgrades.


Autogeneration is an assistant, not an authority

Section titled “Autogeneration is an assistant, not an authority”

The word:

autogenerate

can create a misleading impression.

Alembic does not fully understand the developer’s intention. It compares structural descriptions and proposes operations.

For an added column, that proposal is usually straightforward. For more complicated changes, it may need help.

Suppose we rename:

review_due_date

to:

due_date

Alembic may interpret the difference as:

Drop review_due_date
Add due_date

That could lose the values stored in the old column.

The intended operation might instead be:

op.alter_column(
"documents",
"review_due_date",
new_column_name="due_date",
)

Likewise, autogeneration cannot reliably invent:

  • Data backfills
  • Business-specific transformations
  • The meaning of a renamed table
  • Safe sequencing for a multi-release deployment
  • Whether dropping a column is acceptable

Alembic explicitly describes autogenerated revisions as candidate migrations that must be reviewed and, when necessary, corrected by a developer. Alembic autogeneration documentation

The production pattern is:

Generate
→ read the migration
→ confirm its assumptions
→ test it
→ commit it
→ apply it

The migration is applied with:

Terminal window
alembic upgrade head

Here:

head

means the newest revision in the current migration chain.

Alembic now performs real database work.

Conceptually, it:

  1. Connects to review.db.
  2. Reads the current revision from the alembic_version table.
  3. Finds the unapplied revisions between that revision and head.
  4. Calls their upgrade() functions in order.
  5. Updates the recorded database revision.

For our migration, SQLite receives SQL conceptually resembling:

ALTER TABLE documents
ADD COLUMN review_due_date DATE;

Alembic then records the new revision:

7c2a9e91b414

The exact SQL can vary by database dialect and Alembic operation, but the result is now:

SQLAlchemy metadata
→ review_due_date exists
SQLite table
→ review_due_date exists
Alembic history
→ records when and how it was introduced

The model and the physical database are aligned again.


It is worth drawing a firm boundary here.

During migration:

Alembic command
→ imports Python model metadata
→ connects to SQLite
→ executes schema-changing SQL
→ records the revision

During a later browser request:

Browser
→ middleware
→ authentication
→ Pydantic
→ route
→ service
→ SQLAlchemy Session
→ SQLite
→ response

The browser does not wait while FastAPI generates a migration.

The route does not call:

Terminal window
alembic upgrade head

The request-scoped SQLAlchemy session does not decide whether the schema needs upgrading.

In a conventional deployment, migrations run once as an explicit deployment step before the new application version begins serving normal traffic.

Running migrations during FastAPI startup is tempting, but it causes problems when several workers or application instances start simultaneously. Multiple processes may try to change the same schema at once.

A boring deployment sequence is clearer:

1. Back up important data
2. Apply reviewed migrations
3. Start or restart the application
4. Begin serving traffic

Our migration adds:

nullable=True

because the table already contains rows.

Imagine SQLite currently holds:

id title status
101 Northstar Studio Website Review not_started
202 Harbor Studio Review completed

Neither row has a deadline.

Adding a required column immediately would raise a question:

What value should existing rows receive?

A nullable column provides an honest answer:

We do not yet know.

After the migration, the rows conceptually become:

id title status review_due_date
101 Northstar Studio Website Review not_started NULL
202 Harbor Studio Review completed NULL

A future operation could populate those values.

If the business eventually requires every document to have a deadline, a safer multi-step rollout would be:

  1. Add the nullable column.
  2. Deploy code that writes it for new documents.
  3. Backfill old documents.
  4. Verify that no NULL values remain.
  5. Add a later migration making the column non-nullable.

Trying to perform every change at once can make deployments fragile.


The previous tutorial temporarily used:

Base.metadata.create_all(bind=engine)

That helped demonstrate the first table without introducing another tool simultaneously.

Once Alembic owns the schema lifecycle, routine application startup should no longer depend on create_all().

create_all() can create missing tables, but it does not provide a reliable history of changes to existing tables. It will not generally transform an older table merely because a mapped class acquired a new column.

Alembic gives us something more important than table creation:

A reviewable, ordered history of how schema version A becomes version B

That history can be applied consistently to:

  • A developer database
  • A test database
  • A staging database
  • A production database

Each environment may contain different data, but it follows the same schema evolution.


Existing databases and the initial revision

Section titled “Existing databases and the initial revision”

There is one practical transition issue.

Our SQLite database was initially created using create_all(), while Alembic’s migration history may begin from an empty database.

There are two common approaches.

For a disposable development database, the simplest option is usually:

Delete the disposable database
→ run the initial Alembic migration
→ rebuild test data

For a valuable existing database, the schema must be inspected carefully. If it already exactly matches a particular Alembic revision, Alembic can record that revision without rerunning its schema operations:

Terminal window
alembic stamp 51f77b62d2d1

stamp changes Alembic’s recorded revision. It does not create or verify all the expected tables itself.

Therefore:

stamp
≠
migrate

Stamping the wrong revision can make Alembic believe the database has changes it does not actually possess. It should only follow deliberate schema verification.


Today’s operation is friendly to SQLite:

Add one nullable column

More complicated changes may expose SQLite’s limited support for altering existing tables.

For example:

  • Changing a column type
  • Reordering constraints
  • Removing certain constraints
  • Performing complicated column alterations

For those cases, Alembic supports batch migrations. The broad strategy is:

Create a replacement table
→ copy data from the old table
→ drop the old table
→ rename the replacement

A migration might use:

def upgrade() -> None:
with op.batch_alter_table("documents") as batch_op:
batch_op.alter_column(
"priority",
existing_type=sa.Integer(),
nullable=False,
)

Alembic can use a “move and copy” workflow where SQLite cannot directly perform the requested alteration. Alembic batch-migration documentation

That machinery is unnecessary for today’s simple added column, but it explains why an SQLite migration may sometimes contain more operations than expected.


Now that storage can hold the deadline, the Pydantic models can expose it.

from datetime import date
from typing import Literal
from pydantic import BaseModel, ConfigDict, Field
class DocumentCreate(BaseModel):
model_config = ConfigDict(
extra="forbid",
str_strip_whitespace=True,
)
title: str = Field(
min_length=3,
max_length=200,
)
document_type: Literal[
"advertisement",
"website",
"email",
]
priority: int = Field(
default=3,
ge=1,
le=5,
)
review_due_date: date | None = None
class DocumentRead(BaseModel):
model_config = ConfigDict(
from_attributes=True,
)
id: int
firm_id: int
title: str
document_type: str
priority: int
status: str
review_due_date: date | None

Notice that the same general concept appears in two systems:

Pydantic
review_due_date: date | None
→ HTTP input and output contract
SQLAlchemy
review_due_date: Mapped[date | None]
→ Python-to-database mapping
Alembic migration
sa.Column(..., nullable=True)
→ Physical schema change

They agree about the field, but they are not duplicates in purpose.


After the migration and deployment, the browser can send:

POST /documents/ HTTP/1.1
Content-Type: application/json
Cookie: app_session=...
{
"title": "October Homepage Review",
"document_type": "website",
"priority": 4,
"review_due_date": "2026-10-01"
}

The browser sends the date as JSON text:

"2026-10-01"

Pydantic parses it into a Python object:

date(2026, 10, 1)

The route passes it to the service:

The user remains a dictionary here, just as in lesson 9. Add review_due_date: date | None = None to that service’s keyword-only parameters and include it when constructing Document. Keep the existing commit, rollback, and duplicate-title handling; this excerpt shows the newly added value.

document = create_document(
db=db,
firm_id=current_user["firm_id"],
created_by_user_id=current_user["id"],
title=payload.title,
document_type=payload.document_type,
priority=payload.priority,
review_due_date=payload.review_due_date,
)

The service includes it in the ORM object:

document = Document(
firm_id=firm_id,
created_by_user_id=created_by_user_id,
title=title,
normalized_title=title.casefold(),
document_type=document_type,
priority=priority,
status="not_started",
review_due_date=review_due_date,
)

Then:

db.add(document)
db.commit()
db.refresh(document)

SQLAlchemy adapts the Python date into a representation supported by SQLite.

SQLite stores the row. When it is queried later, SQLAlchemy reconstructs the mapped value as a Python date.

Finally, Pydantic serializes it back into JSON:

{
"id": 203,
"firm_id": 10,
"title": "October Homepage Review",
"document_type": "website",
"priority": 4,
"status": "not_started",
"review_due_date": "2026-10-01"
}

The browser receives text again. The Python date object existed only inside the server application.


There are now two related timelines.

  1. A developer adds review_due_date to the SQLAlchemy model.
  2. Python imports the model and registers the new column in Base.metadata.
  3. Alembic compares that metadata with the existing SQLite schema.
  4. Alembic generates a candidate revision.
  5. The migration is reviewed.
  6. alembic upgrade head connects to SQLite.
  7. SQLite adds the physical column.
  8. Alembic records the new revision.
  9. The updated FastAPI application is deployed.
  1. The browser sends JSON and its authentication cookie.
  2. Session middleware reconstructs the browser session.
  3. The router matches the document-creation endpoint.
  4. The authentication dependency retrieves the current user.
  5. The database dependency creates a SQLAlchemy Session.
  6. Pydantic converts the date string into a Python date.
  7. The route passes prepared values into the service.
  8. The service creates a Document ORM object.
  9. SQLAlchemy generates an INSERT.
  10. SQLite stores the deadline in the migrated column.
  11. The transaction commits.
  12. The service returns the persisted object.
  13. Pydantic produces the public response representation.
  14. FastAPI serializes the date back into ISO-formatted JSON.
  15. The browser receives and displays the created document.
  16. The request-scoped database session closes.

Alembic made the request possible, but Alembic did not participate in the request.


A SQLAlchemy model describes the schema the application expects.

An SQLite table is the schema the database actually has.

An Alembic revision records how to move safely from the old physical schema to the new one:

SQLAlchemy model
→ desired structure
Alembic migration
→ explicit transition
SQLite schema
→ actual persistent structure

Editing the model is not migrating the database. Generating a migration is not applying it. Autogeneration is not a substitute for review.

Once the migration has been applied, the ordinary browser-to-database-to-browser lifecycle can use the new field without the application and database disagreeing about what exists.