Fixing "no such column" and "duplicate column name" with SQLite + Alembic

A couple of errors I ran into while developing with FastAPI + SQLAlchemy + Alembic. Writing them up here as notes to myself.

---

Error 1: no such column

I got the following error when running the API.

sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) no such column: sakes.created_by_id

The sakes table was missing the required created_by_id column.

It was defined in the model, but hadn't been applied to the DB.

---

Error 2: duplicate column name

When I tried running an Alembic migration, I hit this error instead.

sqlite3.OperationalError: duplicate column name: password

The users table already had a password column, and it was failing because it tried to add it again.

In short, the DB and Alembic's migration history were out of sync.

---

How I Fixed It

1. Bring Alembic's history in line with the DB

For columns that already exist, you just need to advance the migration history to match the DB.

poetry run alembic stamp 3ed4e13e6b65

This tells Alembic, "password already exists."

---

2. Create a migration to add the new column

Created a revision to add the missing sakes.created_by_id.

poetry run alembic revision -m "add created_by_id to sakes"

Then added the actual logic to the generated file:

def upgrade() -> None:
    with op.batch_alter_table("sakes") as batch:
        batch.add_column(sa.Column("created_by_id", sa.Integer(), nullable=True))
        batch.create_index("ix_sakes_created_by_id", ["created_by_id"])

And applied it!

poetry run alembic upgrade head

---

3. Verify

sqlite3 ochoko.db "PRAGMA table_info(sakes);"

Confirmed that created_by_id was properly there.

Reran the API and the 500 error was gone 🎉