SQLAlchemy Migrate 0.5 was released last week, and it got me thinking about how important it is.
Evolutionary Database design and data schema refactoring critical to most projects these days, and pretty much everywhere I’ve worked over the past 10 years, we’ve handled database upgrades via a set of sql upgrade and downgrade scripts, a db version number in the db, and a upgrader/downgrader python thingamabober.
A few years ago I did a couple projects in Rails, and Rails Migrations made this easier, by providing a standard API. There were limitations, and worries about lost data, and sometimes it wasn’t clear what SQL would be generated — but it was good enough, and worked well enough, that everybody used it.
But what I don’t like about ActiveRecord, and other ORMs like it is that every object-property change ultimately requires an underlying database change. On large projects I really want the flexiblity of a data-mapper to insulate some of my OO code and OO design decisions from the need for database level changes. I think this is incredibly important on very complex projects where the “data model” needs to change quite frequently, but the underlying database schema needs to change less frequently. SQLAlchemy on the other hand provides a clear, simple, elegant solution to that problem by decoupling the Objects from the Relations by providing an explicit Mapper.
But even with the flexibility of a Data Mapper based ORM, you still need to update your database schema sometimes. And that’s where SQLAlchemy-Migrate comes in. I think it or something like it is an indispensable tool in the SA user’s toolkit.
There’s more we can do to integrate Migrations into TG2, mainly by documenting it better, and setting it up a bit more in quickstart. I know there’s a trade-off there too, because for a lot of smaller applications migrations are too much overhead at the beginning, you can more easily wipe and recreate your database as you make changes, and we should support that way of working — so nothing we do should make that harder.
With that said, I’ll know we have arrived at the right place when we’ve made evolutionary database development or database schema refactoring easy enough that it feels natural and easy, and not doing it feels uncomfortable.