Build a Claude API SQL Query Generator Tool

What a Claude API SQL query generator actually does

A Claude API SQL query generator is a small service that takes a plain-English question — "show me the top 10 customers by revenue last quarter" — and turns it into a valid, runnable SQL query against a specific database schema. Claude doesn't need to see your data to do this; it needs to see your schema (table names, columns, types, relationships) and a clear instruction set describing the SQL dialect you're targeting.

This pattern is popular for internal analytics tools, customer-facing "ask your data" features, and support dashboards where non-SQL users need answers fast. The rest of this guide walks through the architecture, prompt design, and safety layer you need to ship this reliably.

Core architecture

A working text-to-SQL tool has four parts:

1. Schema context — a compact, machine-readable description of your tables and columns
2. System prompt — instructions telling Claude the SQL dialect, output format, and constraints
3. Generation call — the actual API request with the user's question
4. Validation layer — code that checks the generated SQL before it ever touches a real connection

Skipping step 4 is the most common mistake. Never execute model output directly against a production database.

Step 1: Build the schema context

Keep this concise. Feeding Claude a full pg_dump wastes tokens and hurts accuracy. A trimmed version works better:

Table: orders
  id (int, primary key)
  customer_id (int, references customers.id)
  total_cents (int)
  status (text: 'pending' | 'paid' | 'refunded')
  created_at (timestamp)

Table: customers
  id (int, primary key)
  name (text)
  country (text)

Add one-line comments for ambiguous columns (status values, currency units) — this is where most generation errors come from, not from the model misunderstanding SQL syntax.

Step 2: Write a tight system prompt

You are a SQL generator for a PostgreSQL database.
Given the schema below and a user question, output a single
read-only SELECT query. Never use INSERT, UPDATE, DELETE, or DDL.
Return only the SQL inside a ```sql code block, no explanation.
If the question cannot be answered with the schema, say so instead
of guessing table or column names.

Schema:
<schema here>

The "no explanation" instruction matters if you're parsing the output programmatically. If you want Claude to also explain the query to the end user, ask for both in a structured format (e.g., JSON with sql and explanation fields) rather than free text you have to regex out.

Step 3: Make the generation call

Here's a minimal example using the SubToAPI endpoint, which exposes Claude through a standard HTTPS API with an application key:

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": 500,
    "system": "You are a SQL generator for PostgreSQL...",
    "messages": [
      {"role": "user", "content": "Top 10 customers by total paid revenue in Q1 2025"}
    ]
  }'

In Node.js:

const res = await fetch("https://api.subtoapi.app/v1/messages", {
  method: "POST",
  headers: {
    "Authorization": `Bearer ${process.env.SUBTOAPI_KEY}`,
    "Content-Type": "application/json"
  },
  body: JSON.stringify({
    model: "claude-sonnet-4-5",
    max_tokens: 500,
    system: SYSTEM_PROMPT,
    messages: [{ role: "user", content: userQuestion }]
  })
});
const data = await res.json();
const sql = extractSqlBlock(data.content[0].text);

If you're building this as part of a larger product, routing calls through /docs/messages gives you a consistent request shape whether you're calling it from a backend job, a serverless function, or a dashboard tool. The appeal of a wrapper here is operational: one API key per app, usage visible per key, and no need to juggle raw provider credentials across environments.

Step 4: Validate before execution

Treat every generated query as untrusted input:

• Parse it with a SQL parser (e.g., node-sql-parser) and reject anything that isn't a SELECT
• Whitelist table and column names against your actual schema
• Enforce a LIMIT clause if one isn't present
• Run the query against a read-only replica or a database role with SELECT-only grants
• Set a query timeout at the database level, not just the application level

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

function isSafeSelect(sql) {
  const ast = parser.astify(sql, { database: "postgresql" });
  const statements = Array.isArray(ast) ? ast : [ast];
  return statements.every(s => s.type === "select");
}

This check is non-negotiable even if your prompt explicitly forbids writes. Models follow instructions well but not perfectly, and a single malformed query against a writable connection is enough to cause damage.

Improving accuracy

• Few-shot examples: include 2–3 example question/SQL pairs in your system prompt that match your actual schema's quirks (naming conventions, soft deletes, denormalized fields).
• Column comments: a line like status: 'paid' means payment captured, not shipped prevents more errors than any amount of model tuning.
• Lower temperature: SQL generation benefits from deterministic output; keep temperature low or at default rather than high.
• Streaming for long queries: if you're generating multi-CTE queries or also streaming an explanation back to the UI, /docs/streaming covers how to consume server-sent events so users see output as it's produced instead of waiting for the full response.
• Retry with error feedback: if the database returns a syntax or column-not-found error, feed that error back to Claude in a follow-up message asking it to correct the query. This loop fixes the majority of first-pass mistakes.

Where SubToAPI fits

If you're already building internal tools on top of Claude, SubToAPI gives you an application API key (sub_live_...) per project, so your SQL generator, your support bot, and your internal dashboard each get separate usage tracking without separate provider accounts. Team plans add seats so multiple engineers can see request volume and errors in one dashboard. You can check /pricing for the Solo, Team, and Scale tiers, or start with a free trial at /signup and follow /docs/quickstart to get your first key working in a few minutes.

questions

Can Claude generate SQL for any database dialect? Yes, as long as you specify the dialect (PostgreSQL, MySQL, BigQuery, Snowflake, etc.) in your system prompt. Accuracy is highest for common dialects like PostgreSQL and MySQL; less common dialect-specific functions may need extra examples.

Is it safe to run Claude-generated SQL directly against production? No. Always validate the output is a read-only SELECT, run it against a restricted database role or replica, and enforce timeouts and row limits before execution.

How do I handle questions the schema can't answer? Instruct Claude explicitly to say the question is unanswerable with the given schema rather than guessing table or column names — this is far more reliable than trying to catch hallucinated SQL after generation.