Safely Applying Versioned PostgreSQL Schema Migrations

Hello! Last lesson, you designed tables for notes, schedules, review history, embeddings, and reminder claims. That design is still only SQL on a page: changing a running PostgreSQL database requires a way to record which changes have already been applied and to apply the next change without rebuilding the database.

This lesson uses Alembic revisions containing hand-written PostgreSQL SQL. You will create a small, working slice of the proposed schema, change it in a second revision, and verify that an existing note survives. Allow about 45 minutes, including the video, reading, and local walkthrough. Use an empty scratch database, not a database containing notes you need to keep.

What a migration records

A migration is a versioned change, not merely a file of CREATE TABLE statements. Alembic keeps revision scripts in a versions/ directory. Each script has a revision identifier and a down_revision identifying its predecessor. In the database, Alembic records the revision reached in alembic_version. When you request an upgrade, it determines which scripts lie between the recorded revision and your target.

Watch the manual-migration portions of BugBytes’ Alembic Introduction - Migrations and Auto-Generating Revisions from SQLAlchemy Models. The presenter uses SQLAlchemy operations inside revisions; we will use SQL directly inside the same upgrade() and downgrade() structure. You can skip the database-container setup and the later ORM autogeneration segment.

For an authoritative reference beside the demonstration, read the selected parts of Alembic’s Tutorial. You do not need its sample application; focus on what the files and revision identifiers mean.

Alembic can manage revisions even if your application does not use SQLAlchemy ORM models. Your Python domain models remain independent of both FastAPI and PostgreSQL. Here, SQLAlchemy supplies Alembic’s database connection; your revision scripts specify the schema changes.

Prepare an isolated migration environment

You need a running PostgreSQL server, an empty scratch database, and the pgvector extension installed on that server when you later migrate the embedding table. This walkthrough creates only notes, so it does not need pgvector yet. Install the migration tools in your project environment:

python -m pip install alembic sqlalchemy "psycopg[binary]"
alembic init alembic

Keep the generated alembic.ini, alembic/env.py, and alembic/versions/ with your source code. Do not commit a database password. For this walkthrough, provide DATABASE_URL as an environment variable pointing to the scratch database, using a SQLAlchemy URL beginning postgresql+psycopg://. Check the database name and credentials before proceeding; the next lesson will develop a fuller approach to configuration and secrets.

In the generic generated alembic/env.py, leave target_metadata = None. Replace its run_migrations_online() function with the following, adding the imports if necessary:

import os

from sqlalchemy import create_engine, pool


def run_migrations_online() -> None:
    url = os.environ["DATABASE_URL"]
    if not url.startswith("postgresql+psycopg://"):
        raise RuntimeError("DATABASE_URL must use postgresql+psycopg")

    connectable = create_engine(url, poolclass=pool.NullPool)
    try:
        with connectable.connect() as connection:
            context.configure(
                connection=connection,
                target_metadata=None,
            )
            with context.begin_transaction():
                context.run_migrations()
    finally:
        connectable.dispose()

Retain the template’s context import and its if context.is_offline_mode(): ... else: ... dispatch. This configuration is for the online commands used below; it does not configure offline SQL generation. The generated alembic.ini URL is not used by this online function, so leave credentials out of it.

This setup also keeps schema creation out of FastAPI startup. Starting the application should not quietly create tables or attempt to repair an outdated schema.

Create the first revision: notes

The previous design had five tables, but a small slice makes the migration mechanics easier to verify. Our first revision creates notes with its stable ID, current path, content hash, and timestamps. It deliberately leaves out archived_at; the second revision will add that column. After both revisions, the resulting notes table matches the one designed previously.

Generate a manual revision:

alembic revision -m "create notes"

Open the new file in alembic/versions/. Keep the revision and down_revision values Alembic generated; the first revision’s down_revision should be None. Replace its empty functions with:

from alembic import op


def upgrade() -> None:
    op.execute("""
        CREATE TABLE notes (
            note_id       text PRIMARY KEY,
            source_path   text NOT NULL UNIQUE,
            title         text NOT NULL,
            body          text NOT NULL,
            tags          text[] NOT NULL DEFAULT '{}',
            content_hash  text NOT NULL,
            created_at    timestamptz NOT NULL DEFAULT now(),
            updated_at    timestamptz NOT NULL DEFAULT now(),

            CONSTRAINT notes_id_nonblank
                CHECK (btrim(note_id) <> ''),
            CONSTRAINT notes_path_nonblank
                CHECK (btrim(source_path) <> ''),
            CONSTRAINT notes_title_nonblank
                CHECK (btrim(title) <> ''),
            CONSTRAINT notes_hash_format
                CHECK (content_hash ~ '^[0-9a-f]{64}$')
        )
    """)


def downgrade() -> None:
    op.execute("DROP TABLE notes")

op.execute() lets the revision carry the PostgreSQL SQL you reviewed in the prior lesson. upgrade() specifies what to apply. downgrade() specifies a possible reverse operation, not a promise that reversing is safe: dropping a populated notes table destroys its data, and dependent tables would complicate it further. We will not run that downgrade against our test note.

Before applying anything, inspect the file: is it creating notes, rather than unexpectedly dropping or renaming another table? Then run:

alembic history
alembic upgrade head
alembic current

history shows the available revision chain; upgrade head applies it; current reports where this particular database stands. In a psql session connected to the same scratch database, verify the target and inspect the result:

SELECT current_database(), current_user;
SELECT version_num FROM alembic_version;

You can also use \d notes in psql to inspect the columns and constraints. If the first upgrade fails on an empty database, investigate the error and the revision state; do not use alembic stamp to mark an unapplied schema as though it had been created.

Modify the schema without losing an existing note

Before making the next change, put a record in the scratch database. This makes preservation observable rather than assumed:

INSERT INTO notes (note_id, source_path, title, body, content_hash)
VALUES (
    'note-1',
    'reviews/first.md',
    'First note',
    'A small review note.',
    repeat('a', 64)
);

Now generate another revision:

alembic revision -m "add note archival timestamp"

Check that the new file’s down_revision is the first file’s revision. Keep those generated identifiers and use this implementation:

from alembic import op


def upgrade() -> None:
    op.execute("SET LOCAL lock_timeout = '5s'")
    op.execute("""
        ALTER TABLE notes
        ADD COLUMN archived_at timestamptz
    """)


def downgrade() -> None:
    op.execute("SET LOCAL lock_timeout = '5s'")
    op.execute("""
        ALTER TABLE notes
        DROP COLUMN archived_at
    """)

The new column is nullable, so the existing note can receive NULL for archived_at; no invented archival date or data backfill is needed. SET LOCAL lock_timeout makes this local migration fail rather than wait indefinitely for a conflicting table lock. It is not a guarantee of zero disruption: even a quick ALTER TABLE needs a lock, so schedule real migrations with their workload in mind.

Check the revision file, then apply and inspect it:

alembic upgrade head
alembic current

SELECT note_id, title, archived_at
FROM notes
WHERE note_id = 'note-1';

The expected result is the original note with a null archived_at. Compare alembic current with alembic history: the database should now be at the second revision. Running alembic upgrade head again should have no pending revision to apply; the revision record, rather than repeated ALTER TABLE commands, makes that possible.

This small chain demonstrates two distinct cases: a fresh database receives the first revision before the second; a database already at the first revision receives only the second. The remaining tables from your design can be added in reviewed revisions, creating referenced tables before their foreign-key dependents. In particular, a future embedding-table migration must enable vector only after pgvector is available on the target PostgreSQL server, and its vector dimension must match the selected embedding model.

Review is a safety step, not an optional flourish

The supplied workflow diagram shows a common alternative: defining SQLAlchemy models and asking Alembic to propose a revision. Its review decision is the part worth retaining regardless of how the revision was written.

Autogeneration compares a live database with SQLAlchemy table metadata. It does not compare the database with your independent Python domain classes, and it is not required to use Alembic. If you later introduce SQLAlchemy metadata, its generated file is a candidate, not approval to run it. Read the relevant parts of Alembic’s Auto Generating Migrations documentation with that distinction in mind.

On a database with valuable data, make the safety check broader than “the command succeeded”:

• Before: confirm the target database and current revision; review the proposed SQL and its effect on existing rows; take a usable backup and rehearse the change on a test copy.
• During: apply the reviewed revision as a separate deployment step, not on every application startup. PostgreSQL supports transactional DDL for the ordinary statements in this walkthrough, and the online environment runs them within Alembic’s transaction; do not assume every possible migration operation has the same rollback behavior.
• After: verify both the revision and the actual schema, then check representative existing data and application queries. A matching alembic_version alone cannot prove that the resulting schema behaves correctly.

Treat downgrade() with equal scrutiny. Dropping archived_at would discard any archival timestamps recorded after the upgrade. For a harmful change, a corrected forward migration plus a backup-and-restore plan may be safer than running a destructive downgrade.

Conclusion

You now have a reproducible two-revision chain: one revision creates notes, and the next adds archived_at while preserving an existing row. The essential habit is to review the change, apply it to the intended database, and verify both revision state and data. Alembic supplies version tracking; it does not replace judgment about locks, existing rows, or destructive reversals.

Next, you will load and validate environment-specific configuration and secrets for the local FastAPI application, replacing the deliberately minimal database-URL setup used here.