localql 1.0.0

LocalQL packages csvql, a DuckDB-powered CLI for querying local CSV files like SQL tables.

LocalQL

LocalQL packages csvql, a lightweight DuckDB-powered CLI for querying local CSV files like SQL tables. It is built for local analytics work where a small project catalog, saved SQL, readable terminal output, and explicit exports are more useful than a notebook or database service.

csvql query \
  --table customers=examples/saas_revenue/data/customers.csv \
  --table subscriptions=examples/saas_revenue/data/subscriptions.csv \
  "SELECT
      c.segment,
      COUNT(*) AS active_subscriptions,
      SUM(s.current_mrr) AS current_mrr
   FROM customers c
   JOIN subscriptions s USING (customer_id)
   WHERE s.status = 'active'
   GROUP BY c.segment
   ORDER BY current_mrr DESC"

CSVQL does not implement a SQL engine. DuckDB executes SQL; CSVQL owns the local workflow around table aliases, readable output, validation, and project catalog configuration.

Quickstart

Install the distribution with the optional terminal workbench:

pip install "localql[tui]"

Then query one CSV with the installed csvql command:

csvql query examples/saas_revenue/data/revenue_movements.csv \
  "SELECT movement_type, SUM(mrr_delta) AS net_mrr_change
   FROM revenue_movements
   GROUP BY movement_type
   ORDER BY movement_type"

From a source checkout, use uv run instead:

uv sync --all-extras
uv run csvql --help

For the full copy/paste path, see Getting started.

Two command modes matter:

• Using LocalQL: install once, then every command is csvql ....
• Developing LocalQL: work from a source checkout with uv run ....

Status

This repository uses localql as the installable distribution name while keeping csvql as the CLI command, Python import package, and project config prefix. It has the core local workflow implemented for local CLI use: query, inspect/sample, project catalogs, saved SQL, export, profile, configured checks, doctor, benchmark and release-readiness proof scripts, JSON contract documentation, the failure gallery, the polished example project, and the small project-backed Python API. It also includes an optional Textual-powered terminal menu for local interactive work. The package version is 1.0.0.

This README describes the LocalQL 1.0.0 package surface. Release provenance, support boundaries, and the local verification workflow are documented in the changelog, v1 release notes, and release-readiness guide.

Implemented now:

• csvql query --table name=path "SELECT ..."
• .csvql.yml project catalog discovery
• csvql init
• csvql add
• csvql tables
• catalog-backed csvql query "SELECT ... FROM alias"
• csvql inspect data/orders.csv --output json
• csvql sample data/orders.csv --limit 10
• csvql run queries/file.sql
• csvql export queries/file.sql --format csv|json|markdown|text --out path
• catalog-backed csvql inspect alias
• catalog-backed csvql sample alias
• csvql profile data/orders.csv --output json
• catalog-backed csvql profile alias
• configured data-quality checks in .csvql.yml
• csvql check [table] --output json
• csvql doctor --output json
• sampled failure output with csvql check --show-failures
• repeated --table mappings for joins
• single-file shortcut mode
• optional csvql menu terminal workbench through the tui extra
• TUI startup from a project catalog, one CSV path, or repeated --table mappings
• TUI source actions for inspect, sample, profile, add, remove, and explicit project catalog save
• TUI query history for the current session only
• TUI explicit result export and explicit derived result sources under .csvql/results/{alias}.csv
• table and JSON stdout output
• DuckDB in-memory execution
• focused tests, Ruff, mypy, and GitHub Actions scaffolding

Repo-local hardening now:

• benchmark harness with JSON artifact and Markdown summary
• release-readiness verification for version consistency, build smoke, and installed-wheel smoke
• local output under output/

Current local proof evidence is generated under ignored output/ directories when the release-readiness and benchmark workflows are rerun.

Install From Source

uv sync --all-extras

Run the CLI from the repo:

uv run csvql --help

Use the installed CLI examples throughout the public docs as csvql .... When you are developing from a source checkout, prefix those same commands with uv run.

First 10 Minutes

Start with the CLI path. Query one CSV, then decide whether you want the optional terminal workbench.

csvql query examples/saas_revenue/data/revenue_movements.csv \
  "SELECT movement_type, SUM(mrr_delta) AS net_mrr_change
   FROM revenue_movements
   GROUP BY movement_type
   ORDER BY movement_type"

For repeatable work, initialize a project catalog, add sources once, and keep SQL in files:

cd examples/saas_revenue
csvql tables
csvql run queries/revenue_health.sql --output json

Use csvql menu only when an interactive terminal workbench helps. The CLI remains the complete core workflow.

Interactive Terminal Menu

CSVQL can also run an optional Textual-powered terminal menu. For installed usage, install the optional TUI dependency once:

pip install "localql[tui]"

Then launch the menu with csvql:

csvql menu
csvql menu /path/to/orders.csv
csvql menu --table customers=customers.csv --table orders=orders.csv

From a source checkout, use the repo-local environment:

uv sync --all-extras
uv run --all-extras csvql menu

The menu is session-backed by default. Sources added inside the TUI live only for the current session unless you explicitly save them to a .csvql.yml project catalog. Exports are written only when you choose the export action.

You can add sources after launch with F3. On macOS it opens a local CSV picker; elsewhere it opens the same path prompt as Add source. You can also paste .csv paths into the SQL editor. CSVQL turns pasted CSV paths into session sources immediately.

The SQL editor is focused when the menu opens. Type SQL directly, then press F4 or Ctrl+R to run selected SQL. If nothing is selected, CSVQL falls back to the current statement around the cursor. F12 or Ctrl+B runs the buffer of semicolon-delimited statements in the editor, records each statement in History, and shows the final tabular result. Move through History to recall each successful statement's result set. When Results is focused, [ and ] step through the buffer results. The History run column labels current- statement runs as current, buffer runs as buffer, and History reruns as rerun.

Use F2 or Ctrl+Down for the SQL editor, F3 or Ctrl+O to choose CSV file(s) or fall back to the path prompt, F5 for results, F6 or Ctrl+Up for sources, and F8 for history. Printable keys type into SQL while the editor is focused. Source actions use letters only when the source pane is focused: i inspect, s sample, p profile, a add, d remove after confirmation, and w save sources. In History, use Enter to reopen a query and r to rerun a query against the current session sources. Highlighting a successful History row restores that row's result in the Results pane. F1 opens help.

Press F7 to export the active tabular result. The output suffix chooses the format: .csv, .json, .md, .markdown, or .txt. If the path has no suffix, CSVQL writes .csv by default. Relative export paths are resolved from the directory where you launched csvql menu.

The Add source prompt accepts either name=path or pasted .csv path(s). Direct path paste derives aliases from file names; duplicate aliases receive numeric suffixes such as orders_2.

Saving sources to .csvql.yml may persist local filesystem paths. Project-relative paths are portable; external absolute paths and symlink-resolved paths outside the start directory are allowed for local workflows but can reveal machine-specific locations if you share the catalog.

When the source pane is focused, Source Intelligence actions use i to inspect the selected source and load columns, c to load/show columns directly, l to insert the selected source alias, and x to open deterministic starter SQL templates. Preview rows and row count are always available from x; column-aware templates appear after c or i loads metadata.

In the SQL editor, Tab opens explicit SQL completion when items are available; otherwise it inserts four spaces and keeps focus in the SQL editor. Ctrl+Space remains available where the terminal delivers it. Generated SQL is inserted into the editor and does not run until you run it. Pane focus stays on F2, F5, F6, and F8 plus the existing documented control-key paths. Column metadata is session-local and is not written to .csvql.yml.

Ctrl+N or F10 clears the editor for a new query while keeping history and the last result view visible. Query history is in-memory session state only: it is not written to disk, logged, or sent anywhere by CSVQL, and it clears when the TUI exits.

Ctrl+S saves the active tabular result as a derived source. Alt+S is also bound for terminals that emit Alt key events, and F11 is available where it is not intercepted by the OS. macOS may intercept F11 for Show Desktop. CSVQL prompts for an alias, writes .csvql/results/{alias}.csv, and adds that alias to the Sources pane with kind derived so it can be queried or joined later in the same TUI session. The CSV file remains on disk; the alias becomes durable across TUI sessions only if you explicitly save sources to .csvql.yml. Derived result sources are explicit CSV-backed artifacts, not hidden cache or automatic materialization. They use the same trusted local DuckDB SQL posture as other CSVQL sources.

The SQL editor uses the same trusted local DuckDB execution posture as the rest of CSVQL. Do not run untrusted SQL.

The full workbench needs at least 100 columns by 30 rows. A 120x36 terminal is recommended.

See Terminal menu guide for a focused walkthrough of the panes, keybindings, and derived result source workflow.

Python API Example

CSVQL also exposes a project-backed Python API:

from pathlib import Path

from csvql import CSVQLSession

session = CSVQLSession.from_config("examples/saas_revenue")

tables = session.tables()
sample = session.sample("revenue_movements", limit=5)
profile = session.profile("revenue_movements")
result = session.run_file("queries/revenue_health.sql")
Path("examples/saas_revenue/output").mkdir(parents=True, exist_ok=True)
output_path = session.export(
    "queries/revenue_health.sql",
    "output/revenue-health.json",
    format="json",
    force=True,
)

for row in result.as_records():
    print(row)

The Python API is intentionally project-backed: table listing, trusted SQL, saved SQL files, inspect, sample, profile, configured checks, and export. It does not provide direct-path sessions, ad hoc table mappings, config mutation, dataframe helpers, async execution, plugins, or a second execution engine.

Query Examples

Query one CSV with the single-file shortcut. The table name is derived from the file stem, so revenue_movements.csv becomes revenue_movements.

csvql query examples/saas_revenue/data/revenue_movements.csv \
  "SELECT movement_type, SUM(mrr_delta) AS net_mrr_change
   FROM revenue_movements
   GROUP BY movement_type
   ORDER BY movement_type"

Query multiple CSV files:

csvql query \
  --table customers=examples/saas_revenue/data/customers.csv \
  --table subscriptions=examples/saas_revenue/data/subscriptions.csv \
  "SELECT
      c.customer_id,
      c.company_name,
      s.plan_name,
      s.current_mrr
   FROM customers c
   JOIN subscriptions s USING (customer_id)
   WHERE s.status = 'active'
   ORDER BY s.current_mrr DESC"

Return JSON for automation:

csvql query \
  --table revenue_movements=examples/saas_revenue/data/revenue_movements.csv \
  --output json \
  "SELECT movement_month, SUM(mrr_delta) AS net_mrr_change
   FROM revenue_movements
   GROUP BY movement_month
   ORDER BY movement_month"

Project Catalog Examples

Initialize a local project catalog:

csvql init

Register a table once:

csvql add revenue_movements examples/saas_revenue/data/revenue_movements.csv

List registered tables as JSON:

csvql tables --output json

Query a registered table by alias:

csvql query "SELECT COUNT(*) AS movement_count FROM revenue_movements"

For one invocation, explicit --table mappings still work and override catalog aliases with the same name.

csvql query \
  --table revenue_movements=examples/saas_revenue/data/revenue_movements.csv \
  "SELECT COUNT(*) AS movement_count FROM revenue_movements"

Saved Workflow Examples

Run SQL from a file using catalog aliases:

cd examples/saas_revenue
csvql run queries/revenue_health.sql --output json

Inspect a registered catalog alias and profile it:

cd examples/saas_revenue
csvql inspect revenue_movements --output json
csvql profile revenue_movements --output json

Export the main analysis:

cd examples/saas_revenue
mkdir -p output
csvql export queries/revenue_health.sql \
  --format json \
  --out output/revenue-health.json \
  --force

csvql export queries/revenue_health.sql \
  --format markdown \
  --out output/revenue-health.md \
  --force

csvql export queries/revenue_health.sql \
  --format text \
  --out output/revenue-health.txt \
  --force

See examples/saas_revenue/README.md for the full copy/paste walkthrough.

Reusable Result Sources

You can turn a saved SQL result into a reusable CSV source without opening the TUI. This is normal CSV reuse: export a result, add the exported CSV as a table alias, then query it like any other CSVQL source.

cd examples/saas_revenue
mkdir -p .csvql/results
csvql export queries/revenue_health.sql \
  --format csv \
  --out .csvql/results/revenue_health.csv \
  --force

csvql add revenue_health_result .csvql/results/revenue_health.csv --replace
csvql query "SELECT COUNT(*) AS result_rows FROM revenue_health_result"

For one command without catalog persistence, pass the exported result with --table:

cd examples/saas_revenue
csvql query \
  --table revenue_health_result=.csvql/results/revenue_health.csv \
  "SELECT * FROM revenue_health_result"

This CLI path is practical parity with the TUI's Save Result As Source action, but it is not a typed derived-source catalog feature. The current project catalog stores table paths; it does not store source-kind metadata.

Inspect And Sample Examples

Inspect the core revenue-movement table:

csvql inspect examples/saas_revenue/data/revenue_movements.csv --output json

Calculate an exact row count when you explicitly want a full scan:

csvql inspect examples/saas_revenue/data/revenue_movements.csv --exact --output json

Sample rows from the same table:

csvql sample examples/saas_revenue/data/revenue_movements.csv --limit 5

Profile Examples

Profile the revenue-movement CSV with a full scan:

csvql profile examples/saas_revenue/data/revenue_movements.csv

Return JSON profile metrics:

csvql profile examples/saas_revenue/data/revenue_movements.csv --output json

Profile a registered catalog alias:

cd examples/saas_revenue
csvql profile revenue_movements --output json

csvql profile reports row and column counts, duplicate row count, per-column null counts and percentages, non-null counts, distinct counts excluding nulls, and DuckDB min/max values. String min and max use DuckDB lexicographic ordering.

Data Quality Check Examples

Configure checks in .csvql.yml:

version: 1
tables:
  customers:
    path: data/customers.csv
    checks:
      - name: customer_id_required
        type: not_null
        column: customer_id
      - name: customer_id_unique
        type: unique
        column: customer_id
  subscriptions:
    path: data/subscriptions.csv
    checks:
      - name: subscription_id_required
        type: not_null
        column: subscription_id
      - name: subscription_id_unique
        type: unique
        column: subscription_id
      - name: subscription_customer_exists
        type: foreign_key
        column: customer_id
        references:
          table: customers
          column: customer_id
  revenue_movements:
    path: data/revenue_movements.csv
    checks:
      - name: movement_id_required
        type: not_null
        column: movement_id
      - name: movement_id_unique
        type: unique
        column: movement_id
      - name: movement_type_known
        type: accepted_values
        column: movement_type
        values: [new, expansion, contraction, churn, reactivation]

Run all configured checks:

csvql check

Run checks for one registered table and return JSON:

csvql check revenue_movements --output json

Include capped failure samples:

csvql check revenue_movements --output json --show-failures --failure-limit 5

csvql check exits 0 when checks pass or no checks are configured. It exits 11 when configured checks run successfully and find data-quality failures. Missing catalogs, missing files, invalid config, and DuckDB execution errors use the existing CLI error path.

Project Health Examples

Run project doctor from a directory with a .csvql.yml project catalog:

csvql doctor

Return doctor results as JSON for automation:

csvql doctor --output json

csvql doctor exits 0 for passed and warning results. It exits 12 when the project catalog exists but CSVQL finds concrete project-health failures such as invalid config, missing configured CSV files, unreadable CSV inputs, or configured checks that reference missing columns.

Benchmark And Release Hardening

Generate local benchmark evidence:

• uv run python scripts/benchmark_csvql.py --output-root output/benchmarks

Verify build and install proof:

• uv run python scripts/verify_release_readiness.py --work-dir output/release-readiness

Release workflow and notes:

• Changelog
• v1 release notes
• Release readiness

Claims boundary:

• Local benchmark evidence only
• No large-file proof beyond the recorded datasets
• No production-readiness claim
• No sandbox-safety claim
• Release provenance is recorded in the changelog, release notes, and release readiness workflow

Development Checks

uv run ruff format --check .
uv run ruff check .
uv run --all-extras mypy src
uv run --all-extras pytest

Or run the combined local gate:

make ci

Security Model

CSVQL is currently a local developer tool for trusted SQL. DuckDB executes the SQL, and CSVQL does not sandbox DuckDB, restrict DuckDB capabilities, or restrict filesystem access. Do not run untrusted SQL files or input.

Documentation

• Getting started
• FAQ
• Troubleshooting
• Terminal menu guide
• SaaS revenue example
• Architecture
• Benchmarking
• Changelog
• JSON contracts
• Failure gallery
• Release readiness
• v1 release notes
• Roadmap
• Contributing
• Security
• Support
• Development

Maintainer-facing docs:

• Manual v1 QA matrix
• Product direction