# query-forge
[](https://crates.io/crates/query-forge)
[](https://docs.rs/query-forge)
[](LICENSE)
Rust CLI to run SQL queries on one or more XLSX/XML/CSV/JSON/JSONL/Markdown/HTML/Parquet inputs and produce output in text, CSV, JSONL, Markdown, HTML, XML, or a new XLSX file.
## Installation
From crates.io:
```bash
cargo install query-forge
```
From source (local development):
```bash
cargo install --path .
```
## Quick start
Run an inline query:
```bash
qf query \
--input ./input.xlsx:Sheet1 \
--sql "SELECT * FROM table WHERE amount > 10"
```
Or load the query from an SQL file:
```bash
qf query \
--input ./input.xlsx \
--sql-file ./query.sql
```
## Key features
- SQL queries on XLSX/XML/CSV/JSON/JSONL/Markdown/HTML/Parquet data using in-memory SQLite.
- Multi-file support with automatic table mapping: `table`/`table1`, `table2`, `table3`, ...
- Explicit table naming: `name=file.xlsx` assigns a custom SQL table name for readability.
- Sheet selection with `file.xlsx:SheetName` or `file.xlsx#SheetName`.
- For XML inputs, if no sheet/tag is provided the whole file is used; if a sheet is provided, only the subtree inside that XML tag is used.
- CSV, JSON, JSONL, Markdown, HTML and Parquet inputs are supported directly (`.csv`, `.json`, `.jsonl`, `.md`, `.markdown`, `.html`, `.htm`, `.parquet`) and can be mixed with XLSX/XML in the same query.
- In `qf query`, sheet/tag/key selection is expressed via `file:selector` or `file#selector` (XLSX, XML, JSON, Markdown, HTML; for Markdown and HTML, selector is 1-based table index); CSV/JSONL/Parquet do not support selector syntax.
- **Richer JSON extraction modes** via `--json-mode array|object|flatten` — handle arrays, objects, and arbitrarily nested JSON documents.
- **Richer XML extraction modes** via `--xml-mode rows|descendants|attributes` — extract tabular rows, all leaf text nodes, or element attributes as columns.
- **Large CSV/JSONL ingestion improvements** — CSV rows are parsed in one pass, JSONL is read line-by-line, and SQLite registration uses batched inserts.
- Named SQL parameters from CLI with `--param`.
- Output export in `txt`, `csv`, `json`, `jsonl`, `markdown`, `html`, `xml`, `xlsx`, `parquet`.
- Support for headerless sheets with `--no-headers` (`column1`, `column2`, ...).
- **Header normalization** via `--normalize-headers` with `--header-case snake|camel|pascal|screaming-snake` and `--dedupe-headers`.
- **Shell completion scripts** for bash, zsh, fish, elvish, and PowerShell via `qf completions <SHELL>`.
- **Man page generation** via `qf man-page`.
- **Dataset diffing** via `qf diff` with key-based or positional comparison, schema-only mode, and diff-friendly exit codes.
## Examples
### Multiple inputs
```bash
qf query \
--input ./consuntivo.xlsx:Consuntivo \
--input ./wkl.xlsx:WKL \
--sql-file ./wbs-cons-and-wkl-by-month.sql \
--param wbs=TEST_VAL
```
### Explicit table names
Assign readable names to each input for use in SQL queries:
```bash
qf query \
--input sales=./sales.xlsx:Q1 \
--input costs=./costs.csv \
--sql "SELECT * FROM sales JOIN costs ON sales.id = costs.id"
```
Without a name prefix, tables are automatically named `table`, `table2`, `table3`, etc.
### Parameterized query
```sql
SELECT *
FROM table
```
Parameters:
```bash
--param wbs=TEST_VAL --param min_ore=8
```
Multiple values for the same parameter (separated by `,` or `;`):
```bash
--param "wbs=TEST_VAL,IEV092500011.2.3"
```
### XML input
Use the whole XML file:
```bash
qf query \
--input ./inventory.xml \
--sql "SELECT name, price FROM table WHERE active = 1 ORDER BY price DESC"
```
Use only a specific XML tag as sheet:
```bash
qf query \
--input ./inventory.xml:Inventory \
--sql "SELECT name, price FROM table ORDER BY price DESC"
```
### CSV/JSON/JSONL/Markdown/HTML/Parquet input
CSV:
```bash
qf query \
--input ./inventory.csv \
--sql "SELECT product, price FROM table WHERE active = 1 ORDER BY price DESC"
```
JSONL:
```bash
qf query \
--input ./inventory.jsonl \
--sql "SELECT product, price FROM table WHERE active = 1 ORDER BY price DESC"
```
JSON (array at root):
```bash
qf query \
--input ./inventory.json \
--sql "SELECT product, price FROM table WHERE active = 1 ORDER BY price DESC"
```
JSON with key selection as sheet:
```bash
qf query \
--input ./inventory.json:Inventory \
--sql "SELECT product, price FROM table ORDER BY price DESC"
```
Markdown (first table by default):
```bash
qf query \
--input ./inventory.md \
--sql "SELECT product, price FROM table WHERE active = 1 ORDER BY price DESC"
```
Markdown with table index key (second table):
```bash
qf query \
--input ./inventory.md:2 \
--sql "SELECT product, price FROM table"
```
HTML (first `<table>` by default):
```bash
qf query \
--input ./inventory.html \
--sql "SELECT product, price FROM table WHERE active = 1 ORDER BY price DESC"
```
HTML with table index key (second table):
```bash
qf query \
--input ./inventory.html:2 \
--sql "SELECT product, price FROM table"
```
Parquet:
```bash
qf query \
--input ./inventory.parquet \
--sql "SELECT product, price FROM table WHERE active = 1 ORDER BY price DESC"
```
### Large CSV/JSONL workloads
For larger local CSV and JSONL files, `query-forge` now avoids full-file buffering during load and batches inserts into SQLite, which reduces memory pressure and improves ingestion throughput.
```bash
qf query \
--input ./inventory.csv \
--input ./inventory.jsonl \
--sql "SELECT COUNT(*) AS total_rows FROM table UNION ALL SELECT COUNT(*) AS total_rows FROM table2" \
--meta
```
### JSON extraction modes
Control how JSON documents are mapped to rows with `--json-mode`.
**`array` (default)** — each element of a top-level JSON array becomes a row:
```bash
# [{"id":1,"name":"Alice"},{"id":2,"name":"Bob"}]
qf query \
--input ./users.json \
--sql "SELECT id, name FROM table"
```
**`object`** — each key-value pair of a JSON object becomes a row with `key` and `value` columns:
```bash
# {"revenue": 1200, "cost": 800, "profit": 400}
qf query \
--input ./summary.json \
--sql "SELECT key, value FROM table WHERE CAST(value AS REAL) > 500" \
--json-mode object
```
**`flatten`** — recursively flattens nested objects and arrays into a single row per document element, using dot-separated paths as column names:
```bash
# {"user":{"name":"Alice","address":{"city":"London"}},"tags":["a","b"]}
qf query \
--input ./record.json \
--sql "SELECT \"user.name\", \"user.address.city\", \"tags.0\" FROM table" \
--json-mode flatten
```
### XML extraction modes
Control how XML documents are mapped to rows with `--xml-mode`.
**`rows` (default)** — detects and extracts tabular rows from the XML structure (original behaviour):
```bash
qf query \
--input ./inventory.xml \
--sql "SELECT name, price FROM table WHERE active = 1"
```
**`descendants`** — collects every leaf text element (no child elements) as a row with `tag` and `value` columns, regardless of nesting depth:
```bash
# Useful for semi-structured XML where you want all text values
qf query \
--input ./config.xml \
--sql "SELECT tag, value FROM table WHERE tag = 'timeout'" \
--xml-mode descendants
```
**`attributes`** — collects all elements that have at least one attribute; each attribute name becomes a column, and non-empty text content is exposed as a `value` column:
```bash
# <items><item id="1" type="A">hello</item><item id="2" type="B">world</item></items>
qf query \
--input ./items.xml \
--sql "SELECT id, type, value FROM table ORDER BY id" \
--xml-mode attributes
```
### Header normalization
Use `--normalize-headers` to sanitize column names before they are registered as SQLite columns. Non-alphanumeric characters are replaced with underscores, leading/trailing underscores are trimmed, and consecutive underscores are collapsed. Combine with `--header-case` to choose a naming convention.
**`snake`** — lowercase with underscores (e.g. `Product ID` → `product_id`):
```bash
qf query \
--input ./inventory.csv \
--normalize-headers \
--header-case snake \
--sql "SELECT product_id, product_name FROM table"
```
**`camel`** — camelCase, no underscores (e.g. `Product ID` → `productId`):
```bash
qf query \
--input ./inventory.csv \
--normalize-headers \
--header-case camel \
--sql "SELECT productId, productName FROM table"
```
**`pascal`** — PascalCase, no underscores (e.g. `Product ID` → `ProductId`):
```bash
qf query \
--input ./inventory.csv \
--normalize-headers \
--header-case pascal \
--sql "SELECT ProductId, ProductName FROM table"
```
**`screaming-snake`** — UPPER_SNAKE_CASE (e.g. `Product ID` → `PRODUCT_ID`):
```bash
qf query \
--input ./inventory.csv \
--normalize-headers \
--header-case screaming-snake \
--sql "SELECT PRODUCT_ID, PRODUCT_NAME FROM table"
```
Add `--dedupe-headers` to automatically append numeric suffixes when two normalized names would collide.
### Export
CSV:
```bash
qf query \
--input ./input.xlsx:Sheet1 \
--sql "SELECT name, amount FROM table ORDER BY amount DESC" \
--output ./result.csv \
--format csv
```
JSONL:
```bash
qf query \
--input ./input.xlsx:Sheet1 \
--sql "SELECT name, amount FROM table ORDER BY amount DESC" \
--output ./result.jsonl \
--format jsonl
```
JSON:
```bash
qf query \
--input ./input.xlsx:Sheet1 \
--sql "SELECT name, amount FROM table ORDER BY amount DESC" \
--output ./result.json \
--format json
```
Markdown:
```bash
qf query \
--input ./input.xlsx:Sheet1 \
--sql "SELECT name, amount FROM table ORDER BY amount DESC" \
--output ./result.md \
--format markdown
```
HTML:
```bash
qf query \
--input ./input.xlsx:Sheet1 \
--sql "SELECT name, amount FROM table ORDER BY amount DESC" \
--output ./result.html \
--format html
```
XML:
```bash
qf query \
--input ./input.xlsx:Sheet1 \
--sql "SELECT name, amount FROM table ORDER BY amount DESC" \
--output ./result.xml \
--format xml
```
XLSX:
```bash
qf query \
--input ./input.xlsx:Sheet1 \
--sql "SELECT name, amount FROM table" \
--output ./result.xlsx
```
Parquet:
```bash
qf query \
--input ./input.xlsx:Sheet1 \
--sql "SELECT name, amount FROM table ORDER BY amount DESC" \
--output ./result.parquet \
--format parquet
```
### Dataset diff (`qf diff`)
Compare two snapshots by primary key:
```bash
qf diff \
--key id \
--ignore-columns updated_at \
./snapshot-before.csv \
./snapshot-after.csv
```
Side-by-side output with all classes:
```bash
qf diff \
--key id \
--ignore-columns updated_at \
--show all \
--side-by-side \
--format markdown \
--output ./diff-report.md \
./snapshot-before.csv \
./snapshot-after.csv
```
Schema-only comparison:
```bash
qf diff --schema-only ./v1.parquet ./v2.parquet
```
## Ready-to-use examples
The `examples/` folder contains:
- `inventory.xlsx`, `inventory.xml`, `inventory.csv`, `inventory.json`, `inventory.jsonl`, `inventory.md`, `inventory.html`, `snapshot-before.csv`, `snapshot-after.csv` as inputs.
- `active-products.txt`, `active-products.csv`, `active-products.jsonl`, `active-products.md`, `active-products.xml`, `active-products.html`, `active-products-from-xml.csv`, `active-products-from-csv.csv`, `active-products-from-json.csv`, `active-products-from-jsonl.csv`, `active-products-from-markdown.csv`, `active-products-from-html.csv`, `stock-by-category.xlsx`, `diff-products.txt`, `diff-products-side-by-side.md`, `diff-schema.json` as generated outputs.
- `commands.sh` with runnable commands from the repository root.
Regenerate the examples locally:
```bash
./scripts/regenerate_examples.sh
```
## Documentation
- Crate: <https://crates.io/crates/query-forge>
- API docs: <https://docs.rs/query-forge>
- Repository: <https://github.com/mad4j/query-forge>
- Roadmap: [ROADMAP.md](ROADMAP.md)
## Comparison
`query-forge` is designed as a focused CLI for running SQL queries across heterogeneous local files with a uniform `--input` model. Nearby tools exist, but they usually cover only part of the same problem.
| `query-forge` | Yes | Yes | Yes | Yes | Yes | Yes | Unified SQL CLI over heterogeneous document-style inputs |
| `duckdb` | Yes | Partial | Partial | Yes | Yes | No | General-purpose analytical SQL engine |
| `csvkit` | Partial | Via conversion | No | Yes | Limited | No | CSV tooling and CSV-oriented SQL workflows |
| `qsv` / `xsv` / `miller` | Limited / No | No | No | Yes | Limited | No | Fast tabular text processing |
| `jq` | No | No | No | No | Yes | No | JSON transformation and filtering |
| `xmlstarlet` | No | No | Yes | No | No | No | XML querying and transformation |
| `VisiData` | Partial / Interactive | Yes | Limited | Yes | Yes | Limited | Interactive data exploration |
Notes:
- `duckdb` is the closest alternative when the core requirement is "run SQL on local data files", especially for CSV and JSON, but it does not provide the same single-purpose workflow for XLSX/XML/Markdown inputs.
- `csvkit`, `qsv`, `xsv`, and `miller` are strong tabular-data tools, but they are centered on delimited text rather than a unified multi-format SQL interface.
- `jq` and `xmlstarlet` are powerful for JSON and XML respectively, but they use format-specific query languages instead of SQL.
- `VisiData` is excellent for interactive inspection, while `query-forge` is oriented to batch execution and scriptable SQL queries.
## Shell completions and man page
### Shell completions
Print a completion script for your shell and source it:
```bash
# Bash — write to ~/.bash_completion (overwrite to avoid duplicates)
qf completions bash > ~/.bash_completion
# Zsh — add to a directory on $fpath, e.g. ~/.zfunc/
qf completions zsh > ~/.zfunc/_qf
# Fish
qf completions fish > ~/.config/fish/completions/qf.fish
# Elvish
qf completions elvish > ~/.config/elvish/completions/qf.elv
# PowerShell
qf completions powershell > "$Env:UserProfile\qf_completions.ps1"
# Then add `. "$Env:UserProfile\qf_completions.ps1"` to your $PROFILE
```
Packagers can pipe the output directly to the appropriate system path during installation.
### Man page
Print the man page to stdout and install it:
```bash
# Preview
# Install system-wide
# Write to a file
qf man-page --output qf.1
```
## Development
### Running benchmarks
Reproducible benchmarks are provided for every supported input and output format (CSV, JSONL, JSON, Markdown, HTML, XML, XLSX, Parquet) using [Criterion](https://github.com/bheisler/criterion.rs).
```bash
cargo bench
```
Three benchmark groups are measured for 1,000-row datasets:
| `load/<format>` | File I/O + parsing → in-memory `SheetData` |
| `query/<format>` | Full pipeline: load + SQLite ingest + `GROUP BY` aggregation |
| `output/<format>` | Render a pre-built result to the target format |
HTML reports with latency distributions and regression detection are written to `target/criterion/` after each run.
## License
Released under the MIT license. See [LICENSE](LICENSE).