- TechDailyFeed - Your Daily Dose of Tech News, AI, Programming, and More

Introduction Today, I encountered one of the most practical and important real-world lessons in managing database migrations with Alembic and PostgreSQL during live service debugging. This post documents the exact problems, what caused them, how I analyzed them, and how I ultimately fixed everything — step-by-step. If you're working with Alembic, Flask, FastAPI, or any backend Python project that involves database migrations, I hope this saves you hours of frustration! Background Project: menu-swift-menu-service Stack: FastAPI, SQLAlchemy, Alembic, PostgreSQL Problem started when the backend service was throwing 500 Internal Server Errors, and digging deeper revealed: sqlalchemy.exc.ProgrammingError: relation "menu" does not exist The Problems Faced Problem 1: No Application Tables in Database When I ran \dt inside Postgres, I found only the alembic_version table and no other application tables like menu or submenu. This indicated that migrations had not yet been run properly. Problem 2: Alembic Could Not Find a Revision Running alembic current or alembic upgrade head produced: Can't locate revision identified by '382cc7bfcc52' This meant the database was "stuck" at a revision ID that no longer existed in the project's migrations folder. Problem 3: Migration Failed Due to Missing Table Even after trying to stamp the database forward, Alembic crashed again because: ALTER TABLE menu ADD COLUMN total_portions INTEGER NOT NULL But the menu table had never been created because Alembic had skipped applying that migration. What Caused These Problems? A lost migration file (382cc7bfcc52) caused Alembic to break its chain. Incorrect use of alembic stamp marked the DB as upgraded without actually creating tables. As a result, later migrations tried to modify tables that didn’t exist. Step-by-Step Solutions 1. Confirm Existing Revisions I ran: alembic history --verbose to list all valid migrations locally. 2. Correct the Database’s Migration Pointer Inside psql, I manually corrected the alembic_version table: UPDATE alembic_version SET version_num = 'defcb113fe5f'; (where defcb113fe5f is a known good migration that creates the menu table). 3. Realized the Table Still Didn't Exist Because stamp doesn’t apply migrations — it only moves the pointer — the actual menu table still wasn’t created. 4. Fully Reset the Database to Base I reset Alembic's understanding by: alembic stamp base This told Alembic: "Forget about being at any version. Let's start from scratch." 5. Apply Migrations Properly Then I applied all migrations cleanly: alembic upgrade head This ran the SQL to: Create the menu table, Add the total_portions and business_id columns, And apply later upgrades. 6. Restart Backend Service Finally, I restarted the service: sudo systemctl restart menuswift.service No more 500 errors, no more missing tables. Everything worked perfectly. Key Commands Used # See all tables \dt # See table structure \d menu # See current Alembic version SELECT * FROM alembic_version; # Update Alembic manually UPDATE alembic_version SET version_num = 'valid_revision_id'; # Reset Alembic completely alembic stamp base # Apply migrations properly alembic upgrade head Lessons Learned Never manually delete migration files without checking the database state first. alembic stamp does not apply migrations; it only sets a marker. Always check alembic history to confirm available migration chains. If Alembic complains about missing revisions, manually updating alembic_version is necessary. After database corrections, always verify tables and columns with \dt and \d tablename. Conclusion This was one of the most important backend debugging experiences I've had so far. It taught me not only how Alembic internally manages database versions but also gave me real confidence in recovering from broken migration histories without panic. If you ever find yourself stuck with Alembic, remember: "Migration history can break, but with careful manual fixing and a clear head, you can always recover." Thanks for reading! ✨ Feel free to connect if you'd like to discuss Alembic migrations, backend architecture, or cloud-native development!

Apr 29, 2025 - 22:37

Introduction

Today, I encountered one of the most practical and important real-world lessons in managing database migrations with Alembic and PostgreSQL during live service debugging.

This post documents the exact problems, what caused them, how I analyzed them, and how I ultimately fixed everything — step-by-step.

If you're working with Alembic, Flask, FastAPI, or any backend Python project that involves database migrations, I hope this saves you hours of frustration!

Background

Project: menu-swift-menu-service
Stack: FastAPI, SQLAlchemy, Alembic, PostgreSQL
Problem started when the backend service was throwing 500 Internal Server Errors, and digging deeper revealed:

  sqlalchemy.exc.ProgrammingError: relation "menu" does not exist

The Problems Faced

Problem 1: No Application Tables in Database

When I ran \dt inside Postgres, I found only the alembic_version table and no other application tables like menu or submenu.

This indicated that migrations had not yet been run properly.

Problem 2: Alembic Could Not Find a Revision

Running alembic current or alembic upgrade head produced:

  Can't locate revision identified by '382cc7bfcc52'

This meant the database was "stuck" at a revision ID that no longer existed in the project's migrations folder.

Problem 3: Migration Failed Due to Missing Table

Even after trying to stamp the database forward, Alembic crashed again because:

  ALTER TABLE menu ADD COLUMN total_portions INTEGER NOT NULL

But the menu table had never been created because Alembic had skipped applying that migration.

What Caused These Problems?

A lost migration file (382cc7bfcc52) caused Alembic to break its chain.
Incorrect use of alembic stamp marked the DB as upgraded without actually creating tables.
As a result, later migrations tried to modify tables that didn’t exist.

Step-by-Step Solutions

1. Confirm Existing Revisions

I ran:

alembic history --verbose

to list all valid migrations locally.

2. Correct the Database’s Migration Pointer

Inside psql, I manually corrected the alembic_version table:

UPDATE alembic_version SET version_num = 'defcb113fe5f';

(where defcb113fe5f is a known good migration that creates the menu table).

3. Realized the Table Still Didn't Exist

Because stamp doesn’t apply migrations — it only moves the pointer — the actual menu table still wasn’t created.

4. Fully Reset the Database to Base

I reset Alembic's understanding by:

alembic stamp base

This told Alembic:

"Forget about being at any version. Let's start from scratch."

5. Apply Migrations Properly

Then I applied all migrations cleanly:

alembic upgrade head

This ran the SQL to:

Create the menu table,
Add the total_portions and business_id columns,
And apply later upgrades.

6. Restart Backend Service

Finally, I restarted the service:

sudo systemctl restart menuswift.service

No more 500 errors, no more missing tables. Everything worked perfectly.

Key Commands Used

# See all tables
\dt

# See table structure
\d menu

# See current Alembic version
SELECT * FROM alembic_version;

# Update Alembic manually
UPDATE alembic_version SET version_num = 'valid_revision_id';

# Reset Alembic completely
alembic stamp base

# Apply migrations properly
alembic upgrade head

Lessons Learned

Never manually delete migration files without checking the database state first.
alembic stamp does not apply migrations; it only sets a marker.
Always check alembic history to confirm available migration chains.
If Alembic complains about missing revisions, manually updating alembic_version is necessary.
After database corrections, always verify tables and columns with \dt and \d tablename.

Conclusion

This was one of the most important backend debugging experiences I've had so far.

It taught me not only how Alembic internally manages database versions but also gave me real confidence in recovering from broken migration histories without panic.

If you ever find yourself stuck with Alembic, remember:

"Migration history can break, but with careful manual fixing and a clear head, you can always recover."

Thanks for reading! ✨

Feel free to connect if you'd like to discuss Alembic migrations, backend architecture, or cloud-native development!