Search Shortcut cmd + k | ctrl + k
firebird
Downloads 825this week
GitHub stars 5
Extension descriptor (YAML)

The first Firebird extension in the DuckDB Community Extensions registry. Federated read-only access to Firebird (3.0/4.0/5.0) databases from DuckDB, with projection + filter pushdown, INT128 / DECIMAL(38) / TIMESTAMP_TZ support, native ATTACH, and CHARACTER SET NONE handling (default win1252; strict / iso8859_1 / blob also available) with filter-pushdown correctness on transcoded columns.

Maintainer(s): flozer

Installing and Loading 

INSTALL firebird FROM community;
LOAD firebird;

Example 

LOAD firebird;

-- 1. Live scan against a Firebird server.
SELECT * FROM firebird_scan(
    'firebird://APP_READONLY:secret@db.host:3050/srv:/data/prod.fdb?charset=UTF8',
    'EMPLOYEE') LIMIT 10;

-- 2. Projection + filter pushdown — only EMP_NO, FIRST_NAME and the
--    WHERE predicate are sent to Firebird.
SELECT EMP_NO, FIRST_NAME
  FROM firebird_scan('firebird://…', 'EMPLOYEE')
 WHERE DEPT_NO = '600'
   AND HIRE_DATE > DATE '2020-01-01';

-- 3. Discover the schema.
SELECT * FROM firebird_tables('firebird://…');

-- 4. Native ATTACH — every Firebird table reachable through DuckDB's catalog.
ATTACH 'firebird://APP_READONLY:secret@host/path/db.fdb' AS fb (TYPE firebird);
SELECT * FROM fb.main.EMPLOYEE WHERE DEPT_NO = '600';

-- 5. Firebird-native diagnostics (v0.6).
SELECT * FROM firebird_profile_table('fb.main.EMPLOYEE');
SELECT * FROM firebird_pool_stats('fb');

-- 6. Federated JOIN — Firebird ⋈ Parquet.
SELECT e.dept_no, COUNT(*), AVG(e.salary)
  FROM firebird_scan('firebird://…', 'EMPLOYEE') e
  JOIN read_parquet('s3://lake/departments/*.parquet') d
   ON e.dept_no = d.dept_no
 GROUP BY e.dept_no;

-- 7. Legacy database declared CHARACTER SET NONE. Firebird does
--    NOT transliterate NONE columns to UTF-8 on the wire; pick
--    the encoding the writing application used.
SELECT * FROM firebird_scan(
    'C:/legacy/company.fdb',
    'TABENTRADASAIDA',
    none_encoding='win1252');

About firebird

duckdb-firebird exposes Firebird tables as DuckDB tables, with the DuckDB optimiser pushing projection and filter predicates down to the Firebird server. Big aggregations still happen inside DuckDB's vectorised executor; you just stop maintaining a parallel ETL pipeline to export Firebird data to Parquet first.

Surface area

  • firebird_scan(conn, table [, named params]) — single-table scan with projection + filter pushdown and optional PK-range parallel scan.
  • firebird_tables(conn) — list user tables (and views, external tables, GTTs) with PK info.
  • firebird_attach_sql(conn[, schema]) — emits the DDL for a lightweight view-based attach when you don't want the full StorageExtension lifetime.
  • ATTACH 'firebird://…' AS fb (TYPE firebird) — native read-only catalog with SELECT * FROM fb.main.TABLE, federated joins, DESCRIBE, case-insensitive lookup, and connection pooling.
  • firebird_profile_table('fb.main.TABLE') — v0.6 factual diagnostics for PKs, indexes, filter/watermark candidates, view risk, and recommended partitions.
  • firebird_pool_stats('fb') — v0.6 connection-pool counters for one attached Firebird catalog.
  • Metadata Bridge + diagnostics (v1.0.x): firebird_foreign_keys, firebird_indexes, firebird_generators, firebird_domains, firebird_computed_columns, firebird_dependencies, firebird_comments, firebird_explain_pushdown, firebird_type_audit, firebird_health, firebird_index_profile — Firebird catalog and server introspection without leaving DuckDB.
  • Every firebird_* function is discoverable via duckdb_functions() with real parameter names, a description, a runnable example, and categories (v1.0.2), so SQL-connected agents and tools can find and use them without reading the docs.

Pushdown

  • Projection: only requested columns travel the wire.
  • Filters: =, <>, <, >, <=, >=, IS [NOT] NULL, BETWEEN, IN, AND/OR, LIKE 'prefix%' translate to Firebird SQL. Anything else stays in DuckDB above the scan.
  • PK-range parallel scan: opt-in via the partitions=N named parameter — recommended for remote / Classic Firebird servers where parallelism is cheap.
  • Manual row_limit=N: emits Firebird's ROWS N directly.

Type mapping

Firebird 3 + 4 + 5 server compatibility:

  • INTEGER, BIGINT, SMALLINT, CHAR(N), VARCHAR(N), NUMERIC/DECIMAL(p, s) (up to 38 digits), FLOAT, DOUBLE, DATE, TIME, TIMESTAMP, BOOLEAN — exact mapping.
  • INT128 → HUGEINT; DECIMAL(p > 18, s) → DECIMAL(38, s).
  • TIMESTAMP WITH TIME ZONE (including the FB4 extended-TZ form) → DuckDB TIMESTAMP WITH TIME ZONE (UTC instant preserved).
  • TIME WITH TIME ZONE → DuckDB TIME WITH TIME ZONE.
  • BLOB SUB_TYPE 1 (text) → VARCHAR; other BLOBs → BLOB.
  • DECFLOAT(16) / DECFLOAT(34) → lossless VARCHAR via server-side CAST(... AS VARCHAR(64)) (v0.6).

Connection-string forms 

firebird://USER:PASS@HOST:PORT/DB_PATH?charset=UTF8&dialect=3&role=…
user=APP_READONLY;password=secret;database=server:/data/db.fdb;charset=UTF8

A bare path (/var/lib/firebird/test.fdb, C:/data/prod.fdb) is also accepted for local databases.

Override the user / password / charset / role / dialect / partitions / row_limit at call time via named parameters.

Charset handling

DuckDB stores strings as UTF-8 internally. The extension only accepts UTF8, UTF-8, NONE, or OCTETS for the client charset; anything else is rejected at bind time.

Databases declared with a real character set (WIN1252, ISO8859_1, UTF8, …) round-trip cleanly under the default charset=UTF8: Firebird transliterates server-side, so São Paulo, Açúcar, Coração arrive as valid UTF-8 without extra configuration.

Databases (or individual columns) declared CHARACTER SET NONE are different: Firebird returns the raw bytes the writing application stored, with no transliteration. The extension's default win1252 mode decodes the bytes used by many legacy Brazilian and Western-European ERPs. The caller can still pick the encoding the source application used:

  • none_encoding='win1252' (default) — decode bytes as Windows-1252 → UTF-8.
  • none_encoding='strict' — accept only valid UTF-8.
  • none_encoding='iso8859_1' (alias 'latin1') — decode bytes as ISO-8859-1 → UTF-8.
  • none_encoding='blob' — surface NONE text columns as DuckDB BLOB (raw bytes).

The option is accepted both by firebird_scan(…) and by the ATTACH ... (TYPE firebird, none_encoding 'win1252') form. While none_encoding != 'strict', filter pushdown on NONE text columns is deliberately disabled — the SQL literal we'd send is UTF-8 and would not match the raw bytes server-side. DuckDB applies the post-transcode text filter above the scan.

Verified against

  • Firebird 3.0 (apt firebird3.0-server, CI fixture)
  • Firebird 4.0.x (firebirdsql/firebird:4-noble)
  • Firebird 5.0.4 (Windows local + firebirdsql/firebird:5-noble)
  • DuckDB v1.5.3 - v1.5.5 (StorageExtension::Register API)

Added Functions

function_name function_type description comment examples
firebird_attach_sql table Returns one CREATE VIEW statement per Firebird table, each wrapping firebird_scan(), for a view-based workflow instead of a storage ATTACH. NULL [SELECT sql FROM firebird_attach_sql('database=C:/data/erp.fdb user=APP_READONLY password=secret');]
firebird_comments table Lists RDB$DESCRIPTION comments for user tables, views, and columns. NULL [SELECT * FROM firebird_comments('fb');]
firebird_computed_columns table Lists COMPUTED BY columns of all user tables with their expression source. NULL [SELECT * FROM firebird_computed_columns('fb');]
firebird_dependencies table Lists dependencies between database objects (tables, views, procedures, triggers, and others), down to column level when known. NULL [SELECT * FROM firebird_dependencies('fb');]
firebird_domains table Lists user-defined domains with formatted type, nullability, charset, and CHECK/DEFAULT clauses. NULL [SELECT * FROM firebird_domains('fb');]
firebird_explain_pushdown table Analyzes a SELECT over attached Firebird tables plan-only, reporting per scan what would be pushed down (filters, projection, ROWS paging, PK-range partitions) without executing the query. NULL [SELECT * FROM firebird_explain_pushdown('SELECT EMP_ID, EMP_NAME FROM fb.main.EMPLOYEE WHERE EMP_ID > 10');]
firebird_foreign_keys table Lists foreign-key constraints column by column with the real Firebird update and delete referential rules. NULL [SELECT * FROM firebird_foreign_keys('fb');]
firebird_generate_dbt_sources table Generates dbt sources.yml content for every Firebird table exposed by an attached catalog; the YAML is a starting point to review. NULL [SELECT yaml FROM firebird_generate_dbt_sources('fb');]
firebird_generators table Lists user generators/sequences with their initial value and current value (read per generator via GEN_ID(name, 0)). NULL [SELECT * FROM firebird_generators('fb');]
firebird_health table Returns a single-row database and server health diagnostic (engine and ODS version, dialect, charset, page size, transaction counters OIT/OAT/OST, attachments, warning codes) read from MON$ tables. NULL [SELECT * FROM firebird_health('fb');]
firebird_index_profile table Returns one row per index of a Firebird table (columns, uniqueness, activity, PK/FK backing, raw selectivity, structured alerts) plus unindexed filter candidates; a table with no indexes emits one synthetic row. NULL [SELECT * FROM firebird_index_profile('fb.main.CUSTOMER');]
firebird_indexes table Lists all user indexes with per-segment columns, uniqueness, activity, and expression source for expression indexes. NULL [SELECT * FROM firebird_indexes('fb');]
firebird_last_query table Returns telemetry for the most recent Firebird scan in the current session: remote SQL, pushed and residual filters with reasons, pushed paging, timing, rows read, and parallel scan and connection-reuse details. NULL [SELECT * FROM firebird_last_query();]
firebird_pool_stats table Returns config, idle-queue size, and lifetime counters for the connection pool of one attached Firebird catalog, by explicit alias; it never leases a connection. NULL [SELECT * FROM firebird_pool_stats('fb');]
firebird_profile_table table Returns a single-row factual diagnostic for one table or view behind an attached catalog: primary key, indexes, watermark and filter candidates, full-scan risk, advisory recommended_partitions, and structured alerts. NULL [SELECT * FROM firebird_profile_table('fb.main.CUSTOMER');]
firebird_query_log table Returns the bounded per-session log of Firebird scans (opt-in via SET firebird_query_log_size = N), with the same telemetry columns as firebird_last_query(). NULL [SELECT * FROM firebird_query_log();]
firebird_scan table Reads a Firebird table into DuckDB with projection and predicate pushdown, optional parallel PK-range partitioning (partitions=N), ROWS paging, and CHARACTER SET NONE decoding. NULL [SELECT * FROM firebird_scan('database=C:/data/erp.fdb user=APP_READONLY password=secret', 'CUSTOMER');]
firebird_tables table Lists the Firebird tables visible to a connection, without attaching the database. NULL [SELECT * FROM firebird_tables('database=C:/data/erp.fdb user=APP_READONLY password=secret');]
firebird_type_audit table Reports per-column type and charset fidelity findings (NONE charset, DECFLOAT as VARCHAR, INT128, timezone types, text BLOBs) for an attached catalog; only columns with a caveat are emitted. NULL [SELECT * FROM firebird_type_audit('fb');]

Overloaded Functions

This extension does not add any function overloads.

Added Types

This extension does not add any types.

Added Settings

name description input_type scope aliases
firebird_pool_enabled Enable the per-ATTACH FirebirdConnectionPool. When false, every Acquire opens a fresh connection and Release destroys it. BOOLEAN GLOBAL []
firebird_pool_idle_timeout_ms How long (in milliseconds) a released connection may sit in the idle queue before it is discarded on the next Acquire. 0 = no expiry (default). Clock starts at Release(). BIGINT GLOBAL []
firebird_pool_max_size Maximum number of idle connections kept in the pool. 0 = unlimited (default). Caps the idle queue, not active leases. BIGINT GLOBAL []
firebird_query_log_size Maximum entries kept by firebird_query_log() per session. 0 disables the log (default). BIGINT GLOBAL []