Alembic Migrations with SQLModel Micro-Tutorial

"I have a database whose schema doesn't match my SQLModel models. I need to migrate this. I want to use alembic's – autogenerate flag. I am running into issues"

If this is you, that was me 15 minutes ago. Here is a Q&A to save you time.

Detected removed table (I don't want this!)

Did you run ~these commands and get told alembic wants to remove your table?

uv run -m alembic -c migrations/alembic.ini stamp head --purge
uv run -m alembic -c migrations/alembic.ini revision --autogenerate -m "msg"

# OUTPUT
# INFO  [alembic.autogenerate.compare] Detected removed table 'user'
# OH NO

Explanation (Fix Below)

If you see "detected removed table" and you DON'T want this. Alembic probably does not know how to find your models. Inside your env.py file you should see:

target_metadata = SQLModel.metadata

target_metadata SHOULD be a sqlalchemy.schema.MetaData object. SQLModel generates this for you. SQLModel only knows what your metadata is if you have ALREADY EXECUTED the lines of code that define classes on top of SQLModel. e.g.,

# models/user.py
class User(SQLModel, table=True):
    id: int | None = Field(default=None, primary_key=True)
    email: str = Field(unique=True, index=True)

In order to EXECUTE this, you need add an import from the user file. Alternatively, modify __init__.py to import all your models. I prefer the second method.

Fix #1, you have a models/ directory

1. Go into (or create) models/__init__.py and import all your models.

"""Models package - imports all models to populate SQLModel.metadata."""

# if you have models/feedback.py, models/image.py, etc.
from models import feedback, image, product, shopify, user

2. Go into migrations/env.py and import the models package.

import models
target_metadata = SQLModel.metadata

Now SQLModel will be aware of your models.

Fix #2, you have a models.py file

1. Go into migrations/env.py and import the models.

import models
target_metadata = SQLModel.metadata

Fix #3, you have many models files

1. Go into migrations/env.py and import the models.

import model_one
import model_two
target_metadata = SQLModel.metadata

Can't locate revision identified (What???)

Example log:

Fetching current revision...
Current: 60870current -> head
INFO  [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO  [alembic.runtime.migration] Generating static SQL
INFO  [alembic.runtime.migration] Will assume transactional DDL.
BEGIN;

ERROR [alembic.util.messaging] Can't locate revision identified by '60870current'
  FAILED: Can't locate revision identified by '60870current'

Explanation (Fix Below)

You have your DATABASE, you have an alembic_version TABLE inside your DATABASE, you have your SQLModel MODELS, and you have your migrations/versions/ VERSIONS. This error means your TABLE has a row that contains an Alembic version id that is not in the VERSIONS directory. Reference. Alembic wants to make sure every single row in TABLE has a corresponding migration in the VERSIONS directory.

To fix this, we are going to RESET the TABLE.

Fix, stamp and --purge the alembic_version table

We will run the Alembic stamp command with the --purge flag to reset the table.

uv run -m alembic -c migrations/alembic.ini stamp head --purge

Now try your auto migration again.

How to swap between my local database and my remote database?

You probably have a line in migrations/env.py that looks like

url = config.get_main_option("sqlalchemy.url")

This means "load the option called sqlalchemy.url from my migrations/alembic.ini file". Inside alembic.ini you probably have

sqlalchemy.url = sqlite:///./database.db

or something like this. Change that URL to change your database. Alternatively, add a new line to your env.py file to load an environment variable, if defined. I prefer the second.

Fix #1, Move your sqlalchemy.url configuration to env.py

Add this somewhere before your config.get_main_option line

import os
config.set_main_option("sqlalchemy.url", os.environ['DATABASE_URL'])

Alternatively, if you want to override .ini only if the variable exists,

db_url = os.getenv("DATABASE_URL")
if db_url:
    config.set_main_option("sqlalchemy.url", db_url)

Now you can specify DATABASE_URL before your commands or in your environment variables. e.g.,

DATABASE_URL="postgresql:/..." uv run -m alembic -c migrations/alembic.ini stamp head --purge

Fix #2, Change alembic.ini

Change this line in migrations/alembic.ini to your new desired value

sqlalchemy.url = postgresql:/my-custom-database-url-here

Finally, run the migration

uv run -m alembic -c migrations/alembic.ini upgrade head

Conclusion

Hopefully this helps you. I know this will help me when I forget again 6 months from now!

I am interested in helping AI research teams build production-grade Python web services on FastAPI with modern Python best practices. If this is you, reach out at

contact (at) thornewolf.com