TL;DR: duckdb -c "SELECT ... FROM 'file.csv'" runs SQL on a file directly. No server, no load step, no schema declaration.
Most shell users already have three data tools: jq for JSON, sqlite3 for structured data, ripgrep for text.
When the file is structured, is on disk, and you need data aggregation, doing the job with the usual tools requires a pipeline that you build by hand.
Take a JSON-lines file of events, representing the structured logs case (events.jsonl)...
{"user_id": "u0131", "event_type": "login", "ts": "2026-09-01"}
{"user_id": "u0058", "event_type": "login", "ts": "2026-09-01"}
{"user_id": "u0067", "event_type": "upgrade", "ts": "2026-09-01"}
{"user_id": "u0199", "event_type": "login", "ts": "2026-09-02"}
{"user_id": "u0193", "event_type": "login", "ts": "2026-09-02"}
{"user_id": "u0173", "event_type": "upgrade", "ts": "2026-09-02"}
...... and you want to know "how many events per user?". The usual pipeline is:
jq -r '.user_id' events.jsonl | sort | uniq -c | sort -rn | headIt works. But see what the pipeline is doing: extract one field with jq, sort the whole stream, count adjacent duplicates, sort again, and truncate. Six stages to get the result. And the moment the question changes (from "per user" to "per user AND per day", "only upgrade events", or "top user per country"), you rebuild the pipeline.
DuckDB is a single binary that takes the same question as SQL:
duckdb -c "SELECT user_id, count(*) AS n FROM 'events.jsonl' GROUP BY user_id ORDER BY n DESC LIMIT 5"The file is read directly, the structure is detected from the content, and the answer prints as a table. No server running, no database file to create, and no schema to declare. The query runs, prints the result, and exits.
Install duckdb via brew install duckdb on macOS. On Linux, download the binary from duckdb.org.
What "directly on the file" means
Using the FROM 'events.jsonl' clause, DuckDB reads the file and treats it as a table for the duration of the query. The same works for CSVs (users.csv for example):
user_id,country,plan
u0000,US,free
u0001,CH,free
u0002,DE,free
u0003,US,free
u0004,JP,free
u0005,DE,free
u0006,CH,pro
u0007,DE,free
u0008,DE,pro
u0009,DE,free
u0010,FR,free
...duckdb -c "SELECT country, count(*) AS n FROM 'users.csv' GROUP BY country ORDER BY n DESC"Notice that no declaration is needed. The header line and a sample of the CSV file are used by DuckDB to infer the column names and the data types, respectively. If you want to see what was inferred, use:
duckdb -c "DESCRIBE SELECT * FROM 'users.csv'"Any SQL you already know is now available against the file: WHERE for filtering, GROUP BY for aggregation, ORDER BY and LIMIT for shaping, joins for combining tables.
The jq-pipeline version of "events per user" hard-codes the question, while the DuckDB / SQL version is a query you edit.
Other benefits: cross-format join, format conversion
Let's say the data is in two files, in two different formats, and the question asked spans both; users are stored in a CSV file, events are stored in a Parquet file, and you want to get events per country. You would do:
duckdb -c "
SELECT u.country, e.event_type, count(*) AS hits
FROM 'users.csv' u
JOIN 'events.parquet' e ON u.user_id = e.user_id
GROUP BY u.country, e.event_type
ORDER BY hits DESC
LIMIT 20
"The job is done using one query, handling two formats, and without a load step in between. In the usual workflow, to do this, you open a Python notebook or write an import script; here you need just one CLI call.
And DuckDB writes the formats it reads, so converting from a format to another one is also one line. The following filters a big CSV file, and writes the matching rows as Parquet:
duckdb -c "COPY (SELECT * FROM 'big.csv' WHERE ts > '2026-01-01') TO 'filtered.parquet' (FORMAT PARQUET)"With DuckDB, the CLI is a converter as well as a query engine.
What DuckDB is not for
DuckDB is the right tool for ad-hoc queries on local data files. It is not:
A service database. No concurrent clients, no long-running process to point an app at. For a service, use postgres.
An OLTP store. It is an analytics engine; it is not for insert-heavy transactional workloads. For that, use sqlite3.
The tool for one-field extraction. Pulling a single field out of a small JSON file is still jq's job; pull DuckDB when you want aggregation, group-bys over whole files, cross-file joins.
Try it
brew install duckdb # or get the Linux binary from duckdb.org
duckdb -c "SELECT 1" # the smoke check
duckdb -c "DESCRIBE SELECT * FROM some.csv" # any CSV you haveThen take the aggregation question you last answered with a jq | sort | uniq -c chain, and write it as one SQL query instead.
One CLI trick
# Shell glob as the table: query many files as one
duckdb -c "SELECT filename, count(*) AS n FROM 'logs/2026-08-*.jsonl' GROUP BY filename ORDER BY n DESC"Every file matching the pattern is read as one logical table, and the filename pseudo-column tells you which rows came from which file. So the "which day has the most events" question, over a month of daily log files, is one query.
DuckDB is not in the book, but it is a natural addition to the modern CLI stack for those who need to manipulate structured data. If you want the full toolkit, The Modern CLI Stack — 13 Tools Brief is a free ~50-page PDF + EPUB covering mise, starship, zoxide, fzf, broot, ripgrep, fd, bat, eza, delta, tldr, atuin, lazygit.