SQL against CSV, TSV, LTSV, JSON, JSONL, Parquet, Excel, ACH, and Fedwire

sqly runs SQL against CSV, TSV, LTSV, JSON, JSONL, Parquet, Excel, ACH, and Fedwire files. It loads them into an in-memory SQLite database, so joins, CTEs, window functions, and aggregates all work — across formats, in one query.

This site describes v1.0.0-rc8, the final release candidate. It carries substantial breaking changes over v0.x — classified exit codes, visible-only Excel sheets, SIGTERM 143, multiple inputs as one atomic import, a schema-only --inspect, default-deny remote input, stdout carrying nothing but data in every machine-readable format, an export that refuses a value it cannot write rather than changing it, a shell with .header removed, .mode limited to formats a screen can show, and its history in a text file, and now a text input that is not valid UTF-8 refused rather than loaded as mojibake — and only bug fixes and documentation changes land between it and v1.0.0, so read the CHANGELOG before upgrading.

sqly running a query against a CSV file

Try it in 30 seconds #

printf 'name,dept,salary\nalice,eng,120\nbob,sales,90\ncarol,eng,140\n' > staff.csv
go run github.com/nao1215/sqly@latest --sql "SELECT dept, ROUND(AVG(salary)) AS avg FROM staff GROUP BY dept" staff.csv

+-------+-----+
| dept  | avg |
+-------+-----+
| eng   | 130 |
| sales |  90 |
+-------+-----+

The file is the table: staff.csv became staff. Nothing to declare, no schema to write.

Three things to try next #

sqly --output-format json --sql "SELECT * FROM staff" --output staff.json staff.csv   # convert
sqly --sql "SELECT * FROM a JOIN b ON a.id = b.id" a.csv b.parquet      # join across formats
sqly staff.csv                                                          # open the shell

The cookbook has the rest: JSON extraction, Excel sheets, HTTP inputs, inspecting data, writing changes back, and MySQL/PostgreSQL/BigQuery syntax.

It reads and writes a pipe #

sqly is a filter, not a destination. It takes standard input, and its non-table output is meant for the next command:

curl -s https://example.com/sales.csv | sqly --stdin-format csv --sql "SELECT region, SUM(amount) FROM stdin GROUP BY region"
sqly --output-format jsonl --sql "SELECT path FROM logs WHERE status >= 500" logs.csv | jq -r '.path'
sqly --output-format tsv --sql "SELECT status, path FROM logs" logs.csv | cut -f1 | sort -rn | head -n 1

Filter in SQL, shape in jq: SQL has the WHERE, GROUP BY, and JOIN, so jq only ever sees the rows that matter. For nested JSON, json_extract reaches into the document and sqly can stand in for jq entirely. A failed query exits non-zero, so set -e works. Pipe data out has the details.

Why sqly? #

Pick the tool that fits the job:

You want	Use
A field-oriented text processor for logs and columns	awk, Miller
A CSV-native SQL dialect with its own engine and cursors	csvq
SQL over CSV/TSV/JSON with a choice of backend engines	trdsql
SQL over CSV with mature Python tooling	q, textql
SQL over files, with an interactive shell, cross-format joins, and write-back	sqly

sqly’s own emphasis is the session: an interactive shell with completion and history, files of different formats joined as peers, and the ability to write your edits back into the source file.

The shell #

sqly with no --sql opens a REPL. Tab completes keywords, table names, and paths; history persists across sessions; dot-commands cover the things SQL has no syntax for.

the sqly interactive shell

sqly:~/data(table)$ .import user.csv
sqly:~/data(table)$ SELECT user_name FROM user
   ...> WHERE identifier = 1;
sqly:~/data(table)$ .mode json
sqly:~/data(json)$ .save ./out

See Shell for every dot-command.

Install #

go install github.com/nao1215/sqly@latest

Homebrew, the AUR, aqua, mise, and prebuilt binaries are on the install page.