Firebird

Supported Versions

  • 4.0
  • 3.0

Driver

URL format jdbc:firebirdsql://host:port/database
Ships with Flyway Command-line Yes
Maven Central coordinates org.firebirdsql.jdbc:jaybird-jdk18:3.0.5
Supported versions 2.2 and later
Default Java class org.firebirdsql.jdbc.FBDriver

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 separate migrations are necessary.
  • 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