How to Build a Text-to-SQL Tool With Claude

Building a text-to-SQL tool with Claude means combining three things: a model that can translate natural language into SQL, a schema description it can reason against, and an execution layer that safely runs the generated query. This article walks through the full pipeline — from prompt design to validation to returning results — so you can ship a working tool rather than a demo that breaks on real schemas.

The short answer: give Claude your database schema as context, constrain it to generate only SQL (ideally via tool use), validate the query before execution, and run it against a read-only connection. The rest of this guide covers each step in detail.

Why text-to-SQL is a good fit for Claude

Claude handles structured reasoning tasks well, and SQL generation is fundamentally a structured translation problem: natural language intent → relational logic → syntax. The hard part isn't getting a query out of the model — it's getting a correct query that matches your actual schema, handles joins properly, and doesn't silently return wrong results.

That means the quality of your tool depends less on prompt cleverness and more on:

• How much schema context you give the model
• Whether you constrain output format
• Whether you validate before execution
• Whether you give the model a way to self-correct on errors

Step 1: Prepare schema context

Claude needs the actual table and column structure, not a vague description. Generate this programmatically from your database rather than hand-writing it, so it stays in sync.

-- Example: extracting schema for context (Postgres)
SELECT table_name, column_name, data_type
FROM information_schema.columns
WHERE table_schema = 'public'
ORDER BY table_name, ordinal_position;

Format the output as compact DDL-like text rather than a table dump — it's more token-efficient and Claude parses it reliably:

Table: orders
  id (int, primary key)
  customer_id (int, references customers.id)
  total_cents (int)
  status (text) -- values: pending, paid, shipped, cancelled
  created_at (timestamp)

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

Include sample value hints for enum-like columns (like status above). This single addition fixes a large fraction of wrong-filter errors in generated queries.

Step 2: Constrain the output with tool use

Rather than asking Claude to "write SQL" in free text and parsing it out of a response, define a tool that forces structured output. This avoids regex-parsing markdown code blocks and gives you a clean contract.

{
  "name": "run_sql_query",
  "description": "Execute a read-only SQL query against the application database",
  "input_schema": {
    "type": "object",
    "properties": {
      "query": { "type": "string", "description": "A single SELECT statement" },
      "explanation": { "type": "string", "description": "One-sentence plain English summary of what the query does" }
    },
    "required": ["query", "explanation"]
  }
}

The explanation field is not decorative — surface it in your UI so users can sanity-check the query's intent before results come back. It also gives you a cheap signal for logging and debugging misfires.

If you're routing this through SubToAPI instead of calling Claude directly, tool definitions work the same way against the Messages endpoint — see /docs/tools for the request format and how tool calls come back in the response.

Step 3: Write the system prompt

Be explicit about constraints. Vague instructions ("write good SQL") produce inconsistent results; explicit rules produce predictable ones.

You are a SQL generation assistant for a Postgres database.
Rules:
- Only generate SELECT statements. Never generate INSERT, UPDATE, DELETE, or DDL.
- Use only tables and columns provided in the schema context.
- Always use explicit column names, never SELECT *.
- Add a LIMIT 100 unless the user asks for an aggregate or count.
- If the request is ambiguous, ask a clarifying question instead of guessing.
- Return results only via the run_sql_query tool.

That last rule matters more than it looks — without it, Claude will sometimes explain the query in prose instead of calling the tool, which breaks automated pipelines.

Step 4: Validate before execution

Never execute a generated query directly against a writable connection. Add a validation layer between Claude's output and your database:

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

This is a basic guardrail, not a complete sandbox. Pair it with:

• A dedicated read-only database role for the connection the tool uses
• A query timeout (2–5 seconds is usually enough)
• Row limits enforced at the connection level, not just in the prompt

Running the query

const result = await db.query(validateQuery(generatedSql), { timeout: 3000 });

Return both the raw rows and the natural-language explanation to the frontend, and log the original question, generated SQL, and row count together. This log is the single most useful artifact for improving the tool over time — it tells you exactly which phrasings produce bad queries.

Step 5: Handle errors with a feedback loop

When a query fails (wrong column name, type mismatch), don't just show the user a database error. Feed the error back to Claude in the same conversation and let it retry:

messages.push({ role: "user", content: `Query failed with error: ${dbError.message}. Please correct the query.` });

One retry round resolves the large majority of schema-mismatch failures. Cap it at two retries to avoid burning tokens on queries that are fundamentally unanswerable with the given schema.

Where SubToAPI fits

If you're already building against Claude for other features, SubToAPI lets you expose this text-to-SQL logic as a stable internal API — application keys (sub_live_...), streaming for long-running query explanations, and usage metadata per key so you can track which internal tool or team is generating the most queries. Setup takes about the time it takes to read /docs/quickstart, and tool-use requests work the same as calling Claude directly, documented at /docs/tools. Plans start at /pricing with a free trial at /signup.

Questions

Does Claude need write access to my database? No. The tool should only ever connect through a read-only role. Claude generates SQL text; your application decides how and whether to execute it.

How do I handle schemas with hundreds of tables? Don't pass the entire schema every time. Use a retrieval step that selects the relevant tables based on the user's question (via embeddings or keyword matching) and only include those in the context.

What's the biggest source of wrong queries? Ambiguous column semantics — status codes, date ranges, and soft-deleted rows. Document these explicitly in your schema context, since they cause more errors than complex joins do.