News: v0.9.9-m2 has been released!
A while back, I wrote to introduce the first incarnation of the Carbon Five Database Migration tools, a simple though powerful framework for applying discrete changes to a database and tracking which changes have been applied to a specific database. It was inspired by Rails’ Migration support.
We’ve made a number of changes in the v0.9.1 release. We adopted some of the improvements found in Rails 2.1 as well as feedback from our users. Here’s an overview of what’s changed:
- New create, drop, and reset goals for maven plugin. Now you can create a new database, drop an existing one, or reset an existing database by dropping it, creating a new one and then migrating it. This is tested with MySQL and PostgreSQL.
- Each applied migration is tracked in the database schema_version table (instead of just the last one). Also, when it was run and how long it took to run are now saved for each.
- Validate goal now lists which migrations are pending in addition to whether the database is up to date.
- Maven artifact ids have changed (migration -> db-migration, maven-migration-plugin -> maven-db-migration-plugin) and there’s been some restructuring in the core framework.
- Maven plugin is configured a bit differently now; environments have been removed completely since maven supports a better solution out of the box: profiles.
- Maven plugin now looks for migrations in src/main/db/migrations by default; alternate locations can be specified via the <migrationsPath/> element.
- We now recommend using timestamps for migration versions instead of the NNN format, though any numerical character sequence will work.
- Reworked the algorithm for determining which migrations to run to allow for a little more flexibility. Pending migrations aren’t determined by a single version number, they’re determined by comparing what is available to what has already been run. In conjunction with timestamp versions, developers won’t be stepping on each other’s migrations.
- New and updated google code project and documentation.
As you can imagine, some of these changes aren’t backwards compatible. While we’re in pre-release (< v1.0) mode, we feel like it's more important to make the fundamental changes to build a solid foundation than to retain complete backwards compatibility. The release notes give some guidelines for upgrading.
Here’s a quick getting started guide for the maven-db-migration-plugin:
Step 1: Configure maven in your project’s pom.xml
21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 | ... <build> ... <plugin> <groupId>com.carbonfive</groupId> <artifactId>maven-db-migration-plugin</artifactId> <version>RELEASE</version> <configuration> <url>jdbc:mysql://localhost/myapp_test</url> <username>dev</username> <password>dev</password> </configuration> <dependencies> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>5.1.6</version> </dependency> </dependencies> </plugin> </build> ... <pluginRepositories> <pluginRepository> <id>c5-public-repository</id> <url>http://mvn.carbonfive.com/public</url> </pluginRepository> </pluginRepositories> ... |
Lines 29-31 configure the database connection (see the reference for more options).
Lines 35-39 specify the required dependency on our database driver.
Lines 43-48 adds the Carbon Five maven plugin repository.
Step 2: Create a migration script
In src/main/db/migrations, create a script using the format YYYYMMDDHHMMSS.sql (e.g. 20080830174515.sql). Example:
CREATE TABLE users ( id INT PRIMARY KEY, email VARCHAR(255) NOT NULL, password VARCHAR(255), enabled BOOLEAN DEFAULT 'TRUE' );
Step 3: Create the database
$ mvn db-migration:create
The supplied credentials must have the appropriate privileges, of course. If you’re not using MySQL or PostgreSQL, then do this step manually.
Step 4: Check the status of the database
$ mvn db-migration:validate
Your migration will be listed as pending and the database as not up-to-date.
Step 5: Migrate to the latest version
$ mvn db-migration:migrate
The pending migration will be applied to the database and logged in the schema_version table.
Want to learn more?
Check out the google code project page, the release notes and the sample applications.
In the near future, I’m going to look at supporting MS SQL Server and improving the SQL Script Runner. Thanks for all of the feedback and please keep it coming!
Christian
Nice work! Thanks for setting this up as a google code project. As an MS SQL Server user this will really help as I can now submit issues and fixes when and if I run into issues with MS SQL. Thanks!
Lance,
I did some testing against MS SQL Server and found one issue which has been resolved in 0.9.2-SNAPSHOT. So, I’ve successfully run all of the functionality against SQL Server 2000. If you’re up for it, switch to that SNAPSHOT and give it a try. Let me know how it goes. -Christian
I just tested against hsql 1.8 but i am having a problem created the schema version table
Caused by: java.sql.SQLException: Unexpected token: UNIQUE in statement [create table schema_version (version varchar(32) not null u
nique]
I believe that HSQL v1.9 will rectify this problem but there is no release date as yet. Am i able to use my own definition for the schema version table
cheers
Jim, it depends. If you’re using the migration manager directly in your code, then you can plug-in in a new VersionStrategy, with a custom implementation of enabledVersioning(). If you’re using the maven plugin, then there isn’t really an easy hook to do what you need to do.
What is the correct DDL SQL for creating the version table for HSQL?
Another alternative is to switch to H2Database. It’s written by the same team as Hypersonic and is actively developed, unlike HSQL.
I’m midway through a refactor which will allow for better handling of dbms-specific features. When it’s done, it will be easier to handle these cases more gracefully than the framework allows for now.
Thanks Christian
HSQL just doesn’t support the ‘unique’ constraint in the create table construct. I am using MM directly in my code.
I will also look at H2Database
cheers
Chrstian.,
Do you have an rough idea when you will be doen with the refactor
Jim: this time, I do.
I’m wrapping up a few things today and hope to release another 0.9.3-SNAPSHOT this afternoon. If no issues surface with the SNAPSHOT, I’ll release 0.9.3 Monday or Tuesday. Keep an eye on the release notes (http://code.google.com/p/c5-db-migration/wiki/ReleaseNotes) to see what’s changed and when.
Christian,
Very timely indeed. Thank you very much.
Christian,
Just picked up 0.9.3 and i can get it to run against the hsql databse. Much appreciate for the quick turn around. Now i have a question around managing the migration across a number of different databases. For instance the sql that hibenrate spits out for hsql is different to that for mysql.
Is there a mechanism to handle this at the moment?
cheers
Jim: I’ve been meaning to add the recommended approach to the wiki, but haven’t yet had time. Here’s a quick description…
Using maven profiles, you can create a profile for each database supported. In each profile, you can set a property called “dbtype”. Then, in you db-migration configuration, you can override the migrations path like this: file:src/main/resources/db/${dbtype}/migrations/*
I’ll add a more complete example to the documentation one of these days. Good luck!
Cool. I’ll give a burl
Christian,
Works like a charm. I actually ended up extended ResourceMigrationResolver and used the hibernate dialect, which i already had to set the migrationsLocations
Hi,
This is a great looking plugin – we’ve just hooked it in to our CI process and are expecting good things.
The plugin looks fully featured with the exception of rolling back to prior versions a la the way it’s done in Rails. Was this a deliberate step to enforce process, or just a feature that has not been implemented yet?
Thanks,
- max
@Maxim: leaving rollback out was a deliberate decision. Not every migration has an easy-to-write corresponding rollback (e.g. destructive changes). And in my personal experience, I’ve never really needed to rollback a migration in development. We develop our migration against a unit test database that’s easy to reset. Once a migration is satisfactory, it’s run against a local development database, then checked into VCS for the rest of the team. If we need to undo something in a previous migration, we simply do it in a new migration. We’ve had scenarios where migration N adds a column and the migration N + 2 (or something) removes it. That works fine.
Have you found a strong case for rolling back in your experience?
Hello,
I have a developed similar tool.
http://code.google.com/p/garindriver
My tool supports a model with an internal schema manager scripts, JDBC driver wrapper automatically recording of DDL/DML, distributed locking model, and a few other features.
GarinDriver currently supports it’s internal schema manager and also provides a link into liquibase schema manager. With the model I use it should be possible to also link into your migration project if it looks like a good fit. The basic concept of GarinDriver is to automatically delegate to the migration system at JDBC driver connect time using a model where the client can identify the backend JDBC database and required level. GarinDriver will AUTOMATICALLY invoke migrations prior to returning the connection.
In any case, please checkout the project and let me know what you think. It is possible that we could work together a bit to allow your project link into the event chain if you feel that it makes sense.
-Mark
I’ve spent some time looking at this code, and kudos to the layout of the classes!
I’ve been using another dbmigrate implementation that supports different migration steps for different database types (so one directory holding MySQL DDL and another with h2 DDL). That’s proven handy so we can unit test with h2 and run with MySQL.
It would be a fairly small patch to MigrationResolver to make them “DatabaseType”-aware: Set resolve(DatabaseType dbtype);
And then the migrationsLocation could have the dbtype added as a sub-directory suffix, so you’d construct a new subclass of ResourceMigrationResolver with “classpath:/com/abc/project” and then depending on your datasource’s DatabaseType, either “/mysql” or “/h2″ would be appended to the database type.
By being a new subclass, the old behavior would be unaffected.
How do you guys use different database types (one for testing, one for production)?
@Matthew
Thanks! We usually handle different db types using Maven profiles. Each profile points to a different migration directory (src/main/db/mysql, src/main/db/postgres, etc). It’s not that common a use case for most of our projects.
I’m happy to make the change to the MigrationResolver interface and take a look at your patch to see how I can work it to the project. Why don’t you create a ticket (http://code.google.com/p/c5-db-migration/issues/list) and include your patch?
Take care,
Christian
Sweet — you already did the patch! *Exactly* what I had in mind. Thanks!
When will you cut the next release?
@Matthew 0.9.9-m3 has been deployed with your change. Let me know how it works out for you.
As I was writing the subclass of ResourceMigrationResolver that was doing the sub-directory, I realized that the whole resolve() method only had one hook that I could add the dbtype to the end of the resource — if convertMigrationsLocation() was made protected and was also provided the dbType, like:
protected String convertMigrationsLocation(DatabaseType dbType, String migrationsLocation)
otherwise I’d have to clone essentially the whole ResourceMigrationResolver class.
I found a more explicit way of implementing this, at least in spring, but I don’t think it’s as nice (but it’s what I’m doing for now); I’m going to provide the subdirectory as a configuration parameter:
and dbtype is specified in the same file that has the db connectivity information.
Thanks for pushing the release, and I apologize for the death-by-a-thousand-patch-cuts.