duckdb

DuckDB and SQLite

DuckDB is the default engine for local analysis: it reads CSV, Parquet, and JSON directly, attaches SQLite files, and writes any of them back. SQLite stays the embedded store for applications; DuckDB is the tool you point at data.

Commands

duckdb -init /dev/null -batch -bail -c "SELECT name, score FROM 'people.csv' WHERE score > 80 ORDER BY score DESC, name LIMIT 20"
duckdb -init /dev/null -batch -bail -json -c "SELECT count(*) AS event_count FROM 'events/*.json'" # aggregate before displaying
duckdb -init /dev/null -batch -bail lab.duckdb -c "CREATE OR REPLACE TABLE people AS FROM 'people.csv'"
duckdb -init /dev/null -batch -bail -c "COPY (FROM 'people.csv') TO 'people.parquet'"        # durable derived data
duckdb -init /dev/null -batch -bail -c "ATTACH 'app.sqlite' AS s (TYPE sqlite, READ_ONLY); SELECT count(*) FROM s.users"
duckdb -init /dev/null -batch -bail -c "SUMMARIZE FROM 'people.parquet'"                   # column statistics
sqlite3 -init /dev/null -batch -bail -readonly app.sqlite '.schema'
sqlite3 -init /dev/null -batch -bail -noheader -list -readonly app.sqlite 'PRAGMA integrity_check'

Both interactive and batch invocations can load ~/.duckdbrc or ~/.sqliterc, including executable SQL and dot commands; this workstation ships neither, but other machines may. For reproducible scripts, skip those files with -init /dev/null, stop on errors with -batch -bail, and select output explicitly with -json, -csv, or -markdown; an output-mode flag alone does not suppress initialization. SQLite integrity checks return rows: require ok, because a successful process exit alone does not establish integrity.

Workflow

1. Look before querying: DESCRIBE FROM '<file>' and SUMMARIZE reveal types, nulls, and ranges; fix a wrong inference with read_csv('<file>', types={'id': 'BIGINT'}).
2. Bound displayed results: project needed columns, filter rows, and aggregate before returning data. Use an ordered LIMIT for a preview and label it as partial; calculate totals over the full filtered set. Export complete results with COPY when needed instead of printing every row.
3. Keep queries in files: duckdb -init /dev/null -batch -bail -f analysis.sql for anything longer than one line, committed next to the data description.
4. Persist derived data as Parquet: keep rebuildable .duckdb files out of Git and commit the SQL that produces them; use -readonly for inspection of an existing database.
5. Export for the reader: -markdown for a report, -json for another tool, COPY ... TO 'out.csv' (HEADER) for a spreadsheet.