lawinegevaar.nl

Using Flyway with a Firebird database

This example will use Gradle as its build and dependency tool, and for running the example.

The full code of the example is available from https://github.com/mrotteveel/firebird-flyway-example.

build.gradle

plugins {
    id 'java'
    id 'application'
}

group 'nl.lawinegevaar'
version '1.0-SNAPSHOT'

sourceCompatibility = 1.8

repositories {
    mavenCentral()
}

dependencies {
    implementation 'org.firebirdsql.jdbc:jaybird-jdk18:3.0.6'
    implementation 'org.flywaydb:flyway-core:6.0.0'
}

application {
    mainClassName = 'nl.lawinegevaar.firebird.flyway.example.Main'
}

To execute the migrations, we’ll use the following simple main class.

Main

package nl.lawinegevaar.firebird.flyway.example;

import org.flywaydb.core.Flyway;

import java.io.IOException;

public class Main {

    public static void main(String[] args) throws IOException {
        DatabaseProperties databaseProperties = DatabaseProperties.loadDefault();
        migrateDatabase(databaseProperties);
    }

    private static void migrateDatabase(DatabaseProperties databaseProperties) {
        Flyway flyway = Flyway.configure()
                .dataSource(
                        databaseProperties.getUrl(),
                        databaseProperties.getUser(),
                        databaseProperties.getPassword())
                .load();
        flyway.migrate();
    }
}

This example assumes an existing empty database is used. The database connection information is configured in a database.properties file in src/main/resources. This properties file is accessed through DatabaseProperties.

This simple application can be built and run using Gradle:

./gradlew run

When run without existing migrations, the following logging will be produced: ^[1]

Aug 17, 2019 10:14:15 AM org.flywaydb.core.internal.license.VersionPrinter printVersionOnly
INFO: Flyway Community Edition 6.0.0 by Boxfuse
Aug 17, 2019 10:14:16 AM org.flywaydb.core.internal.database.DatabaseFactory createDatabase
INFO: Database: jdbc:firebirdsql://localhost/flyway-example.fdb (Firebird 3.0 3.0)
Aug 17, 2019 10:14:16 AM org.flywaydb.core.internal.command.DbValidate validate
INFO: Successfully validated 0 migrations (execution time 00:00.064s)
Aug 17, 2019 10:14:16 AM org.flywaydb.core.internal.schemahistory.JdbcTableSchemaHistory create
INFO: Creating Schema History table "flyway_schema_history" ...
Aug 17, 2019 10:14:16 AM org.flywaydb.core.internal.command.DbMigrate migrateGroup
INFO: Current version of schema "default": << Empty Schema >>
Aug 17, 2019 10:14:16 AM org.flywaydb.core.internal.command.DbMigrate logSummary
INFO: Schema "default" is up to date. No migration necessary.

Flyway has now created a flyway_schema_history table which is used to track the executed migrations.

First migration

Now, lets add a migration to add a simple person table. Flyway uses a naming format with scripts prefixed by a version, followed by a descriptive name. See SQL-based migrations for details.

The file we create is src/main/resources/db/migrations/V1__Create_person_table.sql

V1__Create_person_table.sql

create table PERSON (
    ID int generated by default as identity constraint PK_PERSON primary key,
    NAME varchar(100) not null
);

Executing our program, this now logs:

...
Aug 17, 2019 10:20:24 AM org.flywaydb.core.internal.command.DbValidate validate
INFO: Successfully validated 1 migration (execution time 00:00.083s)
Aug 17, 2019 10:20:24 AM org.flywaydb.core.internal.command.DbMigrate migrateGroup
INFO: Current version of schema "default": << Empty Schema >>
Aug 17, 2019 10:20:24 AM org.flywaydb.core.internal.command.DbMigrate doMigrateGroup
INFO: Migrating schema "default" to version 1 - Create person table
Aug 17, 2019 10:20:24 AM org.flywaydb.core.internal.command.DbMigrate logSummary
INFO: Successfully applied 1 migration to schema "default" (execution time 00:00.091s)

Second migration

Now, we want to add some data to the person table:

V2__Add_people.sql

insert into PERSON (NAME) values ('Mark');
insert into PERSON (NAME) values ('William');
insert into PERSON (NAME) values ('Andrew');

Running this will produce:

...
Aug 17, 2019 10:26:48 AM org.flywaydb.core.internal.command.DbValidate validate
INFO: Successfully validated 2 migrations (execution time 00:00.110s)
Aug 17, 2019 10:26:48 AM org.flywaydb.core.internal.command.DbMigrate migrateGroup
INFO: Current version of schema "default": 1
Aug 17, 2019 10:26:48 AM org.flywaydb.core.internal.command.DbMigrate doMigrateGroup
INFO: Migrating schema "default" to version 2 - Add people
Aug 17, 2019 10:26:48 AM org.flywaydb.core.internal.command.DbMigrate logSummary
INFO: Successfully applied 1 migration to schema "default" (execution time 00:00.047s)

Procedural SQL in a migration

A migration script is a collection of statement separated by a statement terminator, by default this is a semi-colon (;). To be able to execute procedural SQL statements (eg create procedure or execute block) in a migration, the statement terminator must be changed. To change the statement terminator, the Firebird support in Flyway supports the SET TERM statement that is also used by ISQL and other Firebird tools.^[2]

As an example of creating a stored procedure:

V3__Factorial_procedure.sql

set term #;
create procedure factorial(max_value integer)
  returns (factorial bigint)
as
  declare variable counter integer;
begin
  factorial = 1;
  counter = 0;
  while (counter <= max_value) do
  begin
    if (counter > 0) then
      factorial = factorial * counter;
    counter = counter + 1;
  end
end#
set term ;#

Caveats

Mixing DDL and DML in a single migration

Although Firebird has transactional DDL, it is not possible to use objects created in a transaction in DML in that same transaction. Phrased differently, DML in Firebird can only use objects (tables, columns, etc) as they existed at the start of the transaction. As a result, it is not possible to create a table and in the same migration populate it with data.^[3] You can logically group related DDL and DML together by using minor version numbers in your migration, for example V4_1__Create_department_table.sql and V4_2__Add_departments.sql.

Unsupported statement types

The following statements are not supported as they will invalidate the transaction handle used by Jaybird:

SET TRANSACTION
COMMIT [WORK]
ROLLBACK [WORK]

Dialect 3 only

Flyway uses quoted identifiers for its version table and assumes single quote string literals when parsing scripts. This means that Flyway will only work on dialect 3 databases.

Conclusion

Flyway is a powerful tool to manage changes to your database, and I think it is good news that Firebird is now supported. This example only shows the most basic usage of Flyway, so make sure to read the full documentation so you don’t miss out on other useful features.

If you need help, or more information, ask a question on the Firebird-Java mailinglist. You can subscribe by sending an email to firebird-java-subscribe@yahoogroups.com

1. some warnings produced by Jaybird have been removed

2. SET TERM is not part of the Firebird server statement syntax

3. You can add a COMMIT RETAIN; statement to your script to commit in the middle of a script. This is not recommend as this will break the transactionality of your script, which can result in maintenance and recovery headaches if a script partially succeeds and partially fails

<some deep and thoughtful title here>

Jaybird 4.0.0 released

Jaybird 4.0.0-beta-2 released

Jaybird 3.0.8 released

Jaybird 3.0.7 released

Firebird support in Flyway