The Empty Migration File That Changed How I Manage Databases

This is one of my personal experiences from my development journey, and I would like to share it with you.

Let's jump right into it. I was developing a Python backend server with FastAPI, using PostgreSQL as the database and SQLAlchemy as the ORM to create and manage tables. At first, everything worked fine but midway through the project, requirements changed. We needed to update some tables, modify relationships, and add built-in PostgreSQL functions for ID generation. So I updated the ORM file and ran the migration. SQLAlchemy generated the migration file but I didn't check it. I just ran it and moved on. Everything seemed to work fine.

Later, I had to develop another server connecting to the same database. So I used sqlacodegen to generate the data models. But when generating the models, it didn't pick up some PostgreSQL built-in functions and table relationships like one-to-many and many-to-many. I wondered how that happened. At first I thought it was a sqlacodegen issue but after digging in, I couldn't find anything wrong with the tool. So I went back and checked the SQLAlchemy migration files to verify the migration had applied correctly. When I found it the migration file was empty. The changes were sitting in the ORM file but had never actually made it to the database. The worst part? This had already been in production for a while without me noticing. I did some research and found the root cause: SQLAlchemy can identify things like the PostgreSQL built-in function gen_random_uuid() when a table is first created, but if you update an existing table and add that function later, the ORM simply doesn't detect the change. Same with certain relationships, we assume they're picked up, but SQLAlchemy quietly skips them during migrations.

Just to confirm it wasn't a ORM-specific issue, I created a Next.js project and used Prisma ORM to generate the database models and it had the exact same problem. Functions, relationships, none of it was picked up correctly. That's when it clicked. This wasn't a bug in any of the tools. The real problem was my assumption I had been treating SQLAlchemy as the source of truth for my database schema, but it was never designed for that role. The tools were doing their job fine. I was using them for the wrong job. Now I had a clear problem to solve and it wasn't about finding a better ORM.

Through this experience, I identified several problems that come with relying on an ORM for database migrations:

• Silent empty migration files: When I updated an existing table, SQLAlchemy generated an empty migration file with no error or warning. It just silently did nothing.
• Built-in PostgreSQL functions not detected: Adding a function like gen_random_uuid() to an existing table was completely ignored by the ORM during migration. It only works when the table is first created.
• Relationships not captured: Certain table relationships like one-to-many and many-to-many were not picked up by SQLAlchemy during migrations, even though they were defined in the ORM file.
• Schema drift goes unnoticed: Because the migration file was empty and everything seemed to work, the schema drift spread silently into production before I caught it.
• Generated models were incomplete: When sqlacodegen pulled models from the actual database, the missing functions and relationships weren't there, because they never made it to the database in the first place.
• Problem is not tool-specific: Prisma had the exact same limitations, confirming this isn't a SQLAlchemy bug, it's a fundamental limitation of using an ORM as the schema migration owner.

In the next article, I'll walk you through exactly how I solved this and, it didn't involve finding a better ORM.