Question: how can we track database schema upgrades? How can we make sure our database structure matches the deployed code?
One answer: in Android’s SQLite database, they solve this problem by storing a version number in a database property. When an application opens a connection, the version number in the code is checked against the version number in the database. If they don’t match, Android calls a hook to let the application update the schema.
For our web app purposes, we used the idea of storing the version number in the database. We threw it in a table. Our upgrade scripts are separate from the code, but they still do the job of converting data, applying DDL changes, and finally increasing the version number in the database.
An application should fail early if the database is out of date. For this, we created a ConnectionProvider that validates the version number when a database connection is established. Thus, there is a version stored in the database and a version hard-coded into the application. If we forget to upgrade the dev or test database before deploying the corresponding code, we find out on startup.
Programming experience in a different environment made our lives easier this day. Design was simple because we were aware of a pattern that worked in one architecture. It adapted well to ours.
Wouldn't a good migration framework solve this? The best I've used so far is Play! 2's, which shows a web page with a \”click to upgrade database\” button when necessary.I've also implemented it myself with Migrate4J, a Java port of Rails's migrations, by triggering upgrades on application startup.
What we've done is the very beginning of a migration framework. You're right that if we want to take this further and have the application perform the upgrades automatically, we should look into those. Thanks!