In association with heise online

Luckily, there are tools

Numerous open source libraries and frameworks that can help with versioning a database now exist in the Java world. The top two are the Liquibase and Flyway libraries. Both are released under the Apache 2 licence and are designed to support relational database systems such as Oracle, DB2, SQL Server, MySQL, PostgreSQL, H2, HSQL and Apache Derby.

Adding new systems is no problem because both libraries use the JDBC (Java Database Connectivity) standard. Therefore, any relational database that includes a JDBC driver can be addressed by Liquibase and Flyway. Although the trend points towards using NoSQL databases, the libraries currently only support relational database systems because these systems continue to be used for centralised data storage in enterprise environments and are, at most, supported and extended using NoSQL products.

The documentation for both libraries can be considered good and extensive; any information that may be needed when working with each library can be found on the relevant web pages or in the downloadable documentation. The libraries are designed to be plug-in systems. Liquibase and Flyway can be incorporated into and executed in Ant or Maven when it is time to build. However, API integration directly into the application via Grails or Spring is also possible. For example, the database version can directly be checked, and updated if required, when an application is launched. Alternatively, when the application starts, a simple error message can be displayed to protect the application from encountering avoidable trouble at runtime.

Ready to go

Console execution is an option for non-Java products. Flyway and Liquibase can both be used as standalone programs from any console and can be integrated into arbitrary build scripts using the appropriate commands. Only a Java runtime environment is required at the time of execution.

Both libraries aim to ensure that the database schema information and the actual data are stored in separate files. Flyway uses simple files in which native SQL in the appropriate database dialect is stored and then simply executed. Liquibase introduces another abstraction layer and stores the schema information in a separate, database-neutral XML schema description that will be translated into the appropriate database dialect. In Flyway, each file is a changeset (a number of database changes) that will no longer be modified after it has been checked into the VCS. Additional changes result in additional files. Liquibase supports multiple changesets within one file via its XML schema. The following examples demonstrate how to use Liquibase and Flyway to create a three-column table in a MySQL database.

This is the configuration for a new table in Liquibase (XML will be translated into SQL at runtime):

<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog
xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-2.0.xsd">
   <changeSet id="1" author="nko">
<createTable tableName="my_table">
<column name="id" type="int" autoIncrement="true">
<constraints primaryKey="true" nullable="false"/>
</column>
<column name="name" type="varchar(255)">
<constraints nullable="false"/>
</column>
<column name="active" type="boolean" defaultValueBoolean="true"/>
</createTable>
</changeSet>
</databaseChangeLog>

This is the configuration for a new table in Flyway (native SQL is executed directly).

CREATE TABLE 'my_table' (
'id' INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
'name' VARCHAR(255) NOT NULL,
'active' TINYINT(1) NULL DEFAULT 1,
PRIMARY KEY ('id')
)

Liquibase's apparent independence from the database, and the associated potential extra flexibility in terms of the general replaceability of the target database, is put into perspective when we consider that this independence will be lost when specific parameters are used in the schema description for the target database. For example, a MySQL database can't make much of the Oracle-specific VARCHAR2 data type for text columns, while Oracle won't find the autoincrement attribute very useful. If the structural changes can't be mapped via the XML schema, native SQL must or can be used in Liquibase, with the SQL statement enclosed in a special tag. However, the database independence is also lost in this case. What's more, Liquibase only supports a single SQL statement per tag, making the implementation of complex scripts an involved task that will be difficult, if not impossible, to achieve. Flyway's plain text/SQL files allow complete scripts in database-proprietary languages (such as PL/SQL for Oracle, PL for DB2, T-SQL for MS SQL, Stored Procedures for MySQL or PostgreSQL) to be executed.

It should also be mentioned that the XML description will only be translated into the database dialect at runtime, which slightly lowers its execution speed compared to that of a file with native SQL. Readers can form their own opinion on the readability of database structures within an XML schema compared to the readability of an SQL expression.

To control which database changes are to be executed in which order, Liquibase uses a master XML that will declare and list all files that are to be executed. When a new database script (or rather XML) is added, it must also be listed in the master XML or it won't be executed. Here, Flyway uses the "convention over configuration" approach, stipulating a naming convention for the files that are to be executed. The file name must contain the version identifier in a prescribed way, and then Flyway will automatically control file execution. The only thing to remember is to save the file in the correct directory.

Next page: Conclusion

Print Version | Permalink: http://h-online.com/-1860080
  • Twitter
  • Facebook
  • submit to slashdot
  • StumbleUpon
  • submit to reddit
 


  • July's Community Calendar





The H Open

The H Security

The H Developer

The H Internet Toolkit