An explainable, deterministic, self-learning NL→SQL engine for real databases.
Most NL→SQL tools prompt an LLM and hope: they hallucinate columns, break on joins, and cannot explain their output. DB Buddy compiles SQL instead of generating it.
- Deterministic — the planner produces an execution plan; a Predicate AST renders it to SQL. The same question against the same schema yields the same query, always.
- Explainable — every result carries the reasoning: grouping, aggregation, join inference, ranking.
- AI never writes SQL. It labels the schema semantically, once, at analyze time. If no
model is reachable the engine falls back to deterministic rules and honestly reports
AI used: No. - Self-learning, guarded — learns that "revenue" means
payments.amount, behind frequency thresholds, noise filtering, temporal decay, and memory caps. Learning is assistive, never authoritative.
docker compose upOpen http://localhost:3000 and sign in as analyst@dbbuddy.io / Analyst#12345.
A sample ERP database — customers, orders, order items, payments, regions — is already
connected. Click Analyze Schema once, then ask:
total amount by segment last quarter
You get the answer, the SQL, and the reasoning behind it — which tables it joined, which column it treated as the measure, and why:
SELECT customers.segment, SUM(payments.amount) AS sum_amount
FROM customers
JOIN orders ON customers.customer_id = orders.customer_id
JOIN payments ON orders.order_id = payments.order_id
WHERE payments.paid_at >= '2026-04-01' AND payments.paid_at < '2026-07-01'
GROUP BY customers.segment;Three tables joined from declared foreign keys, the measure picked by column meaning, and the quarter resolved against the payment date — none of it prompted from an LLM.
The demo's secrets and passwords are committed and identical everywhere, so it is a demo, not a deployment.
docker compose down -vremoves it entirely. For anything real, start at docs/DEPLOYMENT.md.
Prerequisites: Python 3.10+ and a MySQL, PostgreSQL, or SQL Server database.
git clone https://github.com/sxjalxo/dbbuddy.git
cd dbbuddy
python -m venv .venv
source .venv/bin/activate # Linux/macOS
.venv\Scripts\activate # Windows
pip install -r requirements.txt
pip install -e .Verify the install with no database required:
python scripts/run_validation.pyThen query your database offline — no backend, no login, password prompted:
dbbuddy analyze --local --engine mysql --host localhost --user root --database sales
dbbuddy query --local --engine mysql --host localhost --user root --database sales "Show all users"
dbbuddy chat --local --engine mysql --host localhost --user root --database sales--local runs the engine in-process. For the full multi-user platform — web app, shared
history, saved charts, audit — start the backend and sign in:
docs/DEPLOYMENT.md. For everything the CLI can do:
docs/CLI.md.
If the
dbbuddyconsole script isn't on your PATH, usepython -m dbbuddy ….
Question: top 3 users by total payments
SELECT users.name, SUM(payments.amount)
FROM users
JOIN payments ON users.id = payments.user_id
GROUP BY users.id, users.name
ORDER BY SUM(payments.amount) DESC
LIMIT 3;Explanation returned with it: grouping on users; aggregation SUM(payments.amount);
join routed from the declared foreign key; ranking DESC + LIMIT 3.
User Query
↓
Semantic Enhancer (memory; AI-assisted enrichment)
↓
Intent Builder
↓
Query Planner (deterministic plan)
↓
SQL Compiler (Predicate AST) ──→ Dialect layer (MySQL / PostgreSQL / SQL Server)
↓
Execution Engine (parameterized, safe)
↓
Explainability Engine
↓
Learning Engine
The planner emits an engine-agnostic plan. The compiler renders it through a Predicate
AST — first-class operators (=, comparisons, LIKE/ILIKE, BETWEEN, IN/NOT IN,
IS [NOT] NULL, EXISTS/ANY/ALL) that each know how to render themselves. The dialect
layer is the only code that knows vendor SQL; there are no if engine == "postgres"
branches in the planner.
Filters are schema-driven and type-aware — numeric, date, boolean, and text
comparisons, plus calendar and relative dates (last month, past 30 days) — derived
from column types, with no hardcoded domain values anywhere in the SQL path. Joins route
from declared foreign keys, which is what makes it work on cryptic ERP schemas whose
key names follow no convention.
Full detail: docs/ARCHITECTURE.md.
The per-database context — connection pool, schema, AI-refined semantic layer, vector
index, relationship graph — is built once per (host, database, schema_hash) and
reused, not recomputed per query. A schema change yields a new key and an automatic
rebuild, so stale joins are never served.
| Step | Latency |
|---|---|
| Analyze Schema (one-time) | ~5–6 s |
| Cold query (first; loads from disk) | ~150–250 ms |
| Warm query | ~5–15 ms |
You can query the moment you connect: while a background analyze runs, queries use fast rule-based labels (Fast mode) and upgrade to AI-enhanced labels when it finishes.
| Engine | Identifier | Driver |
|---|---|---|
| MySQL / MariaDB | mysql |
mysql-connector-python |
| PostgreSQL | postgresql |
psycopg2 |
| SQL Server | sqlserver |
pymssql |
Each engine implements one Dialect contract covering connection lifecycle, schema
introspection, SQL fragments, and capability flags — so adding an engine means
implementing the contract, not editing the planner. Drivers load lazily; a missing
optional driver never breaks the others.
Adding one: docs/ADDING_DATABASE_DIALECT.md.
On top of the engine, DB Buddy is a multi-user platform with its own application database (PostgreSQL in production; your business databases are query targets only): organizations and RBAC, JWT auth with MFA and stateless session revocation, personal API keys, an audit dashboard, saved and published charts, dashboards, a 3D relation graph, an evidence-bound Insights Engine, and scheduled jobs.
See docs/ARCHITECTURE.md and docs/DEPLOYMENT.md.
- Parameterized queries — values are bound, never interpolated into a statement.
SELECTexecutes directly (switchable to review-first); writes require confirmation.- Writes confirmed from natural language run server-stored SQL through a single-use
execution token — the SQL a client posts alongside a token is ignored. Raw write
execution needs the explicit
query:write:manualpermission. - Dry-run previews and foreign-key dependency warnings before a destructive statement.
- Encryption at rest for connection credentials and MFA secrets; CORS allow-list, never a credentialed wildcard; SSRF guard on operator-supplied outbound URLs; opaque 500s so driver errors never echo a DSN back to a caller.
DBBUDDY_ENV=productionrefuses to start withoutJWT_SECRET(≥ 32 bytes),APP_SECRET_KEY, a non-SQLite app database, andALLOWED_ORIGINS.
Full model: docs/SECURITY.md. To report a vulnerability: SECURITY.md.
pytest # unit suite
DBBUDDY_STRICT=1 pytest # contract violations raise instead of being recovered
pytest -m integration # live MySQL/PostgreSQL/SQL Server (needs Docker)Correctness is checked three ways: a schema-adaptive behavioral suite that validates
intent against any connected schema without hardcoded expectations; a schema-portability
corpus of realistic ERP schemas (SAP, Odoo, ERPNext, healthcare, e-commerce, banking);
and dogfood suites that run the full pipeline against eight populated datasets — from
generated erp/hospital/legacy/tpch/tpcds to the real MySQL employees sample
(~4M rows), Microsoft's adventureworks OLTP (68 tables), and airportdb (up to ~59M
rows) — asserting invariants such as grouped totals summing back to the ungrouped total.
Details in CONTRIBUTING.md and docs/DEVELOPER_GUIDE.md.
dbbuddy_core/ → the deterministic engine (planner, compiler, execution, dialects/)
backend/ → FastAPI API + application database (app_db/, Alembic migrations)
frontend/ → React / TanStack Start UI
dbbuddy/ → the CLI
tests/ → pytest suite
scripts/ → dogfood correctness suites, benchmarks, developer utilities
docs/ → documentation
| Doc | Covers |
|---|---|
| ARCHITECTURE.md | The pipeline, execution engine, dialect layer, relation graph, Insights Engine |
| CLI.md | The analyst's CLI and its parity with the web app |
| DEPLOYMENT.md | Production setup, configuration, migrations, security checklist |
| DEVELOPER_GUIDE.md | Codebase map, setup, tests, engineering conventions |
| SECURITY.md | Auth, RBAC, encryption, safe execution, AI output validation |
| SEMANTIC_ROLES.md | How literals are grounded by column meaning, not word order |
| ADDING_DATABASE_DIALECT.md | Adding a SQL engine |
Full index: docs/.
Contributions are welcome — start with CONTRIBUTING.md, especially the
non-negotiables (no hardcoded schema knowledge; planner changes bump PLAN_VERSION;
validators enforce what prompts merely request).
Found a wrong answer? The wrong-answer issue template asks for a minimal schema — it turns your bug into a permanent regression test.