NLQueries GitHub

Blog  / 

Plain-English questions on a DuckDB database

Published . Written for nlqueries-core 0.4.

duckdb quick-start analytics

DuckDB natural language querying is the shortest way I know to try text-to-SQL on real data. There is no server to stand up, no role to create and no network to open: a .duckdb file on your laptop, a model key and five commands. This post is the quick start for analysts with nlqueries-core 0.4. It covers getting your files into a shape the connector can read, what the knowledge base learns when there is no query history, and what the connector refuses to do on your machine. The DuckDB page has the short version.

What you need

  • Python 3.11 to 3.14.
  • An API key for an LLM provider, Anthropic or OpenAI, or any model LiteLLM supports. See configuration.
  • A DuckDB database file with your data in tables. More on that below.

You do not need Qdrant for this. It powers the semantic cache and verified examples, and a question is answered without them.

Five commands

pip install "nlqueries-core[duckdb]"
export ANTHROPIC_API_KEY=sk-ant-...

nlqueries connect duckdb --database ~/data/shop.duckdb --alias shop
nlqueries export-kb shop
nlqueries query shop "Which five products had the most returns last quarter?"

connect checks the file and registers it under the alias. There is no host, port or password. A path that does not exist is refused rather than created: an empty database where you expected your data would look like a database with no tables, which is a worse error to get. Leave out --database and you get a transient in-memory database, which starts empty and is only useful for tests.

export-kb reads the schema and writes the knowledge base the model is given: tables, columns, types, primary keys, and a few sample values per column so the model sees how values are spelled. Run nlqueries extract-schema shop first if you want to see the table list before anything is written.

query generates the SQL in DuckDB's dialect, checks it, runs it and prints the rows. To see the SQL without running it, use ask:

nlqueries ask shop "Average basket size by month this year"

ask never runs the generated statement. With literal grounding on (the default), it may still open the file read-only to check the statement's string literals against stored values, which is how 'shipped' becomes 'Shipped' when that is how it is stored. See ask.

Get your files into tables first

This is the step people miss. Analysts often keep DuckDB as a thin layer over Parquet or CSV files: a view that calls read_parquet('orders/*.parquet'), or a table function in the FROM clause. The connector does not allow either.

DuckDB reads files through table functions, so reading a file is syntactically an ordinary SELECT. Opening the database read-only does not stop it. A generated query could therefore read any file your account can, which is more than you meant to hand a model. So every connection the connector opens has external access switched off and its configuration locked before anything else can use it. With those settings, read_csv_auto, read_text, read_blob, glob, ATTACH, COPY ... TO and INSTALL are all refused, and queries over the database's own tables are unaffected.

The cost is that a view over external files stops working, because reading those files is exactly what is refused. And only tables are read into the knowledge base, not views, so the model would not be told about one anyway. Materialise the data into the file in your own DuckDB session, outside NLQueries:

CREATE TABLE orders AS SELECT * FROM read_parquet('orders/*.parquet');
CREATE TABLE returns AS SELECT * FROM read_csv_auto('returns.csv');

Then run export-kb again so the knowledge base has the new tables.

One more DuckDB rule applies: a file open for writing in one process cannot be opened by another. If your notebook holds a read-write connection to the file, close it, or point NLQueries at a copy.

No query history, so describe your data

On PostgreSQL, Snowflake or BigQuery, the knowledge base learns from your team's real queries: which tables they join, on which keys, with which filters. How to build a text-to-SQL knowledge base from query history explains how. DuckDB keeps no query history between connections, so there is nothing to learn from. process-history returns nothing on DuckDB, and you can skip it. The knowledge base is built from the schema alone.

Two things are missing from that schema. Foreign keys are not read, since analytics files rarely declare them, so the model infers joins from column names. And DuckDB tables seldom carry descriptions. Both improve with context:

  • nlqueries export-kb shop --describe-columns asks the LLM to write column descriptions from the sample values. It skips surrogate keys.
  • If the data is modelled in dbt, which is common with dbt-duckdb, merge the project's model and column docs into the knowledge base with import-dbt.
  • nlqueries kb-stats shop reports how much of the schema has descriptions.

Name your join columns consistently (customer_id on both sides, not cust and id). The model has nothing better to go on.

What it does on your machine

  • The file is opened read-only. An in-memory database cannot be, and it is private to the process and gone when it exits. The external-access lock applies to both.
  • A runaway query is interrupted. DuckDB has no server-side statement timeout, so a watchdog interrupts the query after CONNECTOR_STATEMENT_TIMEOUT_SECONDS (120 by default; 0 turns it off) and the interruption is reported as an error rather than a hang.
  • Large sorts and joins can still finish. A query too big for memory spills to a temporary directory that the connector creates for the process, readable only by your account and removed when the process exits. DuckDB's own default would put it beside the database file.
  • The SQL is checked before it runs. Every statement is parsed and refused unless it is a single read-only query. Why we validate SQL before it runs has the reasoning, and what each connector enforces has the table for every engine.

What leaves your laptop

The database stays where it is, but the LLM call does not. Each question sends the provider the question, the schema from the knowledge base, and up to five sample values per column. Columns whose names mark them as personal data, such as email or phone, never get sample values. The rows query prints are not sent: a SQL answer sends no rows to the model. If a statement fails, up to 1,000 characters of DuckDB's error go back to the model so it can correct it once.

To keep sample values out of the prompt altogether, export with --no-include-samples. The model then has to guess how values are spelled. What leaves this machine has the full list.

A checklist

  1. Put the data in tables in the .duckdb file. Views over files are refused.
  2. Close any read-write connection to the file.
  3. connect, then export-kb, with --describe-columns or import-dbt for descriptions.
  4. ask while you check the SQL, query once you trust it.

To try it, pip install "nlqueries-core[duckdb]" and follow getting started, or read the connector on GitHub.

All posts · RSS feed · Documentation