DuckDB Dialect

DuckDB Dialect Overview DuckDB is an embedded, in-process OLAP (analytical) database. Its deployment model mirrors SQLite — a single local file or an in-memory instance, with no server, host, port, or authentication — but its SQL engine is columnar and analytics-first: real window functions, recursive CTEs, rich composite types (LIST/STRUCT/MAP/UNION), native sequences, a real extension system (INSTALL/LOAD), and first-class Parquet/CSV/JSON table functions. Key facts (from src/dialects/duckdb/index.ts): Dialect name is 'duckdb'; the driver library is the duckdb npm package. Embedded, single-process, single-user. There is no host/port/ username/password. Those fields are accepted on the config object for API parity with networked dialects but are silently ignored. The duckdb driver's public API is callback-based; the dialect wraps every call (Database, connection.all, connection.run, connection.exec) in Promises internally. Columnar/analytical engine: it is optimized for large scans and aggregations, not high-frequency single-row OLTP writes. Connection Options accepted by the dialect (DuckDBDialectOptions): storage — file path to the database, or ':memory:'. Defaults to ':memory:'. readonly — open the database in read-only mode (accessmode: READONLY). config — a Record<string, string> of DuckDB config options passed straight through to the Database constructor, e.g. { maxmemory: '4GB' }. Notes: The connect callback only resolves once the native handle has actually opened, so open failures (corrupt file, permission denied, read-only open against a locked writer) surface at connect() rather than on the first query. getDatabaseVersion() runs SELECT version(). Identifiers and value escaping DuckDB uses standard SQL literals, so escaping differs from MySQL-family dialects: Identifiers are double-quoted: escapeId('my col') → "my col" (embedded " doubled). Booleans escape to TRUE / FALSE (not 0/1). Date values escape to TIMESTAMP '2024-01-01 00:00:00.000'. Buffer values escape to a '\xNN...'::BLOB cast. JS arrays escape to DuckDB list literals: [1, 2, 3]. Positional parameters use ? placeholders (SQLite-style). Data types getDataTypeSql maps ORM type descriptors to DuckDB types: ORM type descriptor | Generated SQL | ------------------------------------------------ | -------------------------------------- | { key: 'STRING' } | VARCHAR(255) | { key: 'STRING', length: 100 } | VARCHAR(100) | { key: 'TEXT' } | VARCHAR | { key: 'INTEGER' } | INTEGER | { key: 'INTEGER', unsigned: true } | UINTEGER | { key: 'BIGINT' } | BIGINT | { key: 'BIGINT', unsigned: true } | UBIGINT | { key: 'FLOAT' } | REAL | { key: 'DOUBLE' } | DOUBLE | { key: 'DECIMAL', precision: 12, scale: 4 } | DECIMAL(12,4) | { key: 'BOOLEAN' } | BOOLEAN | { key: 'DATE' } | TIMESTAMP | { key: 'DATEONLY' } | DATE | { key: 'TIME' } | TIME | { key: 'BLOB' } | BLOB | { key: 'JSON' } / { key: 'JSONB' } | JSON | { key: 'UUID' } | UUID | { key: 'ENUM', values: ['a', 'b'] } | VARCHAR CHECK(col IN ('a','b')) | Note that DATE maps to TIMESTAMP (date + time), while DATEONLY maps to a bare DATE. Composite (nested) types DuckDB has genuine nested types, and the dialect emits them directly: { key: 'LIST', type: <elementType> } (or ARRAY) → <elementType>[], e.g. INTEGER[]. Falls back to VARCHAR[] when the element type is omitted. { key: 'STRUCT', fields: { a: <type>, ... } } → STRUCT(a TYPE, ...). { key: 'MAP', keyType, valueType } → MAP(keyType, valueType). { key: 'UNION', fields: { tag: <type>, ... } } → UNION(tag TYPE, ...). ENUM is emulated DuckDB has a native ENUM, but it requires a separately named type (CREATE TYPE ... AS ENUM (...)) with out-of-band lifecycle management. To keep column definitions self-contained, the dialect instead emulates enums as VARCHAR with a CHECK constraint (the same approach SQLite uses). Auto-increment and sequences DuckDB has no AUTOINCREMENT keyword — it uses native sequences. When a column is defined with autoIncrement: true, the dialect first creates a backing sequence (seq<table><column>) and defines the column with DEFAULT nextval('seq...'). Sequences are also exposed as a first-class, native feature: createIdentityColumn is emulated the same way (DuckDB has no GENERATED ... AS IDENTITY): it creates a sequence and sets the column default to nextval(...). Upsert (ON CONFLICT) DuckDB uses PostgreSQL/SQLite-style INSERT ... ON CONFLICT. Because the conflict target must be named explicitly, an upsert requires conflictFields (the PRIMARY KEY or UNIQUE columns) unless doNothing is set: updateOnDuplicate: ['v'] restricts the DO UPDATE SET list to those columns. doNothing: true emits ON CONFLICT DO NOTHING (with an optional target). conflictTargets accepts multiple chained ON CONFLICT (...) DO ... targets. RETURNING is supported (returning: true → RETURNING ). Omitting conflictFields on a DO UPDATE upsert throws: Transactions Top-level transactions use BEGIN TRANSACTION / COMMIT / ROLLBACK. Nested transactions are emulated with SAVEPOINT / RELEASE SAVEPOINT / ROLLBACK TO SAVEPOINT (see DuckDBTransaction). DuckDB-specific value-add: files as tables DuckDB's signature capability is querying Parquet/CSV/JSON files directly as tables, and exporting results back out. The dialect exposes builders that return table-function expressions usable in a FROM clause: Export a query straight to a file with copyTo: Reading remote data (S3 / httpfs) createExtension('httpfs') installs and loads the extension but does not supply credentials. Two mechanisms are exposed: Extensions Unlike most dialects, DuckDB's extension system is implemented for real: There is no uninstall — dropExtension() throws. To stop using an extension, simply do not LOAD it in future connections. Attaching other databases DuckDB can attach additional database files (DuckDB, or SQLite/Postgres via the respective extension) and query them as alias.table: CREATE DATABASE is emulated via ATTACH (DuckDB has no CREATE DATABASE statement — a "database" is a file). SELECT EXCLUDE (...) is also supported through the attribute builder for column exclusion. Honest limitations Several features from other dialects are not supported and throw a clear error rather than silently doing nothing: Triggers — not supported (createTrigger/dropTrigger throw). Move reactive logic into application code. Stored procedures — no procedural language. createStoredProcedure, executeStoredProcedure, etc. throw; the closest analogue is a scalar/table MACRO or a view, which the error message points to. Materialized views — no CREATE MATERIALIZED VIEW. Use CREATE TABLE ... AS SELECT ... (re-run to refresh) or copyTo() a Parquet file plus readParquet(). Foreign Data Wrappers — PostgreSQL-only; all FDW methods throw. Row-level security / policies — not supported. User / role / privilege management — DuckDB is single-user; all user/role/grant methods throw. ALTER TABLE ADD FOREIGN KEY — not supported; define foreign keys at CREATE TABLE time instead. Partial indexes — CREATE INDEX ... WHERE ... is unsupported; addIndex with a where option throws. Fulltext / spatial indexes — require the fts / spatial extensions; createFulltextIndex / createSpatialIndex throw with guidance to use PRAGMA createftsindex(...) or the RTREE index type. Table-level partitioning is emulated (child tables + a metadata table + a UNION ALL view, like SQLite), since DuckDB's real partitioning lives at the storage layer via Hive-partitioned Parquet (copyTo/readParquet). changeOwner — there is no per-table ownership model to change. Caveats In-memory storage is ephemeral. With storage: ':memory:' (the default), all data is lost when the process exits. Use a file path to persist. Analytical, not OLTP. DuckDB shines at large scans and aggregations over columnar data; it is not designed for high-concurrency single-row writes. Single writer. As an embedded database, a file can have one read-write process at a time; other processes must open it read-only. Related reading All dialects — what each engine supports Data types — how each type maps to this engine Querying — how a FindOptions becomes SQL