Initial Alembic Migration

We've defined our Project model, but our PostgreSQL database doesn't know about it yet. We need a way to translate our Python code into SQL CREATE TABLE statements.

More importantly, as our application evolves, our models will change. We'll add new tables, add columns, and change relationships. We need a system to track these changes over time and safely apply them to our database. This is called database migration.

In the Python ecosystem, the standard tool for this is Alembic, which is maintained by the same team that builds SQLAlchemy.

Setting up Alembic

First, we need to add Alembic and psycopg (the PostgreSQL driver) to our project dependencies:

shell

uv add alembic "psycopg[binary]"

Next, we initialize Alembic in our project:

shell

uv run alembic init alembic

This command creates a new alembic/ directory and an alembic.ini file in your project root.

Configuring Settings

Before Alembic can connect to the database, we need a small settings module to hold the connection string. Centralizing it here means the same string is reused anywhere we talk to the database.

Add pydantic-settings:

shell

uv add pydantic-settings

Create src/release_tracker/config.py:

src/release_tracker/config.py

from functools import lru_cache

from pydantic_settings import BaseSettings, SettingsConfigDict

DEFAULT_DATABASE_URL = "postgresql+psycopg://release_tracker:release_tracker@localhost:5432/release_tracker"


class Settings(BaseSettings):
    database_url: str = DEFAULT_DATABASE_URL

    model_config = SettingsConfigDict(
        env_file=".env", env_file_encoding="utf-8"
    )


@lru_cache
def get_settings() -> Settings:
    return Settings()  # type: ignore[call-arg]

Settings reads from environment variables (or a .env file, if one exists), falling back to DEFAULT_DATABASE_URL for local development. @lru_cache on get_settings() ensures we build the settings object once and reuse it across the application.

Configuration

Alembic needs to know two things:

How to connect to the database.
Where to find our SQLModel definitions.

`alembic.ini`

Update the alembic.ini file to configure the logging output. You can replace the contents of your alembic.ini with this standardized boilerplate:

alembic.ini

[alembic]
script_location = alembic
prepend_sys_path = .
path_separator = os

[loggers]
keys = root,sqlalchemy,alembic

[handlers]
keys = console

[formatters]
keys = generic

[logger_root]
level = WARN
handlers = console

[logger_sqlalchemy]
level = WARN
handlers =
qualname = sqlalchemy.engine

[logger_alembic]
level = INFO
handlers = console
qualname = alembic

[handler_console]
class = StreamHandler
args = (sys.stderr,)
level = NOTSET
formatter = generic

[formatter_generic]
format = %(levelname)-5.5s [%(name)s] %(message)s

`alembic/env.py`

Configure the alembic/env.py file. This is a Python script that runs whenever you execute an Alembic command. We'll update it to pull our database URL from our configuration and import our SQLModel metadata.

Replace the contents of alembic/env.py with the following:

alembic/env.py

from logging.config import fileConfig

from sqlalchemy import engine_from_config, pool
from sqlmodel import SQLModel

from alembic import context

# Import our models so Alembic can see them!
from release_tracker import models  # noqa: F401
from release_tracker.config import get_settings

config = context.config

# Inject the database URL from our settings
config.set_main_option("sqlalchemy.url", get_settings().database_url)

if config.config_file_name is not None:
    fileConfig(config.config_file_name)

# Point target_metadata to SQLModel's metadata
target_metadata = SQLModel.metadata


def run_migrations_offline() -> None:
    url = config.get_main_option("sqlalchemy.url")
    context.configure(
        url=url,
        target_metadata=target_metadata,
        literal_binds=True,
        dialect_opts={"paramstyle": "named"},
    )

    with context.begin_transaction():
        context.run_migrations()


def run_migrations_online() -> None:
    connectable = engine_from_config(
        config.get_section(config.config_ini_section, {}),
        prefix="sqlalchemy.",
        poolclass=pool.NullPool,
    )

    with connectable.connect() as connection:
        context.configure(
            connection=connection, target_metadata=target_metadata
        )

        with context.begin_transaction():
            context.run_migrations()


if context.is_offline_mode():
    run_migrations_offline()
else:
    run_migrations_online()

Fixing the Migration Template

Alembic operates using templates. When you generate a new migration, it reads a template file called script.py.mako to figure out what boilerplate code to write.

By default, Alembic's template doesn't know about SQLModel. If we don't fix this, every migration we generate will have a missing import sqlmodel statement and will fail our linting checks.

Open alembic/script.py.mako and add import sqlmodel right below the SQLAlchemy import (around line 11):

alembic/script.py.mako

from alembic import op
import sqlalchemy as sa
import sqlmodel
${imports if imports else ""}

Now Alembic will automatically stamp that import at the top of every new migration it generates!

How Migrations Work

Alembic operates on a simple two-step workflow:

Generate:

Run alembic revision --autogenerate -m "description". Alembic inspects your current database schema, compares it to your Python SQLModel classes, and automatically generates a migration script containing the exact SQL needed to make the database match your code.
Apply:

Run alembic upgrade head. This takes any pending migration scripts and applies them to the database.