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→ browserToday’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 requestsDuring 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.
Today’s change
Section titled “Today’s change”Our documents table currently contains:
idfirm_idcreated_by_user_idtitlenormalized_titledocument_typeprioritystatusThe application now needs an optional review deadline:
review_due_dateA document might have:
September Website ReviewDue: October 1, 2026Older 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.
Two realities must change
Section titled “Two realities must change”We first update the SQLAlchemy model:
from datetime import date
from sqlalchemy import Date, String, UniqueConstraintfrom 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 | NoneThat means a Document object may contain either:
date(2026, 10, 1)or:
NoneThe 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 yetIf the new application code runs a query immediately, SQLAlchemy might generate:
SELECT documents.id, documents.title, documents.review_due_dateFROM documents;SQLite would respond with an error resembling:
no such column: documents.review_due_dateThe 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.
The Alembic structure
Section titled “The Alembic structure”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:
alembic init alembicThat 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
Connecting Alembic to SQLite
Section titled “Connecting Alembic to SQLite”For our example, alembic.ini contains:
sqlalchemy.url = sqlite:///./review.dbThis 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:
- A connection to the existing database
- 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 Basefrom app.models.documents import Document
target_metadata = Base.metadataThe import of Document may appear unused:
from app.models.documents import DocumentBut it performs an important startup action.
When Python imports the Document class, SQLAlchemy registers its documents table with:
Base.metadataAlembic can then inspect:
target_metadataand 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.
Generating a candidate migration
Section titled “Generating a candidate migration”After changing the model, a developer creates a revision:
alembic revision --autogenerate -m "add review due date"This command does not alter the database.
Instead, Alembic performs a comparison:
Current SQLite schemaversusSQLAlchemy target_metadataIt notices:
Metadata contains documents.review_due_dateSQLite does notAlembic then writes a candidate Python file in:
alembic/versions/It might look like this:
"""add review due date"""
from alembic import opimport sqlalchemy as sa
revision = "7c2a9e91b414"down_revision = "51f77b62d2d1"branch_labels = Nonedepends_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.
The revision identifiers
Section titled “The revision identifiers”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 changeAlembic uses this chain to determine which migrations a particular database still needs.
upgrade()
Section titled “upgrade()”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_dateop is Alembic’s operations interface. It expresses schema changes without requiring us to manually assemble every dialect-specific SQL statement.
downgrade()
Section titled “downgrade()”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:
autogeneratecan 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_dateto:
due_dateAlembic may interpret the difference as:
Drop review_due_dateAdd due_dateThat 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 itApplying the migration
Section titled “Applying the migration”The migration is applied with:
alembic upgrade headHere:
headmeans the newest revision in the current migration chain.
Alembic now performs real database work.
Conceptually, it:
- Connects to
review.db. - Reads the current revision from the
alembic_versiontable. - Finds the unapplied revisions between that revision and
head. - Calls their
upgrade()functions in order. - Updates the recorded database revision.
For our migration, SQLite receives SQL conceptually resembling:
ALTER TABLE documentsADD COLUMN review_due_date DATE;Alembic then records the new revision:
7c2a9e91b414The 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 introducedThe model and the physical database are aligned again.
Alembic is not part of the HTTP request
Section titled “Alembic is not part of the HTTP request”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 revisionDuring a later browser request:
Browser→ middleware→ authentication→ Pydantic→ route→ service→ SQLAlchemy Session→ SQLite→ responseThe browser does not wait while FastAPI generates a migration.
The route does not call:
alembic upgrade headThe 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 data2. Apply reviewed migrations3. Start or restart the application4. Begin serving trafficWhy the new column is nullable
Section titled “Why the new column is nullable”Our migration adds:
nullable=Truebecause 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:
- Add the nullable column.
- Deploy code that writes it for new documents.
- Backfill old documents.
- Verify that no
NULLvalues remain. - Add a later migration making the column non-nullable.
Trying to perform every change at once can make deployments fragile.
Removing create_all()
Section titled “Removing create_all()”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 BThat 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 dataFor 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:
alembic stamp 51f77b62d2d1stamp changes Alembic’s recorded revision. It does not create or verify all the expected tables itself.
Therefore:
stamp≠migrateStamping the wrong revision can make Alembic believe the database has changes it does not actually possess. It should only follow deliberate schema verification.
SQLite and complex alterations
Section titled “SQLite and complex alterations”Today’s operation is friendly to SQLite:
Add one nullable columnMore 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 replacementA 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.
Updating the HTTP boundary
Section titled “Updating the HTTP boundary”Now that storage can hold the deadline, the Pydantic models can expose it.
from datetime import datefrom 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 | NoneNotice that the same general concept appears in two systems:
Pydanticreview_due_date: date | None→ HTTP input and output contract
SQLAlchemyreview_due_date: Mapped[date | None]→ Python-to-database mapping
Alembic migrationsa.Column(..., nullable=True)→ Physical schema changeThey agree about the field, but they are not duplicates in purpose.
The post-migration request
Section titled “The post-migration request”After the migration and deployment, the browser can send:
POST /documents/ HTTP/1.1Content-Type: application/jsonCookie: 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.
The complete lifecycle
Section titled “The complete lifecycle”There are now two related timelines.
Deployment-time schema lifecycle
Section titled “Deployment-time schema lifecycle”- A developer adds
review_due_dateto the SQLAlchemy model. - Python imports the model and registers the new column in
Base.metadata. - Alembic compares that metadata with the existing SQLite schema.
- Alembic generates a candidate revision.
- The migration is reviewed.
alembic upgrade headconnects to SQLite.- SQLite adds the physical column.
- Alembic records the new revision.
- The updated FastAPI application is deployed.
Request-time data lifecycle
Section titled “Request-time data lifecycle”- The browser sends JSON and its authentication cookie.
- Session middleware reconstructs the browser session.
- The router matches the document-creation endpoint.
- The authentication dependency retrieves the current user.
- The database dependency creates a SQLAlchemy
Session. - Pydantic converts the date string into a Python
date. - The route passes prepared values into the service.
- The service creates a
DocumentORM object. - SQLAlchemy generates an
INSERT. - SQLite stores the deadline in the migrated column.
- The transaction commits.
- The service returns the persisted object.
- Pydantic produces the public response representation.
- FastAPI serializes the date back into ISO-formatted JSON.
- The browser receives and displays the created document.
- The request-scoped database session closes.
Alembic made the request possible, but Alembic did not participate in the request.
The central idea
Section titled “The central idea”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 structureEditing 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.