Managing database changes in Java

All past projects I have worked with have used some kind of version control software. Almost all of them have also used a relational database. But with few exceptions the database changes have never been managed using the version control.

For example this is how my current project used to manage the changes:

  • Ad hoc

    Everyone is responsible of their own database. Changes to the database are maybe communicated with email. Production database is the only one that is being kept in working order, fixes being applied to it as things break.
    • Everyone will have a different version of the database
    • Taking dumps from production is the only way to get the latest version of the database
    • Making changes to the database is so difficult that any database refactoring is out of the question
  • Hibernate schema update

    Enable the schema update and hibernate will make schema changes to the database as the mapped objects change.
    • Your database will be littered with obsolete tables and columns, especially if you are using JPA
    • Hibernate will only manage your schema, it won't manage your data
    • The changes Hibernate does are very limited
    • Hibernate developers don't recommend it
Both of the ways were, wrong, and didn't really manage the database changes. But of course there is a proper way to do it:
  • Use a database changes management tool

    As you make changes to your code, you also write a migration that will update your database as your code is deployed. You commit this migration to source control with the code that requires the change. The migrations have version information which describes the order they should be applied and lets the tool determine if the change has already been applied to the database.
    • Your code and database will always have the same version, when you branch your code you will also branch your database
    • If you like you can apply the changes automatically in deployment
    • You can make any kind of changes to your database
    • Database changes are so easy that it encourages database refactoring

Currently there are several tools available for database change management for Java. Some of them use raw SQL for the migrations while the more advanced ones like LiquiBase provide support for multiple different DBMS systems using an additional layer of abstraction for the migrations.

The tool I'm currently using for database migrations is c5-db-migration. It supports migrations written in SQL, which I prefer for our project since we are only using MySQL. The migrations are versioned by timestamp and with the Spring support they are installed automatically to the database when the web application is being deployed. We have even extended it to support migrations written in Java, which was needed for a very special case!

No comments:

Post a Comment