Firebird Dialect

Firebird Dialect Overview Firebird is a lightweight, open-source relational database derived from InterBase. In this ORM, the Firebird dialect (FirebirdDialect) is implemented as a SQL-generation layer: it extends PostgresDialect to reuse its query-builder scaffolding (WHERE/ORDER BY compilation, parameter binding, JOIN/include handling, CTEs, index DDL, etc.) and overrides only the pieces of grammar where Firebird's SQL differs from PostgreSQL. Practically, this means: The dialect's job is to emit correct Firebird SQL. The bundled tests exercise every override synchronously, with query() stubbed, so no live database is required to verify the generated SQL. A real deployment would still need a dedicated Firebird driver (for example node-firebird) to actually execute these statements. The connection scaffolding is inherited from PostgresDialect and is not a Firebird wire protocol implementation. Treat this dialect as "SQL-generation verified," not "validated against a live Firebird server" (see Caveats). The default SQL service port is 3050 (Firebird's default), not PostgreSQL's 5432. It is applied in the constructor: FirebirdDialectOptions is identical in shape to PostgresDialectOptions — the dialect reuses the base connection option shape and only changes the default port. Connection You can also construct the dialect directly: Firebird-specific grammar Row limiting: FIRST n SKIP m (not LIMIT/OFFSET) Firebird has no trailing LIMIT ... OFFSET ... clause. Instead it places FIRST n SKIP m immediately after the SELECT keyword. The dialect neutralizes the base buildLimitOffset (it returns an empty string) and injects the FIRST/SKIP fragment right after the top-level SELECT. emits: Limit-only omits SKIP: The injection targets the top-level SELECT at parenthesis depth 0, so it works correctly with JOINs (include) and leaves CTE inner SELECTs untouched: For UPDATE and DELETE, Firebird limits affected rows with ROWS n (not LIMIT n). The base builder's LIMIT n fragment is rewritten in place: Auto-increment: GENERATED BY DEFAULT AS IDENTITY Firebird 3+ uses the SQL-standard GENERATED BY DEFAULT AS IDENTITY clause rather than PostgreSQL's SERIAL. Auto-increment INTEGER/BIGINT columns map accordingly in both getDataTypeSql and the column/DDL builders. getIdentityColumnSql builds a full identity-column definition: CREATE TABLE emits identity columns (never SERIAL): Generators / sequences: createGenerator Firebird's sequence objects were historically called "generators." The createGenerator(name) helper emits the modern, SQL-standard CREATE SEQUENCE spelling that Firebird accepts: Upsert: UPDATE OR INSERT ... MATCHING Firebird has no PostgreSQL INSERT ... ON CONFLICT. It uses UPDATE OR INSERT INTO t (...) VALUES (...) MATCHING (...), where MATCHING names the key columns. buildUpsertQuery emits exactly this form: When conflictFields is omitted, the MATCHING clause is dropped and Firebird matches on the table's primary key: RETURNING is supported on upserts: buildInsertQuery with { upsert: true } produces the same UPDATE OR INSERT form rather than an INSERT: Because UPDATE OR INSERT always overwrites every listed column on a match, updateOnDuplicate (a PostgreSQL-style subset of columns to update) has no Firebird equivalent and is ignored. Plain INSERT is unchanged and supports RETURNING and inline Literal values: Column DDL: no COLUMN keyword Firebird's ALTER TABLE omits the COLUMN keyword that PostgreSQL uses. Adding is ADD col type; dropping is DROP col: Column definitions place DEFAULT before NOT NULL (a Firebird requirement), and identity columns take no default: Inline FOREIGN KEY and CHECK table constraints are supported: Note: Firebird does not support CREATE TABLE IF NOT EXISTS, so the ifNotExists table option is ignored by buildCreateTableSQL. Type mapping getDataTypeSql overrides the mappings that differ from PostgreSQL and delegates everything else to the base dialect. ORM type (key) | Firebird SQL | Notes | ------------------ | ----------------------------------------- | ----- | STRING | VARCHAR(n) | length defaults to 255 | CHAR | CHAR(n) | length defaults to 1 | TEXT | BLOB SUBTYPE TEXT | Firebird has no TEXT type | INTEGER | INTEGER / INTEGER GENERATED BY DEFAULT AS IDENTITY | identity when autoIncrement | BIGINT | BIGINT / BIGINT GENERATED BY DEFAULT AS IDENTITY | identity when autoIncrement | FLOAT | FLOAT | | DOUBLE | DOUBLE PRECISION | | DECIMAL | DECIMAL(precision,scale) | defaults to DECIMAL(10,0) | BOOLEAN | BOOLEAN | | DATE | TIMESTAMP / TIMESTAMP(precision) | ORM DATE is a timestamp | DATEONLY | DATE | | TIME | TIME / TIME(precision) | | BLOB | BLOB | | JSON / JSONB | BLOB SUBTYPE TEXT | no native JSON type | Any other type falls through to PostgresDialect.getDataTypeSql. Caveats Identifier case. Firebird upper-cases unquoted identifiers, while a double-quoted identifier becomes case-sensitive. This dialect keeps the base's double-quote quoting ("identifier"), which preserves case as written. Be aware that mixing quoted and unquoted references to the same object in hand-written SQL can produce case mismatches on a live server. RETURNING is supported on singleton INSERT / UPDATE / DELETE / UPDATE OR INSERT statements, so the inherited RETURNING handling is kept (RETURNING or RETURNING "col", ...). updateOnDuplicate is ignored for upserts — Firebird's UPDATE OR INSERT overwrites every listed column (see above). Verification status. All grammar overrides above are SQL-generation verified by the connection-free tests (tests/dialects/firebird.test.ts and tests/dialects/firebird-crud.test.ts), which stub query() and assert on the emitted SQL strings. They have not been validated end-to-end against a live Firebird server, and executing them requires wiring in a real Firebird driver. Treat this dialect as a SQL-generation layer, not a production-tested Firebird client. Related reading All dialects — what each engine supports Data types — how each type maps to this engine Querying — how a FindOptions becomes SQL