Database Versioning
adam.blog.heroku.com
adam.blog.heroku.com
Essentially, DataMapper already provides the solution that Adam outlines in his post; Replace schema.yml with DataMapper model definitions, and have the discipline to not write data migrations. Write specs for your migrations, like everything else, and use DM migrations’ sane versioning, rather than AR’s irritating one, and you should be fine. There a definetly improvements to be made with DM migrations, to be sure, but I feel like I got the underlying design mostly right.
Datamapper migrations are perfect since they focus on one time runnable data migrations, or a bulk schema per release, instead of having a huge chain of tiny schema tweaks that can go back and forward.
Here's how you do it:
- every database has a build number stamped into it
- every schema change goes into a change script called build_105-106.sql
- changes are applied to the dev database as they are made
- the automated build pins the change script and runs it against an integration database
- a schema diff tool is used to compare dev and integration schemas. if they don't match, the build breaks.
- the build creates a file called build_106-107.sql and sticks it into source control
But the big thing the author is getting wrong is that he's trying to define schema from his application. His ORM seems to be generating both code and schema from config files, which seems a bit silly from an outsider's viewpoint. It's causing him a lot of pain and he doesn't understand why.
Define the schema in the schema. Generate your code from that. Problem solved.
I guess that one necessary assumption is that your shop doesn't have a strong requirement for Code Ownership (in capitals). Since anybody can change the schema, and thus the object model, anybody needs to be able to propagate out their changes as far as necessary to keep the tests running and the build unbroken.
Basically, you can know with certainty that you can move from any numbered version to another by simply running the intervening scripts.
But then, since you'll generally have a continuous build is running on 5 minute intervals, it's pretty rare to have two independent schema changes in the same script.
ORMs are basically trying to recreate SQL in their own special non-portable immature bug-ridden manner. Thus, even the schema has to be defined in the ORM and that is where the problem comes in.
With that in mind, most people don't want the availability of methods and classes in their application to depend on external things like the database schema. That makes error reporting and debugging tough. So, you dump the DB schema to some format your ORM understands, and then consider that authoritative.
Many of us consider the database a tool for storing and querying data, rather than a way of life. In our case, the way the ORM works is perfect. The database is abstracted away, and we can write our software in our language of choice.
In our case we have a config file because A) it makes it easier to abstract the schema across different databases and B) we have extra metadata in there above and beyond just the schema information.
Wrapping a relational database with an object layer is really a simple, straightforward problem. It surprising that so many smart people feel compelled to spend so much time and effort engineering overly complex solutions to it.
The problem with pretty much any kind of framework, ORM or not, is that eventually it becomes bloated by being flexible enough to meet the requirements of thousands of different developers, meaning that any given one of them only ever uses a small percentage of the overall functional footprint. Keeping things simple yet powerful is definitely the hardest part about it all, especially given that different users have vastly different opinions about what's simple or what power they need.
But the amount of polish that you need to turn an in-house thing into an open source project is just immense. And worse, by the time you have it flexible enough to handle every possible use case, you've bloated it out to the point where it's no fun to use anymore. Pick pretty much any off the shelf ORM to see the end result of that path.
We do still use explicit version numbering for version triggers (our name for migrations), since it's just too hard if the numbers aren't meaningful. If you just used a checksum, how would you know if A248B5FC comes before or after 3F56EB2? It's a little easier if you can say "the DB is at version 23, and the latest is version 26, so we need to run these three triggers." The database stores both the metadata checksum as a hash and a version number; if the hash changes without the version number changing, we at least know that's an error and can detect it. Certain types of changes (like adding a nullable column) we handle automatically by diffing the current schema against the actual DB schema, while anything more complicated (and anything that touches the data) requires an explicit trigger.
Since we build deployed enterprise software that gets upgraded on the scale of years, not days or weeks, we also follow the course of using triggers only on production databases. It's still not really a perfect solution for all sorts of reasons I could go into, but it's interesting that we ended up pretty close to what he's proposing.
But we did have the luxury of running a (fairly small-scale) website rather than distributing an application.