sqly

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/

Try it in 30 seconds

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.

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 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.

Install

go install github.com/nao1215/sqly@latest

brew install nao1215/tap/sqly

Arch Linux users can install the AUR package:

yay -S sqly-bin

sqly is in the aqua standard registry, and mise installs it through the same registry:

aqua g -i nao1215/sqly
mise use aqua:nao1215/sqly

Prebuilt 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.

Recipes

Look at a file you have never seen

--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.csv

schema_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.

Join two files, of any format, in one query

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      |
+-----------+-----------+

Convert between formats

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.csv

Output sql result to user.json (output mode=json)

Query JSON and JSONL

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 |
+---------+--------+

Read a row too wide for the terminal

--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

Pipe the result into another tool

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}

Load a directory, a URL, or standard input

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.

Rank, window, aggregate

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 |
+-------------------+------+

Write MySQL, PostgreSQL, or BigQuery syntax

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.

Run SQL or a sqly script from a file

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.

The shell

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. Completion reads the statement being typed — a table position offers tables, a condition offers the columns of the tables in the FROM, and alias. offers that table's columns.

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

.edit reopens the last statement in $VISUAL or $EDITOR and runs what you save, so a long query is edited rather than retyped. SQL is colored as you type it; .theme switches between the themes and remembers the choice.

.help lists every command; the shell page documents them.

Write changes back

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.

Formats

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.

Flags

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.

Limitations

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, and ATTACH / DETACH DATABASE
• DCL such as GRANT / REVOKE

Benchmark

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.

Contributing

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.

Libraries and tools used

• filesql — the database/sql driver 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 sqly binary, not a mock, from the plain-YAML specs in e2e/atago/. make test-e2e runs that suite locally, and CI runs it on Linux, macOS, and Windows after installing atago with setup-atago

Acknowledgments

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.

LICENSE

The sqly project is licensed under the terms of MIT LICENSE.

Contributors ✨

Thanks goes to these wonderful people (emoji key):

CHIKAMATSU Naohiro 💻 📖	Wozzardman 💻	edsilegxrepo 💻	まるこめ 💻	Jon Edvardsson 🐛	JGStew 🐛	Ricardo Seriani 🐛
Ephraim Steve Micaiah 🐛	Adam Shannon 💵 🤔	Shoki Hata 💵 🤔 📓	Rafael Baboni Dominiquini 📦
Add your contributions

This project follows the all-contributors specification. Contributions of any kind welcome!