17 August 2019
Flyway is a database migration (versioning) tool that supports a wide variety of database systems. Recently, they accepted my pull request to add support for Firebird in Flyway 6.0. Although my pull request supported Firebird 2.1 and higher, the Flyway team has decided to only support Firebird 3 and higher. I assume this was decided as Firebird 2.5 and earlier are all end-of-life.
| At this time, the release of Flyway 6.0 has not been announced yet, but it is already available from Maven central. |
Flyway provides a number of ways to migrate database:
Personally, I prefer to use the Java API to migrate the database from within an application. However, that solution requires the application to have access to credentials with sufficient rights in the database, which isn’t alway an option. Depending on your deployment model, it might be better to provide a separate application or use one of the other three options to migrate a database.
I will show a small example of use of Flyway with Firebird 3 below. Be sure to read Flyway’s Get Started and Flyway’s documentation for more information and an overview of all features. This example doesn’t cover all options available.
|
In its current form, the example does what is better done using the command-line or the Maven or Gradle plugins. Consider it a stepping stone for a migration feature as part of a bigger application. |
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.
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.
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.
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
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)
Now, we want to add some data to the person table:
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)
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:
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 ;#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.
The following statements are not supported as they will invalidate the transaction handle used by Jaybird:
SET TRANSACTION
COMMIT [WORK]
ROLLBACK [WORK]
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.
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
SET TERM is not part of the Firebird server statement syntax
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