Hacker News Reader

Comment by kanobo | Hacker News Reader

The reason the author flat out didn't consider SQLAlchemy’s alembic seems incorrect? Don't most people simply use a local testing db, generate migrations on that test db then just perform the upgrade on prod if it looks good instead of generating the migration directly on prod?

View on HN

Yes that's how you'd do it. That said, while I really love SQLAlchemy and worked with Alembic for a long time I switched to manually generated SQL-based migrations in all of my projects, as there are simply too many corner-cases that are quite hard to capture with tools like Alembic. Writing plain SQL files gives you much more expressive power and also makes it quite easy to build complex migrations that e.g. involve first some schema changes, then a data migration, and then some more schema changes, which is hard to do with most migration tools.

I also found that the use-case which most of these migration tools optimize for (being somewhat independent of specific SQL dialects) rarely works in practice, except maybe if you stick to the smallest common denominator of all SQL dialects. And that again is wasteful IMO as not using a lot of the functionality that's present e.g. in Postgres can make your life a lot harder.

I'm working with dotnet rather than Python, and worked with Entity Framework's automated migrations for years. I say "worked with", but all too often it was "battled with" - just as you mention, there were just too many corner-cases for anything other than the simplest of databases.

A few years back I switched to plain SQL based migrations (using a lightweight library called DbUp to execute them) - I couldn't be happier with it!

What better tool to express database changes with than SQL? I also like the transparency it brings you - the files are right there for you to read. And I'm a stickler for formatting and convention, so it's great being able to write SQL files that include comments and use my preferred formatting.

Yes, that is exactly how you use sqla alembic: You connect it to your development db, generate the migration, then run the migration locally first then on staging/prod.

This has both the pro and con of your app being quite tightly coupled with the database.

> your app being quite tightly coupled with the database

I mean, this is the case any time your app talks directly to a database

Only if your DB is purely a way of keeping your objects warm, otherwise you can design your DB with the business in mind, the app with the apps domain in mind, and an adapter between the two. Layer of abstraction but might help BAs work against the database and not be stumbled by application specific things that affected the design. Depends on the system of course!

Then your "app" is the adaptor, and the tying still applies.

why would you ever add a layer between you and the db?

Some DBs have inherent limitations and you need to design something to get around them. Sometimes you have many apps and you don't want to reinvent the wheel to support what are otherwise standard accesses of a database. Sometimes it's a "special" database which makes this more convoluted, or perhaps you need to implement things like a gradual back-off retry to prevent swamping the backend. Sometimes you don't want to have to patch every app that uses the database if a database change is breaking. Sometimes you want to prevent developers from abusing your database, provide more fine-grained access control, prevent poorly researched queries which can kill performance, etc. Sometimes you're talking to more than one db.

Personally, I would start writing the abstraction as soon as I plan to have more than two apps.

Think I was using the wrong terminology. What I meant is that your ORM code in the app needs to reflect your database pretty much exactly, otherwise you can get unwanted migrations.

Which is in most cases desirable, but for example if you want to use database features that SQLAlchemy/Alembic don't (yet) support it can get a bit messy and/or require some manual plumbing to keep getting the migrations you want.

Yeah, that seems off to me too. I don't know of anyone who just goes straight to prod in that way, and I wouldn't take them seriously if they did.

I think you're right -- I've never managed an SQLAlchemy on a production site. I'm going to edit this paragraph.

Reply on news.ycombinator.com