sqly runs SQL against CSV, TSV, LTSV, JSON, JSONL, Parquet, Excel, ACH, and Fedwire files. It loads them into an in-memory SQLite3 database, so joins, CTEs, window functions, and aggregates all work — across formats, in one query. Compressed files (.gz, .bz2, .xz, .zst, .z, .snappy, .s2, .lz4) are read transparently.
Documentation: https://nao1215.github.io/sqly/
This documentation describes v1.0.0. 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 a text input that is not valid UTF-8 refused rather than loaded as mojibake — so read the CHANGELOG before upgrading.
If you have Go, paste this:
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. No schema to declare, no import step.
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 long-standing, mature tooling | q, textql |
| SQL over files, with an interactive shell, cross-format joins, and write-back | sqly |
sqly's emphasis is the session: an interactive shell with completion and history, files of different formats joined as peers, and the ability to write edits back into the source file.
go install github.com/nao1215/sqly@latestbrew install nao1215/tap/sqlyArch Linux users can install the AUR package:
yay -S sqly-binsqly is in the aqua standard registry, and mise installs it through the same registry:
aqua g -i nao1215/sqly
mise use aqua:nao1215/sqlyPrebuilt binaries are on the release page. Runs on Windows, macOS, and Linux; building from source needs Go 1.25 or later. Releases ship cosign-signed checksums, an SPDX SBOM, and SLSA provenance — see Install for the verification commands.
--inspect prints the tables a file becomes, their columns, and their row counts — as JSON, so jq can read it too. It is schema-only: it does not print the data.
sqly --inspect user.csv{
"schema_version": 1,
"sqly_version": "v1.0.0",
"tables": [
{
"name": "user",
"source": "/home/nao/data/user.csv",
"row_count": 3,
"columns": [
{ "name": "user_name", "type": "TEXT", "nullable": true, "primary_key": false },
{ "name": "identifier", "type": "INTEGER", "nullable": true, "primary_key": false }
],
"sample_rows": []
}
]
}Add --inspect-sample N when you do want rows, and you get at most N per table:
sqly --inspect --inspect-sample 1 user.csvschema_version says how to read the document and sqly_version says which binary wrote it. The contract, the compatibility policy, and the JSON Schema are on the reference page.
The file is the table, whatever the format: a gzipped CSV and a Parquet file join like two tables in one database.
sqly --sql "SELECT u.user_name, i.position
FROM user u JOIN identifier i ON u.identifier = i.id" user.csv.gz identifier.parquet+-----------+-----------+
| user_name | position |
+-----------+-----------+
| booker12 | developrt |
| jenkins46 | manager |
| smith79 | neet |
+-----------+-----------+
The destination's extension picks the format; --output-format names it when the extension cannot.
sqly --output user.json --sql "SELECT * FROM user" user.csv
sqly --output user.xlsx --sql "SELECT * FROM user" user.csvOutput sql result to user.json (output mode=json)
A JSON file becomes one table with a data column; SQLite's json_extract() reaches into it.
sqly --sql "SELECT json_extract(data, '$.name') AS name,
json_extract(data, '$.city') AS city FROM sample" sample.jsonl+---------+--------+
| name | city |
+---------+--------+
| Alice | Tokyo |
| Bob | Osaka |
| Charlie | Nagoya |
+---------+--------+
--output-format vertical prints one column per line, in a block per record.
sqly --output-format vertical --sql "SELECT * FROM actor LIMIT 1" actor.csv-[ RECORD 1 ]-----------------------------------------------
actor | Harrison Ford
total_gross | 4871.7
number_of_movies | 41
average_per_movie | 118.8
best_movie | Star Wars: The Force Awakens
gross | 936.7
jsonl for jq, tsv for cut/awk/sort. Both write nothing but the rows, so a pipeline stays a pipeline.
sqly --output-format jsonl --sql "SELECT user_name, identifier FROM user" user.csv | jq -r '.user_name'
sqly --output-format tsv --sql "SELECT status, path FROM logs" logs.csv | cut -f1 | sort -rn | head -n 1{"user_name":"booker12","identifier":1}
{"user_name":"jenkins46","identifier":2}
sqly ./data --sql "SELECT * FROM users"
sqly --allow-remote --sql "SELECT * FROM user" https://example.com/user.csv
cat user.csv | sqly --stdin-format csv --sql "SELECT COUNT(*) FROM stdin"A URL needs --allow-remote: without it sqly refuses the input and makes no HTTP
request at all, so a wrapper that never passes the flag has turned sqly's
downloading off. It is a network capability, not a sandbox or an SSRF defense —
it decides whether a request happens, not where it may go. The download limits,
and what the capability does not protect against, are on the
formats page.
SQLite is the engine, so its whole query language is available — window functions, CTEs, json_*, and the rest.
sqly --sql "SELECT actor, RANK() OVER (ORDER BY total_gross DESC) AS rank FROM actor" actor.csv+-------------------+------+
| actor | rank |
+-------------------+------+
| Harrison Ford | 1 |
| Samuel L. Jackson | 2 |
| Morgan Freeman | 3 |
+-------------------+------+
sqly --dialect postgresql --sql "SELECT user_name, identifier::text FROM \"user\" WHERE user_name ILIKE 'b%'" user.csv--dialect is translation, not emulation: constructs with no SQLite equivalent are rejected by name, and SQL that SQLite accepts is passed through, where the answer can differ from the source dialect. The dialects page lists both, with the divergences sqly knows about.
sqly --sql-file examples/report.sql examples/data/sales.csv
sqly --script-file examples/update.sqly examples/data/sales.csv--sql-file takes SQL only; a dot-command in it is a usage error. --script-file
takes what the shell takes — SQL and dot-commands alike — so it is the one to use
when the script has a side effect such as .save. --script-file rejects
--output; use .dump inside the script instead.
Both are runnable in examples/, along with join.sql, which
answers one query over a CSV and a JSONL at once. The
reference compares the script flags
in full and the cookbook has more.
sqly with no --sql opens a REPL: tab completion for keywords, tables, columns, and paths, history across sessions, and dot-commands for everything SQL has no syntax for.
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
.help lists every command; the shell page documents them.
A session is in-memory only. .save writes the tables the session changed back
out — in the shell, or in a script piped to sqly:
printf "UPDATE user SET first_name = 'Rachelle' WHERE identifier = 1;\n.save ./out\n" | sqly user.csv.save DIR writes copies into DIR and leaves the sources alone; .save --in-place overwrites them. Format and compression are preserved either way,
and an in-place save keeps the source file's permissions; a copy into DIR is a
new file and is created 0600. A table the session did not change is not
rewritten, and a save covering several files is all-or-nothing. See the shell
page for what it can and cannot write.
Writing a query result somewhere is a different job, and that one is a flag:
--output.
| Format | Extensions | Becomes |
|---|---|---|
| CSV / TSV / LTSV | .csv .tsv .ltsv |
one table, columns from the header |
| JSON / JSONL | .json .jsonl |
one table with a data column; query with json_extract() |
| Parquet | .parquet |
one table |
| Excel | .xlsx |
one table per sheet, named file_sheet; only the sheets the workbook shows, unless --include-hidden-sheets |
| ACH | .ach |
several tables (_file_header, _batches, _entries, _addenda) |
| Fedwire | .fed |
one _message table |
Multiple inputs are loaded atomically: if one file cannot be read, none of them are imported and the run exits 3. See the cookbook.
All except ACH and Fedwire also read the compression extensions above. Text inputs without a BOM decode as UTF-8, or as Shift-JIS, EUC-JP, ISO-2022-JP, or UTF-16 with --encoding. See Formats.
sqly has no subcommands: sqly --help, not sqly help. The flags fall into five groups — input, query, output, inspection, and the two meta ones — and sqly --help lists them that way. Everything else, including writing changes back, is a dot-command inside the shell, run at the prompt, from a piped script, or from a --script-file.
The reference lists every flag and what it applies to, the multi-result rules, the table name rules, and the exit codes.
sqly runs each statement in its own transaction on an in-memory database, so a few SQLite statements are rejected with a clear error rather than failing confusingly:
- Explicit transaction control:
BEGIN,COMMIT,ROLLBACK,SAVEPOINT,RELEASE VACUUM/VACUUM INTO, andATTACH/DETACH DATABASE- DCL such as
GRANT/REVOKE
A historical measurement from sqly v0.30.0: importing 100,000 rows and querying them took about half a second, which was the same range as the CSV-focused tools. It has not been re-measured since and is not a performance guarantee for the current release. The numbers, the comparison, and the machine they were measured on are on the about page.
Thanks for taking the time to contribute; see CONTRIBUTING.md and how to build and test. Contributions are not only about code: a GitHub Star also motivates development.
When adding features or fixing bugs, please write unit tests. The README demos are recorded with charmbracelet/vhs from doc/vhs/*.tape (make demo); the commands they show are separately asserted against the real binary by the atago specs in e2e/atago/ (make test-e2e). The documentation site is built from website/ with make website.
Bugs and feature requests go to GitHub Issues.
- filesql — the
database/sqldriver that loads and writes back every supported file format, and the dialect translation behind--dialect - prompt — the line editor behind the interactive shell
- atago — the end-to-end test runner: it drives the real
sqlybinary, not a mock, from the plain-YAML specs ine2e/atago/.make test-e2eruns that suite locally, and CI runs it on Linux, macOS, and Windows after installing atago with setup-atago
sqly is a memorable project for me because it connected me with two GitHub Sponsors.
Adam Shannon, who works in the payments industry at Moov, inspired sqly's support for financial file formats such as ACH and Fedwire. Shoki Hata, a colleague of mine, has actively used both filesql and sqly; feedback from an actual user improved filesql's performance and led to many bug fixes in sqly.
Thank you both for supporting the project as sponsors, and thanks to everyone who has improved sqly through code, documentation, bug reports, and other contributions.
The sqly project is licensed under the terms of MIT LICENSE.
Thanks goes to these wonderful people (emoji key):
 CHIKAMATSU Naohiro 💻 📖 |
 Wozzardman 💻 |
 edsilegxrepo 💻 |
 まるこめ 💻 |
 Jon Edvardsson 🐛 |
 JGStew 🐛 |
 Ricardo Seriani 🐛 |
 Ephraim Steve Micaiah 🐛 |
 Adam Shannon 💵 🤔 |
 Shoki Hata 💵 🤔 📓 |
||||
Add your contributions
|
||||||
This project follows the all-contributors specification. Contributions of any kind welcome!