AskDB

AskDB is an open-source NL-to-SQL toolkit for developers: it uses your LLM to generate checked, schema-grounded SQL, then hands it back to your app to review and run. BYO model, BYO database, BYO vector store.

AskDB grounds generation in a human-enriched schema artifact, and it keeps database execution, permissions, tenant policy, and audit logging inside the host application. Library, CLI, Studio, and HTTP surfaces share the same core pipeline.

Ask your data. Keep control of the query.

Status: pre-release beta. Every published package ships -beta.N prerelease versions on npm: the askdb CLI, @askdb/core, @askdb/config, @askdb/client, @askdb/http-api, and the @askdb/ai-* adapters are on the 1.0.0-beta line, and the other packages are 0.x betas. The core API (ask()), schema artifact, and config are documented, but public APIs may still change before 1.0 — Studio and RAG are the most likely to move.

AskDB returns SQL for review; the library, CLI, and HTTP API never execute it (Studio's optional Playground Execute is a local development tool). AskDB's SQL checks are heuristic defense in depth, not a security boundary. Run generated SQL under a read-only, least-privilege database role. See Run generated SQL safely.

Why AskDB exists

NL-to-SQL isn't a new problem — academic research goes back to LUNAR in 1972, and LLM-based generation has been studied since Codex in 2021. The hard part in production isn't the LLM call. It's everything around it: capturing the business context behind your schema, getting the right enrichment into the prompt, wiring up RAG when the schema is too big to fit, and repeating that work for every new project. AskDB is the toolkit for that part.

Quickstart

For a new project, use Node 22.14+, a database or schema source, and an API key from your model provider.

npx askdb@latest init
# create .env with the model/database keys referenced by askdb.config.ts
npx askdb@latest introspect --url "$DATABASE_URL" --out my-app.schema --schema-id my-app
npx askdb@latest ask --schema my-app.schema --question "Which tables look active?"

Use npx askdb@latest studio --schema my-app.schema when you want the local browser UI for schema enrichment and sample NL-to-SQL checks.

AskDB uses a schema artifact: either a directory such as my-app.schema/, a bundled JSON file, or a direct schema.json. To create that artifact from a real database, use introspection:

npx askdb@latest introspect --url "$DATABASE_URL" --out my-app.schema --schema-id my-app
npx askdb@latest ask --schema my-app.schema --question "Which tables look active?"

The detailed first-run paths live in the docs-site quickstart, packages/introspect/README.md, and docs/integration/installable-package.md.

Use as a library

# `ai` (Vercel AI SDK 6 or 7) is a required peer dependency — your app owns its version
pnpm add @askdb/core ai
pnpm add @askdb/postgres
# Example provider for the code below
pnpm add @ai-sdk/openai

pg is optional and only needed for live Postgres introspection through @askdb/postgres.

import { ask, loadSchema } from "@askdb/core";
import { postgresDialect } from "@askdb/postgres";
import { createOpenAI } from "@ai-sdk/openai";

const schema = loadSchema("./my-app.schema");
const openai = createOpenAI({ apiKey: process.env.OPENAI_API_KEY });

const { sql } = await ask({
  question: "How many users signed up last week?",
  schema,
  model: openai("gpt-4o-mini"),
  dialect: postgresDialect,
});

Per-provider model recipes (OpenAI, Azure OpenAI / Foundry, Google Gemini, and Anthropic) and introspection recipes live in docs/integration/installable-package.md.

Constitution

Product direction and technical baseline live in docs/:

• docs/mission.md — north star, principles, non-goals
• docs/architecture.md — package boundaries, diagrams, install profiles, connectors vs. optional peers
• docs/platform.md — languages, monorepo shape, Postgres-first
• docs/roadmap.md — phased implementation order
• docs/specs/ — per-feature specs with status (core pipeline, schema format, modes and observability, introspection, RAG, Studio, HTTP API, multi-tenancy, distribution)
• docs/contracts/modes-v1.md — operating modes (schema_only, bounded_results)
• docs/contracts/sensitive-fields-and-modes.md — sensitive schema markers vs. models, bounded summaries
• docs/integration/reuse-core-phase-3.md — stable @askdb/core entrypoints for wrappers (MCP/HTTP)
• docs/integration/installable-package.md — install + BYO model + introspection workflow recipes
• docs/specs/introspection.md — schema introspection spec
• — core NL→SQL pipeline and CLI spec

Development

Stack: pnpm workspace + Turborepo, TypeScript, and focused packages/apps:

• packages/core (@askdb/core) — dialect-agnostic NL→SQL pipeline (ask()) and schema artifact loader.
• packages/ai (@askdb/ai) — AI provider registry and shared config helpers for AI SDK language and embedding models.
• packages/ai-openai (@askdb/ai-openai) — OpenAI provider adapter for @askdb/ai.
• packages/ai-azure (@askdb/ai-azure) — Azure OpenAI / Foundry provider adapter for @askdb/ai.
• packages/ai-google (@askdb/ai-google) — Google Gemini provider adapter for @askdb/ai.
• packages/ai-anthropic (@askdb/ai-anthropic) — Anthropic Claude provider adapter for @askdb/ai.
• packages/client (@askdb/client) — config-aware facade (createAskDb) that resolves schema, model, and dialect from config.
• packages/config (@askdb/config) — defineConfig, env(), and askdb.config.* discovery/bootstrap.
• packages/connectors (@askdb/connectors) — deprecated re-export of the connector registry (now in ).

pnpm install
pnpm build    # turbo run build — builds the `askdb` binary used below
# The repo root already ships askdb.config.ts. Optionally create .env with the keys it references
# (e.g. OPENAI_API_KEY), or set ASKDB_MOCK_SQL to skip live model calls.
pnpm exec askdb ask \
  --schema fixtures/schemas/orders-users.schema \
  --question "How many orders are there?"
pnpm test     # turbo run test (integration runs when DATABASE_URL is set)
pnpm lint     # turbo run lint (TypeScript noEmit)

Before opening or updating a PR, run the release-style checks:

pnpm smoke:install
pnpm preflight

Public release steps are tracked in docs/release.md.

Multi-engine dev fixture — the same logical schema and data in PostgreSQL, MySQL, MariaDB, SQL Server and SQLite (fixtures/multi-engine/README.md):

pnpm fixture:up       # start the containers, wait for health, seed (idempotent)
pnpm fixture:down     # stop the containers (keeps volumes)
pnpm fixture:reset    # delete volumes and the SQLite file, then fixture:up

Then point AskDB at one of them, for example:

npx askdb introspect --engine postgres --schemas org,people,billing,ref \
  --url "postgres://fixture_reader:[email protected]:15432/askdb_fixture"

Schema artifacts — see docs/contracts/schema-v2.md, fixtures/schemas/README.md, and the sample fixtures/schemas/orders-users.schema/. A schema artifact can be hand-authored, bundled as JSON, or produced from Postgres with @askdb/introspect. Optional sensitive markers tag columns/tables in NL→SQL DDL by default ((sensitive)); use --omit-sensitive-from-prompt or ASKDB_OMIT_SENSITIVE_FROM_PROMPT to withhold names instead. Policy for modes and summaries is in docs/contracts/sensitive-fields-and-modes.md.

Environment variables

See .env.example for a copy/paste template. Keep real secrets in a local .env (gitignored); the CLI loads .env automatically and then reads process.env.

Variable	Purpose
OPENAI_API_KEY	Required for NL→SQL (BYO; OpenAI-compatible).
OPENAI_BASE_URL	Optional custom base URL for OpenAI-compatible APIs.
ASKDB_MODEL or OPENAI_MODEL	Optional model id (default gpt-4o-mini).
DATABASE_URL	Optional; commonly passed to askdb introspect --url for live Postgres introspection.
ASKDB_LOG_LEVEL	Optional structured log level: trace | debug | info | warn | error | fatal | silent (default: silent unless --verbose, --log-file, or --log-stdout implies info).
ASKDB_CORRELATION_ID	Optional; override the correlation id emitted on every JSON log line for the run.
ASKDB_MODE	Optional operating mode (schema_only | bounded_results); default schema_only. Formal contract: docs/contracts/modes-v1.md.
ASKDB_OMIT_SENSITIVE_FROM_PROMPT	When true/1/yes, omit sensitive column/table names from NL→SQL DDL (default is to include names, tagged (sensitive)). See docs/contracts/sensitive-fields-and-modes.md.

Structured logging (Phase 2) — JSON lines via Pino; diagnostics go to stderr by default so stdout stays free for SQL. Flags:

Flag	Purpose
-v / --verbose	Sets log level to info (stderr).
--log-level <level>	Explicit level (overrides ASKDB_LOG_LEVEL).
--log-file <path>	Append the same JSON logs to a file (sync writes; parent dirs created). Implies info if level was silent.
--log-stdout	Mirror structured logs to stdout. Implies info if level was silent.
--correlation-id <id>	Override correlation id (else random UUID per run).
--explain	After -- sql --, print -- explain -- plus JSON describing heuristic guardrails satisfied (statementKind, checksVerified, remediationNote).
--omit-sensitive-from-prompt	Omit sensitive identifiers from NL→SQL DDL (default: include names with (sensitive) tag). Overrides default when combined with ASKDB_OMIT_SENSITIVE_FROM_PROMPT.
--mode <id>	Operating mode: schema_only (default) or bounded_results. Current AskDB surfaces return SQL only.

Modes + structured logs (Phase 2) — same ask subcommand; pass --mode or set ASKDB_MODE. Use -v / --log-file so JSON events, including askdb.pipeline.mode, appear on stderr or in a file — see docs/contracts/modes-v1.md.

pnpm build
# Generate only — logs on stderr include askdb.pipeline.mode (default schema_only unless you pass --mode)
pnpm exec askdb ask \
  --schema fixtures/schemas/orders-users.schema \
  --question "How many orders?" \
  --mode schema_only \
  -v

CLI example (generate SQL only):

pnpm build
pnpm exec askdb ask \
  --schema fixtures/schemas/orders-users.schema \
  --question "How many orders are there?"

AskDB returns SQL for review; it does not execute generated SQL. Treat generated SQL as untrusted: approve it and run it under your own least-privilege, read-only database roles, database-enforced tenant isolation (for example Postgres row-level security), statement timeouts, and audit logging. See Run generated SQL safely and SECURITY.md.

Engines: PostgreSQL, MySQL, SQLite, and SQL Server are all first-class — install the matching @askdb/postgres, @askdb/mysql, @askdb/sqlite, or @askdb/sqlserver adapter and pass the dialect to ask(). Postgres is the reference dialect; the others ship dialect and introspection with parity tracked on the roadmap.

Current limitations (pre-1.0 / dev): AskDB returns SQL only (execution stays in your app); SQL guardrails are heuristic (not a full SQL parser); a first-party MCP server, richer report generation, and a hosted dashboard are roadmap work. Per-feature scope and status: docs/specs/.

CI: .github/workflows/ci.yml runs pnpm install --frozen-lockfile, pnpm build, starts the multi-engine fixture, runs pnpm test, runs the installable smoke test, and validates publish with a dry run.

What it does

• Natural language → checked SQL grounded in your database schema, with heuristic guardrails that catch common model mistakes (non-SELECT statements, stacked statements, write keywords, missing tenant columns, sensitive columns).
• Human-reviewed schema enrichment — introspect your database, then enrich the schema artifact with descriptions, aliases, and business concepts using Studio.
• Clear execution boundary — AskDB returns SQL; the library, CLI, and HTTP API do not execute it against your database. Your application decides whether to show it, review it, approve it, run it, log it, or reject it.
• Multiple surfaces — the same schema artifact and generation pipeline across the CLI, a Node library, and an HTTP API.
• Multi-tenancy — define a tenant policy (roots, hierarchy, scoped/polymorphic/global tables) and pass a runtime tenant scope to ask(); AskDB prompts the model to filter by tenant, binds your tenant IDs into the SQL, and runs a heuristic check that each scoped table has a tenant predicate that filters (its tenant column compared with the tenant placeholder, ANDed into the filter). Enforce tenant isolation in the database as the primary boundary.

Product notes

• BYO API keys — developers bring their own model credentials.
• Schema as input — describe your schema in a supported format; later support multiple formats and retrieval (e.g. RAG) over schema/metadata.
• Clarification — prompt or surface follow-ups when intent or schema context is unclear.
• Sensitive fields — schema artifacts can mark tables/columns; NL→SQL prompts list identifiers by default (tagged) with an optional omit mode (docs/contracts/sensitive-fields-and-modes.md); RAG paths remain policy-controlled by mode/host.
• Multi-tenant — questions can target a tenant; query scope must respect tenant boundaries when the deployment requires it.

Modes (trust boundaries)

How much of the schema context the model sees depends on the chosen mode:

1. Schema only — model proposes SQL from schema context only.
2. Schema + report shape — roadmap-only structured report template alongside generated SQL.
3. Schema + bounded results — reserved for hosts that may summarize approved result subsets outside the current AskDB surfaces.
4. Full AI-assisted reporting — roadmap-only richer AI-driven report generation where product rules allow.

Engine v1 (CLI @askdb/core): selectable modes schema_only and bounded_results are documented in docs/contracts/modes-v1.md. Current AskDB surfaces still return SQL only.

Status

Phases 1–10 are implemented on this branch: core, CLI, HTTP API, installable package seams, schema artifact format, Postgres introspection, schema enrichment, RAG indexing and retrieval, Studio (React browser UI), and multi-tenancy proof. Later phases are in docs/roadmap.md.

License

Apache-2.0 © Yahya Gilany. See LICENSE and NOTICE.