How to update database by ant (versioning)?

Question

I have a web application on java, which is working with database. I need an ant’s script that will deploy or update my application to latest version. There is no problem with application part, but I don't know how to do database update.

I have an idea to build-in some meta-information (number of version) to the names of sql scripts.

For example:

• DB_1.0.0.sql
• DB_1.0.1.sql
• DB_1.2.0.sql
• DB_2.0.0.sql
• DB_2.1.0.sql

My script detected, that current version was 1.0.1, so I need to execute DB_1.2.0.sql, DB_2.0.0.sql, DB_2.1.0.sql files by SQL task. Problem is: how to find files with ant, that I need to execute.

Maybe it is not the best way to update database. Do you have any other idea?

Answer 1

It looks like your filenames follow a strict convetion. In that case you can find files by matching a pattern filelist and execute using sql.

You can use LiquiBase to write some tasks that can help in database schema updates-

Answer 2

Flyway works as you've described. It keeps a record of the SQL files already applied to the database, enabling an automatic upgrade. Simple and straight forward to use.

A more powerful solution, IMHO, is liquibase. It has an XML syntax to record database changes, enabling the generation of cross-platform SQL. It also has some powerful features such as the ability to roll-back changes and perform diff's between databases.