Update a backend database on software update with Java

Question

With which tool / library it is possible to update an existing database structure. On the update of the software it is also needed to change the database. Because there can be different versions of the software it should compare the current status with the target status of the database. It should:

• add table columns, fill it with default values.
• delete table columns
• change the data type of columns, for example varchar(30) --> varchar(40)
• add / remove indexes
• add / alter / delete views
• update some data in the tables
• ...

It should support the DBMS:

• MS SQL Server 2000 - 2008
• Oracle Server 8 - 11
• MySQL

Because our software setup and application run in Java that it must also be run in Java. What can we use?

Ideally it scan our development and save it in an XML file. Then we can add some data modification SQL command. Then it can be run on customer side with the setup of the update.

Answer 1

Autopatch is what we are using. It works pretty well.

It allows sql patches, data patches, and java patches all applied to your sql database.

Answer 2

Check out Liquibase. A database migrations tool, like dbmigrate, might also be worth a lok.