My team and I are currently working on an project we first started in early 2010. The application is in production since sometime late 2010 and there has been no active development except for minor enhancements and bugfixes since then. Even if our code, processes and tools were good in 2010 we've improved a lot since then. Working on my old projects is one of the occasions, where this becomes most evident.
When we start a new project today we usually use the database migration tool Liquibase right from the beginning. The tool keeps code and the database schema in sync and usually takes care of automatic migration during application startup.
Back then we usually used SQL scripts, which had to be executed manually during deployment, to keep the database up to date. Out of laziness or lack of time, this was also the first approach we took this week to handle database changes. These scripts are checked into version control along with any code changes.
This may work pretty well in the beginning, but can also become annoying very fast: Everything you've to do manually is destined to fail some time. ALL THE TIME! It fails on my colleagues working machines, it fails on our continuous integration server (Jenkins) and it will probably fail hard on production, if you don't pay enough attention during a deployment.
So there we were, about 60 minutes ago, standing there with a database dump from production and a bunch of SQL scripts, which accumulated during this week of development. Well, it is friday and I wanted to test something new so I remembered a talk I attended earlier this year about an alternative to Liquibase: flyway.
What it basically does, is to execute a bunch of SQL scripts it hasn't already executed on the given database. To get started I saved the dump of the production system into the db/migration/ package of our web application:
mkdir -p src/main/respources/db/migration/ && cp prod_dump.sql src/main/resources/db/migration/V1_initial_import.sql
As many of our applications, this one too is based on Spring and Maven. So I added the flyway dependency to our pom.xml and also some XML to the bean configuration.
Even if there is a Maven plugin to execute the migration scripts, we got used to migrating the database during the application boot process (because you will never have to think about it again, it simply migrates...). So we add the flyway bean to our bean configuration file. It is important that the flyway bean is instantiated early because it has to migrate the database before anyone else uses it. In our case "anyone" is actually the EntityManager, so i configured the persistenceUnitManager to depend on flyway (which means flyway is running first):
<bean id="flyway" init-method="migrate">
<property name="dataSource" ref="dataSource"/>
<bean id="persistenceUnitManager" depends-on="flyway"
<property name="defaultDataSource" ref="dataSource" />
Of course there are several configuration options for the flyway object. You can refer to the documentation for further details.
By default, flyway will now search for SQL scripts in your classpath. It expects the scripts in the db.migration package, following a particular naming scheme: Vxxx__description.sql, just like the one we already created earlier (V1_initial_import.sql). It will also remember at which version the database currently is and will only execute scripts it has not executed so far. So when we start our application flyway will find our script and will execute it. Afterwards it will know, that the database is at version 1 and will not execute the V1__ file again. This will only work on an empty database so you should drop and create your local database at this point.
5:29:11,362 INFO .flyway.core.metadatatable.MetaDataTable: 111 - Creating Metadata table: schema_version (Schema: mydb)
15:29:11,408 INFO glecode.flyway.core.migration.DbMigrator: 120 - Current schema version: null
15:29:11,412 INFO glecode.flyway.core.migration.DbMigrator: 205 - Migrating to version 1
15:29:24,694 INFO glecode.flyway.core.migration.DbMigrator: 191 - Successfully applied 1 migrations (execution time 00:1.290s).
Now if I have database changes, I simply add a new SQL file containing the change with the prefix V2__ and so on. If my colleagues update their working copy they will also get my SQL changes and flyway will execute it during application bootup (or integration-test) and nobody has to do this manually anymore.
Ok, nice. But what about production? When we deploy the new version of the app we also want the scripts to be executed but not the initial import, right? I dont want to execute "drop database dbname; create database dbname;" there. Flyway initializes itself on the first start but only if the database it writes to is empty. So the migration will fail on production.
For this case flyway also comes with a goal that creates the metadata tables. It comes with the ability, to initialize your metadata tables at any given version. You can accomplish this by code (call init() on the flyway object), via the Maven plugin (flyway:init) or on the commandline. Because I did not want to install any extra software on the production machine, I simply prepared an SQL dump of the metadata table (schema_version) right after the initial import was executed. This will now be executed against the production database right before the next deployment. Yes, manually... but for the last time ;).