This tutorial makes the database setup production-ready by transitioning from SQLite to PostgreSQL and introducing Alembic for database migrations. Throughout the series, development conveniences have simplified learning: SQLite as the database (just a file—blog.db—with no setup required) and create_all() in the app lifespan to recreate tables on startup.

These development conveniences become production problems. SQLite cannot handle concurrent writes well. Production databases obviously cannot be deleted and recreated. create_all() is not a migration system—if a new column is added to a model, create_all() sees that the table already exists and does nothing. The new column would not be added.

This tutorial transitions to PostgreSQL, introduces Alembic for database migrations to update database structure without deleting existing data, and removes create_all() from the lifespan in favor of proper schema management.

Why PostgreSQL?

PostgreSQL is the industry standard for web applications. Most production FastAPI applications use PostgreSQL for good reasons: it handles concurrent connections well, it’s the likely production database anyway, and running it locally catches database-specific issues during development instead of after deployment.

Installing PostgreSQL

Before installing PostgreSQL, delete the blog.db file if it exists from previous tutorials. The application is fully transitioning to PostgreSQL, so SQLite is no longer needed. If data needs to be preserved, it must be migrated (outside this tutorial’s scope). Starting fresh with a new PostgreSQL installation is simplest.

Installation Methods

Two primary methods exist for running PostgreSQL locally.

Direct installation on the machine:
- macOS: Use Homebrew
- Linux: Use apt
- Windows: Download the installer
Docker:

docker run -d -p 5432:5432 -e POSTGRES_PASSWORD=secret postgres

This runs PostgreSQL in a container on port 5432 with the password set to “secret”. Docker keeps the host OS clean, and many developers prefer this approach. However, Docker can be intimidating for those unfamiliar with it. This tutorial uses a local installation to avoid introducing Docker to new users.

Installing via Homebrew (macOS)

Install PostgreSQL 18 using Homebrew:

brew install postgresql@18

Note: Homebrew requires specifying the version number. There is no unversioned PostgreSQL formula anymore—@18 (or whichever version desired) must be specified.

Start the PostgreSQL service:

brew services start postgresql@18

PostgreSQL is now running locally.

However, you can also use to open a PostgreSQL interactive shell inside a running Docker container:

docker exec -it <container_name_ir_id> psql -U postgres

Creating a Database User

Create a database user with a password. On macOS with Homebrew, the local user is trusted by default, making commands straightforward. Linux and Windows require slightly different commands (displayed on-screen in the video).

Create the user:

psql postgres -c "CREATE USER blog_user WITH PASSWORD 'blog_pass';"

Or in Docker container:

CREATE USER blog_user WITH PASSWORD 'blog_pass';

In production, use a strong password. “blog_pass” is simple for tutorial purposes. Creating a user with a password (instead of passwordless local development) is more realistic—production always requires database credentials. Setting this up now establishes good practices.

Create the database and set the owner:

createdb blog -O blog_user

Or in Docker container:

CREATE DATABASE blog OWNER blog_user;

This creates a database named blog with blog_user as the owner.

Verifying the Setup

Connect to the database as the new user:

psql blog -U blog_user

Or in Docker container:

\c blog blog_user

Here’s the full comand sequence in Docker:

The prompt changes to show connection to the blog database. Check for tables:

\dt

The output shows “Did not find any relations”—expected because no migrations have run yet. The database is completely empty.

Exit psql:

\q

PostgreSQL is now running locally and ready for the application.

Installing the Python Driver

A Python driver is needed to connect to PostgreSQL. Install psycopg, the modern PostgreSQL driver for Python:

uv add "psycopg[binary]"

psycopg (version 3, branded simply as “psycopg”) is the modern PostgreSQL driver supporting both synchronous and asynchronous operations in one package. This is what the official FastAPI full-stack template uses. If you’ve used psycopg2 before (from Flask or Django tutorials), this is the natural upgrade with a familiar API.

The psycopg-binary extra provides precompiled binaries, eliminating the need for local compilation and making installation much easier.

SQLAlchemy automatically uses async mode with create_async_engine(), so this replaces aiosqlite for async operations. The application remains fully async, consistent with earlier tutorials in the series.

Configuring the Database URL

Currently, the database URL is hardcoded in database.py:

DATABASE_URL = "sqlite+aiosqlite:///./blog.db"

With PostgreSQL, the connection string contains username and password—this must not be hardcoded. Pydantic Settings, configured earlier in the series, is the solution.

Open config.py and add a database URL field to the settings class:

class Settings(BaseSettings):
    model_config = SettingsConfigDict(
        env_file=".env",
        env_file_encoding="utf-8"
    )
    
    database_url: str  # New field - no default means it's requiredenv
    # ... other settings ...

No default is provided, meaning the value must be set in the system environment or .env file.

Adding the Database URL to .env

Open .env and add the PostgreSQL connection string:

# Database URL format: postgresql+psycopg://user:password@host/database
DATABASE_URL=postgresql+psycopg://blog_user:blog_pass@localhost/blog

The format is postgresql+psycopg:// followed by user:password@host/database. Pydantic Settings automatically reads DATABASE_URL from .env and populates the setting.

Critical reminder: .env must be in .gitignore. This file contains secrets like API keys and passwords. Never commit it to version control.

Updating database.py

Open database.py and import settings:

from config import settings

Update the engine creation to use the setting:

engine = create_async_engine(settings.database_url)

Remove the hardcoded DATABASE_URL variable and the SQLite-specific connect_args (the check_same_thread parameter is SQLite-specific and unnecessary for PostgreSQL).

The file now cleanly uses settings for the database URL without SQLite-specific configuration.

The Problem with create_all()

The application is now connected to PostgreSQL, but the database is empty. Tables must be created. Until now, create_all() in the app’s lifespan has created tables on startup.

Understanding what create_all() actually does is critical. It looks at models and runs CREATE TABLE IF NOT EXISTS for each. The key phrase is “IF NOT EXISTS.” If the table already exists, it does nothing—even if the table differs from the model. create_all() is not a migration system. It’s only a creation system.

Consider an example: tables already exist, but a likes field is added to the Post model to track post popularity. Running the app with this change would result in create_all() seeing that the posts table exists, doing nothing, and the new likes column never being added to the database.

The only way to pick up schema changes with create_all() is to delete the database and start over—acceptable during development but obviously not realistic for production.

Understanding Database Migrations

Migrations solve this problem. Migrations track schema changes over time, apply incremental changes safely, and act as version control for the database schema.

Think of each migration file like a Git commit for the database. It records exactly what changed. You can move forward or backward through history. If something goes wrong, you can roll back.

Later in this tutorial, a likes column will actually be added to the database using migrations to demonstrate how this works.

Removing create_all()

Open main.py and locate the lifespan function:

@asynccontextmanager
async def lifespan(_app: FastAPI):
    # Startup: create tables
    async with engine.begin() as conn:
        await conn.run_sync(Base.metadata.create_all)  # REMOVE THIS
    yield
    # Shutdown: dispose engine
    await engine.dispose()

Remove the create_all block but keep engine disposal (proper cleanup):

@asynccontextmanager
async def lifespan(_app: FastAPI):
    yield
    # Shutdown: dispose engine
    await engine.dispose()

Update imports—Base is no longer needed:

from database import engine, get_db  # Remove Base

The schema will now be managed by migrations, not app startup.

Installing and Initializing Alembic

Alembic is a database migration tool specifically for SQLAlchemy. It was created by Mike Bayer, the same person who created SQLAlchemy, so they integrate seamlessly.

Alembic tracks migrations in a versions folder. Each migration is a Python file containing two functions: upgrade() to apply changes and downgrade() to roll back.

Install Alembic:

uv add alembic

Initialize Alembic in the project:

uv run alembic init -t async alembic

The -t async flag tells Alembic to use the async template (required for async applications). The final alembic argument is the directory name where migration files will live.

Understanding the Created Structure

Alembic creates several files and directories:

alembic/
├── versions/        # Migration files go here
├── env.py          # Configuration for running migrations
├── README
└── script.py.mako  # Template for generating new migrations
alembic.ini         # Main Alembic settings file (project root)

Don’t be intimidated. After a couple of small configuration changes, these files rarely need attention. Alembic handles everything automatically.

Configuring Alembic

Two files need updating: alembic.ini for the database connection and alembic/env.py for metadata and async setup.

Configuring alembic.ini

Open alembic.ini and find the sqlalchemy.url line. It contains a placeholder:

sqlalchemy.url = driver://user:pass@localhost/dbname

Leave this empty. The URL will be set programmatically in env.py using settings. This is better because hardcoded credentials in committed files are a security risk:

sqlalchemy.url =

Configuring alembic/env.py

Open alembic/env.py. This file contains boilerplate from the async template, but only a few changes are needed.

Add imports near the top (these will auto-sort in most IDEs):

import models  # noqa: F401
from config import settings
from database import Base

models is imported but appears unused. The import ensures all model classes register with SQLAlchemy’s Base metadata. Without importing models, Alembic won’t see tables when auto-generating migrations. The # noqa: F401 comment tells linters to ignore the “unused import” warning.

Set the database URL. Find the line config = context.config and add immediately after:

config.set_main_option("sqlalchemy.url", settings.database_url)

Set the target metadata. Find target_metadata = None and change it to:

target_metadata = Base.metadata

This tells Alembic to use Base’s metadata as the source of truth for what the database schema should look like. When Alembic runs auto-generate, it compares this metadata against the actual database to determine what changed.

Only two changes were made: setting sqlalchemy.url and setting target_metadata. Alembic is now configured—it knows about the database connection and models.

Generating the Initial Migration

The database has no tables yet (fresh PostgreSQL installation). Generate the first migration to create all tables for existing models.

The workflow: define truth in models, then let Alembic generate migrations by comparing models to current database state.

Run the migration generation command:

uv run alembic revision --autogenerate -m "initial schema"

alembic revision creates a new migration. The --autogenerate flag tells Alembic to compare models against the database and automatically generate the migration file. This only creates the file—it doesn’t apply anything to the database. That happens in a separate step.

Auto-generate is extremely convenient during development because migration files don’t need to be written manually.

Think of this as the first “commit” of the database schema.

The output reports detected tables:

A migration file is created in alembic/versions.

Examining the Migration File

Open the generated file in alembic/versions/. The filename contains a hash followed by the message (e.g., abc123def456_initial_schema.py).

"""initial schema  
  
Revision ID: f09b20834e16  
Revises: Create Date: 2026-04-27 23:40:37.135439  
  
"""  
from typing import Sequence, Union  
  
from alembic import op  
import sqlalchemy as sa  
  
  
# revision identifiers, used by Alembic.  
revision: str = 'f09b20834e16'  
down_revision: Union[str, Sequence[str], None] = None  
branch_labels: Union[str, Sequence[str], None] = None  
depends_on: Union[str, Sequence[str], None] = None  
  
  
def upgrade() -> None:  
    """Upgrade schema."""  
    # ### commands auto generated by Alembic - please adjust! ###  
    op.create_table('users',  
    sa.Column('id', sa.Integer(), nullable=False),  
    sa.Column('username', sa.String(length=50), nullable=False),  
    sa.Column('email', sa.String(length=120), nullable=False),  
    sa.Column('password_hash', sa.String(length=200), nullable=False),  
    sa.Column('image_file', sa.String(length=200), nullable=True),  
    sa.PrimaryKeyConstraint('id'),  
    sa.UniqueConstraint('email'),  
    sa.UniqueConstraint('username')  
    )  
    op.create_index(op.f('ix_users_id'), 'users', ['id'], unique=False)  
    op.create_table('password_reset_tokens',  
    sa.Column('id', sa.Integer(), nullable=False),  
    sa.Column('user_id', sa.Integer(), nullable=False),  
    sa.Column('token_hash', sa.String(length=64), nullable=False),  
    sa.Column('expires_at', sa.DateTime(timezone=True), nullable=False),  
    sa.Column('created_at', sa.DateTime(timezone=True), nullable=False),  
    sa.ForeignKeyConstraint(['user_id'], ['users.id'], ),  
    sa.PrimaryKeyConstraint('id'),  
    sa.UniqueConstraint('token_hash')  
    )  
    op.create_index(op.f('ix_password_reset_tokens_id'), 'password_reset_tokens', ['id'], unique=False)  
    op.create_table('posts',  
    sa.Column('id', sa.Integer(), nullable=False),  
    sa.Column('title', sa.String(length=100), nullable=False),  
    sa.Column('content', sa.Text(), nullable=False),  
    sa.Column('user_id', sa.Integer(), nullable=False),  
    sa.Column('date_posted', sa.DateTime(timezone=True), nullable=False),  
    sa.ForeignKeyConstraint(['user_id'], ['users.id'], ),  
    sa.PrimaryKeyConstraint('id')  
    )  
    op.create_index(op.f('ix_posts_id'), 'posts', ['id'], unique=False)  
    op.create_index(op.f('ix_posts_user_id'), 'posts', ['user_id'], unique=False)  
    # ### end Alembic commands ###  
  
  
def downgrade() -> None:  
    """Downgrade schema."""  
    # ### commands auto generated by Alembic - please adjust! ###  
    op.drop_index(op.f('ix_posts_user_id'), table_name='posts')  
    op.drop_index(op.f('ix_posts_id'), table_name='posts')  
    op.drop_table('posts')  
    op.drop_index(op.f('ix_password_reset_tokens_id'), table_name='password_reset_tokens')  
    op.drop_table('password_reset_tokens')  
    op.drop_index(op.f('ix_users_id'), table_name='users')  
    op.drop_table('users')  
    # ### end Alembic commands ###

The revision is a unique identifier for this migration. down_revision is None because this is the first migration—nothing exists to roll back to.

The upgrade() function contains all create_table statements for the models: creating the users table with all defined columns, creating indexes, and setting up foreign key relationships.

The downgrade() function does the opposite—it drops all tables in reverse order.

It’s good practice to glance at generated migrations before applying them. Auto-generate is helpful but not perfect. It might occasionally miss something or generate unexpected statements.

Applying the Migration

Apply the migration to run the CREATE TABLE statements against PostgreSQL:

uv run alembic upgrade head

head means “apply all migrations up to the latest version.”

The output shows:

Tables should now exist. Verify by connecting to the database:

\c blog blog_user

List tables:

\dt

Four tables appear:

The alembic_version table is how Alembic tracks which migrations have been applied.

Examine the posts table structure:

\d posts

All columns appear with correct types:

Note timestamp with time zone for date_posted. This was problematic in SQLite. PostgreSQL properly maintains timezone information.

Exit PostgreSQL:

\q

Checking Migration State

Alembic provides commands to check migration status:

uv run alembic current

This shows the current revision with (head) indicating it’s the latest version:

Run:

uv run alembic history

This shows the complete migration history:

Adding a Column via Migration

Migrations handle schema changes properly. Earlier, the example was given: adding a likes field to the Post model wouldn’t work with create_all(). Traditionally, the solution was deleting the database and recreating it. With Alembic, the field is added and the database is updated properly.

Open models.py and add a likes field to the Post model:

class Post(Base):
    __tablename__ = "posts"
    
    ...
    likes: Mapped[int] = mapped_column(Integer, nullable=False, default=0, server_default="0")
    ...

Two defaults exist: default=0 and server_default="0". The default parameter is the Python-side default—when SQLAlchemy creates a new Post object, it sets likes to zero. The server_default is the database-side default.

server_default is critical when adding a non-null column to a table with existing data. Without it, the database would try to set NULL for existing rows, failing because the column doesn’t allow null values.

When adding a new column to an existing table, always consider what happens to existing rows. Declaring a server default in the model ensures Alembic includes it in the generated migration, which is cleaner than manually editing migration files afterward.

Generate a migration for this change:

uv run alembic revision --autogenerate -m "add likes to posts"

The output detects the change:

INFO  [alembic.autogenerate.compare] Detected added column 'posts.likes'

Examine the generated migration in alembic/versions/:

"""add likes to posts  
  
Revision ID: 6ded30bafc10  
Revises: f09b20834e16  
Create Date: 2026-04-27 23:52:11.401960  
  
"""  
from typing import Sequence, Union  
  
from alembic import op  
import sqlalchemy as sa  
  
  
# revision identifiers, used by Alembic.  
revision: str = '6ded30bafc10'  
down_revision: Union[str, Sequence[str], None] = 'f09b20834e16'  
branch_labels: Union[str, Sequence[str], None] = None  
depends_on: Union[str, Sequence[str], None] = None  
  
  
def upgrade() -> None:  
    """Upgrade schema."""  
    # ### commands auto generated by Alembic - please adjust! ###  
    op.add_column('posts', sa.Column('likes', sa.Integer(), server_default='0', nullable=False))  
    # ### end Alembic commands ###  
  
  
def downgrade() -> None:  
    """Downgrade schema."""  
    # ### commands auto generated by Alembic - please adjust! ###  
    op.drop_column('posts', 'likes')  
    # ### end Alembic commands ###

The revision ID is new. The down_revision points to the previous migration (abc123def456).

upgrade() adds the likes column with server_default='0' (specified in the model). downgrade() drops the column.

Notice the migration only touches the likes column—nothing else changes. Migrations are incremental, tracking step-by-step differences.

The migration file exists but hasn’t been applied yet. Apply it:

uv run alembic upgrade head

Output confirms:

Verify the column exists:

docker exec -it b49de psql -U postgres
\c blog blog_user
\d posts

The likes column appears with a default of zero:

Removing SQLite Workarounds

In the previous tutorial (password reset), a workaround was added for SQLite. SQLite stores datetimes without timezone information, requiring manual timezone addition when comparing dates.

Open routers/users.py and locate the reset_password endpoint (around line 192):

# SQLite quirk - remove this workaround
if reset_token.expires_at.replace(tzinfo=UTC) < datetime.now(UTC):

PostgreSQL handles timezone-aware datetimes natively. It has a TIMESTAMP WITH TIME ZONE type that properly stores timezones. The workaround is unnecessary.

Remove .replace(tzinfo=UTC):

if reset_token.expires_at < datetime.now(UTC):

Much cleaner. This is one benefit of using a proper production database—database-specific quirks don’t require workarounds.

The Migration Workflow Going Forward

The migration workflow for future development:

Fresh Setup

Create the database: createdb blog -O blog_user
Apply migrations: alembic upgrade head

This brings the database up to date with all migrations.

Making Schema Changes

Modify models in models.py (models define truth)
Generate migration: uv run alembic revision --autogenerate -m "descriptive message"
Review the generated migration file (verify Alembic generated what you expected)
Apply migration: uv run alembic upgrade head

Rolling Back Mistakes

If a mistake is made:

Roll back: uv run alembic downgrade -1 (goes back one migration)
Fix the migration or model
Reapply: uv run alembic upgrade head

Checking Status

Current position: uv run alembic current
Migration history: uv run alembic history

Example history output:

<base> -> abc123def456, initial schema
abc123def456 -> def456ghi789 (head), add likes to posts

This shows progression from base to the first revision (initial schema), then to the current head (add likes to posts).

Testing the Application

PostgreSQL is configured and Alembic has created tables. Verify everything works.

Start the development server. Navigate to the application in a browser. The page loads without errors, but no data exists (fresh database).

Populate the database using the populate_db.py script (updated for each tutorial in the series—all code available for download):

uv run populate_db.py

The script creates users and posts. Restart the server and reload the page. Sample posts appear and pagination works. Everything functions correctly with PostgreSQL.

Verify directly in the database:

docker exec -it b49de psql -U postgres
\c blog blog_user

Query posts:

SELECT id, title, likes FROM posts;

Results show ID, title, and likes for all posts. All posts have zero likes initially (the default):

Exit PostgreSQL:

\q

The database is now properly managed with PostgreSQL and Alembic.

Summary

This tutorial accomplished several critical production-readiness tasks:

Swapped SQLite for PostgreSQL
Moved the database URL into environment settings
Replaced create_all() in app startup with Alembic migrations (lifespan now only handles proper engine disposal)
Cleaned up SQLite-specific workarounds

Production Considerations

Compare Type Setting

By default, Alembic’s auto-generate doesn’t detect column type changes. Changing String(50) to String(100) wouldn’t be detected. Enable this in alembic/env.py by adding compare_type=True in the context.configure() call:

context.configure(
    connection=connection,
    target_metadata=target_metadata,
    compare_type=True,  # Add this
)

Many additional configuration options exist as needed.

Production Deployment Workflow

Migration files are not created on production servers. The workflow:

Generate migration files locally during development
Commit migration files to version control (like any other code)
On production, only run uv run alembic upgrade head to apply them

This ensures migrations are reviewed before production deployment. This workflow will be demonstrated when deploying the application later in the series.

Next Steps

The database setup is now production-ready—a major step in transitioning from development to production that many tutorials skip.

Other production improvements remain. Currently, application images are stored on the local filesystem. Few production applications do this anymore.

The next tutorial makes file uploads production-ready by moving from local file storage to AWS S3. Following that, testing and deployment to different platforms will be covered, showing how to get everything learned into the real world.

Quartz 4

Explorer

15. PostgreSQL and Alembic - Database Migrations for Production

Why PostgreSQL?

Installing PostgreSQL

Installation Methods

Installing via Homebrew (macOS)

Creating a Database User

Verifying the Setup

Installing the Python Driver

Configuring the Database URL

Adding the Database URL to .env

Updating database.py

The Problem with create_all()

Understanding Database Migrations

Removing create_all()

Installing and Initializing Alembic

Understanding the Created Structure

Configuring Alembic

Configuring alembic.ini

Configuring alembic/env.py

Generating the Initial Migration

Examining the Migration File

Applying the Migration

Checking Migration State

Adding a Column via Migration

Removing SQLite Workarounds

The Migration Workflow Going Forward

Fresh Setup

Making Schema Changes

Rolling Back Mistakes

Checking Status

Testing the Application

Summary

Production Considerations

Compare Type Setting

Production Deployment Workflow

Next Steps

Graph View

Table of Contents