DuckDB

sq’s DuckDB driver implements connectivity for DuckDB. It makes use of the backing duckdb/duckdb-go/v2 library, which statically links libduckdb. The driver implements all optional sq driver features.

Add source #

Use sq add to add a source. The location argument is the filepath to the DuckDB file. For example:

# Relative path
$ sq add ./sakila.duckdb

# Absolute path
$ sq add /Users/neilotoole/sakila.duckdb

sq can detect that a file is a DuckDB datafile via the .duckdb or .ddb extension, or by the DUCK magic header at byte offset 8. If auto-detection doesn’t trigger, specify the driver explicitly:

$ sq add --driver=duckdb ./sakila.duckdb

Use the duckdb:// scheme to pass connection parameters or to open an in-memory database:

# Persistent file via URI
$ sq add 'duckdb:///abs/path/sakila.duckdb'

# In-memory database
$ sq add 'duckdb://:memory:'

# File with connection parameters
$ sq add 'duckdb:///path/sakila.duckdb?memory_limit=4GB&threads=4'

Bundled extensions #

The driver statically links the standard set of in-tree DuckDB extensions. They are available immediately — no INSTALL or LOAD required:

Because the extensions are statically bundled, queries like the following work without any setup:

-- Query a Parquet file directly
SELECT * FROM read_parquet('file.parquet');

-- Query a remote CSV via HTTPS
SELECT * FROM read_csv_auto('https://example.com/data.csv');

-- Query an S3 object (set AWS credentials first)
SELECT * FROM read_parquet('s3://bucket/key.parquet');

Connection parameters #

Pass parameters as URL query strings after the file path:

$ sq add 'duckdb:///path/db.duckdb?memory_limit=4GB&threads=4'

DuckDB supports many more settings; the list above covers the parameters most commonly used from the CLI.

Read-only access by default #

For commands that don’t write to the source, sq opens DuckDB databases with access_mode=READ_ONLY. This applies to sq inspect, sq, sq diff, and sq ping.

The benefits: sq does not touch the file’s WAL or modification time, multiple sq processes can read the same file concurrently, and sq inspect works on files you have read-only access to.

Commands that write (sq tbl copy, sq tbl drop, sq tbl truncate) continue to open READ_WRITE.

For sq sql, the default remains READ_WRITE because the SQL statement is opaque to sq. Use sq sql --readonly (or --ro) to opt in to read-only mode.

To override the default for any command, set access_mode explicitly in the source location:

$ sq add 'duckdb:///data/inventory.duckdb?access_mode=READ_WRITE' --handle @inv

An explicit URL access_mode always wins over the implicit defaults for sq inspect, sq slq, sq diff, and sq ping. For sq sql --readonly (or --ro), the explicit flag and an explicit URL access_mode=READ_WRITE are treated as a contradiction and sq returns a conflict error: the flag says “do not write”, the URL says “open for writes”, and the user has to resolve the ambiguity before any open happens.

Type mapping #

Composite types (LIST, STRUCT, MAP) are currently stringified via Go’s default formatting (fmt.Sprintf("%v", v)). First-class JSON projection via DuckDB’s to_json(col) is planned as a follow-up — see #609.

Limitations #

• DuckDB enforces a single writer per database file. If two sq processes open the same .duckdb file simultaneously, the second will receive a lock error. For sq’s typical single-shot CLI usage this is rarely a problem, but scripts that parallelize sq against the same file should serialize writes. Read-only access (access_mode=READ_ONLY) from multiple processes is safe.