Claude API SQL Query Generation Agent Guide

Building a SQL Query Generation Agent with the Claude API

A SQL query generation agent takes a natural language question — "show me revenue by region for last quarter" — and turns it into a correct, executable SQL query against your actual database schema. Claude is well suited for this because it reasons well about relational structure, follows column and table constraints precisely when given them, and can call tools to validate or execute queries before returning results to the user.

The core pattern is always the same: give Claude your schema, give it strict output constraints, let it generate SQL, then validate and execute that SQL in your own code rather than trusting it blindly. This article walks through that pattern end to end, including tool use, guardrails against destructive queries, and streaming partial results back to users.

Why this is an agent problem, not a single prompt

A one-shot "write me a SQL query" prompt works for demos but breaks in production for a few reasons:

• Schema drift — tables, columns, and relationships change, and the agent needs the current schema, not a static one baked into the prompt.
• Ambiguity — "last quarter" needs to resolve against the current date; "top customers" needs a defined metric.
• Safety — generated SQL must never run DROP, DELETE, or UPDATE against production unless explicitly intended.
• Correctness feedback — if the query fails or returns an empty result, the agent should be able to see that and retry with a fix.

This is where tool use matters. Instead of asking Claude to just output text, you give it a run_query tool (and optionally get_schema) so it can inspect the database, propose a query, see the actual error or result, and self-correct.

Step 1: Expose schema as a tool

Don't paste your entire schema into every prompt if it's large. Instead, give Claude a tool to fetch relevant table definitions on demand.

{
  "name": "get_schema",
  "description": "Return column names and types for the given table names",
  "input_schema": {
    "type": "object",
    "properties": {
      "tables": {
        "type": "array",
        "items": { "type": "string" }
      }
    },
    "required": ["tables"]
  }
}

For smaller schemas (under ~30 tables), it's often simpler and more reliable to just include the full DDL in the system prompt. Claude handles long schema context well, and avoiding a round trip reduces latency.

Step 2: Add a read-only query execution tool

{
  "name": "run_query",
  "description": "Execute a read-only SQL SELECT query and return rows. Only SELECT statements are allowed.",
  "input_schema": {
    "type": "object",
    "properties": {
      "sql": { "type": "string" }
    },
    "required": ["sql"]
  }
}

On your backend, enforce this at the database layer too — never rely on the model's good behavior alone:

function assertSafeQuery(sql) {
  const normalized = sql.trim().toLowerCase();
  if (!normalized.startsWith("select")) {
    throw new Error("Only SELECT statements are permitted");
  }
  const forbidden = ["insert", "update", "delete", "drop", "alter", "truncate"];
  if (forbidden.some((kw) => normalized.includes(kw))) {
    throw new Error("Query contains a forbidden keyword");
  }
}

Run queries against a dedicated read-only database role as a second layer of defense, independent of string checks.

Step 3: System prompt that constrains output

You are a SQL assistant for a Postgres database. You only generate
SELECT statements. Always use the get_schema tool before writing a
query if you are unsure of column names. Never guess at column names.
If a question is ambiguous (e.g. a date range), ask a clarifying
question instead of guessing. Return the final SQL in a single

Being explicit about "ask a clarifying question instead of guessing" matters more than most prompt details here — it's the single biggest reduction in subtly wrong queries.

## Step 4: The agent loop

let messages = [{ role: "user", content: userQuestion }];

while (true) { const response = await client.messages.create({ model: "claude-sonnet-4", max_tokens: 1024, system: systemPrompt, tools: [getSchemaTool, runQueryTool], messages, });

const toolUse = response.content.find((c) => c.type === "tool_use"); if (!toolUse) break; // final answer reached

const result = await handleTool(toolUse.name, toolUse.input); messages.push({ role: "assistant", content: response.content }); messages.push({ role: "user", content: [ { type: "tool_result", tool_use_id: toolUse.id, content: result }, ], }); }

This loop is identical regardless of which model provider's SDK you use — the important part is that every tool call result feeds back into the conversation so Claude can correct a bad query after seeing a real database error.

## Routing this through SubToAPI

If you already build with Claude but want a stable HTTPS endpoint for internal tools, cron jobs, or a team of developers sharing one subscription, [SubToAPI](https://subtoapi.app) turns your existing Claude access into an API with its own `sub_live_` keys. That's useful for a SQL agent specifically because:

- Each internal service (dashboard, Slack bot, scheduled report job) gets its own key and usage breakdown, so you can see which one is generating the most queries or burning the most tokens.
- Tool use and streaming work the same way — see [/docs/tools](/docs/tools) and [/docs/streaming](/docs/streaming) for the exact request shapes.
- Team seats mean the whole data team can build against the same agent without sharing one raw credential.

Setup is the same `messages` call shown above, pointed at `https://api.subtoapi.app/v1/messages` with `Authorization: Bearer $SUBTOAPI_KEY`. The [quickstart](/docs/quickstart) and [messages docs](/docs/messages) cover the request format if you're wiring this up for the first time, and [pricing](/pricing) has the Solo/Team/Scale breakdown if you're deciding how to split keys across a team.

## Guardrails worth adding before production

- **Row limits** — always inject a `LIMIT` if the generated query doesn't have one, to avoid accidentally pulling millions of rows.
- **Query timeouts** — set a statement timeout at the database connection level, not just in application code.
- **Logging every generated query** — store the natural language input, generated SQL, and result row count for every request. This is your best debugging and improvement signal over time.
- **A confirmation step for anything beyond trivial SELECTs** — if a query joins more than three tables or touches sensitive tables (billing, PII), surface it to the user for a yes/no before executing.

### What database should the agent connect to?

Any SQL database works — Postgres, MySQL, Snowflake, BigQuery. The pattern is the same: give Claude the schema and a safe execution tool, and adjust the system prompt's SQL dialect notes accordingly.

### How do I stop Claude from hallucinating column names?

Always give it the real schema via a tool call or inline DDL rather than relying on memory of a previous conversation, and instruct it explicitly to call `get_schema` when unsure rather than guessing.

### Can this handle multi-turn clarification, like "what do you mean by last quarter"?

Yes — that's one of the main reasons to build this as an agent loop rather than a single prompt. Keep the conversation history across turns so Claude can ask a clarifying question and incorporate the answer into the final query.