DEV Community

Ashish sinha
Ashish sinha

Posted on

schemagate: ask your database a question in English, get the rows back

You ask a question in English. You get rows back. One command.

export ANTHROPIC_API_KEY=...        # or OPENAI_API_KEY, GEMINI_API_KEY

schemagate select "which customers owe us money" --url "$DB" \
  --answer --provider anthropic --model <model-id>
Enter fullscreen mode Exit fullscreen mode 
-- SQL written by Anthropic / <model-id>, from 6 tables

customer_name  owed
-------------  ----
Acme Corp      5100
Globex         1150
Enter fullscreen mode Exit fullscreen mode

No pasting. No copying SQL around. schemagate picks the tables, the model writes the SQL, schemagate runs it and prints the rows.

Two things make it different from every other text-to-SQL wrapper: it is much cheaper per question, and it knows who is asking.

Apache-2.0. pip install schemagate.

Why it is cheap and fast

Most tools send your whole schema to the model on every single question. Real numbers from the built-in 42-table demo schema:

Characters sent Rough tokens
Whole schema 8,104 ~2,000
What schemagate sends 1,722 ~430

About 79% less prompt, every question. On a 400-table warehouse the gap is far bigger.

You pay for input tokens on every question, so a fifth of the prompt is a fifth of the bill. It is also faster to send and easier for the model to get right — 8 relevant tables beats 400 and "find the right ones yourself".

Picking the tables costs nothing at all:

$ time schemagate demo "which customers owe us money"
real  0m0.456s
Enter fullscreen mode Exit fullscreen mode

Half a second on a laptop. No API key, no model call, no network — BM25 plus embeddings, running locally. A model is only involved once the prompt is already small.

Why it knows who is asking

This is the bug I kept meeting in production.

Something picks tables for the model. That picker does not know who the user is. It hands the model a table this user cannot read. The model writes correct SQL. The database runs it, row-level security strips every row, and the user is told:

No records found.

Nothing crashed. Nothing was logged. A wrong answer, in a confident voice.

schemagate picks tables with the caller's identity. A restricted table is not ranked low — it never enters the list, so no rewording of the question can reach it.

echo '{"restrict": {"hr_compensation": ["payroll"]}}' > catalog.json
Enter fullscreen mode Exit fullscreen mode

Analyst asks:

schemagate select "what do we pay people" --url "$DB" \
  --config catalog.json --principal okta:analyst
Enter fullscreen mode Exit fullscreen mode 
2 of 3 objects selected
  main.crm_customer
  main.sales_invoice
Enter fullscreen mode Exit fullscreen mode

Payroll asks the same question:

schemagate select "what do we pay people" --url "$DB" \
  --config catalog.json --principal okta:hr --role payroll
Enter fullscreen mode Exit fullscreen mode 
3 of 3 objects selected
  main.hr_compensation
  main.crm_customer
  main.sales_invoice
Enter fullscreen mode Exit fullscreen mode

Same question, same database, different person, different answer.

Install and connect 

pip install schemagate
pip install "schemagate[oracle]"      # or [postgres]
Enter fullscreen mode Exit fullscreen mode 
# Oracle
--url 'oracle+oracledb://user:pw@host:1521/?service_name=FREEPDB1'
# PostgreSQL
--url 'postgresql+psycopg://user:pw@host:5432/dbname'
Enter fullscreen mode Exit fullscreen mode

Flags worth knowing: --top-k 10, --schema SALES, --exclude 'stg_*', --limit 50, --explain.

--provider takes anthropic | openai | gemini | oci | local | auto. --model is required on purpose — model ids change, and a stale default is worse than an error. Every run prints who wrote the SQL, which matters with --provider auto.

schemagate never writes to your database. It reads the catalog, and the SQL it runs is checked first: anything that is not a single SELECT or WITH is refused — writes, a second statement, a statement hidden after a -- or /* */ comment. The SQL only ever sees the selected tables, so it cannot reach a table this caller may not see.

Does this scale to Entra ID / AD groups?

Straight answer, because it matters.

What scales: roles are opaque strings and matching is a set intersection, so you pass Entra group object IDs straight through. A user in 300 groups costs the same as a user in 3.

Entra traps: past ~200 groups Entra stops putting them in the token and sends _claim_names — you must call Graph transitiveMemberOf and cache. memberOf is not transitive. And group claims are GUIDs unless you configure on-prem names.

Where it does not scale yet: the restrict map is hand-written. For 400 tables that is a second copy of an ACL you already hold in your directory and in the database's own GRANTs. Two copies drift, and drift in an ACL is exactly the bug this library exists to prevent.

The right fix is to read the grants the database already has:

-- PostgreSQL
SELECT has_table_privilege('app_user','sales.invoice','SELECT');
-- Oracle
SELECT table_name FROM all_tab_privs
WHERE grantee IN (SELECT role FROM session_roles);
Enter fullscreen mode Exit fullscreen mode

Map Entra group to database role once — a dozen mappings, not four hundred. schemagate does not do this today. It is the obvious next step; if it is your blocker, say so on the issue tracker.

When the answer is wrong

It matches words. "What do we pay our doctors" fails if no table or column contains "pay" or "doctor". Describe your tables once:

schemagate describe --url "$DB" --all --provider anthropic --model <id> --config catalog.json
Enter fullscreen mode Exit fullscreen mode

No key? --provider none prints a prompt to paste into any chat; save the JSON reply and apply it with --apply reply.json. Same trick works for --answer: leave --provider off and schemagate hands you a ready-made prompt, and you feed the SQL back with --sql "SELECT ...".

From an AI client 

pip install "schemagate[mcp]"
schemagate studio --url "$DB"                                  # browser UI
SCHEMAGATE_DATABASE_URL="$DB" python -m schemagate.mcp_server   # Cursor, Claude Desktop
Enter fullscreen mode Exit fullscreen mode

Honest limits

On a 127-object, 3-domain schema (~7M rows, live Oracle ADB 26ai), recall@6 was 47% with bare table names, 60% with Gemini 2.5 Pro descriptions, 80% with Sonnet descriptions — against 92% on a single-domain schema. Descriptions matter a lot; cross-domain schemas are still hard.

Token counts above are characters divided by four, the usual rough rule. Your tokenizer will differ a little.

Certified end to end on Oracle Autonomous Database 26ai and PostgreSQL 16.

Fifteen years on Oracle and Postgres, and this is the bug I kept meeting. If an answer comes back wrong for you, open an issue with the question — that is the most useful bug report there is.

Top comments (0)