How to Build a Claude-Powered SQL Query Generator

Building a Claude-powered SQL query generator means sending Claude your database schema plus a user's plain-English question, having it return a SQL query, and then validating that query before it ever touches your database. The core pattern is straightforward: schema-in, SQL-out, with a safety layer in between. The hard part isn't the prompt — it's handling ambiguous questions, preventing destructive queries, and keeping the model grounded in your actual table structure instead of hallucinating columns.

This guide walks through the full architecture: how to describe your schema to Claude, how to structure the prompt so you get clean, parameterizable SQL, how to validate and execute it safely, and how to wire it into an API using tool use so the output is structured rather than free text you have to regex out of a response.

Why natural-language-to-SQL needs more than a prompt

A naive implementation — "here's my schema, write SQL for this question" — works for demos and breaks in production. Three things go wrong consistently:

• Schema drift: the model invents a column or table name that looks plausible but doesn't exist.
• Ambiguous intent: "top customers" could mean by revenue, by order count, or by signup date, and the model will guess.
• Dangerous output: without constraints, nothing stops the model from generating DROP TABLE or an unbounded DELETE if the user's phrasing nudges it that way.

The fix for all three is the same: constrain the model's output format, give it the full schema explicitly, and treat the generated SQL as untrusted input to your own validation layer — never execute it directly against a connection with write permissions.

Step 1: Describe your schema precisely

Claude performs noticeably better when the schema is given as structured DDL rather than a loose description. Extract it directly from your database rather than hand-writing it, so it never goes stale.

CREATE TABLE customers (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  email TEXT NOT NULL,
  created_at TIMESTAMP NOT NULL
);

CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  customer_id INTEGER REFERENCES customers(id),
  total_cents INTEGER NOT NULL,
  status TEXT NOT NULL,
  created_at TIMESTAMP NOT NULL
);

Include foreign keys, column types, and a few sample rows if your schema has ambiguous columns (e.g., a status field with specific enum values). Sample values cut down on guessed filters like WHERE status = 'completed' when your actual value is 'fulfilled'.

Step 2: Use tool use for structured output

Rather than asking Claude to "write the SQL" in prose and parsing it out, define a tool so the response comes back as structured JSON — the query, a short explanation, and a flag for whether it's read-only.

{
  "name": "generate_sql",
  "description": "Generate a SQL query from a natural language question",
  "input_schema": {
    "type": "object",
    "properties": {
      "sql": { "type": "string" },
      "explanation": { "type": "string" },
      "is_read_only": { "type": "boolean" }
    },
    "required": ["sql", "explanation", "is_read_only"]
  }
}

This gives you a predictable payload to validate programmatically instead of parsing markdown code fences. If you're new to this pattern, see /docs/tools for the full request/response shape.

Step 3: Write the system prompt

The system prompt should be explicit about dialect, constraints, and what to do when the question is ambiguous:

You are a SQL generator for a PostgreSQL database. You will be given
a schema and a question. Generate a single SELECT query that answers
the question.

Rules:
- Only generate SELECT statements. Never generate INSERT, UPDATE,
  DELETE, DROP, ALTER, or TRUNCATE.
- Use only tables and columns that exist in the provided schema.
- If the question is ambiguous, pick the most reasonable
  interpretation and state your assumption in the explanation field.
- Always use explicit JOIN syntax, never implicit comma joins.
- Limit results to 100 rows unless the user specifies otherwise.

Being explicit about dialect matters — SQL generated for PostgreSQL often uses syntax that fails silently or errors on MySQL or SQLite, especially around date functions and LIMIT/OFFSET ordering.

Step 4: Validate before executing

Never execute model-generated SQL directly. At minimum:

1. Parse it with a real SQL parser (e.g., node-sql-parser or sqlglot) to confirm it's syntactically valid and is actually a SELECT.
2. Whitelist tables and columns against your known schema — reject anything referencing an object you didn't provide.
3. Run it against a read-only database role, so even a bypassed check can't mutate data.
4. Cap row counts and query timeout at the connection level, not just in the prompt.

const { Parser } = require("node-sql-parser");
const parser = new Parser();

function validateQuery(sql, allowedTables) {
  const ast = parser.astify(sql);
  if (ast.type !== "select") throw new Error("Only SELECT allowed");
  const tables = parser.tableList(sql);
  for (const t of tables) {
    const name = t.split("::").pop();
    if (!allowedTables.includes(name)) {
      throw new Error(`Unknown table: ${name}`);
    }
  }
  return ast;
}

Step 5: Wire it into an API

Once the generation and validation logic work locally, expose them behind an endpoint your frontend or internal tools can call. If you're already calling Claude through SubToAPI, the request looks like any other messages call with your tool definition attached — you get the same streaming and usage metadata as a direct integration, plus an application API key you can hand to internal teams without sharing your underlying Claude access. See /docs/quickstart to get a sub_live_... key running, and /docs/messages for the request format this example builds on.

curl https://api.subtoapi.app/v1/messages \
  -H "Authorization: Bearer $SUBTOAPI_KEY" \
  -H "Content-Type: application/json" \
  -d '{
    "model": "claude-sonnet-4-5",
    "max_tokens": 1024,
    "tools": [{"name": "generate_sql", "input_schema": {...}}],
    "messages": [
      {"role": "user", "content": "Show me customers who placed more than 3 orders in the last 30 days"}
    ]
  }'

For teams building this as a shared internal tool, per-key usage metadata makes it easy to see which app or team is generating the most queries, and seat-based plans (/pricing) keep billing simple as more people start using it.

Keep a feedback loop

Log every generated query alongside the validation result and, where possible, whether the user accepted or edited it. This gives you a dataset to spot recurring failure patterns — a specific join Claude consistently gets wrong, or a phrase that triggers ambiguous queries — and to refine your schema descriptions or few-shot examples over time.

questions

Does Claude need direct database access to generate SQL? No. Claude only needs the schema as text (DDL plus optional sample rows). It never connects to your database — your application executes the generated query against a read-only connection after validation.

How do I stop Claude from generating destructive queries? Constrain it at three layers: instruct it to only produce SELECT statements in the system prompt, validate the output with a real SQL parser before running it, and execute against a database role that has no write or drop permissions.

Which Claude model works best for SQL generation? Claude Sonnet models handle most schema sizes and question complexity well at lower cost. For very large schemas (50+ tables) or multi-step analytical queries, testing against a stronger model is worth the extra cost to compare accuracy before committing.