query-forge

Rust CLI to run SQL queries on one or more XLSX/XML/CSV/JSON/JSONL/Markdown/HTML/Feather/Parquet inputs and produce output in table (default), CSV, JSONL, Markdown, HTML, XML, Feather, Parquet, a new XLSX file, or the system clipboard for text-based formats.

query-forge is built for one core job: run SQL on messy local files, fast, with zero database setup.

Installation

From crates.io:

cargo install query-forge

From source (local development):

cargo install --path .

Quick start

Run an inline query:

qf query \

  --input ./input.xlsx:Sheet1 \

  --sql "SELECT * FROM table WHERE amount > 10"

Or load the query from an SQL file:

qf query \

  --input ./input.xlsx \

  --sql-file ./query.sql

Who it is for

Analysts and Excel power users who need SQL without moving data to a DB first.
Data engineers and consultants who reconcile heterogeneous local exports quickly.
QA, auditors, and operations teams who compare snapshots and validate changes.

Start in 60 seconds (high-value workflows)

Query a local file:

qf query --input ./inventory.csv --sql "SELECT product, price FROM table WHERE active = 1"

Compare two snapshots:

qf diff --key id --ignore-columns updated_at ./snapshot-before.csv ./snapshot-after.csv

Create a pivot table:

qf pivot --input ./inventory.csv --rows category --cols active --values stock --agg sum

Inspect schema and data quality quickly:

qf inspect --input ./inventory.csv --stats --sample 5

Use qf tables and qf schema before writing complex joins to confirm table names and inferred types.

Key features

SQL queries on XLSX/XML/CSV/JSON/JSONL/Markdown/HTML/Feather/Parquet data using in-memory SQLite.
Multi-file support with automatic table mapping: table, 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, Feather and Parquet inputs are supported directly (.csv, .json, .jsonl, .md, .markdown, .html, .htm, .feather, .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/Feather/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 table (default), csv, json, jsonl, markdown (alias md), html, xml, xlsx, feather, parquet.
Explicit input/output format overrides with |type, including clipboard pseudo-paths such as @clipboard|csv and @clipboard|json.
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.
CTE chaining via --with 'name AS (...)' to prepend named subqueries before the main --sql or --sql-file query; repeatable for multiple CTEs.
Multi-statement SQL files — --sql-file files may contain several ;-separated statements (DDL, DML, or SELECT); all statements except the last are executed in order and the last SELECT is returned.
AI-friendly envelope output via global --ai-mode for query, tables, schema, inspect, diff, pivot, and conv, with structured JSON data, metadata, warnings, and errors.
Capabilities discovery via qf capabilities (JSON contract for commands, formats, AI-mode support, and error-code families).
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.
Pivot tables via qf pivot for cross-tabulation and aggregation; always computes as static data via SQL CASE WHEN crosstab, compatible with all output formats.

Examples

Multiple inputs

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:

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.

Explicit format overrides

Use |type to force the input or output format when the extension is missing, ambiguous, or intentionally different from the real data:

qf query \

  --input ./data.txt|csv \

  --sql "SELECT * FROM table"

The same syntax works with selectors:

qf query \

  --input ./inventory.xlsx#Main|xlsx \

  --sql "SELECT * FROM table"

You can also use |type on outputs, and clipboard paths must use this syntax (for example @clipboard|csv and @clipboard|json).

Parameterized query

SELECT *
FROM table
WHERE "Elemento WBS" LIKE '%' || :wbs || '%'
  AND CAST("ore" AS REAL) > :min_ore;

Parameters:

--param wbs=TEST_VAL --param min_ore=8

Multiple values for the same parameter (separated by , or ;):

--param "wbs=TEST_VAL,W092500011.2.3"

XML input

Use the whole XML file:

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:

qf query \

  --input ./inventory.xml:Inventory \

  --sql "SELECT name, price FROM table ORDER BY price DESC"

CSV/JSON/JSONL/Markdown/HTML/Feather/Parquet input

CSV:

qf query \

  --input ./inventory.csv \

  --sql "SELECT product, price FROM table WHERE active = 1 ORDER BY price DESC"

JSONL:

qf query \

  --input ./inventory.jsonl \

  --sql "SELECT product, price FROM table WHERE active = 1 ORDER BY price DESC"

JSON (array at root):

qf query \

  --input ./inventory.json \

  --sql "SELECT product, price FROM table WHERE active = 1 ORDER BY price DESC"

JSON with key selection as sheet:

qf query \

  --input ./inventory.json:Inventory \

  --sql "SELECT product, price FROM table ORDER BY price DESC"

Markdown (first table by default):

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

qf query \

  --input ./inventory.md:2 \

  --sql "SELECT product, price FROM table"

HTML (first <table> by default):

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

qf query \

  --input ./inventory.html:2 \

  --sql "SELECT product, price FROM table"

Feather:

qf query \

  --input ./inventory.feather \

  --sql "SELECT product, price FROM table WHERE active = 1 ORDER BY price DESC"

Parquet:

qf query \

  --input ./inventory.parquet \

  --sql "SELECT product, price FROM table WHERE active = 1 ORDER BY price DESC"

Clipboard input/output

Use @clipboard|type as a pseudo-path to read text-based datasets from the system clipboard or copy text-based results back to it. In PowerShell, quote these values so @ is not parsed as splatting, for example --input '@clipboard|csv' and --output '@clipboard|json'.

qf query \

  --input '@clipboard|csv' \

  --sql "SELECT product, price FROM table WHERE active = 1 ORDER BY price DESC" \

  --output '@clipboard|json' \

  --format json

Supported clipboard input types are csv, json, jsonl, md/markdown, html/htm, and xml. Clipboard output supports text, csv, json, jsonl, markdown/md, html, and xml. Extension syntax such as @clipboard.csv is not supported.

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.

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

AI mode (`--ai-mode`)

Use global --ai-mode to wrap output into a stable JSON envelope.

Supported commands in AI mode: query, tables, schema, inspect, diff, pivot, conv.

qf --ai-mode query \

  --input ./inventory.csv \

  --sql "SELECT product, price FROM table WHERE active = 1"

The envelope contains:

ok success flag
schema_version
command
data (rows/columns)
metadata (timings, inputs, optional output target)
warnings
error (null on success, object on failure)

When a command fails in --ai-mode, query-forge emits a JSON error envelope with a stable error.code (for example QF_INVALID_ARGUMENT, QF_INPUT_NOT_FOUND, QF_SQL_ERROR).

Capabilities discovery (`qf capabilities`)

Use qf capabilities as first call from AI tools to discover supported commands and formats.

qf capabilities

Output is JSON and includes:

command inventory
per-command supports_ai_mode
output-format lists
known error-code families

Type inference controls

By default, query-forge tries to infer SQLite-compatible types from incoming values. Use these flags to make the behavior explicit or tune it for locale-specific data:

qf query \

  --input ./orders.csv \

  --sql "SELECT order_date, amount, paid FROM table WHERE amount > 10" \

  --date-format %d/%m/%Y \

  --decimal-comma \

  --null-values NULL,N/A \

  --true-values YES,Y \

  --false-values NO,N

--infer-types explicitly enables typed ingestion.
--all-text disables inference and preserves all values as text.
--decimal-comma interprets values like 12,50 as decimals.
--date-format parses matching date strings during inference.
--null-values, --true-values, and --false-values add custom tokens for conversion.

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:

# [{"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:

# {"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:

# {"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):

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:

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

# <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):

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

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

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

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

Table (default, console-friendly output):

qf query \

  --input ./input.xlsx:Sheet1 \

  --sql "SELECT name, amount FROM table ORDER BY amount DESC"

Or explicitly with --format table:

qf query \

  --input ./input.xlsx:Sheet1 \

  --sql "SELECT name, amount FROM table ORDER BY amount DESC" \

  --format table

CSV:

qf query \

  --input ./input.xlsx:Sheet1 \

  --sql "SELECT name, amount FROM table ORDER BY amount DESC" \

  --output ./result.csv \

  --format csv

JSONL:

qf query \

  --input ./input.xlsx:Sheet1 \

  --sql "SELECT name, amount FROM table ORDER BY amount DESC" \

  --output ./result.jsonl \

  --format jsonl

JSON:

qf query \

  --input ./input.xlsx:Sheet1 \

  --sql "SELECT name, amount FROM table ORDER BY amount DESC" \

  --output ./result.json \

  --format json

Markdown:

qf query \

  --input ./input.xlsx:Sheet1 \

  --sql "SELECT name, amount FROM table ORDER BY amount DESC" \

  --output ./result.md \

  --format md

HTML:

qf query \

  --input ./input.xlsx:Sheet1 \

  --sql "SELECT name, amount FROM table ORDER BY amount DESC" \

  --output ./result.html \

  --format html

XML:

qf query \

  --input ./input.xlsx:Sheet1 \

  --sql "SELECT name, amount FROM table ORDER BY amount DESC" \

  --output ./result.xml \

  --format xml

XLSX:

qf query \

  --input ./input.xlsx:Sheet1 \

  --sql "SELECT name, amount FROM table" \

  --output ./result.xlsx

Feather:

qf query \

  --input ./input.xlsx:Sheet1 \

  --sql "SELECT name, amount FROM table ORDER BY amount DESC" \

  --output ./result.feather \

  --format feather

Parquet:

qf query \

  --input ./input.xlsx:Sheet1 \

  --sql "SELECT name, amount FROM table ORDER BY amount DESC" \

  --output ./result.parquet \

  --format parquet

CTE chaining (`--with`)

Prepend named CTE definitions before the main query with --with. Each value is a name AS (...) expression; the WITH keyword is added automatically. Repeat the flag for multiple CTEs.

qf query \

  --input ./inventory.csv \

  --with 'active AS (SELECT * FROM table WHERE active = 1)' \

  --with 'pricey AS (SELECT * FROM active WHERE price > 50)' \

  --sql 'SELECT category, COUNT(*) AS cnt FROM pricey GROUP BY category ORDER BY cnt DESC'

Works with both --sql and --sql-file.

Multi-statement SQL files

A .sql file passed to --sql-file may contain multiple ;-separated statements. All statements except the last are executed as DDL or DML (e.g. CREATE TEMP TABLE, INSERT). The last statement must be a SELECT and its result is the command output.

-- analysis.sql
CREATE TEMP TABLE recent AS
  SELECT * FROM table WHERE year >= 2024;

CREATE TEMP TABLE summary AS
  SELECT category, SUM(revenue) AS total
  FROM recent
  GROUP BY category;

SELECT category, total
FROM summary
ORDER BY total DESC;

qf query \

  --input ./sales.csv \

  --sql-file ./analysis.sql

Semicolon splitting respects single-quoted strings, '' escape sequences, -- line comments, and /* */ block comments.

Dataset diff (`qf diff`)

Compare two snapshots by primary key:

qf diff \

  --key id \

  --ignore-columns updated_at \

  ./snapshot-before.csv \

  ./snapshot-after.csv

Side-by-side output with all classes:

qf diff \

  --key id \

  --ignore-columns updated_at \

  --show all \

  --side-by-side \

  --format md \

  --output ./diff-report.md \

  ./snapshot-before.csv \

  ./snapshot-after.csv

Schema-only comparison:

qf diff --schema-only ./v1.parquet ./v2.parquet

Pivot tables (`qf pivot`)

Compute a cross-tabulation (pivot table) from any supported input.

Frequency table — count rows by a single dimension:

qf pivot \

  --input ./inventory.csv \

  --rows category

Output:

category     | count
electronics  | 2
office       | 2

Crosstab — sum a value column broken down by two dimensions (--rows × --cols):

qf pivot \

  --input ./inventory.csv \

  --rows category \

  --cols active \

  --values stock \

  --agg sum

Output:

category     | false | true
electronics  |     2 |   32
office       |    50 |   80

Export to any format — the result is static data, compatible with all output formats including xlsx:

# Export pivot as CSV

qf pivot \

  --input ./inventory.csv \

  --rows category --cols active --values stock --agg sum \

  --output ./pivot.csv --format csv


# Export pivot as XLSX (static cells, not a native Excel PivotTable)

qf pivot \

  --input ./inventory.csv \

  --rows category --cols active --values stock --agg sum \

  --output ./pivot.xlsx

Inspect the generated SQL with --show-sql:

qf pivot \

  --input ./inventory.csv \

  --rows category --cols active --values stock --agg sum \

  --show-sql

Prints the CASE WHEN crosstab query to stderr before executing it.

Supported aggregations: count (default), sum, avg, min, max. --values is required for sum, avg, min, max; optional for count.

Note on static output: qf pivot always computes the pivot table via a SQL CASE WHEN crosstab query and writes the result as static data, regardless of the chosen output format. For xlsx output this means pre-computed cells rather than a native Excel PivotTable (which rust_xlsxwriter does not currently support). Use --show-sql to retrieve the generated SQL for use in other tools that support native pivot tables.

Ready-to-use examples

The examples/ folder contains:

inventory.xlsx, inventory.xml, inventory.csv, inventory.json, inventory.jsonl, inventory.html, inventory.md, inventory.feather, 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.feather, active-products-from-xml.csv, active-products-from-csv.csv, active-products-from-json.csv, active-products-from-jsonl.csv, active-products-from-html.csv, active-products-from-markdown.csv, active-products-from-feather.csv, stock-by-category.xlsx, diff-products.txt, diff-products-side-by-side.md, diff-schema.json, pivot-count-by-category.txt, pivot-stock-by-category.csv, pivot-stock-by-category.xlsx as generated outputs.
commands.sh with runnable commands from the repository root.

Regenerate the examples locally:

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

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.

Shell completions and man page

Shell completions

Print a completion script for your shell and source it:

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

# Preview

qf man-page | man -l -


# Install system-wide

qf man-page | gzip -c > /usr/share/man/man1/qf.1.gz


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

cargo bench

Three benchmark groups are measured for 1,000-row datasets:

Group	What is timed
`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.

query-forge 0.9.0