Python FastAPI Tutorial (Part 15): PostgreSQL and Alembic - Database Migrations for Production

The tutorial’s core shift is replacing SQLite plus “create tables on startup” with PostgreSQL plus Alembic-style migrations (via SQLAlchemy’s migration tool, Olympic). That change matters because production systems need safe, incremental schema evolution—without deleting and recreating databases or relying on startup logic that can’t detect model changes.

In development, SQLite is convenient: it’s just a local file, and the app previously used SQLAlchemy’s create_all inside the FastAPI lifespan to recreate tables when the server starts. That workflow breaks down in production. SQLite struggles with concurrent writes, create_all is not a migration system (it uses “CREATE TABLE IF NOT EXISTS,” so new columns never get added), and deleting/recreating a production database is not an option. The fix is to move to PostgreSQL—an industry-standard choice for web apps because it handles concurrent connections well—and to manage schema changes through migrations.

The setup begins by wiping the old blog.db SQLite file and installing PostgreSQL locally (Homebrew on macOS, with Docker and other OS options mentioned). A dedicated database user and password are created, then a blog database is created with that user as owner. On the Python side, the PostgreSQL driver is installed using psychopg (v3) with the binary extra, which supports both synchronous and asynchronous operations and fits SQLAlchemy’s async engine approach.

Next, configuration moves database credentials out of code. The database URL is added to Pydantic settings (loaded from an .env file) and the SQLAlchemy engine in database.py is updated to use settings.database_url, removing SQLite-specific connection arguments. With PostgreSQL running, the app no longer creates tables at startup; instead, schema management is delegated to migrations.

Olympic is installed and initialized for async SQLAlchemy. Its configuration is adjusted so it reads the database URL from settings (avoiding hardcoded credentials) and uses SQLAlchemy’s base metadata as the source of truth for autogeneration. The first migration is generated with revision --autogenerate, then applied with upgrade head. A detour highlights a common pitfall: if tables already exist (because create_all previously ran), autogenerate may produce an empty migration. Dropping the public schema and regenerating ensures the initial migration contains the expected CREATE TABLE, indexes, and foreign keys.

After the baseline schema is applied, the tutorial demonstrates incremental migration. A new likes column is added to the Post model with both a Python default and a server_default of 0, ensuring existing rows won’t fail when the new non-null column is introduced. Autogenerate detects the added column, produces a migration that adds/drops only that column, and upgrade head applies it cleanly. Verification in PostgreSQL confirms the column exists and defaults to zero.

Finally, SQLite-specific workarounds are removed—most notably a timezone fix that was needed because SQLite stored datetimes without timezone information. PostgreSQL’s timestamp with time zone support makes that workaround unnecessary. The workflow going forward is clear: generate migrations locally after model changes, review them, commit them, and apply them on production with upgrade head. For production reliability, the tutorial also flags an Olympic setting (compare_type) to catch column type length changes that autogenerate might otherwise miss.