Csvql

[](https://github.com/melihbirim/csvql/actions/workflows/ci.yml) [](LICENSE.md) [](https://github.com/melihbirim/csvql/releases)

The analytical CSV query engine for AI agents.

Run SQL analytics — GROUP BY, aggregates, joins, time-series — on CSV files in place: no database, no import, no ingest. csvql ships as an MCP server, so an LLM can query a gigabyte file for a few hundred tokens instead of pasting it (impossible) into context. A single static binary written in Zig. Your data never leaves your machine.

> A database is something you load your data into. csvql is a query you run on the data where it already lives.

Read-only and on-prem by design. csvql only runs SELECT — it has no INSERT/UPDATE/DELETE/DROP and physically cannot modify your data. It makes zero network calls, needs no cloud, and runs fully air-gapped. Our next north star: the safe way to give AI agents query access to corporate data — run csvql next to the data on your own servers (read-only, nothing leaves the box) instead of shipping files out to an LLM.

Token economics: query files instead of pasting them

Pasting a 417 MB CSV into an LLM costs 230 million tokens — it fits no context window. Over MCP, the agent queries the file in place and gets back only the answer:

| Question an agent asks | Tokens used | | ---------------------- | ----------- | | "How many trips per cab type?" | 43 | | "Which year was busiest?" | 49 | | "Average fare by passenger count?" | 123 |

Same answers, ~1,000–500,000× fewer tokens — flat, regardless of file size. One command wires it into Claude: [csvql install](#setup). Measure it yourself: [bench/bench_tokens.py](bench/bench_tokens.py).

$ csvql "SELECT cab_type, COUNT(*) FROM 'trips.csv' GROUP BY cab_type"
cab_type,COUNT(*)
green,32447
yellow,967553
  0.05s — no import, queried straight off the file

Website · [Quick Start](#quick-start) · [Installation](#installation) · [Performance](#performance) · [SQL Reference](#sql-reference) · [Docs](#documentation)

---

Quick Start

csvql auto-detects SQL or simple mode from your input:

# SQL mode
csvql "SELECT name, salary FROM 'data.csv' WHERE age > 30 ORDER BY salary DESC LIMIT 10"

# Simple mode — same query, shorter syntax
csvql data.csv "name,salary" "age>30" 10 "salary:desc"

# Just browse a file
csvql data.csv

Unix Pipes

cat data.csv | csvql "SELECT name, age FROM '-' WHERE age > 25"
csvql "SELECT * FROM 'data.csv' WHERE status = 'active'" > output.csv
csvql "SELECT email FROM 'users.csv'" | wc -l

Flags

| Flag | Short | Description | | -------------------- | ----- | --------------------------------------------------- | | --no-header | | Suppress header row in output | | --no-input-header | | Treat the first row as data; auto-name columns c1..cN | | --delimiter | -d | Field delimiter (default ,). Use \t for TSV | | --json | | Output as a JSON array ([{...}, ...]) | | --jsonl | | Output as JSONL / NDJSON (one JSON object per line) | | --threads | | Worker threads for parallel execution; 0 uses automatic detection | | --version | -v | Show version | | --help | -h | Show help | | --mcp | | Start as an MCP server (stdio JSON-RPC transport) | | --root | | Confine file access to a directory (repeatable via commas) | | --audit | | Append a JSONL audit record per query (timestamp, SQL) |

# TSV file
csvql "SELECT name, salary FROM 'data.tsv'" -d $'\t'

# Pipe into another tool that expects no header
csvql "SELECT name, age FROM 'data.csv'" --no-header | awk -F, '{print $2}'

# TSV input, no header in output
cat data.tsv | csvql "SELECT * FROM '-'" -d $'\t' --no-header

Installation

Homebrew (macOS / Linux)

brew install melihbirim/csvql/csvql

Or in two steps if you plan to install multiple tools from this tap:

brew tap melihbirim/csvql
brew install csvql

> melihbirim/csvql is the tap (the formula repository), and the trailing /csvql is the formula name inside it.

Prebuilt Binaries

Download from GitHub Releases:

# macOS (Apple Silicon)
curl -L https://github.com/melihbirim/csvql/releases/latest/download/csvql-macos-aarch64.tar.gz | tar xz
sudo mv csvql-macos-aarch64 /usr/local/bin/csvql

# macOS (Intel)
curl -L https://github.com/melihbirim/csvql/releases/latest/download/csvql-macos-x86_64.tar.gz | tar xz
sudo mv csvql-macos-x86_64 /usr/local/bin/csvql

# Linux (x86_64)
curl -L https://github.com/melihbirim/csvql/releases/latest/download/csvql-linux-x86_64.tar.gz | tar xz
sudo mv csvql-linux-x86_64 /usr/local/bin/csvql

Build from Source

Requires Zig 0.13.0+ (tested with 0.15.2):

git clone https://github.com/melihbirim/csvql.git
cd csvql
zig build -Doptimize=ReleaseFast
sudo cp zig-out/bin/csvql /usr/local/bin/

Performance

1M rows, 35MB CSV, Apple M2 — all tools forced to output all rows (no display tricks):

| Query | csvql | DuckDB | Speedup | | --------------------------------- | ---------- | ------ | -------- | | WHERE + ORDER BY LIMIT 10 | 0.020s | 0.179s | 9x | | ORDER BY LIMIT 10 | 0.041s | 0.165s | 4x | | ORDER BY (all 1M rows) | 0.156s | 1.221s | 7.8x | | WHERE (full output) | 0.141s | 0.739s | 5.2x | | Full scan (all 1M rows) | 0.196s | 1.163s | 5.9x | | COUNT(*) GROUP BY (6 groups) | 0.060s | 0.110s | 1.8x | | SUM + AVG GROUP BY (6 groups) | 0.070s | 0.110s | 1.6x | | SUM(CASE WHEN) GROUP BY | 0.016s | 0.114s | 7.1x | | SELECT DISTINCT city (8 values) | 0.060s | 0.110s | 1.8x | | SELECT COUNT(*) scalar | 0.050s | 0.100s | 2x | | SELECT SUM(salary) scalar | 0.050s | 0.110s | 2.2x |

35x less memory than DuckDB (1.8MB vs 63.5MB).

5M rows, 173MB CSV, Apple M2 — output-format benchmark (full output, all rows matched, > /dev/null):

| Output format | csvql | DuckDB | Speedup | | -------------------------- | ---------- | ------ | -------- | | CSV | 0.100s | 0.354s | 3.5x | | JSON array (--json) | 0.164s | 0.434s | 2.6x | | JSONL / NDJSON (--jsonl) | 0.172s | 0.422s | 2.5x |

Outputs are semantically/byte-identical to DuckDB (verified: CSV byte-for-byte diff; JSONL byte-for-byte diff; JSON array Python-parsed row comparison).

Run the benchmark yourself: [bench/bench_all.sh --section formats](bench/bench_all.sh)

5M rows, 173MB CSV, Apple M2 — LIKE operator benchmark (CSV output, > /dev/null):

| Pattern | Description | csvql | DuckDB | Speedup | | ------------------------------ | ------------------------ | --------- | ------ | -------- | | WHERE name LIKE 'A%' | Prefix wildcard | 0.06s | 2.17s | ~36x | | WHERE city LIKE '%on' | Suffix wildcard | 0.06s | 1.12s | ~19x | | WHERE department LIKE '%ing' | Suffix, high selectivity | 0.07s | 2.54s | ~36x |

Row counts verified identical to DuckDB.

Run the benchmark yourself: [bench/bench_all.sh --section like](bench/bench_all.sh)

1M rows, 35MB CSV, Apple M2 — JOIN benchmark (hash-join, CSV output, > /dev/null):

| Query | csvql | DuckDB | Speedup | | ---------------------------------------------- | ---------- | ------ | --------- | | JOIN departments (1M × 6 rows) | 0.140s | 1.492s | 10.7x | | JOIN + WHERE d.region = 'West' (1M × 6) | 0.102s | 0.600s | 5.9x | | JOIN SELECT * (1M × 6, all cols) | 0.220s | 4.130s | 18.8x | | JOIN cities (1M × 8 rows) | 0.146s | 1.464s | 10.0x | | JOIN bonus_50k (1M × 50K rows, numeric key) | 0.104s | 0.276s | 2.7x |

Run the benchmark yourself: [bench/bench_all.sh --section join](bench/bench_all.sh)

NYC Taxi benchmark — 20M rows, 8 GB CSV, Apple M-series — the canonical Billion-Taxi-Rides queries on DuckDB's own dataset. Both engines query the raw uncompressed CSV directly (no preload into a native store), cold per run, best-of-5, warm OS cache:

| Query | csvql | DuckDB | Speedup | | ----------------------------------------------------------- | --------- | ------ | -------- | | Q01 COUNT(*) GROUP BY cab_type | 1.29s | 3.55s | 2.8x | | Q02 AVG(total_amount) GROUP BY passenger_count | 1.41s | 3.81s | 2.7x | | Q03 COUNT(*) GROUP BY passenger_count, year | 1.36s | 4.01s | 2.9x | | Q04 GROUP BY passenger_count, year, ROUND(distance) ... | 1.38s | 3.99s | 2.9x |

Results verified identical to DuckDB. The gap widens on smaller files — ~10x on the 417 MB / 1M-row sample, where DuckDB's process and CSV-reader startup dominate; on 8 GB the actual parse+aggregate work dominates and csvql holds a clean ~2.8x.

Reproduce: [bench/bench_taxi.sh](bench/bench_taxi.sh) — ./bench/bench_taxi.sh --sample (417 MB, quick) or ./bench/bench_taxi.sh 1 (full 20M rows, ~8 GB download).

Memory & storage — same 8 GB / 20M-row file (peak memory footprint, single cold run):

| Query | csvql peak | DuckDB peak | | ----- | ---------- | ----------- | | Q01 | 29 MB | 178 MB | | Q02 | 30 MB | 210 MB | | Q03 | 34 MB | 208 MB | | Q04 | 38 MB | 219 MB |

~6x less memory — and csvql needs 0 bytes of extra storage: it queries the CSV in place via mmap, no ingest. DuckDB's fast "with storage" path first materializes a 2.1 GB native store (21.7 s one-time ingest) before it can reach comparable query times; querying the raw CSV directly (as csvql does), it uses ~6x the memory and stays ~2.8x slower.

Reproduce: ./bench/bench_taxi.sh --resources 1 (or --resources --sample).

At scale, csvql reads raw CSV about as fast as your OS can hand it the bytes. On the 8 GB file, SELECT COUNT(*), GROUP BY cab_type, and GROUP BY + AVG all run in ~1.31 s — the same time as cat file > /dev/null (~1.30 s) on the same machine. The parsing, grouping, and aggregation are effectively free; the whole query is bounded by the file read itself. There is no meaningful parsing overhead left to remove — csvql is already at the read ceiling, which is why the raw-CSV gap over DuckDB (which does more work per byte) holds at ~2.8x.

Run the full suite (all sections): [bench/bench_all.sh](bench/bench_all.sh)

How is csvql so fast?

• Memory-mapped I/O — zero-copy reading at 1.4 GB/sec
• 7-core parallel execution — lock-free architecture, 669% CPU utilization
• SIMD field parsing — vectorized comma detection
• Radix sort — O(8N) with IEEE 754 f64→u64 bit trick and pass-skipping
• Top-K heap — O(N log K) for LIMIT queries, avoids sorting entire dataset
• Hardware-aware thresholds — ARM vs x86 tuned for L1 cache
• Zero per-row allocations — arena buffers, zero-copy slices
• Adaptive GROUP BY pre-sizing — hash table capacity tuned to chunk size, eliminates rehash cycles
• Zero-copy worker scans — each thread iterates a direct mmap slice, no pread syscalls or seam buffers

See [ARCHITECTURE.md](ARCHITECTURE.md) for the full optimization story.

Benchmark methodology

DuckDB and DataFusion CLIs default to displaying only 40 rows, making them appear faster than they are. Our benchmarks use -csv mode (DuckDB) and FORMAT CSV (ClickHouse) to force full output materialization. DataFusion CLI caps output at ~8K rows regardless of settings, so full-output numbers are unavailable.

See [BENCHMARKS.md](BENCHMARKS.md) for the complete analysis.

SQL Reference

Supported

| Feature | Syntax | | ------------- | ----------------------------------------------------------------------- | | SELECT | SELECT col1, col2 or SELECT * | | AS alias | SELECT expr AS alias — rename any column or expression in output | | DISTINCT | SELECT DISTINCT col1, col2 — deduplicates output rows | | FROM | FROM 'file.csv' or FROM - (stdin) | | WHERE | =, !=, >, >=, 5) | | STRFTIME | STRFTIME('%Y-%m', col) — date bucketing in SELECT and GROUP BY | | DATEPART | DATE_PART('year', col) — extract year/month/day/hour/minute/second; alias for STRFTIME in SELECT and GROUP BY | | UPPER / LOWER | SELECT UPPER(col), LOWER(col) — case conversion | | TRIM | SELECT TRIM(col) — strip leading and trailing whitespace | | LENGTH | SELECT LENGTH(col) — byte length of the value | | SUBSTR | SELECT SUBSTR(col, start, len) — substring (1-based, len optional) | | REPLACE | SELECT REPLACE(col, 'from', 'to') — replace all occurrences of a substring | | SPLITPART| SELECT SPLIT_PART(col, 'delim', n) — n-th field (1-based) after splitting on delim | | GREATEST / LEAST | SELECT GREATEST(a, b, ...), LEAST(a, b, ...) — row-wise max/min (numeric or lexicographic) | | ABS / CEIL / FLOOR | SELECT ABS(col), CEIL(col), FLOOR(col) — numeric functions | | MOD | SELECT MOD(col, n) — modulo by a numeric literal | | ROUND | SELECT ROUND(col) — round to integer; ROUND(col, n) — round to n decimal places | | COALESCE | SELECT COALESCE(col, 'default') — replace empty/null with fallback | | CAST | SELECT CAST(col AS INTEGER/FLOAT/TEXT) — type conversion | | DATEDIFF | DATEDIFF('unit', start_col, end_col) — duration between two datetime columns. Units: second, minute, hour, day, week, month (≈30 days), year (≈365 days). Auto-detects ISO-8601, US (MM/DD/YYYY), EU (DD.MM.YYYY) and mixed formats in the same file | | DATEADD | DATEADD('unit', amount, date_col) — add/subtract interval from a datetime column. amount may be negative. Units: second, minute, hour, day, week, month (≈30 days), year (≈365 days). Returns YYYY-MM-DD HH:MM:SS | | ORDER BY | ORDER BY col [ASC\|DESC], multi-column ORDER BY col1 ASC, col2 DESC, alias, or positional (ORDER BY 1) | | LIMIT | LIMIT n |

Aggregate Examples

# Scalar aggregates (whole table)
csvql "SELECT COUNT(*), SUM(salary), AVG(salary), MIN(age), MAX(age) FROM 'data.csv'"

# Grouped aggregates
csvql "SELECT department, COUNT(*), AVG(salary) FROM 'data.csv' GROUP BY department ORDER BY department"

# HAVING — filter groups after aggregation
csvql "SELECT department, SUM(salary) FROM 'data.csv' GROUP BY department HAVING SUM(salary) > 500000"
csvql "SELECT category, COUNT(*) FROM 'orders.csv' GROUP BY category HAVING COUNT(*) > 1000"

# CASE WHEN inside aggregates — conditional counting and summing
csvql "SELECT department, COUNT(*) AS total, SUM(CASE WHEN city = 'London'

…

## Source & license

This open-source MCP server is cataloged on AgentStack and links to its original source — we do not rehost the code.

- **Author:** [melihbirim](https://github.com/melihbirim)
- **Source:** [melihbirim/csvql](https://github.com/melihbirim/csvql)
- **License:** MIT
- **Homepage:** https://melihbirim.github.io/csvql/

Install and usage instructions live in the source repository linked above.