Schemagate

schemagate — fewer tokens for text-to-SQL, and only the tables the caller may read

Most text-to-SQL prompts carry the whole schema, and on a big database that is most of the prompt, on every question. schemagate sends the model only the tables the question needs: 91.1% fewer schema tokens (median) on databases with 50+ tables (benchmark). It filters by the caller's grants first, so restricted tables are absent from the prompt rather than ranked low. Works with LangChain, LlamaIndex, MCP, or any SQL agent, on Postgres, Oracle, MySQL, SQL Server and SQLite.

Measured, not drawn: python docs/media/make_token_gif.py rebuilds it from the seeded 1,201-object fixture, counting with tiktoken's cl100k_base.

#1 on BIRD's Mini-Dev leaderboard in all three dialects: SQLite 81.0, PostgreSQL 72.6, MySQL 72.4 execution accuracy, with Claude Opus 5.5 and one model call per question (leaderboard, run files). Smaller prompts, no accuracy given up.

If it cut your prompt, a ⭐ helps other people building SQL agents find it.

With row-level security alone the failure is quiet: the model writes valid SQL against a table the caller cannot read, RLS strips every row, and the user is told "no records found" — indistinguishable from "this data does not exist."

Demo · Install · Benchmarks · Local models · What it costs · Coming from Vanna

Same question, two callers, no database and no key:

schemagate demo "salary by employee"                                     # hr_compensation absent

schemagate demo "salary by employee" --principal okta:hr --role payroll # now it is first

Absent, not ranked low. A table the caller may not read never enters the prompt, so no rewording of the question reaches it and there is nothing to filter out of the answer afterwards.

*Try it in the browser — no install, no database, no model call.*

And it answers

The selection is a prompt, so the rest follows:

pip install schemagate

schemagate demo "which customers owe us money" --answer --provider --model

main.crm_customer main.crm_contact main.v_customer_balance (+5) 8 of 42 objects · ~383 prompt tokens instead of ~2,036

-- SQL written by / , from 8 tables SELECT c.id, p.display_name, v.account_number, v.invoiced, v.paid, (v.invoiced - v.paid) AS balance_due FROM v_customer_balance v JOIN crm_customer c ON c.id = v.id_customer JOIN core_party p ON p.id = c.id_party WHERE v.invoiced > v.paid

id display_name account_number invoiced paid balance_due

1 Northwind Trading ACC-1001 33960.0 22080.0 11880.0 2 Kellner GmbH ACC-1002 8760.0 3000.0 5760.0

Rows, from a question, with no database to set up — that runs against a bundled 42-object schema. is the model service you choose: openai, anthropic, gemini, oci, local (no key at all), or auto (whichever key is set). Point it at your own database with --url:

schemagate select "which customers owe us money" \

--url "postgresql+psycopg://user:pw@host/db" --answer --provider --model

No key? Drop --provider and it prints a prompt to paste into any chat, then run the SQL it gives you back with --sql "SELECT ...".

More of the bundled schema, with the questions people actually type:

schemagate demo "which customers owe us money"

schemagate demo "late shipments by carrier" --prompt # the DDL the model gets

Against your own database it's the same shape:

schemagate select "revenue by month" --url postgresql://localhost/app --principal okta:jdoe --role finance

schemagate studio --url postgresql://localhost/app # the same thing, as a page

schemagate studio opens a local page where you type questions, switch the caller's roles, edit hints, and watch what reaches the prompt and what doesn't. The same page runs publicly at https://ashishsinha1602.github.io/schemagate/ on the six bundled schemas, in your browser, with no server behind it. The selector on that page is a JavaScript port of this library, and a test runs both against 1,789 cases and requires identical rankings.

If you're coming from Vanna (archived March 2026), docs/migrating-from-vanna.md is the short version: Vanna applied identity when the SQL *ran*; schemagate applies it before the model sees the schema. Your User maps to a Principal in one line.

What it saves

Every text-to-SQL call pays for the schema in the prompt. Dump the whole thing and you pay for every table on every question; hand the model six tables and you pay for six. Measured on the test schemas, average over their golden questions, same built-in estimator as tests/bench.py:

The last row is the one that matters: the selection stays around six tables no matter how big the schema is, so the saving grows with the schema. Real databases are the last row, not the first.

The same count on public schemas nobody here wrote (no model called; BENCHMARKS.md has the script). On Spider 2.0's real BigQuery and Snowflake warehouses, the median database with 50 or more tables went from its full schema to a selection 91.1% smaller (24 databases). On BIRD, whose databases have a median of seven tables, the median saving was 8.8%: there was little to cut. The price of cutting is that a needed table can be left out. On the warehouses, every table the gold query reads was in the selection for 86.7% of questions. Against the retrievers other tools use, at the same number of tables and no model calls, schemagate selected every needed table for 85.3% of warehouse questions where BM25 managed 78.7% and embedding search 78.2%, and 98.7% of BIRD questions against 81.0% and 89.4% (head-to-head). Worked example, with a price you should replace with your own: a 260-object schema, 5,000 questions a day, an input price of $3 per million tokens. Full schema: 16,095 × 5,000 × 30 = 2.4 billion tokens a month, about $7,200. With schemagate: 444 × 5,000 × 30 = 67 million, about $200. The browser demo has these two numbers as editable fields under the stats, so you can put in your own volume and price and watch it recompute against whatever question you ask.

Two more things that cost nothing here and money elsewhere: the selector itself never calls a model (BM25 plus a hashed embedder, offline, milliseconds), and the optional descriptions can be written by any chat window you already pay for instead of an API key — see Without an API key.

The problem this solves

Two things go wrong when you point an LLM at a database schema.

The first is cost. Most systems paste the whole schema into the prompt on every question. That's fine for twenty tables and ruinous for two thousand.

The second is worse, and it's the reason I wrote this. Schema selection happens *before* the query runs, so it happens before row-level security can do anything. If your selection step isn't identity-aware, the model gets handed a table the caller can't read. It writes perfectly good SQL. RLS or VPD filters every row out. The user sees "no records found" and believes it.

That's not an access-denied message. It's a wrong answer with a confident tone, and the user has no way to tell the difference. Filtering the catalog by identity first is the only way I know to avoid it.

from schemagate import Catalog, Principal

cat = Catalog().bootstrap("postgresql://localhost/app") # or: from schemagate.demo_schema import demo_catalog; cat = demo_catalog() cat.hint("invoice_draft", "pre-issue drafts only, not real revenue") cat.restrict("hr_compensation", ["payroll"])

sel = cat.select("revenue by month", top_k=6, principal=Principal("okta:jdoe", roles={"finance"}))

sel.prompt_fragment() # compact DDL, ready for the system prompt sel.object_list # [{'owner': ..., 'name': ...}] sel.explain() # why each object was picked

hr_compensation is not in that result and its name does not appear anywhere in the prompt text.

Install

pip install schemagate

That's the whole thing. One dependency (SQLAlchemy), no API key, no model download. The default embedder is a hashed n-gram vectoriser that runs offline and gives byte-identical results on every machine.

Extras, all optional:

pip install 'schemagate[postgres]'