Firebird Database

Firebird Database — Skill

Use this skill when working with Firebird database in Delphi projects via FireDAC.

When to Use

• When configuring FireDAC connection with Firebird
• When creating tables, generators, stored procedures, triggers, domains and views
• When implementing Repositories with FireDAC + Firebird
• When working with transactions, isolation levels and concurrency
• When planning schema migrations (versioned scripts)
• When optimizing queries and indexes for Firebird

Firebird Versions

| Version | Relevant News | |--------|----------------------| | 2.5 | Trace API, LIST() aggregate, Windows Trusted Auth | | 3.0 | Native BOOLEAN, IDENTITY columns, Packages, UDR (replaces UDF), Window Functions (OVER), Encryption | | 4.0 | DECFLOAT, INT128, TIME/TIMESTAMP WITH TIME ZONE, Replication, Batch API, LATERAL join | | 5.0 | WHEN NOT MATCHED BY SOURCE, Parallel Backup, SQL Security hardening, Profiler |

> Recommendation: Use Firebird 3.0+ for new projects. Avoid deprecated features like UDFs.

FireDAC connection with Firebird

Minimum Configuration

unit MeuApp.Infra.Database.Connection;

interface

uses
  FireDAC.Comp.Client,
  FireDAC.Phys.FB,        // Driver Firebird
  FireDAC.Phys.FBDef,     // Defaults do Firebird
  FireDAC.Stan.Def,
  FireDAC.Stan.Pool,
  FireDAC.DApt;

type
  /// 
  ///   Factory de connection Firebird via FireDAC.
  /// 
  TFirebirdConnectionFactory = class
  public
    /// 
    ///   Cria e configura uma connection Firebird.
    /// 
    /// Caminho completo do arquivo .fdb
    /// User (default: SYSDBA)
    /// Senha do banco
    /// Connection FireDAC configurada e aberta
    class function CreateConnection(
      const ADatabasePath: string;
      const AUserName: string = 'SYSDBA';
      const APassword: string = 'masterkey'
    ): TFDConnection;

    /// 
    ///   Cria uma connection via Embedded Server (sem fbserver).
    /// 
    class function CreateEmbeddedConnection(
      const ADatabasePath: string
    ): TFDConnection;
  end;

implementation

uses
  System.SysUtils;

class function TFirebirdConnectionFactory.CreateConnection(
  const ADatabasePath: string;
  const AUserName: string;
  const APassword: string): TFDConnection;
begin
  if ADatabasePath.Trim.IsEmpty then
    raise EArgumentException.Create('ADatabasePath não pode ser vazio');

  Result := TFDConnection.Create(nil);
  try
    Result.DriverName := 'FB';
    Result.Params.Database := ADatabasePath;
    Result.Params.UserName := AUserName;
    Result.Params.Password := APassword;

    { Configurações recomendadas }
    Result.Params.Values['CharacterSet'] := 'UTF8';
    Result.Params.Values['Protocol'] := 'TCPIP';     // Local: 'Local'
    Result.Params.Values['Server'] := 'localhost';
    Result.Params.Values['Port'] := '3050';
    Result.Params.Values['SQLDialect'] := '3';        // ALWAYS Dialect 3
    Result.Params.Values['PageSize'] := '16384';      // 16KB recomendado

    { Opções do driver FireDAC }
    Result.FormatOptions.StrsTrim2Len := True;         // Trim CHAR para VARCHAR
    Result.FetchOptions.Mode := fmAll;                 // Fetch completo
    Result.ResourceOptions.AutoReconnect := True;      // Reconexão automática
    Result.TxOptions.Isolation := xiReadCommitted;     // Isolation default

    Result.Connected := True;
  except
    Result.Free;
    raise;
  end;
end;

class function TFirebirdConnectionFactory.CreateEmbeddedConnection(
  const ADatabasePath: string): TFDConnection;
begin
  Result := TFDConnection.Create(nil);
  try
    Result.DriverName := 'FB';
    Result.Params.Database := ADatabasePath;

    { Embedded: sem servidor, sem user/password obrigatórios no FB3+ }
    Result.Params.Values['Protocol'] := 'Local';
    Result.Params.Values['CharacterSet'] := 'UTF8';
    Result.Params.Values['SQLDialect'] := '3';

    Result.Connected := True;
  except
    Result.Free;
    raise;
  end;
end;

FDPhysFBDriverLink — Configure Client Library

uses
  FireDAC.Phys.FBWrapper,
  FireDAC.Phys.FB;

var
  LDriverLink: TFDPhysFBDriverLink;
begin
  LDriverLink := TFDPhysFBDriverLink.Create(nil);
  try
    { Apontar fbclient.dll customizado (32/64-bit) }
    LDriverLink.VendorLib := 'C:\Firebird\fbclient.dll';

    { Embedded: usar fbclient.dll local ao .exe }
    // LDriverLink.VendorLib := ExtractFilePath(ParamStr(0)) + 'fbclient.dll';
  finally
    { DriverLink geralmente vive por toda a aplicação — criar no DataModule }
  end;
end;

Connection Pooling

{ No FDManager ou no Connection Definition }
FDManager.ConnectionDefs.AddConnectionDef;
with FDManager.ConnectionDefs.ConnectionDefByName('FB_POOL') do
begin
  DriverID := 'FB';
  Database := 'C:\Data\MeuBanco.fdb';
  UserName := 'SYSDBA';
  Password := 'masterkey';
  Params.Values['CharacterSet'] := 'UTF8';
  Params.Values['Pooled'] := 'True';
  Params.Values['POOL_MaximumItems'] := '50';
  Params.Values['POOL_CleanupTimeout'] := '30000';
  Params.Values['POOL_ExpireTimeout'] := '90000';
end;

Dialects — ALWAYS Dialect 3

| Feature | Dialect 1 | Dialect 3 | |---------|-----------|-----------| | DATE | Includes time | Date only (use TIMESTAMP for date+time) | | "Identificadores" | Syntax error | Allows case-sensitive names with double quotes | | Numerical precision | DOUBLE PRECISION | NUMERIC(18, x) up to 18 digits | | Recommendation | ❌ Legacy | ✅ Mandatory for new projects |

> ⚠️ Rule: Always SQLDialect := 3. Dialect 1 is legacy from InterBase and causes ambiguities with DATE.

Data Types — Firebird Mapping ↔ Delphi

| Firebird | Delphi (FireDAC) | Note | |----------|------------------|------------| | INTEGER | ftInteger / AsInteger | 32-bit | | BIGINT | ftLargeint / AsLargeInt | 64-bit | | SMALLINT | ftSmallint / AsSmallInt | 16-bit | | VARCHAR(N) | ftString / AsString | Use with CHARACTER SET UTF8 | | CHAR(N) | ftFixedChar | Fill with spaces — prefer VARCHAR | | NUMERIC(P,S) | ftBCD / AsCurrency | Monetary values ​​| | DOUBLE PRECISION| ftFloat / AsFloat | Ponto flutuante | | DATE | ftDate / AsDateTime | Date only (Dialect 3) | | TIME | ftTime / AsDateTime | Just in time | | TIMESTAMP | ftDateTime / AsDateTime | Data + Hora | | BOOLEAN (FB3+) | ftBoolean / AsBoolean | TRUE/FALSE native | | BLOB SUB_TYPE TEXT | ftMemo / AsString | Texto grande (CLOB) | | BLOB SUB_TYPE 0 | ftBlob / AsBytes | Binary data |

Generators (Sequences)

Create Generator

/* Generator clássico (todas as versões) */
CREATE GENERATOR GEN_CUSTOMER_ID;

/* Sequence (Firebird 3+, mais moderno) */
CREATE SEQUENCE SEQ_CUSTOMER_ID;

Get Next Value in Delphi

/// 
///   Obtém o próximo valor de um generator Firebird.
/// 
function GetNextGeneratorValue(
  AConnection: TFDConnection;
  const AGeneratorName: string): Int64;
var
  LQuery: TFDQuery;
begin
  LQuery := TFDQuery.Create(nil);
  try
    LQuery.Connection := AConnection;
    LQuery.SQL.Text := 'SELECT GEN_ID(' + AGeneratorName + ', 1) FROM RDB$DATABASE';
    LQuery.Open;
    Result := LQuery.Fields[0].AsLargeInt;
  finally
    LQuery.Free;
  end;
end;

/// 
///   Alternativa moderna com NEXT VALUE FOR (Firebird 3+).
/// 
function GetNextSequenceValue(
  AConnection: TFDConnection;
  const ASequenceName: string): Int64;
var
  LQuery: TFDQuery;
begin
  LQuery := TFDQuery.Create(nil);
  try
    LQuery.Connection := AConnection;
    LQuery.SQL.Text := 'SELECT NEXT VALUE FOR ' + ASequenceName + ' FROM RDB$DATABASE';
    LQuery.Open;
    Result := LQuery.Fields[0].AsLargeInt;
  finally
    LQuery.Free;
  end;
end;

IDENTITY Columns (Firebird 3+)

/* Auto-increment nativo — dispensa generator manual */
CREATE TABLE customers (
  id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  name VARCHAR(100) NOT NULL
);

/* Para pegar o ID gerado após INSERT: */
INSERT INTO customers (name) VALUES ('João') RETURNING id;

RETURNING in Delphi (get ID after Insert)

procedure TFirebirdCustomerRepository.Insert(ACustomer: TCustomer);
var
  LQuery: TFDQuery;
begin
  LQuery := TFDQuery.Create(nil);
  try
    LQuery.Connection := FConnection;
    LQuery.SQL.Text :=
      'INSERT INTO customers (name, cpf, email) ' +
      'VALUES (:name, :cpf, :email) RETURNING id';
    LQuery.ParamByName('name').AsString := ACustomer.Name;
    LQuery.ParamByName('cpf').AsString := ACustomer.Cpf;
    LQuery.ParamByName('email').AsString := ACustomer.Email;
    LQuery.Open; { Open, não ExecSQL — pois RETURNING retorna dados }
    ACustomer.Id := LQuery.Fields[0].AsInteger;
  finally
    LQuery.Free;
  end;
end;

Stored Procedures in Firebird

Selectable (returns resultset — uses SUSPEND)

CREATE OR ALTER PROCEDURE SP_CUSTOMERS_BY_STATUS (
  P_STATUS SMALLINT
)
RETURNS (
  O_ID       INTEGER,
  O_NAME     VARCHAR(100),
  O_CPF      VARCHAR(14),
  O_STATUS   SMALLINT
)
AS
BEGIN
  FOR SELECT id, name, cpf, status
      FROM customers
      WHERE status = :P_STATUS
      INTO :O_ID, :O_NAME, :O_CPF, :O_STATUS
  DO
    SUSPEND;  /* Retorna cada linha (como um cursor) */
END

Call in Delphi (treated as SELECT):

LQuery.SQL.Text := 'SELECT * FROM SP_CUSTOMERS_BY_STATUS(:P_STATUS)';
LQuery.ParamByName('P_STATUS').AsSmallInt := Ord(csActive);
LQuery.Open;

Executable (performs action — does not use SUSPEND)

CREATE OR ALTER PROCEDURE SP_DEACTIVATE_CUSTOMER (
  P_CUSTOMER_ID INTEGER
)
AS
BEGIN
  UPDATE customers SET status = 1 WHERE id = :P_CUSTOMER_ID;
END

Call in Delphi:

LQuery.SQL.Text := 'EXECUTE PROCEDURE SP_DEACTIVATE_CUSTOMER(:P_CUSTOMER_ID)';
LQuery.ParamByName('P_CUSTOMER_ID').AsInteger := ACustomerId;
LQuery.ExecSQL;

Execute Block (Anonymous SQL with PSQL)

/* Útil para lotes e scripts sem criar procedure permanente */
EXECUTE BLOCK (P_LIMIT INTEGER = :P_LIMIT)
RETURNS (O_NAME VARCHAR(100), O_TOTAL INTEGER)
AS
BEGIN
  FOR SELECT name, COUNT(*) FROM orders
      GROUP BY name
      HAVING COUNT(*) > :P_LIMIT
      INTO :O_NAME, :O_TOTAL
  DO
    SUSPEND;
END

Domains (Reusable Types)

/* Domínios centralizam validações e tipos no schema */
CREATE DOMAIN DM_ID        AS INTEGER NOT NULL;
CREATE DOMAIN DM_NAME      AS VARCHAR(100) NOT NULL;
CREATE DOMAIN DM_CPF       AS VARCHAR(14);
CREATE DOMAIN DM_EMAIL     AS VARCHAR(150);
CREATE DOMAIN DM_MONEY     AS NUMERIC(15, 2) DEFAULT 0;
CREATE DOMAIN DM_STATUS    AS SMALLINT DEFAULT 0 CHECK (VALUE IN (0, 1, 2));
CREATE DOMAIN DM_BOOLEAN   AS SMALLINT DEFAULT 0 CHECK (VALUE IN (0, 1)); /* Firebird 2.5 */
/* Firebird 3+: usar BOOLEAN nativo em vez de DM_BOOLEAN */

CREATE TABLE customers (
  id     DM_ID,
  name   DM_NAME,
  cpf    DM_CPF,
  email  DM_EMAIL,
  status DM_STATUS,
  PRIMARY KEY (id)
);

##Triggers

/* Trigger para auto-increment com Generator */
CREATE OR ALTER TRIGGER TRG_CUSTOMER_BI FOR customers
  ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
  IF (NEW.id IS NULL OR NEW.id = 0) THEN
    NEW.id = GEN_ID(GEN_CUSTOMER_ID, 1);
END

/* Trigger de auditoria */
CREATE OR ALTER TRIGGER TRG_CUSTOMER_AU FOR customers
  ACTIVE AFTER UPDATE POSITION 0
AS
BEGIN
  INSERT INTO audit_log (table_name, record_id, action, changed_at)
    VALUES ('customers', NEW.id, 'UPDATE', CURRENT_TIMESTAMP);
END

Transactions and Isolation Levels

Isolation Levels in Firebird

| Level | FireDAC | Usage | |-------|---------|-----| | Read Committed | xiReadCommitted | ✅ Standard — reads committed data, without dirty reads | | Snapshot (Concurrency) | xiSnapshot | Reports — consistent view of START momentum | | Snapshot Table Stability | xiSerializable | Rare — exclusive lock on table |

Manual Transaction Control

/// 
///   Executa operaction dentro de transaction explícita.
/// 
procedure ExecuteInTransaction(AConnection: TFDConnection; AProc: TProc);
begin
  AConnection.StartTransaction;
  try
    AProc;
    AConnection.Commit;
  except
    AConnection.Rollback;
    raise;
  end;
end;

{ Uso }
ExecuteInTransaction(FConnection,
  procedure
  begin
    FCustomerRepo.Insert(LCustomer);
    FOrderRepo.Insert(LOrder);
    FStockRepo.DecreaseStock(LOrder.Items);
  end
);

Transaction with Specific Isolation Level

var
  LTransaction: TFDTransaction;
begin
  LTransaction := TFDTransaction.Create(nil);
  try
    LTransaction.Connection := FConnection;
    LTransaction.Options.Isolation := xiSnapshot; { Leitura consistente }
    LTransaction.Options.ReadOnly := True;
    LTransaction.StartTransaction;
    try
      { Queries de relatório aqui — snapshot imutável }
      LTransaction.Commit;
    except
      LTransaction.Rollback;
      raise;
    end;
  finally
    LTransaction.Free;
  end;
end;

Event Alerter (Bank Events)

/* No Firebird: */
CREATE OR ALTER TRIGGER TRG_ORDER_NOTIFY FOR orders
  ACTIVE AFTER INSERT POSITION 0
AS
BEGIN
  POST_EVENT 'NEW_ORDER';
END

{ No Delphi: escutar eventos do banco }
uses
  FireDAC.Phys.FB; // TFDPhysFBEventAlerter

var
  LAlerter: TFDEventAlerter;
begin
  LAlerter := TFDEventAlerter.Create(nil);
  try
    LAlerter.Connection := FConnection;
    LAlerter.Names.Text := 'NEW_ORDER';
    LAlerter.Options.Timeout := 0;  { Sem timeout — espera indefinidamente }
    LAlerter.OnAlert := HandleNewOrderEvent;
    LAlerter.Active := True;
  finally
    { Manter vivo enquanto a aplicação rodar — liberação no Destroy }
  end;
end;

procedure TMyService.HandleNewOrderEvent(ASender: TFDCustomEventAlerter;
  const AEventName: string; const AArgument: Variant);
begin
  if AEventName = 'NEW_ORDER' then
    RefreshOrderList;
end;

Schema Creation — Migration Script

/* migration_001_initial_schema.sql */

/* ===== Domains ===== */
CREATE DOMAIN DM_ID       AS INTEGER NOT NULL;
CREATE DOMAIN DM_NAME     AS VARCHAR(100) NOT NULL;
CREATE DOMAIN DM_CPF      AS VARCHAR(14);
CREATE DOMAIN DM_EMAIL    AS VARCHAR(150);
CREATE DOMAIN DM_MONEY    AS NUMERIC(15,2) DEFAULT 0 NOT NULL;
CREATE DOMAIN DM_STATUS   AS SMALLINT DEFAULT 0 CHECK (VALUE BETWEEN 0 AND 2);
CREATE DOMAIN DM_MEMO     AS BLOB SUB_TYPE TEXT SEGMENT SIZE 4096;

/* ===== Generators ===== */
CREATE GENERATOR GEN_CUSTOMER_ID;
CREATE GENERATOR GEN_PRODUCT_ID;
CREATE GENERATOR GEN_ORDER_ID;
CREATE GENERATOR GEN_ORDER_ITEM_ID;

/* ===== Tables ===== */
CREATE TABLE customers (
  id       DM_ID,
  name     DM_NAME,
  cpf      DM_CPF,
  email    DM_EMAIL,
  status   DM_STATUS,
  notes    DM_MEMO,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT PK_CUSTOMER PRIMARY KEY (id),
  CONSTRAINT UQ_CUSTOMER_CPF UNIQUE (cpf)
);

CREATE TABLE products (
  id         DM_ID,
  name       DM_NAME,
  price      DM_MONEY,
  stock_qty  INTEGER DEFAULT 0 NOT NULL,
  status     DM_STATUS,
  CONSTRAINT PK_PRODUCT PRIMARY KEY (id)
);

CREATE TABLE orders (
  id            DM_ID,
  customer_id   INTEGER NOT NULL,
  order_date    TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
  total_amount  DM_MONEY,
  status        DM_STATUS,
  CONSTRAINT PK_ORDER PRIMARY KEY (id),
  CONSTRAINT FK_ORDER_CUSTOMER FOREIGN KEY (customer_id)
    REFERENCES customers (id) ON DELETE RESTRICT ON UPDATE CASCADE
);

CREATE TABLE order_items (
  id          DM_ID,
  order_id    INTEGER NOT NULL,
  product_id  INTEGER NOT NULL,
  quantity    INTEGER NOT NULL CHECK (quantity > 0),
  unit_price  DM_MONEY,
  total_price DM_MONEY,
  CONSTRAINT PK_ORDER_ITEM PRIMARY KEY (id),
  CONSTRAINT FK_ITEM_ORDER FOREIGN KEY (order_id)
    REFERENCES orders (id) ON DELETE CASCADE,
  CONSTRAINT FK_ITEM_PRODUCT FOREIGN KEY (product_id)
    REFERENCES products (id) ON DELETE RESTRICT
);

/* ===== Indices ===== */
CREATE INDEX IDX_CUSTOMER_NAME    ON customers (name);
CREATE INDEX IDX_ORDER_DATE       ON orders (order_date);
CREATE INDEX IDX_ORDER_CUSTOMER   ON orders (customer_id);
CREATE INDEX IDX_ITEM_ORDER

…

## Source & license

This open-source skill is cataloged on AgentStack and links to its original source — we do not rehost the code.

- **Author:** [delphicleancode](https://github.com/delphicleancode)
- **Source:** [delphicleancode/delphi-spec-kit](https://github.com/delphicleancode/delphi-spec-kit)
- **License:** MIT
- **Homepage:** https://inovefast.com.br

Install and usage instructions live in the source repository linked above.