Blog  /  2026-10-19

How to build a text-to-SQL knowledge base from query history

Published 2026-10-19. Written for nlqueries-core 0.3.

knowledge-base query-history postgresql snowflake bigquery

If you want to improve text-to-SQL accuracy on your own database, a larger model is rarely the first thing to try. Better context is. A schema tells a model which tables and columns exist. It does not say which of three order tables the finance team trusts, how they join to customers, or which columns every report filters on. Much of that already exists in your database, in the queries people run every day. This post is the how-to: where the history comes from on each engine, and how nlqueries-core 0.3 turns it into part of the knowledge base.

If you have not read it, the text-to-SQL overview shows where the knowledge base sits in the pipeline.

What a schema cannot tell the model

A schema says which tables and columns exist, not which joins people use, which filters every report applies, or which of four near-duplicate tables is the canonical one. Query history records the shape of all of that, though not the values: every literal becomes a placeholder, so what "active" means is something to write into the knowledge base's glossary and business rules. The argument is made at length in your query history knows what your schema does not.

Where the history comes from

nlqueries process-history reads the history the database already keeps. No agent sits in the query path and nothing is logged by NLQueries itself. The source depends on the engine (see connectors):

MySQL (which 0.3 reaches through the generic SQLAlchemy connector), DuckDB, SQLite and any other SQLAlchemy URL have no history source in 0.3, so their knowledge base comes from the schema alone.

On PostgreSQL the extension has to be enabled first. Managed services usually preload the library and need only CREATE EXTENSION; a self-hosted server needs a change to shared_preload_libraries and a restart. Enabling query history capture has the steps. One PostgreSQL quirk: pg_stat_statements keeps no per-query timestamps, so --days has no effect there and you get everything since the last reset. Snowflake and BigQuery do honour --days, with one Snowflake limit: when the role cannot read ACCOUNT_USAGE, the connector falls back to INFORMATION_SCHEMA.QUERY_HISTORY, which keeps only the last 7 days, whatever --days asks for. The PostgreSQL, Snowflake and BigQuery pages cover the rest of each setup.

From raw history to query capsules

nlqueries process-history prod --days 30

The command runs a pipeline that turns thousands of raw statements into a short list of query capsules: annotated, parameterised templates. See process-history for every flag.

1. Filter. Queries run fewer than --min-executions times (3 by default) are dropped, and only SELECT statements are kept. Driver chatter (SET, SHOW and the like), queries against system catalogs such as information_schema and pg_catalog, and statements too short or too long to be useful go too. What remains is normalised with sqlglot so formatting differences disappear, duplicates are removed, and --max-queries (500 by default) caps how many are kept.
2. Cluster. Each query is fingerprinted with its literal values blanked out, so WHERE region = 'EU' and WHERE region = 'US' land in the same cluster. Clusters that read largely the same tables and differ only in the order of their SELECT columns are merged, and clusters are ranked by how often their queries ran.
3. Parameterise. Every literal in a cluster's representative query becomes a typed placeholder such as [region:VARCHAR] or [created_at:DATE]. The template keeps the joins and the filtered columns, but not the values: a customer's email in a WHERE clause, for instance, does not end up in the template.
4. Annotate (on by default). The LLM writes a one-line description of each template, phrased as the business question it answers. --no-annotate skips this, and nlqueries annotate fills it in later.
5. Embed (with --embed). Capsules are uploaded to Qdrant. In 0.3 the question path does not search that collection, so this step is optional.

The capsules are saved under ~/.nlqueries/capsules/.

Into the knowledge base

Capsules do nothing until they are exported:

nlqueries export-kb prod

export-kb combines the live schema with the saved capsules and writes the YAML knowledge base that query and ask read. Each capsule appears under query_capsules with its description, template and frequency. It has to be re-run after every process-history run. Table and column descriptions, glossary terms and rules you wrote by hand in the file survive it, but query_capsules is rebuilt from the saved capsules every time, so edits there do not. See export-kb.

At question time, the most frequent capsules in the knowledge base, up to five, are placed in the prompt as example queries. When a verified example promoted from thumbs-up feedback is similar enough to the question, it is shown instead. Either way the model sees the joins and filtered columns your team actually uses, not only a list of tables.

Getting useful capsules

• A quiet database produces nothing. A fresh or lightly used database often yields zero capsules. That is fine: the knowledge base still carries the full schema. On a dev database, run some representative queries first and try --min-executions 1.
• History reflects habits, good and bad. If the most frequent query on a table forgets to exclude test accounts, that template becomes an example. Read the capsules, and use the knowledge base's glossary and business rules to state the conventions explicitly. See glossary hierarchy.
• Reading history needs privilege. Reading STL_QUERY or sys.dm_exec_query_stats needs more privilege than answering questions does, and in 0.3 the same connector login does both, so decide deliberately whether the query login should hold it. The security post covers that login.
• Measure coverage. nlqueries kb-stats prod reports schema, description, capsule and intent coverage, and nlqueries eval prod re-asks the capsules that have a description (up to --max-cases, 50 by default) and checks that the generated SQL parses and references only known tables. See kb-stats and eval.
• Feed corrections back. nlqueries feedback with --thumbs-up or --corrected-sql records what was right, and promote-feedback (also run at the end of every export-kb) makes thumbs-up pairs available as verified examples. See feedback.

Where to go next

• Getting started, steps 4 and 5, for the first run
• process-history and export-kb for every flag
• Connectors for per-engine history caveats
• import-dbt if your models are documented in dbt

All posts · RSS feed · Documentation