Just came across  a nice tool to manage database migrations – flyway. Flyway lets you manage your database changes in text files and migrates them in a systematic and repeatable manner.

Changes to business requirements often mean changes in the structure of your database (add new columns or tables for example). A common approach folks use is to write the DDL statements in sql text files and check them into the same repository as the application code. Someone then runs these sql files just before the code deployment. This does not give you a method to manage changes in a systematic manner and even compare what changes exist in your database vs whats still needs to be committed.

Enter tools like flyway and liquibase. Both tools can do wonders when you have nothing else being used. Flyway uses a set of files which can contain your native DDL statements in them. The files are named in a certain format such as V1__something.sql or V1_1__somethingelse.sql, where the version number is embedded in the file name. Flyway maintains a database table named schema_version which contains metadata about the state of your database. As you execute each new version of sql changes the metadata table will get entries added to track that. You can now use flyway commands to compare the existing database state to any pending changes you have written but not migrated yet.

Liquibase gives you one key ability – that is to write the database changes in an abstraction dialect which is then converted over to the database specific DDL. If you know for sure that your database will never change then flyway may be a good option. Your call.

With Flyway you can start with both an new empty schema or from an existing schema. For existing schema you will need to extract/dump the DDL SQL into say a V1__myinitial_db.sql and then run “flyway init” to create an entry in the metadata table schema_version. See docs at flyway on managing existing database.