NLQueries GitHub

Blog  / 

A FastAPI Wrapper Around the NLQueries Python Interface: Prototype and Acceptance Tests

Published . Written for nlqueries-core 0.4.

fastapi python api

Say your reporting portal needs natural-language questions. Analysts want to type "For tenant A, what were paid-order revenue and paid-order count in October 2026?" and see the generated SQL before anything runs. A nightly workflow wants the numbers themselves. Someone spikes it in an afternoon: a FastAPI route that takes question, agent_id and execute from the JSON body and hands all three to the library. The demo works, and it's also an open door. Any caller who can reach that route can pick any configured database and decide whether the SQL runs.

Wrapping a library in HTTP turns it into a public input boundary. Everything the library trusted its caller to get right (which database, whether to execute, how long to wait) now arrives in a request body. This post designs a small internal FastAPI wrapper around NLQueries Core 0.4.1 that keeps those decisions on the server: two routes, /preview and /query, identity resolved before any model call, server-owned agent configuration, and timeouts and error responses that each stop something you can name. Read it as a wrapper-contract prototype, not a finished service. The routes have been exercised only against a scripted identity verifier and a scripted pipeline, two holes that run exposed are patched below but not re-run, and nothing here has run end to end through HTTP against a real identity provider, the real NLQueries pipeline or PostgreSQL.

The wrapper is yours, and so is the boundary

NLQueries supplies the query pipeline: an async run_query function, a synchronous run_query_sync wrapper and a structured AgentQueryResult (sync_runner.py). FastAPI supplies the HTTP surface. Everything in between is yours to write and maintain: routes, request and response models, authentication, the user-to-agent mapping, status codes and concurrency limits.

That distinction matters in two directions. First, /preview and /query are routes invented for this example. They aren't the NLQueries Enterprise REST API, which the vendor documents as a separate edition (NLQueries Core vs. Enterprise). If you need a supported HTTP contract rather than one you own, that's a different evaluation. Second, Core is released under the Business Source License 1.1, whose Additional Use Grant restricts offering the functionality to third parties as a hosted service (LICENSE). Writing your own wrapper doesn't change that. The design below is for an internal service. If your portal's users are paying customers, read that grant, or talk to the vendor, before you write the first route.

Core needs Python 3.11 or later and is installed with pip install nlqueries-core. You then register a connector, extract the schema and export a knowledge base with the CLI (Getting started). One detail from that guide shapes the code: the library takes the full connector ID, such as postgres:localhost:mydb, not the CLI alias.

# Versions are illustrative pins; lock your own.
python -m venv .venv && . .venv/bin/activate
pip install "nlqueries-core==0.4.1" "fastapi==0.115.6" "httpx==0.28.1" "pytest==8.3.4"
# Then follow the getting-started guide once per database:
#   nlqueries connect postgres ... ; nlqueries extract-schema <alias> ; nlqueries export-kb <alias>

The activation line assumes a POSIX shell, and the pins aren't a verified combined lockfile.

Who's asking, and which agent may they use?

The first rule of the service is that nothing reaches the model until you know who's calling and what they're allowed to touch. FastAPI's dependency system is the natural place for this: a dependency resolves the caller, and a route that declares it can't run without it (FastAPI security tutorial). That tutorial is a good starting point for token parsing. It isn't a production identity policy, though. Issuer and audience checks, key rotation, and the step from "valid token" to "allowed to query this dataset" are still yours.

The second rule is that the client never names the agent. It names a dataset from a short, server-defined list, and the server maps that to a connector ID and dialect. The request model forbids unexpected fields, so a body that tries to slip in agent_id gets a 422 before any work happens.

# app.py, part 1. Custom application code, not an NLQueries API.
from __future__ import annotations

import asyncio
import logging
import time
import uuid
from dataclasses import dataclass
from typing import Any, Literal

from fastapi import Depends, FastAPI, HTTPException
from fastapi.responses import JSONResponse
from fastapi.security import HTTPAuthorizationCredentials, HTTPBearer
from pydantic import BaseModel, ConfigDict, Field

from nlqueries.execution import ExecutionPolicy
from nlqueries.orchestrator.sync_runner import run_query

log = logging.getLogger("nlq_service")
app = FastAPI(title="internal-nlq")


@dataclass(frozen=True)
class Principal:
    subject: str
    allowed_agents: frozenset[str]


def verify_access_token(token: str) -> Principal | None:
    """Validate the bearer token with your existing IdP library.

    Check signature, issuer, audience and expiry there, then resolve the
    caller's entitlements. Deliberately unimplemented: shipping an allow-all
    placeholder here would turn this service into an open SQL endpoint.
    """
    raise NotImplementedError("wire this to your identity provider")


bearer = HTTPBearer(auto_error=False)


def get_principal(
    creds: HTTPAuthorizationCredentials | None = Depends(bearer),
) -> Principal:
    if creds is None:
        raise HTTPException(status_code=401, detail="missing bearer token")
    principal = verify_access_token(creds.credentials)
    if principal is None:
        raise HTTPException(status_code=401, detail="invalid token")
    return principal


@dataclass(frozen=True)
class AgentConfig:
    agent_id: str
    dialect: str


# Keyed by a name the client may send; the agent_id and dialect never come
# from the request body.
AGENTS: dict[str, AgentConfig] = {
    "sales": AgentConfig(agent_id="postgres:reporting-db:sales_a", dialect="postgres"),  # connector ID, not an alias
}


class AskRequest(BaseModel):
    model_config = ConfigDict(extra="forbid")  # an unexpected "agent_id" is a 422
    dataset: Literal["sales"]
    question: str = Field(min_length=3, max_length=500)

verify_access_token raises on purpose. It's the one piece left unsketched, because a placeholder that returns a fake user tends to survive into production. Wire it to whatever library already validates tokens in your organization.

The running example hides a subtler point. The question says "tenant A", and a well-behaved model will put tenant_id = 'A' in the SQL. That predicate gives you the right number. It doesn't give you authorization. If the agent's database role can read tenant B's rows, a reworded question can too. The NLQueries hardening guide treats its SQL policy and the database's own permissions as separate safeguards (Hardening the database NLQueries reads). In this design, "Alice may use the sales dataset" only means something if the connector behind postgres:reporting-db:sales_a uses a role that can see tenant A's data and nothing else.

Preview and query are two policies, not one flag

The spike passed execute: true straight through. Core gives you something better: an immutable ExecutionPolicy with two constructors, generate_only() and execute_read_only(). The default, when a caller passes nothing, is generate-only (execution.py). The policy is a frozen dataclass with no setter, so code downstream of your route can't widen it. With one route per mode, execution is chosen by which URL is called, not by a boolean in the body. As written, though, both routes accept anyone entitled to the dataset; if only some callers may execute, add that check to /query.

Two caveats are easy to miss. First, generate-only doesn't mean the database is never touched. Literal grounding reads the stored values a literal is compared to, through the agent's connector, and the source says to set NLQ_LITERAL_GROUNDING=false if you want a mode that opens no connector at all. For a preview route, that's usually what you want. Second, /query doesn't execute the SQL the analyst just previewed. Each call reruns the whole pipeline, and a regenerated statement can differ. If your workflow needs "a human approved exactly this SQL, now run it", that's a separate approval design that binds the approved statement.

# app.py, part 2. Response contract, bounded execution and routes (custom).
# Wrapper-contract prototype. The missing-result branch and the serialization
# guard at the end of _ask are untested sketches: they still need the
# failure-path tests described at the end of this article.
import json

from fastapi.responses import Response

Outcome = Literal["ok", "sql_rejected", "unclear_question", "database_error"]


class AskResponse(BaseModel):
    request_id: str
    outcome: Outcome
    execution_mode: str
    sql: str | None = None
    sql_is_valid: bool
    columns: list[str] | None = None
    rows: list[list[Any]] | None = None
    row_count: int | None = None
    truncated: bool = False
    truncation_reason: str | None = None
    answer: str | None = None


MAX_IN_FLIGHT = 4
REQUEST_BUDGET_S = 20.0   # how long the HTTP caller waits for the whole turn
DB_BUDGET_S = 15.0        # per-statement budget passed to NLQueries
_slots = asyncio.Semaphore(MAX_IN_FLIGHT)


def _error(status: int, request_id: str, code: str) -> JSONResponse:
    return JSONResponse(status_code=status, content={"request_id": request_id, "error": code})


async def _ask(body: AskRequest, principal: Principal, execution: ExecutionPolicy):
    request_id = uuid.uuid4().hex
    agent = AGENTS[body.dataset]
    if agent.agent_id not in principal.allowed_agents:
        raise HTTPException(status_code=403, detail="not allowed for this dataset")
    if _slots.locked():
        return _error(503, request_id, "busy")

    async with _slots:
        try:
            async with asyncio.timeout(REQUEST_BUDGET_S):
                result = await run_query(
                    question=body.question,
                    agent_id=agent.agent_id,
                    dialect=agent.dialect,
                    available_types=("sql",),
                    execution=execution,
                    timeout_seconds=DB_BUDGET_S,
                    deadline=time.monotonic() + REQUEST_BUDGET_S,
                    use_cache=False,  # until cache context carries entitlements
                )
        except TimeoutError:
            log.warning("request %s exceeded %.0fs", request_id, REQUEST_BUDGET_S)
            return _error(504, request_id, "timeout")
        except Exception:
            log.exception("request %s failed", request_id)
            return _error(500, request_id, "internal_error")

    log.info("request %s subject=%s agent=%s mode=%s",
             request_id, principal.subject, agent.agent_id, result.execution_mode)

    rows = result.sql_result
    if result.agent_type == "unclear":
        outcome, status = "unclear_question", 422
    elif not result.sql_is_valid:
        outcome, status = "sql_rejected", 422
    elif result.execution_mode != "generate_only" and rows is None:
        # Untested sketch: execution was permitted but nothing came back.
        outcome, status = "database_error", 502
    elif rows is not None and rows.error:
        log.error("request %s database error: %s", request_id, rows.error)
        outcome, status = "database_error", 502
    else:
        outcome, status = "ok", 200

    # Untested sketch: build and encode the response inside the error boundary.
    # Type policy: row values go out unchanged; NaN, Infinity and anything json
    # can't encode fail the request with a request ID instead of being rewritten.
    try:
        payload = AskResponse(
            request_id=request_id,
            outcome=outcome,
            execution_mode=result.execution_mode,
            sql=result.sql,
            sql_is_valid=result.sql_is_valid,
            answer=result.answer if outcome == "ok" else None,
        )
        if rows is not None and outcome == "ok":
            payload.columns = rows.columns
            payload.rows = rows.rows
            payload.row_count = rows.row_count
            payload.truncated = rows.truncated
            payload.truncation_reason = rows.truncation_reason
        content = json.dumps(payload.model_dump(), allow_nan=False)
    except (TypeError, ValueError):
        log.exception("request %s response could not be serialized", request_id)
        return _error(500, request_id, "serialization_error")
    return Response(content=content, status_code=status, media_type="application/json")


@app.post("/preview", response_model=AskResponse)
async def preview(body: AskRequest, principal: Principal = Depends(get_principal)):
    return await _ask(body, principal, ExecutionPolicy.generate_only())


@app.post("/query", response_model=AskResponse)
async def query(body: AskRequest, principal: Principal = Depends(get_principal)):
    return await _ask(body, principal, ExecutionPolicy.execute_read_only())

use_cache=False is a deliberate gate. The semantic cache can serve a stored answer to a later, similar question, and use_cache=False neither reads nor writes it (sync_runner.py). Until you've shown that your integration carries each caller's entitlements into cache lookups, the safe default is to leave it off and accept the extra model calls.

Status codes people can act on

The worst thing this service can return is a 200 that hides what happened: an empty result could mean "no paid orders in October", "the validator refused the SQL" or "nothing ran". The NLQueries result object is built to avoid that ambiguity. It carries sql_is_valid and execution_mode explicitly, and a comment in the source explains why: sql_result is None means three different things, so a caller that infers it will guess wrong (AgentQueryResult). The rows themselves come back as a QueryResult with an error field and explicit truncated and truncation_reason fields, because silently returning the first N rows of a larger answer is a wrong answer (connectors/base.py).

One case is easy to overlook: a request that was allowed to execute, passed validation and came back with no sql_result at all. A query that matches nothing returns an empty row list; a missing result means nothing ran, so the wrapper reports it as a failure, while a genuine generate-only preview still returns ok.

The wrapper turns those fields into a small, stable vocabulary. The mapping below is a design choice for this service, not anything the library prescribes:

Situation HTTP status outcome / error
No token, or a token your IdP rejects 401 —
Valid user, dataset not in their entitlements 403 —
Unknown field, missing field, question over 500 chars 422 FastAPI validation detail
Route classified the question as unclear 422 unclear_question
Generated SQL failed validation 422 sql_rejected
Execution permitted, but no result came back 502 database_error
Database returned an error 502 database_error
Service at its in-flight limit 503 busy
Whole turn exceeded the request budget 504 timeout
Response couldn't be encoded under the type policy 500 serialization_error
Anything else 500 internal_error

The serialization row needs explaining. Core's orchestrator converts Decimal values to float and dates to ISO strings before results travel back to the caller (orchestrator.py), and run_query rebuilds the rows from that transport. A float can be NaN or Infinity, and strict JSON has no spelling for either. The prototype's policy is deliberately blunt: row values go to the wire unchanged, and anything json.dumps(..., allow_nan=False) refuses becomes a 500 with the request ID, inside the same boundary as every other failure. It doesn't rewrite NaN to zero, because a revenue figure that quietly turns into 0 is worse than an error. Whether some values should be normalized instead is a product-contract decision, and either way the type policy and its failure paths are still untested here.

Notice what doesn't go in the response: driver messages, role names, provider errors and stack traces. They go to your log, keyed by the request_id the caller receives, so support can find the failure without the client ever seeing permission denied for role .... Whether a given connector reports failures as QueryResult.error or as a raised exception is for fault-injection tests against a real database to establish; the wrapper handles both.

For an independent record of what ran, NLQueries emits query_execution audit events on the nlqueries.audit logger. Each one carries a SHA-256 digest of the statement rather than its text, and setting NLQ_AUDIT_LOG writes them to a dedicated file (Observability). One documented trap: execution events are only built when that logger would emit at INFO, or when the file is set, so a service tuned to WARNING silently loses them. The example event includes principal and request_id fields, but the pages cited here don't say how a library caller populates them, so don't assume the wrapper's request ID reaches Core's records. The wrapper logs its own request ID and subject instead.

Deadlines: what each timeout actually stops

There are three clocks here, and each stops something different.

timeout_seconds is the per-statement budget. In 0.4.1 it's forwarded to the SQL sub-agent's execute_query call and applied as SET LOCAL statement_timeout on PostgreSQL, so a runaway query is aborted by the database instead of holding locks after the caller has gone. The docstring is explicit that it doesn't bound the LLM call, and that support varies by dialect (run_query). Hence DB_BUDGET_S sits a little below the request budget, as the docstring recommends.

deadline is a time.monotonic() value for the whole turn. The pipeline uses it to decide whether there's enough time left to attempt its one correction after a database error.

The outer asyncio.timeout(REQUEST_BUDGET_S) is the wrapper's own wall clock, and it's what turns a slow turn into a 504. Cancelling the coroutine stops your service from waiting. Whether an in-flight provider HTTP call is actually aborted depends on the client underneath, and nothing cited here settles that. Treat it as unknown until a test with a slow provider shows otherwise, and don't read a 504 as proof that the database stopped working either.

The semaphore caps concurrent turns per process and returns 503 rather than queuing without limit. Don't push long questions into FastAPI's BackgroundTasks. Those run in the same application after the response is sent, and the FastAPI docs point heavier work toward a proper job system (Background Tasks). A request that must survive a restart needs a durable queue.

Finally, async all the way down. FastAPI expects an async def route to await async libraries (Concurrency and async/await), and run_query is async, so the routes await it directly. The tempting shortcut, calling run_query_sync from inside the route, fails outright. It wraps asyncio.run, which can't be called from a running event loop, and the docstring says so. Expect CPython's standard error:

RuntimeError: asyncio.run() cannot be called from a running event loop

What a scripted run caught, and what it can't tell you

The first version of these routes had no missing-result branch and ended with a bare return JSONResponse(status_code=status, content=payload.model_dump()). It was driven through FastAPI's TestClient with two stand-ins: a scripted token verifier with hard-coded users, and a scripted run_query returning real Core 0.4.1 AgentQueryResult and QueryResult objects with canned values, on Python 3.11.16, FastAPI 0.115.6, Starlette 0.41.3, Pydantic 2.14.3 and httpx 0.28.1.

No token and a bad token each got a 401, an unentitled user got a 403, and a body carrying agent_id got a 422, all without run_query being called. Most of the response matrix held. The preview came back 200 as generate_only with no rows, and the query returned the canned row [30000, 3]. A validator rejection became a 422, and a database error became a 502 whose body didn't contain the planted role name, which appeared only in the server log. A query with no matches returned 200 with an empty row list, and the truncated flag passed through. A provider exception carrying a planted secret became a 500 without the secret. A slow turn, with the budget shortened for the test, became a 504, and a saturated semaphore produced a 503.

Two cases failed. A valid execute request with no sql_result came back as 200 ok with null rows, exactly the "nothing ran, reported as success" response the status table exists to prevent. And a NaN in a result row escaped the error boundary, producing a plain-text Internal Server Error 500 with no request ID, because the response was encoded after the try block had ended. The two untested sketches in part 2 are written to close those holes, but neither has been executed, and the prototype stays a prototype until they are.

That run is a contract check of the wrapper's own control flow. It says nothing about the NLQueries pipeline, whether sql_is_valid and QueryResult.error behave this way against a real database, how real tokens map to entitlements, or how the provider client reacts to cancellation. The [30000, 3] row matches the hand-checked answer only because the scripted pipeline was told to return it.

The acceptance tests that stand between this and a service

Replace each stand-in with the real thing, one boundary at a time, and treat what follows as blocking before anyone calls this a service. Start with a fixture you can check by hand: three customers (two in tenant A, one in tenant B) and six orders: two paid tenant A October orders worth 30,000 cents together, plus a cancelled October order, a paid order dated November 1, a tenant B order and a paid October order with a NULL amount. The hand-checked answer to the running question is 30,000 cents across three paid orders; the NULL-amount order counts but adds no revenue. Load it into PostgreSQL behind a role that sees only tenant A, and point the sales connector at it. Then work through three layers:

  • The two patches. Re-run the contract matrix with the new code. A valid execute request with no result must be an explicit failure, a genuine generate-only preview must still return ok, and neither error body may carry driver text. Push finite and very large numbers, NaN, Infinity, NULL, dates and bytes through the actual connector and HTTP response, and check that every failure arrives with a request ID.
  • The real stack. Wire verify_access_token to a test identity provider, remove the scripted pipeline, and confirm that /query returns 30,000 and 3 against the fixture with the cache off.
  • The boundaries a script can't reach. Ask about tenant B through tenant A's dataset and confirm the database, not the prompt, refuses it. Revoke a user's entitlement between two requests and confirm the second one fails. Kill a slow statement mid-flight and check pg_stat_activity for leftover work.

Keep commands, versions and redacted outputs for every run. Start with the first layer: it's the cheapest, it exercises the two sketches directly, and until it passes nothing else here should carry weight.

References

All posts · RSS feed · Documentation