Blog /
Embedding the NLQueries Python Library in an Internal Backend: An Integration Prototype
Published . Written for nlqueries-core 0.4.
python integration text-to-sql
Say your revenue operations team has an internal admin tool, and someone asks for a box where they can type "what was paid revenue for tenant A in October?" instead of filing a ticket. The backend is Python: a nightly job assembles a report for analysts, and an async service sits behind the admin UI. Both could use natural-language-to-SQL (NL2SQL): the job to propose SQL that an analyst reviews, and the service to return a read-only answer.
The first version usually takes an afternoon and goes wrong quietly. Someone copies a snippet with a guessed client class, the answer comes back as a string, and an empty result looks the same whether the query found no orders, failed validation, or never ran. Embed a library and its configuration, credentials, event-loop behaviour, connections and failure modes become yours.
This post walks through how NLQueries Core 0.4.1 fits into that backend: the real entry points, an explicit execution policy on every call, a response shape that says what happened, and the controls the library leaves to you. Treat it as an integration prototype. Pieces have been exercised with a scripted model in place of a real provider, but the complete setup and the end-to-end acceptance tests haven't been demonstrated, and those component checks don't establish a working deployment or any measured advantage.
Start by deciding what the backend is allowed to ask for
Settle the trust boundary first. The caller supplies one thing, the question. Everything else comes from trusted server-side configuration: which NLQueries agent to use, its SQL dialect, whether the request may execute SQL, and who owns any conversation session.
The call signature shows why. run_query takes an agent_id, a dialect, an execution policy, a session_id and a history (sync_runner.py). If those flow straight from a request body, a user can point the call at a connector they shouldn't reach, or flip a preview into an execution. The library can't know which users may see which database; per-user authorization is your application's job, and NLQueries' SQL policy is a separate safeguard, not a replacement (database hardening).
The running example has one agent, sales, mapped in configuration to an allowed set of internal users. The nightly job only previews SQL; the admin service may execute it read-only. Neither accepts SQL, connector IDs or policies from the client.
What turns that mapping into a control is an identity adapter, and this prototype doesn't include one. It takes the principal your existing authentication already verified, looks it up in the server-side mapping, and refuses the request before any NLQueries call. It has to fail closed: an unknown user, a missing mapping or a lookup error is a denial, not a fallback to a default agent. And it has to run before the pipeline, because once run_query starts, the question is on its way to the model. That proof comes from your code, not the library's parameters, and it needs its own test.
Seed a fixture you can check, then register it
NLQueries isn't a stateless function. A library call depends on a registered connection, an exported knowledge base (KB) describing the schema, a configured model provider and the right database driver (getting started). The KB is a YAML file built from the live schema and, optionally, sample values; query and ask won't work without it, and it must be re-exported when the schema or query history changes (CLI reference).
The data comes first. The SQLite connector reads an existing database file; it doesn't create one, and it raises an error when the path doesn't exist (sqlite.py). The fixture is a small, synthetic database with a known answer, so you can tell a correct response from a plausible one. Save it as fixture.sql in a fresh, disposable directory:
-- fixture.sql (synthetic sample data)
CREATE TABLE customers(id INTEGER PRIMARY KEY, tenant_id TEXT NOT NULL, region TEXT);
CREATE TABLE orders(id INTEGER PRIMARY KEY, customer_id INTEGER REFERENCES customers(id),
order_date TEXT, amount_cents INTEGER, status TEXT);
CREATE TABLE order_items(id INTEGER PRIMARY KEY, order_id INTEGER REFERENCES orders(id), sku TEXT);
INSERT INTO customers VALUES (1,'A','East'),(2,'A','West'),(3,'B','North');
INSERT INTO orders VALUES
(101,1,'2026-10-01',10000,'PAID'), (102,1,'2026-10-02',5000,'CANCELLED'),
(103,2,'2026-10-31',20000,'PAID'), (104,3,'2026-10-02',70000,'PAID'),
(105,1,'2026-11-01',40000,'PAID'), (106,2,'2026-10-15',NULL,'PAID');
INSERT INTO order_items VALUES (1,101,'a'),(2,101,'b'),(3,103,'c');
-- Reference answer: tenant A, paid orders, October 2026
SELECT SUM(o.amount_cents) AS revenue_cents, COUNT(*) AS paid_order_count
FROM orders AS o JOIN customers AS c ON c.id = o.customer_id
WHERE c.tenant_id = 'A' AND o.status = 'PAID'
AND o.order_date >= '2026-10-01' AND o.order_date < '2026-11-01';
Run against SQLite 3.45.3, the reference query returns (30000, 3). Order 106 counts as paid but adds nothing to revenue because its amount is NULL, and order 101 has two line items, which matters later. The tenant_id = 'A' predicate is arithmetic for the expected answer, not an authorization boundary; a real permission test needs access control the database enforces.
The package declares Python >=3.11,<3.15 for this release (pyproject.toml); pin the exact package version too. Set state paths and provider settings before anything imports NLQueries: NLQ_STATE_DIR is the root for everything it keeps between runs, and KB_PATH overrides where knowledge bases live (configuration). The seed step needs the sqlite3 command-line tool.
# Untested setup sketch. Requires Python >=3.11,<3.15 (Core 0.4.1) and the sqlite3 CLI.
# Run in a fresh, disposable directory that contains fixture.sql (above).
python -m venv .venv && source .venv/bin/activate
pip install "nlqueries-core==0.4.1"
# Keep secrets in your secret store or a .env that never reaches source control.
export ANTHROPIC_API_KEY="<from-your-secret-store>"
export NLQ_STATE_DIR="$PWD/.nlq-state" # project-local state
export KB_PATH="$NLQ_STATE_DIR/knowledge_base"
export NLQ_LITERAL_GROUNDING=false # strict preview: see below
# The SQLite connector reads an existing file and does not create one: seed it first.
sqlite3 "$PWD/fixture.db" < fixture.sql
# Register the fixture database under a fixed connector ID, then export the KB
# without sampling column values.
nlqueries connect sqlite --database "$PWD/fixture.db" --connector-id sales
nlqueries export-kb sales --no-include-samples
nlqueries health
Two details matter more than they look. The library's agent_id is the full connector ID, not an alias: the CLI resolves aliases, the library doesn't (getting started). Registering with --connector-id sales gives you a stable ID to hard-code. And --no-include-samples stops export-kb from storing column values in the KB, which also means it doesn't run the sampling queries at all (CLI reference). That's a sensible default until you've decided which values belong in a prompt.
On the seeded fixture, export-kb warns that no query capsules were found and suggests process-history, then still writes the KB with zero capsules. health also probes the Qdrant vector store and the model provider, so offline it reports those as unhealthy and exits non-zero.
Two entry points, and the event loop picks between them
The public Python API in nlqueries.orchestrator.sync_runner has two functions: run_query, which is async, and run_query_sync, a wrapper that calls asyncio.run() around it (sync_runner.py). The source says the sync wrapper can't be called from inside a running event loop, so the nightly job, a plain script, uses run_query_sync, and the async admin service awaits run_query directly.
What a request may do is set by an ExecutionPolicy from nlqueries.execution: generate_only() produces SQL and never executes the generated statement, and execute_read_only() runs it as a read. The default is generate-only. The policy is a frozen dataclass with no escalation method, so downstream code can't widen it in place (execution.py). Read-only still isn't a safety claim: the source says it permits execution without asserting the statement is safe, and the database's own grants stay in force.
Here's the module both callers share. It's custom application code, not an NLQueries SDK, using the signatures in the pinned source:
# nlq_backend.py: untested sketch against NLQueries Core 0.4.1.
# Assumes the 'sales' connector is registered and its KB exported (see setup).
from nlqueries.execution import ExecutionPolicy
from nlqueries.orchestrator.sync_runner import run_query, run_query_sync
# Trusted server-side configuration. Never taken from the request.
AGENT_ID = "sales" # full connector ID; the library does not resolve aliases
DIALECT = "sqlite"
STATEMENT_TIMEOUT_S = 20.0 # bounds one statement, not the model call
def preview_sql(question: str):
"""Nightly job: draft SQL for analyst review. No generated statement runs."""
return run_query_sync(
question=question,
agent_id=AGENT_ID,
dialect=DIALECT,
available_types=("sql",),
execution=ExecutionPolicy.generate_only(),
use_cache=False,
explain=True,
)
async def answer_read_only(question: str):
"""Admin service: generate and execute as a read, in one request."""
return await run_query(
question=question,
agent_id=AGENT_ID,
dialect=DIALECT,
available_types=("sql",),
execution=ExecutionPolicy.execute_read_only(),
timeout_seconds=STATEMENT_TIMEOUT_S,
use_cache=False,
explain=True,
)
This module has run in one narrow setting: Core 0.4.1's real pipeline and SQLite connector on the seeded fixture, with a scripted model and stand-ins for search and caching. There the preview returns SQL with no sql_result, and the read-only call returns [[30000, 3]]. That confirms the interface and the arithmetic, not how a real model handles the question, so the "untested sketch" label stays until it runs against a configured provider behind an identity layer.
The two functions are independent request modes, not a preview-then-approve workflow. Calling answer_read_only after an analyst approves a preview generates SQL again; it doesn't execute the statement the analyst saw. "Execute exactly what was approved" is a separate design to build and secure yourself.
Preview mode has one more subtlety: generate-only doesn't mean zero database reads. Literal grounding, which checks string literals in generated SQL against stored column values, reads through the agent's connector under a read-only policy of its own, whatever the request's policy says. The source says to set NLQ_LITERAL_GROUNDING=false for a mode that opens no connector at all (execution.py). That's why the setup block turns it off: if analysts preview against a production replica and someone asks "did the preview touch the database?", you want a truthful answer.
Turn the result into a response your callers can trust
run_query returns an AgentQueryResult. sql holds the generated statement. sql_is_valid says whether it survived validation. execution_mode records what the request was permitted to do. sql_result holds rows only if something executed, and from_cache and latency_ms describe how the answer was produced (sync_runner.py). A source comment explains why sql_is_valid exists: without it, a caller could only infer validity from sql_result is None, which is also what a generation-only run and a rejected statement look like, and the CLI once ran SQL the validator had refused because of that inference.
So don't infer success from the answer text or an empty row list. Map the result into a small, allowlisted response with an explicit status, and watch for one trap. It's tempting to write if qr is None: return preview. Checked against real Core result objects, that version mapped rows, previews, empty results, rejected SQL and driver errors correctly, then labelled a read-only request that came back without a result as a preview: exactly the ambiguity the mapper exists to remove. A missing result is only a preview when the request asked for one, so the branch below checks execution_mode:
# response_mapping.py: custom application code; untested sketch.
from dataclasses import dataclass
from typing import Any
@dataclass(frozen=True)
class NLQResponse:
status: str # "preview" | "rows" | "empty" | "invalid_sql" | "db_error"
sql: str | None
columns: list[str]
rows: list[list[Any]]
truncated: bool
from_cache: bool
latency_ms: int
def to_response(result) -> NLQResponse:
base = dict(sql=result.sql, from_cache=result.from_cache, latency_ms=result.latency_ms)
if not result.sql_is_valid:
return NLQResponse(status="invalid_sql", columns=[], rows=[], truncated=False, **base)
qr = result.sql_result
if qr is None:
status = "preview" if result.execution_mode == "generate_only" else "db_error"
return NLQResponse(status=status, columns=[], rows=[], truncated=False, **base)
if qr.error:
# Log qr.error server-side; don't echo raw driver errors to the client.
return NLQResponse(status="db_error", columns=[], rows=[], truncated=False, **base)
status = "rows" if qr.row_count else "empty"
return NLQResponse(status=status, columns=list(qr.columns), rows=qr.rows,
truncated=qr.truncated, **base)
An equivalent guard has been exercised separately (a permitted execution with no result maps to db_error, a genuine preview still maps to preview), but this exact branch hasn't been re-run, so put both cases in your own fault tests. The QueryResult fields used here (columns, rows, row_count, error, truncated) come from the connector base class in the pinned source; the status values are yours. What matters is that "invalid", "nothing was meant to run", "ran and found nothing" and "the database failed" are four different responses, and none can be mistaken for a zero.
Exceptions need the same discipline. The configuration docs describe an LLMTimeout for a model call that stops producing data, and OutputBudgetExhausted when a model produces nothing within its output budget (configuration). Catch those at your service boundary and return a failure status, never a numeric answer.
Some failures no status field can catch. Suppose the model joins order_items to filter for orders with line items:
-- Plausible but wrong: the join fans out order 101 and drops order 106.
SELECT SUM(o.amount_cents) AS revenue_cents, COUNT(DISTINCT o.id) AS paid_order_count
FROM orders AS o JOIN customers AS c ON c.id = o.customer_id
JOIN order_items AS i ON i.order_id = o.id
WHERE c.tenant_id = 'A' AND o.status = 'PAID'
AND o.order_date >= '2026-10-01' AND o.order_date < '2026-11-01';
Against the same fixture on SQLite 3.45.3, this returns (40000, 2). Order 101's two line items double its amount, order 106 has no items so the count drops, and the DISTINCT does nothing for the sum. It's valid SQL, a read-only policy lets it run, and the mapper reports status="rows". Validation says the statement is well-formed and allowed, not that the answer is right. That's why the response carries sql: an analyst, or an evaluation suite comparing results with a fixture like this, can catch the fan-out where a status code can't.
Timeouts, caches and sessions are still your job
Timeouts come in layers, and each covers less than you might assume. timeout_seconds is forwarded to the SQL sub-agent's execution and applied as SET LOCAL statement_timeout on PostgreSQL, so the database aborts a runaway query. The source says plainly that it doesn't yet bound the LLM call (sync_runner.py), and which dialects honour it depends on the connector. Separately, deadline takes a time.monotonic() value for the whole turn and gates whether the SQL sub-agent attempts its one correction after a database error. CONNECTOR_STATEMENT_TIMEOUT_SECONDS (default 120) covers queries that don't pass their own timeout (configuration). Add an outer wall-clock budget in your own code, but don't advertise it as cancelling everything underneath.
Caching deserves the most caution. With use_cache=True (the default), answers go to a semantic cache, and the docs note that run_query_sync awaits the background cache write that a bare asyncio.run would otherwise cancel (getting started). But a cache shared between users with different entitlements is a leak path, so both functions above set use_cache=False until you've designed a cache scope that matches your permissions. Across several processes, they all need the same NLQ_CACHE_SIGNING_KEY (configuration).
Sessions follow the same logic. session_id and history enable follow-up questions, but the library passes them through; it doesn't decide who owns a conversation. Keep histories keyed to the authenticated user on the server, and never accept someone else's session ID from a client.
Under all of this sits the database role. The PostgreSQL connector opens every query in a read-only transaction, but the hardening guide notes that this still allows pg_sleep, advisory locks, and SELECT on anything the role can reach: "read-only is not the same as least-privilege" (database hardening). The SQLite connector opens the file read-only and installs an authorizer that refuses ATTACH, which is why the local fixture is fine for a smoke test. For anything shared, give NLQueries a restricted role that can only see what you meant to expose.
Where "internal" stops and licensing starts
All of this assumes an internal backend for your own staff, and that assumption does licensing work too. NLQueries Core 0.4.1 is source-available under the Business Source License 1.1, and the license's own notice says it isn't an Open Source license. Its Additional Use Grant permits production use as long as it doesn't include offering the work to third parties "on a hosted or managed basis, whereby a third party remotely accesses the functionality of the Licensed Work as a service" (LICENSE). The license applies per version: each converts to Apache 2.0 on its stated Change Date (June 4, 2030 for this release) or the fourth anniversary of that version's first public distribution, whichever comes first. It becomes Open Source only after that applicable conversion.
An admin tool for your revenue team sits comfortably on the internal side. Exposing the same endpoint to customers is a different question: settle it with the licensor before writing code for it, because no architecture fixes a missing right, and leave whether a given deployment fits the grant to your own legal review.
The tests that turn this prototype into a tutorial
The honest minimum is a trusted agent ID, two request modes, an explicit policy per call, an identity check in front, and a response with explicit states. What's missing is proof that it holds together with a real provider. In a disposable environment, start from an empty state directory, run the seed and setup block as written, configure a real model provider with pinned versions, then run three checks:
- A preview with literal grounding off produces valid SQL and leaves no generated-statement execution in the database logs.
- The read-only call returns
(30000, 3)for the October question, and each failure path maps to its own status: an invalid question, an empty result, a provider failure, a database timeout on a connector that enforcestimeout_seconds, and a permitted execution that returns no result. - A caller who isn't mapped to the
salesagent is rejected beforerun_queryis ever called.
Each catches something the component checks can't. The first proves the preview path is read-free. The second shows whether a real model gets the fixture right and whether provider errors and timeouts surface as failures or leak through as empty answers. The third is the only evidence your authorization exists, because no NLQueries setting can stand in for it.
Keep the exact commands, versions and redacted outputs, and record what actually happened, failures included, before calling any of this a tested build. Start with the third check: write the identity adapter and its denial test first, since it's the one control the library can't give you. Once all three pass in a clean environment, the next step is putting the module behind an HTTP API with real authentication, which is where a FastAPI layer, identity mapping and concurrency limits come in.
References
- NLQueries Core 0.4.1. nlqueries.orchestrator.sync_runner. Commit 2fbf276. Accessed 2026-11-04.
- NLQueries Core 0.4.1. nlqueries.execution. Commit 2fbf276. Accessed 2026-11-04.
- NLQueries Core 0.4.1. nlqueries.connectors.sqlite. Commit 2fbf276. Accessed 2026-11-07.
- NLQueries Core 0.4.1. Getting Started with NLQueries Core. Accessed 2026-11-05.
- NLQueries Core 0.4.1. CLI Command Reference. Accessed 2026-11-04.
- NLQueries Core 0.4.1. Configuration Reference. Accessed 2026-11-04.
- NLQueries Core 0.4.1. pyproject.toml. Accessed 2026-11-04.
- NLQueries Core 0.4.1. Hardening the database NLQueries reads. Accessed 2026-11-04.
- NLQueries Core 0.4.1. Business Source License 1.1. Accessed 2026-11-04.
- NLQueries. Documentation (v0.4.1 banner). Accessed 2026-11-05.