Poll

Firebird

Supported Versions

• 4.0
• 3.0

Support Level

Support Level determines the degree of support available for this database (learn more).

Driver

Java Usage

Firebird support is a separate dependency for Flyway and will need to be added to your Java project to access these features.

Maven

Community

<dependency>
    <groupId>org.flywaydb</groupId>
    <artifactId>flyway-firebird</artifactId>
</dependency>

Teams

<dependency>
    <groupId>org.flywaydb.enterprise</groupId>
    <artifactId>flyway-firebird</artifactId>
</dependency>

Gradle

Community

dependencies {
    compile "org.flywaydb:flyway-firebird"
}

Teams

dependencies {
    compile "org.flywaydb.enterprise:flyway-firebird"
}

SQL Script Syntax

• Standard SQL syntax
• Terminator changes supported using `SET TERM <new terminator><old terminator>
• Firebird dialect 3 only

Compatibility

• DDL exported by Firebird isql needs to be split into separate files on each COMMIT WORK, removing the COMMIT WORK statement from the script.
• Any Firebird SQL script executed by Flyway, can be executed by the Firebird tools (after the placeholders have been replaced).

Example

/* Single line comment */
CREATE TABLE test_data (
  value VARCHAR(25) NOT NULL PRIMARY KEY
);
 /*
Multi-line
comment
*/
 -- Sql-style comment
 -- Placeholder
ALTER TABLE ${tableName} ADD id INT NOT NULL;
 -- Terminator changes
SET TERM #;
CREATE OR ALTER PROCEDURE SHIP_ORDER (
    PO_NUM CHAR(8))
AS
BEGIN
  /* Stored procedure body */
END#
SET TERM ;#

Limitations

• Mixing DDL and DML involving the same tables in a single migration is not supported. Firebird disallows DDL changes to be used by DML in the same transaction, so one of the following is necessary:
  • separate migrations for DML / DDL, or
  • a per-script override of executeInTransaction
• SET TRANSACTION and COMMIT [WORK] are not supported in migrations. COMMIT RETAIN can be used, but this will only allow partial rollback of a migration; only statements after the last COMMIT RETAIN can be rolled back.
• Migrations modifying or dropping objects while the database is in use can result in long delays or “object X is in use” errors as Firebird uses existence locks on metadata objects when they are in use. It may be necessary to shut down the database and run the migration with exclusive access.

Existing database setup