query-forge 0.5.0

# query-forge


[![Crates.io](https://img.shields.io/crates/v/query-forge.svg)](https://crates.io/crates/query-forge)
[![Docs.rs](https://docs.rs/query-forge/badge.svg)](https://docs.rs/query-forge)
[![License: MIT](https://img.shields.io/badge/License-MIT-yellow.svg)](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`, ...).

## 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
WHERE "Elemento WBS" LIKE '%' || :wbs || '%'
  AND CAST("ore" AS REAL) > :min_ore;
```

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

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

## Ready-to-use examples


The `examples/` folder contains:

- `inventory.xlsx`, `inventory.xml`, `inventory.csv`, `inventory.json`, `inventory.jsonl`, `inventory.md`, `inventory.html` 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` 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.

| Tool | SQL queries | XLSX | XML | CSV | JSON/JSONL | Markdown tables | Primary focus |
| --- | --- | --- | --- | --- | --- | --- | --- |
| `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.

## License


Released under the MIT license. See [LICENSE](LICENSE).