csvql

<p align="center">
  <img src="logo.svg" alt="csvql" width="420"/>
</p>

[![CI](https://github.com/melihbirim/csvql/actions/workflows/ci.yml/badge.svg)](https://github.com/melihbirim/csvql/actions/workflows/ci.yml)
[![License: MIT](https://img.shields.io/badge/License-MIT-blue.svg)](LICENSE.md)
[![Release](https://img.shields.io/github/v/release/melihbirim/csvql)](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](https://modelcontextprotocol.io/) 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).

```bash
$ 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](https://melihbirim.github.io/csvql/) · [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:

```bash
# 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

```bash
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` |
| `-o`, `--output <file>` |    | Write results to a file instead of stdout           |
| `--delimiter <char>` | `-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 <N>`      |       | Worker threads for parallel execution; `0` uses automatic detection |
| `--strict`           |       | Error on a WHERE numeric comparison against a non-numeric value instead of silently skipping that row (see [CORRECTNESS.md](CORRECTNESS.md#strict-and-exit-codes)) |
| `--version`          | `-v`  | Show version                                        |
| `--help`             | `-h`  | Show help                                           |
| `--mcp`              |       | Start as an MCP server (stdio JSON-RPC transport)   |
| `--root <dir>`       |       | Confine file access to a directory (repeatable via commas) |
| `--audit <file>`     |       | Append a JSONL audit record per query (timestamp, SQL)     |

```bash
# 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)

```bash
brew install melihbirim/csvql/csvql
```

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

```bash
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](https://github.com/melihbirim/csvql/releases):

```bash
# 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](https://ziglang.org/) 0.13.0+ (tested with 0.15.2):

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

## Performance

**2M rows, 56 MB CSV, Apple M2 Pro** — aggregates on the raw CSV (best-of-5):

| Query                        | csvql      | DuckDB | Speedup   |
| ----------------------------- | ---------- | ------ | --------- |
| `SELECT COUNT(*)` scalar      | **0.012s** | 0.136s | **11.3x** |
| `COUNT(*) GROUP BY`           | **0.020s** | 0.146s | **7.3x**  |
| `JOIN SELECT *` (2M × 6)      | **0.088s** | 7.832s | **89x**   |

**NYC Taxi, 20M rows, 8 GB CSV** — raw CSV, no ingest, both engines: **~3.2x** faster, **~6x** less memory, and **0 bytes** of extra storage (DuckDB's fast path needs a 2.1 GB native store first). At this scale csvql reads raw CSV about as fast as `cat` — the read itself is the bound, not parsing.

Full breakdown (LIKE, multi-table JOIN, subqueries, memory/storage, methodology): **[BENCHMARKS.md](BENCHMARKS.md)**. Reproduce any number yourself: [`bench/bench_all.sh`](bench/bench_all.sh).

## SQL Reference

`SELECT`/`FROM`/`WHERE`/`GROUP BY`/`HAVING`/`ORDER BY`/`LIMIT`/`OFFSET`, `JOIN`, subquery `IN`/`NOT IN`, `LIKE`/`ILIKE`/`BETWEEN`/`IS NULL`/`AND`/`OR`/`NOT`, aggregates (`COUNT`/`SUM`/`AVG`/`MIN`/`MAX`/`VARIANCE`/`STDDEV`/`MEDIAN`/`GROUP_CONCAT`), `CASE WHEN`, and scalar functions (`UPPER`/`LOWER`/`TRIM`/`CONCAT`/`SUBSTR`/`REPLACE`/`SPLIT_PART`/`ROUND`/`CAST`/`COALESCE`/`STRFTIME`/`DATEDIFF`/`DATEADD`/and more).

```bash
csvql "SELECT department, COUNT(*), AVG(salary) FROM 'data.csv' WHERE salary > 50000 GROUP BY department HAVING COUNT(*) > 10 ORDER BY department"
csvql "SELECT e.name, d.dept_name FROM 'employees.csv' e JOIN 'departments.csv' d ON e.dept_id = d.id WHERE d.dept_name = 'Engineering'"
csvql "SELECT id FROM 'orders.csv' WHERE customer_id IN (SELECT id FROM 'customers.csv' WHERE region = 'EU')"
```

Full syntax table, runnable examples for every feature, known differences from DuckDB, and current limitations: **[SQL_REFERENCE.md](SQL_REFERENCE.md)**.

Positional "simple mode" is also available for quick one-off filters without writing SQL: `csvql data.csv "name,salary" "age>30" 10 "salary:desc"` — see [SIMPLE_QUERY_LANGUAGE.md](SIMPLE_QUERY_LANGUAGE.md).

## MCP Server

csvql ships as a [Model Context Protocol](https://modelcontextprotocol.io/) server, letting AI assistants (Claude, Copilot, etc.) query your CSV files directly.

```bash
csvql --mcp
```

### Why query instead of paste?

A 1 MB CSV costs **~560,000 tokens** to paste into an LLM — it doesn't even fit a 200K-token context window. Pasting a real dataset is impossible past a few hundred KB, and expensive long before that. With `csvql --mcp` the agent *queries* the file instead and gets back only the rows it asked for:

| CSV size | Paste into context | Query via `csvql --mcp` | Savings |
| -------- | ------------------ | ----------------------- | ------- |
| 1 MB     | 559K tokens ❌ *(overflows)* | ~540 tokens | **1,000x** |
| 10 MB    | 5.6M tokens ❌      | ~550 tokens | **10,000x** |
| 100 MB   | 55M tokens ❌       | ~565 tokens | **98,000x** |
| 417 MB   | 230M tokens ❌      | ~560 tokens | **~410,000x** |

The query cost is **flat** — it's the SQL plus a few result rows, independent of file size — so a 417 MB file costs the same ~560 tokens as a 1 MB one. Five real questions, answered against DuckDB's NYC-taxi data; token counts via `tiktoken` (exact `cl100k`). Reproduce: [`bench/bench_tokens.py`](bench/bench_tokens.py). Your data never leaves your machine.

### Exposed Tools

| Tool | Description |
|------|-------------|
| `csv_query(sql)` | Execute any supported SQL query, returns results as JSON |
| `csv_schema(file)` | Column names and sample rows for a CSV file |
| `csv_list(directory?)` | List CSV files in a directory |

### Supported Queries via MCP

`csv_query` accepts the full SQL dialect supported by csvql. You can ask your AI assistant things like:

| Natural language prompt | SQL sent to `csv_query` |
|---|---|
| "Show me the top 10 customers by revenue" | `SELECT customer, SUM(revenue) AS total FROM 'sales.csv' GROUP BY customer ORDER BY total DESC LIMIT 10` |
|