How to ship database updates?

Question

I have a Java application (generic) that uses a database via hibernate. I ship it via jar to clients. Which is the best way to ship database updates (schema and data) to clients? Consider that clients can have different version of my application, and the update must be automatic, without user support. And if the application is written with grails?

Answer 1

You can do this by keeping track of the current schema version (i.e. revision number) and then having a patch file to bring the schema up to the next version. You can keep applying patches incrementally until you reach the new prod/update version.

E.g. Say client is at revision 5. You have since shipped out revision 10 and 12 but he only updates to the latest one -- 15.

You can do:

foreach rev in [clientRev ... currentRev]:
   apply rev.patch

So you'll be applying the patch to bring up to rev 10; then another one to bring it to rev 12; then another one to bring it to rev 15;

If a different client is at rev 12 already they will only need to apply the last patch.

Answer 2

As @user779 says, keeping each change in some format is the way to go. The Grails documentation mentions a couple of libraries that might take out some of the hard work: LiquiBase and DbMigrate.

Answer 3

You can ship Ruby and Active Record Migrations in your distribution and have your updater execute the new migrations.