Downloads 286this week

GitHub stars 4

Extension repository on GitHub

Extension descriptor (YAML)

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.

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:[email protected]: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.

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 (StorageExtension::Register API)

Added Functions

Overloaded Functions

This extension does not add any function overloads.

Added Types

This extension does not add any types.

Added Settings