csvql
llms.txt31 starsChanged 46 days agoMITView on GitHub
csvql (published as csvql-query on npm and PyPI) runs read-only SQL on CSV, TSV and .xlsx files in place, built for AI agents. This file is the README, the SQL reference and the security notes concatenated. Short index: https://melihbirim.github.io/csvql/llms.txt Generated from the repository at https://github.com/melihbirim/csvql by scripts/gen_llms_full.sh. Relative links below point into that repository. ==================== README.md ==================== Give an AI agent SQL over your CSV files, not your computer. Out of the box it can't write, can't reach the network,…
- Installs packages
In this repositoryOne other file it gives its agents.
- docs/llms.txtllms.txt
The file
We split this llms-full.txt into the rules it gives an agent
12 rules. About 0 are common, 4 are distinctive, and 8 are very specific to this project.
≈0
Common · estimate
Many files like this have a rule of this kind.
≈4
Distinctive · estimate
This author's own choices. Worth a look.
8
Very specific
Tied to their project's names, files or tools. Probably skip.
- Common
- Distinctive
- Very specific
- Very specific: line 50
Input is CSV, TSV or
.xlsx: No Parquet, no JSON or JSONL input. - Very specific: line 115
Run csvql on the server next to the data and connect over SSH.
- Very specific: line 126
Default settings, no hardening flags.
- Very specific: line 137
Check the DuckDB column yourself: duckdb -c "COPY (SELECT 1) TO 'x.csv'" and duckdb -c "SELECT current_setting('enable_external_access')".
- Distinctive: line 211
Every number has a reproduce script.
- Distinctive: line 329
If you need character count instead of byte length, NULL instead of empty-string semantics, or truncated instead of fractional interval …
- Distinctive: line 333
Not yet supported — these error clearly rather than silently returning wrong data, and are tracked as open issues:
- Very specific: line 610
If you're evaluating csvql for an air-gapped or hardened-filesystem environment, verify this claim yourself rather than take it on faith — …
- Very specific: line 618
When running csvql on a server that an agent or remote user queries:
- Distinctive: line 621
Run as a dedicated, unprivileged user with a read only mount of the data.
- Very specific: line 622
Prefer the SSH access model (ssh host csvql --mcp --root /data).
- Very specific: line 624
If you expose an HTTP transport later, keep csvql behind a reverse proxy that terminates TLS and handles authentication.
≈ “Common” is an estimate: at least 1 in 5 of the 3,727 llms.txt files we read have a rule of the same kind, not the same words. “Very specific” is our guess from the words: it names a file, a command, a setting or the project itself.
Files
# csvql, full documentation in one file
> csvql (published as csvql-query on npm and PyPI) runs read-only SQL on CSV, TSV and .xlsx files in place, built for AI agents. This file is the README, the SQL reference and the security notes concatenated. Short index: https://melihbirim.github.io/csvql/llms.txt
Generated from the repository at https://github.com/melihbirim/csvql by scripts/gen_llms_full.sh. Relative links below point into that repository.
==================== README.md ====================
**Give an AI agent SQL over your CSV files, not your computer.** Out of the box it can't write, can't reach the network, can't leave its folder, and can't pour a whole table into the context.
> A database is something you load your data *into*. csvql is a query you run on the data where it already lives.
csvql (published as `csvql-query` on npm and PyPI, `csvql` on Homebrew) runs `SELECT` on CSV files where they sit: no import, no ingest, no server. It ships as an [MCP](https://modelcontextprotocol.io/) server, so an agent can ask a question of a gigabyte file and get back a few hundred tokens instead of the file. Safe by construction, not by configuration: the SQL has no write statements and the binary has no network code. It is about as fast as DuckDB and polars on raw CSV and uses 3 to 11x less memory.
```bash
$ csvql "SELECT cab_type, COUNT(*) FROM 'trips.csv' GROUP BY cab_type"
cab_type,COUNT(*)
green,32447
yellow,967553
```
[Website](https://melihbirim.github.io/csvql/) · [Quick Start](#quick-start) · [For agents](#for-agents-mcp) · [Install](#installation) · [Performance](#performance) · [SQL](#sql) · [Docs](#documentation)
---
## Why this exists
Agents are getting access to company data. The usual paths are a database connection or uploading files to the model. Both move the data. csvql is the third option: run it next to the data, on your own box, and let the agent query it there. Everything below is checkable in the source.
- **Read-only by construction.** The parser has no `INSERT`, `UPDATE`, `DELETE` or `DROP`. Nothing writes to an input file.
- **No network.** No `std.net`, `std.http` or socket call in the codebase. No telemetry, no update check. `strace -f -e trace=network` shows an empty trace.
- **Sandboxed by default for agents.** `csvql --mcp` only opens files under its working directory and refuses to start in the filesystem root; `--root /data` picks the folders instead, and `--root /` allows everything. `/etc/passwd` and `../` traversal fail with `PathOutsideAllowedRoot`.
- **Private columns stay private.** `--deny-columns email,ssn` refuses any query that reads those columns, in SELECT, WHERE, ORDER BY or a subquery, and `SELECT *` leaves them out. The agent can still run `AVG(salary) GROUP BY dept`.
- **Bounded output.** MCP results are capped at about 100 rows / 12 KB per call. A careless `SELECT *` cannot flood the context. The cap is per call, so an agent that pages with `OFFSET` can still read a whole table; keep data it must never see out with `--deny-columns`.
- **Flat memory.** An aggregate holds a running accumulator, not a dataset: 74 MB on a 0.7 GB file and 74 MB on an 8 GB one. It fits on the small VM beside the data.
- **Auditable.** `--audit <file>` appends one JSONL record per query. Over SSH you inherit your existing keys, bastion and logs.
Full posture, including two known gaps on macOS and Windows: [SECURITY.md](SECURITY.md).
## What it does not do
- **It is not a database.** No `UNION`, no window functions, no `RIGHT`/`FULL`/`CROSS JOIN` (inner and left hash joins only). The only subquery shape is `col IN (SELECT ...)`. These error rather than return wrong data. If you need full SQL, use DuckDB.
- **Scalar functions in `WHERE`** are limited to `DATEDIFF`, `DATEADD` and `EXTRACT`. Others (`UPPER`, `ROUND`, ...) are `SELECT`-only and currently fail in `WHERE` with a misleading `ColumnNotFound`.
- **Excel files are read, not streamed.** `FROM 'sales.xlsx'` queries the first sheet (dates come out as `YYYY-MM-DD`, formulas as their saved value), but the sheet is unpacked in memory first: a 9 MB, 300k row workbook takes about 230 MB and 0.6 s. Other sheets, `.xls` and `.xlsb` are not supported.
- **Quoted CSV is about 3x slower** than unquoted. Quotes disable the 16-byte skip the scanner relies on.
- **`MEDIAN` buffers every value in a group**, so on 20M rows it is 5x slower than DuckDB ([#205](https://github.com/melihbirim/csvql/issues/205)). High-cardinality `GROUP BY` (millions of groups) is also slower.
- **Input is CSV, TSV or `.xlsx`.** No Parquet, no JSON or JSONL input; JSON and JSONL are output formats only.
Full list with tracking issues: [SQL_REFERENCE.md#known-limitations](SQL_REFERENCE.md#known-limitations).
## Quick Start
```bash
# SQL mode
csvql "SELECT name, salary FROM 'data.csv' WHERE age > 30 ORDER BY salary DESC LIMIT 10"
# Simple mode: same query, positional
csvql data.csv "name,salary" "age>30" 10 "salary:desc"
# Pipes
cat data.csv | csvql "SELECT name, age FROM '-' WHERE age > 25"
csvql "SELECT * FROM 'data.csv' WHERE status = 'active'" > output.csv
# Other formats
csvql "SELECT name, age FROM 'data.tsv'" -d $'\t'
csvql "SELECT name, age FROM 'data.csv' LIMIT 3" --json # or --jsonl, --markdown
# What is in this file?
csvql --schema data.csv
```
Flags: `-d/--delimiter`, `--json`, `--jsonl`, `--markdown`, `--no-header`, `--no-input-header` (headerless files get `c1..cN`), `-o/--output`, `--threads`, `--strict`, `--schema`, `--root`, `--deny-columns`, `--audit`, `--mcp`. Details in `csvql --help`. Simple mode: [SIMPLE_QUERY_LANGUAGE.md](SIMPLE_QUERY_LANGUAGE.md).
## For agents (MCP)
### Setup
```bash
cd ~/data && csvql install # registers csvql in Claude Code and Claude Desktop; --print to dry-run
```
It runs `claude mcp add` if the Claude Code CLI is present and merges an `mcpServers.csvql` entry into the Claude Desktop config, preserving your other servers. Claude Desktop starts MCP servers in `/`, so the Desktop entry gets `--root` set to the folder you ran `install` from (or `--root <folder>`). Without a `--root`, `csvql --mcp` refuses to start in the filesystem root. Restart Claude afterward.
Without MCP, a Claude Code skill teaches the agent to use the `csvql` CLI whenever a CSV comes up: `claude plugin marketplace add melihbirim/csvql` then `claude plugin install csvql@csvql`.
Alternatives: a one-click `csvql-<platform>.mcpb` bundle for Claude Desktop on the [Releases](https://github.com/melihbirim/csvql/releases) page (Settings → Extensions), which asks for the data folder on install, or a manual entry:
```json
{ "mcpServers": { "csvql": { "command": "/usr/local/bin/csvql", "args": ["--mcp", "--root", "/data"] } } }
```
VS Code (Copilot) takes the same `command`/`args` under `servers` in `.vscode/mcp.json` with `"type": "stdio"`.
### Tools
| Tool | Returns |
|------|---------|
| `csv_query(sql)` | Results as JSON, capped at about 100 rows / 12 KB |
| `csv_schema(file)` | Column names and a few sample rows |
| `csv_list(directory?)` | CSV files in a directory |
The agent writes ordinary SQL against single-quoted paths:
```sql
SELECT customer, SUM(revenue) AS total FROM 'sales.csv' GROUP BY customer ORDER BY total DESC LIMIT 10
SELECT STRFTIME('%Y-%m', order_date) AS month, COUNT(*) FROM 'orders.csv' GROUP BY month ORDER BY 1
SELECT o.id, c.name FROM 'orders.csv' o JOIN 'customers.csv' c ON o.customer_id = c.id WHERE c.region = 'West'
```
### Remote and on-prem
Run csvql on the server next to the data and connect over SSH. Only the SQL and the small result cross the wire. No open port, no reverse proxy, no new credentials:
```jsonc
// client MCP config; csvql runs on the remote server
{ "command": "ssh", "args": ["analyst@dataserver", "csvql", "--mcp", "--root", "/data"] }
```
Without `--root`, `--mcp` is confined to the directory it was started in. Set `--root` explicitly when an agent or another user can reach csvql, since that directory depends on how the client launches the server. Pair it with an unprivileged OS user and a read-only mount. Checklist: [SECURITY.md#hardening-for-on-prem-and-remote-access](SECURITY.md#hardening-for-on-prem-and-remote-access).
### What an agent can do, out of the box
Default settings, no hardening flags. DuckDB is the CLI an agent with a shell would call; it can be locked down with `SET enable_external_access = false` and read-only mode, but only if someone remembers to.
| An agent asks the tool to... | csvql `--mcp` | DuckDB 1.5 CLI |
| --- | --- | --- |
| write a file (`COPY ... TO 'x.csv'`) | no, there is no write statement | yes |
| read `/etc/hosts` or `../` outside its folder | no, `PathOutsideAllowedRoot` | yes |
| fetch a URL or install an extension | no, no network code | yes (`enable_external_access` and `autoinstall_known_extensions` default to true) |
| return a whole large table in one call | no, about 100 rows / 12 KB cap | yes |
| read a column you marked private | no, with `--deny-columns email` | yes, unless you build views and only expose those |
| peak memory, `GROUP BY` on a 0.72 GB file | 54 MB | 221 MB (polars 2.0: 1137 MB) |
Check the DuckDB column yourself: `duckdb -c "COPY (SELECT 1) TO 'x.csv'"` and `duckdb -c "SELECT current_setting('enable_external_access')"`.
### Token cost
Five questions about a taxi dataset cost about 640 tokens over MCP, tool schema included, and the cost does not change with file size. Pasting the 417 MB file would cost about 230 million tokens, which is a feasibility argument, not a savings figure. An agent that already has a shell and runs `duckdb -csv` uses slightly fewer tokens than csvql over MCP, because MCP returns JSON. The case for csvql with agents is `--root`, read-only execution, the output cap and memory, not token count. Measure it: [`bench/bench_tokens.py`](bench/bench_tokens.py).
## Installation
**Homebrew** (macOS / Linux):
```bash
brew install melihbirim/csvql/csvql
```
**Prebuilt binaries** 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
```
Windows (x86_64): download `csvql-windows-x86_64.zip` from the same page and put `csvql-windows-x86_64.exe` on your `PATH`.
**Libraries**, same engine, no subprocess:
```bash
pip install csvql-query # Python: csvql.query(), .query_df(), .query_csv() → docs/PYTHON.md
npm install csvql-query # Node.js: require('csvql-query'), plus a `csvql-query` CLI → docs/NODE.md
```
**From source**, Zig 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/
```
**Shell completions** for bash and zsh live in [`completions/`](completions/). They complete the flags in `csvql --help`, the `install` subcommand and file paths.
```bash
# bash: add to ~/.bashrc (on macOS, Terminal opens login shells, so use ~/.bash_profile)
source /path/to/csvql/completions/csvql.bash
# zsh: put _csvql in a directory on your fpath
mkdir -p ~/.zsh/completions
cp completions/_csvql ~/.zsh/completions/_csvql
# then in ~/.zshrc, before compinit runs:
# fpath=(~/.zsh/completions $fpath)
# autoload -Uz compinit && compinit
```
## Performance
Same `GROUP BY department` on raw CSV, one process per engine, Apple M2 Pro with 16 GB. Peak memory and wall time:
| File | csvql | DuckDB | polars 2.0 |
| --- | --- | --- | --- |
| 0.69 GB | **74 MB** / 0.2s | 254 MB / 0.8s | 1151 MB / 0.6s |
| 1.89 GB | **74 MB** / 0.4s | 253 MB / 1.2s | 2107 MB / 1.1s |
| 3.80 GB | **75 MB** / 0.7s | 275 MB / 2.1s | 2666 MB / 9.8s |
| 8.14 GB | **74 MB** / 2.4s | 367 MB / 5.5s | 1754 MB / 26.7s |
Memory is flat because an aggregate holds an accumulator, not a dataset. That holds for aggregates, not for `ORDER BY`, which has to keep rows. Speed is in the same band as the other two: csvql is 2 to 5x ahead on scans, filters and small files, far ahead on joins against raw CSV, level with polars on `COUNT(*)` and top-N, and behind DuckDB on `MEDIAN` and on `GROUP BY` with millions of groups. None of that matters on an agent's timescale. The memory does, because it decides what box csvql can run on.
Every number has a reproduce script. Full tables, including quoted CSV, wide files, joins, subqueries and the losses: **[BENCHMARKS.md](BENCHMARKS.md)**.
## SQL
Files: `.csv`, `.tsv` (with `-d '\t'`) and `.xlsx` (first sheet). `SELECT` / `FROM` / `WHERE` / `GROUP BY` / `HAVING` / `ORDER BY` / `LIMIT` / `OFFSET`, `JOIN` and `LEFT JOIN` (chained), `col IN (SELECT ...)`, `LIKE` / `ILIKE` / `BETWEEN` / `IS NULL` / `AND` / `OR` / `NOT`, `DISTINCT`, `CASE WHEN`, arithmetic (`+ - * /` in `SELECT`, `WHERE`, inside aggregates and over their results: `SUM(price * qty)`, `WHERE price * qty > 100`, `SUM(a) / COUNT(*)`), column names with spaces as `"First Name"`, `` `First Name` `` or `[First Name]`. Aggregates: `COUNT`, `SUM`, `AVG`, `MIN`, `MAX`, `VARIANCE`, `STDDEV`, `MEDIAN`, `QUANTILE`, `MODE`, `GROUP_CONCAT`, `COUNT(DISTINCT)`. Scalars in `SELECT`: string (`UPPER`, `LOWER`, `TRIM`, `LENGTH`, `REVERSE`, `CONCAT`, `LTRIM`, `RTRIM`, `LEFT`, `RIGHT`, `REPEAT`, `POSITION`, `INSTR`, `SUBSTR`, `REPLACE`, `SPLIT_PART`, `LPAD`, `RPAD`), math (`ABS`, `SIGN`, `CEIL`, `FLOOR`, `MOD`, `ROUND`, `TRUNC`, `SQRT`, `POWER`, `LN`, `LOG`, `EXP`, `GREATEST`, `LEAST`), `CAST`, `COALESCE`, `NULLIF`, `IFNULL`, dates (`STRFTIME`, `DATE_PART`, `EXTRACT`, `DATEDIFF`, `DATEADD`, `CURRENT_DATE`, `NOW()`, `CURRENT_TIMESTAMP`).
```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 and limitations: **[SQL_REFERENCE.md](SQL_REFERENCE.md)**. What is differentially tested against DuckDB: **[CORRECTNESS.md](CORRECTNESS.md)**.
## Documentation
| Document | Description |
| --- | --- |
| [SQL_REFERENCE.md](SQL_REFERENCE.md) | Full SQL syntax, runnable examples, DuckDB differences, limitations |
| [SECURITY.md](SECURITY.md) | Security posture, network and disk-write guarantees, hardening |
| [CORRECTNESS.md](CORRECTNESS.md) | What is tested against DuckDB, how, known gaps, error behaviour |
| [BENCHMARKS.md](BENCHMARKS.md) | Full performance analysis vs DuckDB, polars, pandas, DataFusion |
| [ARCHITECTURE.md](ARCHITECTURE.md) | Engine design, optimization techniques |
| [SIMPLE_QUERY_LANGUAGE.md](SIMPLE_QUERY_LANGUAGE.md) | Simple mode syntax reference |
| [docs/PYTHON.md](docs/PYTHON.md) · [docs/NODE.md](docs/NODE.md) · [docs/LIBRARY.md](docs/LIBRARY.md) | Python, Node.js and Zig library APIs |
| [CONTRIBUTING.md](CONTRIBUTING.md) | Contribution guidelines |
## Planned
- HTTP/SSE MCP transport, so one csvql can serve a team as a shared service ([#60](https://github.com/melihbirim/csvql/issues/60)).
- `MEDIAN` in O(groups) memory ([#205](https://github.com/melihbirim/csvql/issues/205)).
Shipped features are in the [release notes](https://github.com/melihbirim/csvql/releases). [Good first issues](https://github.com/melihbirim/csvql/labels/good%20first%20issue) are scoped with file pointers and done-when criteria.
## License
MIT, see [LICENSE.md](LICENSE.md).
==================== SQL_REFERENCE.md ====================
# SQL Reference
Full syntax reference and runnable examples for every SQL feature csvql supports. For a quick-glance summary, see the [README](README.md#sql-reference).
## Supported
| Feature | Syntax |
| ------------- | ----------------------------------------------------------------------- |
| **SELECT** | `SELECT col1, col2` or `SELECT *` |
| **AS alias** | `SELECT expr AS alias` — rename any column or expression in output; `AS` is optional (`SELECT expr alias` also works) |
| **DISTINCT** | `SELECT DISTINCT col1, col2` — deduplicates output rows |
| **FROM** | `FROM 'file.csv'` or `FROM -` (stdin); optional table alias — `FROM 'file.csv' AS t` or bare `FROM 'file.csv' t`, reference columns as `t.col` |
| **WHERE** | `=`, `!=`, `>`, `>=`, `<`, `<=` with auto numeric coercion |
| **Modulo** | `WHERE col % n = val` — integer remainder test in WHERE (also `MOD(col, n)` as a SELECT expression) |
| **LIKE / NOT LIKE** | `WHERE col LIKE 'pattern'`, `WHERE col NOT LIKE 'pattern'` — `%` any sequence, `_` any single character (one UTF-8 character, so `'Z_rich'` matches `Zürich`) |
| **ILIKE / NOT ILIKE** | `WHERE col ILIKE 'pattern'`, `WHERE col NOT ILIKE 'pattern'` — same as LIKE but case-insensitive (ASCII letters) |
| **BETWEEN** | `WHERE col BETWEEN low AND high` — inclusive numeric or string range |
| **IN / NOT IN** | `WHERE col IN ('a', 'b', 'c')`, `WHERE col NOT IN ('a', 'b')` — membership test and its negation |
| **IN (subquery)** | `WHERE col IN (SELECT col FROM 'other.csv' WHERE ...)` — non-correlated, subquery must select exactly one column (also `NOT IN`); see [CORRECTNESS.md](CORRECTNESS.md#subqueries) |
| **IS NULL** | `WHERE col IS NULL` / `WHERE col IS NOT NULL` — empty-field test; also usable as a SELECT expression (`SELECT col IS NOT NULL AS has_val`), returns `true`/`false` |
| **NOT** | `WHERE NOT expr` — logical negation of any condition. Binds tighter than AND and OR, as in SQL: `NOT a = 1 AND b = 2` is `(NOT a = 1) AND b = 2`; use parentheses for `NOT (a = 1 AND b = 2)`. An empty field is unknown, not false: `NOT age > 30`, `age != 30` and `age NOT IN (30)` all exclude a row with an empty `age`, same as `age > 30` does. Only `IS NULL`, `IS NOT NULL` and a comparison against `''` see the empty field itself |
| **AND / OR** | `WHERE cond1 AND cond2` / `WHERE cond1 OR cond2` — compound conditions |
| **JOIN** | `FROM 'a.csv' a [INNER] JOIN 'b.csv' b ON a.key = b.key` |
| **GROUP BY** | `GROUP BY col1` or `GROUP BY alias` — groups rows; accepts SELECT aliases |
| **COUNT** | `COUNT(*)` or `COUNT(col)` — with or without `GROUP BY` |
| **SUM** | `SUM(col)` or `SUM(CASE WHEN cond THEN n ELSE m END)` — conditional sum |
| **AVG** | `AVG(col)` — full precision; with or without `GROUP BY` |
| **VARIANCE / STDDEV** | `VARIANCE(col)`, `STDDEV(col)` — sample variance / std deviation, N-1 denominator (aliases `VAR`, `VAR_SAMP`, `STDDEV_SAMP`). Population variants: `VAR_POP(col)`, `STDDEV_POP(col)` |
| **MEDIAN** | `MEDIAN(col)` — median of numeric values (mean of the two middles for an even count) |
| **QUANTILE** | `QUANTILE(col, p)` — the `p`-th quantile of numeric values, `p` a numeric literal from 0 to 1 (`QUANTILE(latency_ms, 0.95)` is the p95). Interpolates linearly between the two nearest values, like DuckDB's `quantile_cont`, so `QUANTILE(col, 0.5)` equals `MEDIAN(col)`. Empty and non-numeric fields are skipped; a group with none gives an empty field. A `p` outside 0 to 1, or a missing `p`, is a query error. Like `MEDIAN`, it buffers every value of a group, so memory is O(rows) rather than O(groups); several `QUANTILE`/`MEDIAN` calls in one query each keep their own buffer |
| **MODE** | `MODE(col)` — the most frequent value, as text. Empty fields are not counted; a group with no values gives an empty field. Ties go to the smallest value (numerically when both are numbers, otherwise byte order), so the result does not depend on row order or `--threads`. Memory is O(distinct values) per group |
| **GROUP_CONCAT** | `GROUP_CONCAT(col [, 'sep'])` — concatenate group values (default separator `,`; alias `STRING_AGG`) |
| **CASE WHEN** | `CASE WHEN col OP val THEN n ELSE m END` inside any aggregate function, or wrapping an aggregate in its own condition — `CASE WHEN AVG(col) > n THEN ... END` |
| **MIN / MAX** | `MIN(col)`, `MAX(col)` — with or without `GROUP BY` |
| **HAVING** | `HAVING expr` — filter groups after aggregation (e.g. `HAVING COUNT(*) > 5`); `AND`/`OR` of multiple aggregates not in `SELECT` also works (e.g. `HAVING COUNT(*) > 5 AND MAX(col) > 100`) |
| **STRFTIME** | `STRFTIME('%Y-%m', col)` — date bucketing in `SELECT` and `GROUP BY` |
| **DATE_PART** | `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; one level of nesting supported, e.g. `LOWER(TRIM(col))` |
| **TRIM** | `SELECT TRIM(col)` — strip leading and trailing whitespace; nestable, e.g. `TRIM(UPPER(col))` |
| **REVERSE** | `SELECT REVERSE(col)` — reverse a string by Unicode code point; nestable, e.g. `REVERSE(TRIM(col))` |
| **LENGTH** | `SELECT LENGTH(col)` — byte length of the value; nestable, e.g. `LENGTH(TRIM(col))` |
| **CONCAT** | `SELECT CONCAT(col1, '-', col2, ...)` — concatenate columns and/or string literals |
| **LTRIM / RTRIM** | `SELECT LTRIM(col)`, `RTRIM(col)` — trim ASCII whitespace from the left or right side only (`TRIM` does both); nestable as the inner call, e.g. `UPPER(LTRIM(col))` |
| **LEFT / RIGHT** | `SELECT LEFT(col, n)`, `RIGHT(col, n)` — the first or last `n` bytes. A negative `n` drops that many bytes from the other end (`LEFT('abcdef', -2)` is `abcd`, `RIGHT('abcdef', -2)` is `cdef`). `n` larger than the value returns the whole value; `0` returns an empty field. `n` is an integer literal. Counts are bytes, like `LENGTH` and `SUBSTR`, but a cut never lands inside a multi-byte character, so the result is always valid UTF-8 and can be shorter than `n`. An empty field stays empty |
| **REPEAT** | `SELECT REPEAT(col, n)` — `col` repeated `n` times. `n` is an integer literal. `n` of `0` or less returns an empty field. A result longer than 1 MiB (1,048,576 bytes) is also an empty field, so a typo cannot allocate gigabytes; DuckDB would build it. An empty field stays empty |
| **POSITION / INSTR** | `SELECT POSITION('sub' IN col)`, `INSTR(col, 'sub')` — the 1-based index of the first occurrence of the quoted literal `sub` in `col`, or `0` when it is absent, so a value from either can feed `SUBSTR`. Matching is case-sensitive and the index counts bytes, like `LENGTH` and `SUBSTR`, so it is larger than the character index after a multi-byte character. An empty `sub` gives `1`. An empty field stays empty. `sub` must be a quoted literal, not a column |
| **SUBSTR** | `SELECT SUBSTR(col, start, len)` — substring (1-based, `len` optional) |
| **REPLACE** | `SELECT REPLACE(col, 'from', 'to')` — replace all occurrences of a substring |
| **SPLIT_PART**| `SELECT SPLIT_PART(col, 'delim', n)` — n-th field (1-based) after splitting on delim |
| **LPAD / RPAD** | `SELECT LPAD(col, n [, 'pad'])`, `RPAD(col, n [, 'pad'])` — pad a string to length `n` on the left/right; `pad` defaults to a single space when omitted. A longer value is truncated to `n`; an empty field stays empty |
| **GREATEST / LEAST** | `SELECT GREATEST(a, b, ...)`, `LEAST(a, b, ...)` — row-wise max/min (numeric or lexicographic) |
| **ABS / SIGN / CEIL / FLOOR** | `SELECT ABS(col), SIGN(col), CEIL(col), FLOOR(col)` — numeric functions; `SIGN` returns `-1`, `0`, or `1` |
| **MOD** | `SELECT MOD(col, n)` — modulo by a numeric literal |
| **SQRT / POWER / LN / LOG / EXP / TRUNC** | `SELECT SQRT(col)`, `POWER(col, n)` (alias `POW`), `LN(col)`, `LOG(col)` (base 10), `LOG(base, col)`, `EXP(col)`, `TRUNC(col)` (toward zero, so `TRUNC(-2.7)` is `-2.0`). `n` and `base` are numeric literals. Results are doubles and print with a decimal point, like `CEIL`/`FLOOR`. A result that is not a finite number (`SQRT` of a negative, `LN`/`LOG` of zero or a negative, an overflowing `EXP`/`POWER`) is NULL and written as an empty field, where DuckDB raises an error. An empty field stays empty and non-numeric text is returned unchanged. A `LOG` base that is not positive or is `1` is a query error. `LOG(base, col)` for a base other than 2 or 10 is computed as a ratio of logarithms, so an answer that is mathematically a whole number can come back a last-bit off and can differ from other engines: `LOG(3, 243)` is `4.999999999999999` here against DuckDB's `5.0`, and `LOG(6, 216)` is `3.0000000000000004` against DuckDB's `2.9999999999999996`. Neither is more correct; there is no ratio of logarithms that is exact for every base. Wrap in `ROUND` if you need a stable result |
| **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 |
| **NULLIF / IFNULL** | `SELECT NULLIF(col, -1)`, `NULLIF(col, 'N/A')`, `IFNULL(col, 0)`. `NULLIF` turns a sentinel into NULL (an empty field) when the field equals the literal. A bare number compares as a number, so `NULLIF(age, 30)` also matches `30.0` and ` 30 `; a quoted literal compares byte for byte, so `NULLIF(code, '30')` does not match `30.0`. Anything else passes through unchanged, including an empty field. `IFNULL(col, fallback)` is `COALESCE` with exactly two arguments: the first must be a column, the second a column or a literal. A wrong argument count, or an unquoted non-numeric second argument to `NULLIF`, is a query error. These work in the SELECT list; like every scalar function they cannot yet be wrapped in an aggregate such as `AVG(NULLIF(col, -1))` |
| **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` |
| **CURRENT_DATE / NOW() / CURRENT_TIMESTAMP** | The current UTC date or time: `CURRENT_DATE` is `YYYY-MM-DD`, `NOW()` and `CURRENT_TIMESTAMP` are `YYYY-MM-DD HH:MM:SS`. The parentheses are optional on `CURRENT_DATE` and `CURRENT_TIMESTAMP` and required on `NOW()`, so a column called `now` still works. Usable in the `SELECT` list (the header is the text you wrote unless you give an alias) and as the value on the right of a `WHERE` comparison, e.g. `WHERE created_at >= CURRENT_DATE`. The clock is read once when the query is parsed, so every row, and a `col IN (SELECT ...)` subquery, sees the same instant. Set `CSVQL_NOW` to `YYYY-MM-DD` or `YYYY-MM-DD HH:MM:SS` (UTC) to pin it for scripts and tests; a value that does not parse is a query error. Not supported: arithmetic such as `CURRENT_DATE - 7`, `DATEADD`/`DATEDIFF` on the keywords (those take a column), and the keywords on the left of a `WHERE` comparison. Quoted text and longer identifiers such as `current_dates` are left alone, but a column named `current_date` cannot be selected unquoted |
| **ORDER BY** | `ORDER BY col [ASC\|DESC]`, multi-column `ORDER BY col1 ASC, col2 DESC`, alias, positional (`ORDER BY 1`), or a column not in the `SELECT` list (sorts by the raw source/grouping column) |
| **LIMIT / OFFSET** | `LIMIT n [OFFSET m]` — return up to `n` rows after skipping the first `m` result rows |
## Known differences from DuckDB
Two intentional differences, found via differential testing against DuckDB (see the [blog post](https://melihbirim.github.io/csvql/blog/what-one-comment-found.html)) and kept as-is rather than "fixed", documented here so they don't surprise anyone migrating queries:
| Behavior | csvql | DuckDB |
| -------- | ----- | ------ |
| `LENGTH(col)` on a unicode string | Byte length (UTF-8 bytes) | Character count (codepoints) |
| `LEFT(col, n)` / `RIGHT(col, n)` on a unicode string | `n` counts bytes; a character that would be split is dropped | `n` counts codepoints |
| `POSITION` / `INSTR` after a unicode character | The index counts bytes | The index counts codepoints |
| Empty CSV field | Stays an empty string in output and in `GROUP BY`; in `WHERE` it behaves like `NULL` (no comparison, `!=`, `NOT` or `NOT IN` matches it; `IS NULL` and `= ''` do) | Inferred as `NULL` |
| `MODE(col)` with several equally frequent values | The smallest value | Not specified |
| `DATEDIFF('hour'/'minute'/etc, a, b)` on a non-exact interval | Fractional (e.g. `8.5` hours) | Truncated to whole units (e.g. `8`) |
If you need character count instead of byte length, NULL instead of empty-string semantics, or truncated instead of fractional interval math, be aware the two engines diverge here rather than assume identical output.
## Known limitations
Not yet supported — these error clearly rather than silently returning wrong data, and are tracked as open issues:
| Limitation | Tracking |
| ---------- | -------- |
| Subqueries other than `col IN (SELECT ...)` / `col NOT IN (SELECT ...)` (non-correlated, single-column — see [CORRECTNESS.md](CORRECTNESS.md#subqueries)) | [#124](https://github.com/melihbirim/csvql/issues/124) |
| `UNION` / `INTERSECT` / `EXCEPT` | [#122](https://github.com/melihbirim/csvql/issues/122), [#127](https://github.com/melihbirim/csvql/issues/127) |
| Window functions (`RANK() OVER (...)`, etc.) | [#126](https://github.com/melihbirim/csvql/issues/126) |
## Aggregate Examples
```bash
# 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"
# Tail latency and most common value per group
csvql "SELECT endpoint, QUANTILE(latency_ms, 0.95) AS p95, MODE(status) AS common_status FROM 'logs.csv' GROUP BY endpoint"
# 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' THEN 1 ELSE 0 END) AS london_count FROM 'data.csv' GROUP BY department"
csvql "SELECT SUM(CASE WHEN status = 'active' THEN 1 ELSE 0 END) AS active, SUM(CASE WHEN status = 'inactive' THEN 1 ELSE 0 END) AS inactive FROM 'data.csv'"
csvql "SELECT product, COUNT(*) AS total, SUM(CASE WHEN status = 'returned' THEN 1 ELSE 0 END) AS returns FROM 'orders.csv' GROUP BY product ORDER BY returns DESC"
# DISTINCT
csvql "SELECT DISTINCT city FROM 'data.csv' ORDER BY city"
csvql "SELECT DISTINCT city, department FROM 'data.csv'"
# DISTINCT with WHERE
csvql "SELECT DISTINCT department FROM 'data.csv' WHERE salary > 100000"
```
## Scalar Function Examples
Scalar functions transform column values row-by-row in `SELECT`. They can also be used in `GROUP BY` projections.
```bash
# String functions
csvql "SELECT UPPER(name), LOWER(city), TRIM(notes) FROM 'data.csv'"
csvql "SELECT name, LENGTH(name), SUBSTR(name, 1, 3) FROM 'data.csv'"
csvql "SELECT LTRIM(name), RTRIM(name), LEFT(code, 2), RIGHT(file, 4) FROM 'data.csv'"
csvql "SELECT REPEAT(code, 3), POSITION('@' IN email), INSTR(email, '.') FROM 'data.csv'"
# Numeric functions
csvql "SELECT name, ABS(balance), SIGN(balance), CEIL(score), FLOOR(score) FROM 'data.csv'"
csvql "SELECT name, MOD(age, 10) AS age_decade FROM 'data.csv'"
csvql "SELECT SQRT(area), POWER(side, 2), LN(price), LOG(2, size), TRUNC(delta) FROM 'data.csv'"
csvql "SELECT name, ROUND(price) AS rounded, ROUND(price, 2) AS price_2dp FROM 'data.csv'"
# COALESCE — replace empty values with a fallback
csvql "SELECT name, COALESCE(email, 'unknown') FROM 'data.csv'"
csvql "SELECT COALESCE(phone, COALESCE(email, 'no contact')) FROM 'contacts.csv'"
# NULLIF turns a sentinel into NULL; IFNULL is COALESCE with two arguments
csvql "SELECT name, NULLIF(age, -1) AS age, IFNULL(email, 'unknown') AS email FROM 'data.csv'"
# CAST — explicit type conversion
csvql "SELECT name, CAST(price AS INTEGER), CAST(id AS TEXT) FROM 'products.csv'"
csvql "SELECT CAST(year AS INTEGER) AS yr, SUM(revenue) FROM 'data.csv' GROUP BY yr"
# ILIKE — case-insensitive LIKE
csvql "SELECT * FROM 'data.csv' WHERE name ILIKE '%smith%'"
csvql "SELECT * FROM 'data.csv' WHERE email ILIKE '%@gmail.com'"
# Scalar functions work with GROUP BY
csvql "SELECT UPPER(city), COUNT(*) FROM 'data.csv' GROUP BY city"
csvql "SELECT LOWER(department) AS dept, AVG(salary) FROM 'data.csv' GROUP BY department"
# Mix scalars with AS aliases
csvql "SELECT UPPER(name) AS Name, CAST(salary AS INTEGER) AS Salary FROM 'data.csv' ORDER BY Salary DESC"
# CONCAT — combine columns and string literals
csvql "SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM 'data.csv'"
csvql "SELECT CONCAT(name, ' (', department, ')') FROM 'data.csv'"
# Nested functions — one level of composition
csvql "SELECT name, LOWER(TRIM(department)) FROM 'data.csv'"
csvql "SELECT department, LENGTH(TRIM(department)) FROM 'data.csv' GROUP BY department"
# IS NULL / IS NOT NULL as a SELECT expression
csvql "SELECT name, email IS NOT NULL AS has_email FROM 'data.csv'"
# Table alias outside JOIN
csvql "SELECT t.name, t.salary FROM 'data.csv' AS t WHERE t.salary > 100000"
```
## WHERE Filter Examples
```bash
# Comparison operators
csvql "SELECT name, salary FROM 'data.csv' WHERE salary > 80000"
# BETWEEN — inclusive range (numeric or string)
csvql "SELECT name, salary FROM 'data.csv' WHERE salary BETWEEN 50000 AND 80000"
csvql "SELECT * FROM 'orders.csv' WHERE order_date BETWEEN '2025-01-01' AND '2025-12-31'"
# IN — membership test
csvql "SELECT name FROM 'data.csv' WHERE city IN ('London', 'Paris', 'Berlin')"
# IS NULL / IS NOT NULL — test for missing (empty) fields
csvql "SELECT * FROM 'data.csv' WHERE email IS NULL"
csvql "SELECT * FROM 'data.csv' WHERE email IS NOT NULL"
# NOT — negate any condition
csvql "SELECT * FROM 'data.csv' WHERE NOT city IN ('London', 'Paris')"
csvql "SELECT * FROM 'data.csv' WHERE NOT salary BETWEEN 40000 AND 60000"
# AND / OR — compound conditions
csvql "SELECT * FROM 'data.csv' WHERE age > 30 AND department = 'Engineering'"
csvql "SELECT * FROM 'data.csv' WHERE city = 'London' OR city = 'Berlin'"
csvql "SELECT * FROM 'data.csv' WHERE status LIKE 'active%' AND salary > 50000"
# AS alias + ORDER BY alias or positional
csvql "SELECT name AS employee, salary AS pay FROM 'data.csv' ORDER BY pay DESC LIMIT 10"
csvql "SELECT city, COUNT(*) AS cnt FROM 'data.csv' GROUP BY city ORDER BY cnt DESC"
csvql "SELECT name, salary FROM 'data.csv' ORDER BY 2 DESC LIMIT 5" # ORDER BY positional
```
## ORDER BY Examples
```bash
# Single-column ORDER BY
csvql "SELECT name, salary FROM 'data.csv' ORDER BY salary DESC LIMIT 10"
csvql "SELECT * FROM 'data.csv' ORDER BY name ASC"
# Multi-column ORDER BY — sort by primary key, then break ties with secondary key(s)
csvql "SELECT name, department, salary FROM 'data.csv' ORDER BY department ASC, salary DESC"
csvql "SELECT * FROM 'data.csv' ORDER BY city, age, name"
csvql "SELECT name, city, salary FROM 'employees.csv' WHERE salary > 80000 ORDER BY city ASC, salary DESC"
# Multi-column ORDER BY with GROUP BY results
csvql "SELECT department, AVG(salary) AS avg_sal FROM 'data.csv' GROUP BY department ORDER BY avg_sal DESC, department ASC"
# ORDER BY alias (resolved from SELECT clause)
csvql "SELECT name, salary AS pay FROM 'data.csv' ORDER BY pay DESC LIMIT 5"
# ORDER BY positional (1-based column index)
csvql "SELECT name, city, salary FROM 'data.csv' ORDER BY 3 DESC LIMIT 10"
```
## Time-Series and Date Bucketing
`STRFTIME('%fmt', column)` extracts or truncates date components for time-series aggregation.
Supported format specifiers: `%Y` (year), `%m` (month), `%d` (day), `%H` (hour), `%M` (minute), `%S` (second).
Input dates can be ISO-8601 date (`YYYY-MM-DD`) or datetime (`YYYY-MM-DD HH:MM:SS`).
```bash
# Monthly revenue trend — GROUP BY the full STRFTIME expression
csvql "SELECT STRFTIME('%Y-%m', order_date), COUNT(*), SUM(price) FROM 'orders.csv' GROUP BY STRFTIME('%Y-%m', order_date)"
# Same query using AS alias — GROUP BY the alias name
csvql "SELECT STRFTIME('%Y-%m', order_date) AS month, COUNT(*) AS orders, SUM(price) AS revenue FROM 'orders.csv' GROUP BY month ORDER BY month"
# Year-over-year breakdown by category
csvql "SELECT category, STRFTIME('%Y', order_date) AS yr, SUM(price) FROM 'orders.csv' GROUP BY category, yr"
# Date range filter + monthly bucketing + HAVING
csvql "SELECT STRFTIME('%Y-%m', order_date) AS month, COUNT(*), SUM(price) FROM 'orders.csv' WHERE order_date >= '2026-01-01' GROUP BY month HAVING COUNT(*) > 1000000"
# Daily active users
csvql "SELECT STRFTIME('%Y-%m-%d', event_date) AS day, COUNT(DISTINCT user_id) FROM 'events.csv' GROUP BY day ORDER BY day"
```
## DateTime and Duration Functions
`DATEDIFF` and `DATEADD` work with **four datetime formats** in the same CSV — no pre-processing needed:
| Format | Example |
|--------|---------|
| ISO-8601 (space) | `2026-01-15 09:30:00` |
| ISO-8601 (T) | `2026-01-16T10:00:00` |
| US (MM/DD/YYYY) | `01/15/2026 08:00:00` |
| EU (DD.MM.YYYY) | `15.01.2026 07:30:00` |
```bash
# Order workflow: time from order to pick (in minutes)
csvql "SELECT order_id, DATEDIFF('minute', ordered_at, picked_at) AS pick_min FROM 'orders.csv' WHERE picked_at != ''"
# Delivery time in days
csvql "SELECT order_id, DATEDIFF('day', shipped_at, delivered_at) AS ship_days FROM 'orders.csv' WHERE shipped_at != '' AND delivered_at != '' ORDER BY ship_days DESC"
# SLA check — DATEDIFF, DATEADD and EXTRACT can be compared in WHERE (other scalar functions are SELECT-only for now)
csvql "SELECT order_id, customer_name, DATEDIFF('hour', ordered_at, picked_at) AS hrs FROM 'orders.csv' WHERE DATEDIFF('hour', ordered_at, picked_at) > 2"
# Average processing time by status
csvql "SELECT status, AVG(DATEDIFF('minute', ordered_at, packaged_at)) AS avg_proc_min FROM 'orders.csv' WHERE packaged_at != '' GROUP BY status ORDER BY avg_proc_min"
# DATEADD — compute SLA deadlines
csvql "SELECT order_id, ordered_at, DATEADD('hour', 2, ordered_at) AS pick_deadline FROM 'orders.csv'"
# Estimated delivery date (ship date + 2 days)
csvql "SELECT order_id, shipped_at, DATEADD('day', 2, shipped_at) AS est_delivery FROM 'orders.csv' WHERE shipped_at != ''"
# Supported units for both functions: second, minute, hour, day, week, month (approx 30 days), year (approx 365 days)
csvql "SELECT order_id, DATEDIFF('second', ordered_at, picked_at) AS pick_secs FROM 'orders.csv'"
csvql "SELECT order_id, DATEADD('week', -1, delivered_at) AS sent_reminder FROM 'orders.csv'"
```
```bash
# Relative to now: the clock is fixed once per query
csvql "SELECT COUNT(*) FROM 'events.csv' WHERE created_at >= CURRENT_DATE"
csvql "SELECT id, NOW() AS queried_at FROM 'events.csv' LIMIT 5"
# Pin the clock for a reproducible run
CSVQL_NOW="2026-03-05 07:08:09" csvql "SELECT CURRENT_DATE, NOW() FROM 'events.csv' LIMIT 1"
```
**Mixed formats work automatically** — a single CSV can have some dates as `2026-01-15 09:30:00`, others as `01/15/2026 08:00:00`, and `DATEDIFF` handles them all.
## JOIN Examples
```bash
# Basic INNER JOIN — select columns from both tables using aliases
csvql "SELECT e.name, d.dept_name FROM 'employees.csv' e INNER JOIN 'departments.csv' d ON e.dept_id = d.id"
# Bare JOIN (INNER is optional)
csvql "SELECT e.name, d.dept_name FROM 'employees.csv' e JOIN 'departments.csv' d ON e.dept_id = d.id"
# JOIN with WHERE — filter on joined columns
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'"
# SELECT * on join returns all columns from both tables
csvql "SELECT * FROM 'orders.csv' o JOIN 'customers.csv' c ON o.customer_id = c.id"
# JOIN with LIMIT
csvql "SELECT e.name, d.dept_name FROM 'employees.csv' e JOIN 'departments.csv' d ON e.dept_id = d.id LIMIT 10"
```
**Notes:**
- Table aliases are required when using qualified column references (`alias.col`)
- Unqualified column names are resolved from the left table first, then the right
- The right table is fully loaded into memory (build side); the left table is streamed (probe side)
## Simple Mode
Positional args: `csvql <file> [columns] [filter] [limit] [sort]`
```bash
csvql data.csv "name,salary" "age>30" 10 "salary:desc"
```
See [SIMPLE_QUERY_LANGUAGE.md](SIMPLE_QUERY_LANGUAGE.md) for the full reference.
==================== SECURITY.md ====================
# Security
## Reporting a vulnerability
Report privately through GitHub's [Security Advisories](https://github.com/melihbirim/csvql/security/advisories/new) for this repository. Please do not open a public issue for security reports. You will get an acknowledgement, and a fix or mitigation plan once the report is triaged.
## Supported versions
Fixes land on the latest release. Please upgrade to the latest version before reporting.
## Security posture
csvql is built to be safe to run next to sensitive data, including on corporate servers.
**Read only.** csvql runs `SELECT` only. The parser has no `INSERT`, `UPDATE`, `DELETE`, or `DROP`. It cannot modify, delete, or write your data files.
**No data egress.** Data stays on the machine. A query returns only its result, never the file. Over MCP, `csv_query` results are capped (about 12 KB per response), which also limits how much an agent can pull out across queries.
**No network, air gappable.** csvql makes zero outbound connections. No telemetry, no cloud, no API keys, no update checks. It runs fully offline. Verified by reading the source, not just asserted: there is no `std.net`, `std.http`, or socket call anywhere in the codebase.
**No unrequested disk writes.** csvql writes to disk only when you tell it to: `-o/--output <file>` and `--audit <file>` are the only two ways to make it write something, and both require an explicit flag.
A few internal operations (resolving `IN (SELECT ...)` subqueries, staging a JOIN followed by GROUP BY, and `--table` output rendering) need a scratch buffer while a query runs. **On Linux**, that scratch buffer is `memfd_create()` — an anonymous, RAM-backed file descriptor that was never given a path and is never written to a real disk block. **On macOS and Windows**, those same four internal operations still fall back to a real, immediately-deleted temp file in `$TMPDIR`/`%TEMP%` — a known, tracked gap, not a silent one. See [#164](https://github.com/melihbirim/csvql/issues/164) for the work closing it (macOS's POSIX shared-memory alternative to `memfd_create` turns out not to support the streaming-write pattern csvql needs; closing this fully needs a small internal refactor, not a workaround).
If you're evaluating csvql for an air-gapped or hardened-filesystem environment, verify this claim yourself rather than take it on faith — `strace -f -e trace=network,open,openat` (Linux) or the equivalent will show it directly. That's the point: this is meant to be provable, not just stated.
**Filesystem scoping.** By default csvql can read any file the process user can. Pass `--root <dir>[,<dir>]` to confine all file access (CLI and MCP) to those directory trees. Paths are resolved with `realpath`, so `../` traversal and symlink escape are rejected. Always set `--root` when exposing csvql to an agent or another user.
**Minimal attack surface.** A single static binary. No runtime, no interpreter, no dependencies to track for CVEs. With the SSH access model there is no long running daemon and no open port.
## Hardening for on prem and remote access
When running csvql on a server that an agent or remote user queries:
* Set `--root` to the data directory so nothing else is readable.
* Run as a dedicated, unprivileged user with a read only mount of the data.
* Prefer the SSH access model (`ssh host csvql --mcp --root /data`). It reuses your existing SSH keys, bastion, and audit trail, and needs no open port.
* Enable `--audit <file>` to record every query (timestamp and SQL) as JSONL for compliance and forensics.
* If you expose an HTTP transport later, keep csvql behind a reverse proxy that terminates TLS and handles authentication.
See the README section "Remote and on-prem" for the SSH setup.
Does it work?
Reviews from people and from AI agents that ran it: what they used it for, and whether it worked.
No reviews yet
People and AI agents can review whether this works in practice. Say what you used it for, and whether it worked.
- It worked
- Partly
- It didn't
Using an agent? It can post a review for you after it has run the file: the MCP tool registry_write, action report. How to connect one.
Your trail
Files you open will collect here, so you can find them again.
Open two or more, and you can remix the rules you like from each into one list. Kept in this browser only.
Now do this for your own.
Bring your CLAUDE.md, AGENTS.md or skills. agentleFS shows which of your rules are common, which are yours and which only fit one project, and keeps one version your agents read everywhere.

