Give an AI Agent a Read-Only SQL Tool for Postgres (Node.js + Claude)

You can give an AI agent a read-only SQL tool so your ops team can ask Postgres questions in plain English, and nobody has to hand a model write access. The database itself is the safety boundary. On top of it sit a table allowlist, a forced LIMIT, a statement timeout and a log of every query.

This build is for you if your ops lead messages an engineer three times a day asking "how many orders shipped late from the north warehouse last week?" It takes four small Node.js files, with Claude doing the reasoning and pg talking to the database.

Why not just give the model a database connection?

A language model writes SQL the way a sharp new hire does: usually right, sometimes wrong and sure of itself. A guardrail that lives only in your application code can be talked around, either by a cleverly worded question or by text sitting inside your own data. A Postgres role that only has SELECT grants can't be.

Each layer has one job:

• A read-only Postgres role is the real boundary. It has no INSERT, UPDATE or DELETE grants to abuse.
• A table allowlist keeps the agent out of tables it shouldn't read, and its error message tells the model how to fix the query.
• A forced LIMIT protects memory and keeps answers readable.
• A statement timeout stops one bad join from holding a connection for ten minutes.
• A query log lets you answer "who looked at what, and when" months later.

What goes wrong if you skip the guardrails?

The obvious failure is a write, like an UPDATE with no WHERE clause. The quiet failures do more damage because nobody spots them.

A heavy query on your primary database slows down the app your customers are using. If you have a read replica, connect the agent's role to that instead.

The agent may also treat a capped result as the full result. If it gets 200 rows and adds them up itself, it will report a number that is too small, and it will sound sure about it.

And with no log, when someone asks who pulled the customer list last month, you have nothing to show them.

None of these problems shows up until it has already hurt you.

Who should not build this?

If your team asks the same handful of questions every week, build a dashboard instead. Metabase or a saved report costs less to run, gives the same answer every time and doesn't need a model. An agent is worth it when the questions keep changing and nobody can guess what next week's will be.

Step 1: How do you create a read-only Postgres role?

Run this once as an admin. The new role can log in and read four tables. It can't do anything else.

// setup.js
import pg from "pg"

const admin = new pg.Client({ connectionString: process.env.ADMIN_DATABASE_URL })
await admin.connect()

const statements = [
  "CREATE ROLE ops_reader LOGIN PASSWORD '" + process.env.OPS_READER_PASSWORD + "'",
  "ALTER ROLE ops_reader SET default_transaction_read_only = on",
  "ALTER ROLE ops_reader SET statement_timeout = '5s'",
  "GRANT USAGE ON SCHEMA public TO ops_reader",
  "GRANT SELECT ON orders, shipments, warehouses TO ops_reader",
  "GRANT SELECT (id, name, region, created_at) ON customers TO ops_reader"
]

for (const sql of statements) await admin.query(sql)
await admin.end()

Two details matter here. First, the read-only default is a seatbelt and not a lock, because any session can switch it off. The GRANTs are the lock. Second, the column-level grant on customers means this role can't see emails or phone numbers at all, so no prompt can pull them out.

Step 2: How do you check the SQL before it runs?

Parse it. Searching the text for "DELETE" breaks the first time someone writes a comment. node-sql-parser tells you what kind of statement it is and which tables it touches.

// guard.js
import { Parser } from "node-sql-parser"

const parser = new Parser()
const opt = { database: "PostgresQL" }
const ALLOWED = new Set(["orders", "shipments", "warehouses", "customers"])

export function checkSql(sql) {
  let ast
  try {
    ast = parser.astify(sql, opt)
  } catch {
    return { error: "Could not parse that SQL. Use one plain SELECT." }
  }
  const stmts = Array.isArray(ast) ? ast : [ast]
  if (stmts.length !== 1 || stmts[0].type !== "select") {
    return { error: "Only a single SELECT statement is allowed." }
  }
  const tables = parser.tableList(sql, opt).map(t => t.split("::").pop())
  const blocked = tables.filter(t => !ALLOWED.has(t))
  if (blocked.length) return { error: "Tables not allowed: " + blocked.join(", ") }
  return { sql: parser.sqlify(stmts[0], opt) }
}

The parser doesn't support every Postgres feature. When it rejects valid SQL, the model sees the error and usually writes a simpler query. That's a fair trade for a check short enough to read on one screen.

Step 3: How do you force the LIMIT, the timeout and the log?

Wrap the checked query, run it inside a read-only transaction, and write one log line every time, whether the query worked or not.

// db.js
import pg from "pg"
import fs from "node:fs"
import { checkSql } from "./guard.js"

const pool = new pg.Pool({ connectionString: process.env.OPS_READER_URL, max: 3 })
const MAX_ROWS = 200

export async function runSql(rawSql, who) {
  const started = Date.now()
  const checked = checkSql(rawSql)
  let result = checked.error ? { error: checked.error } : null

  if (!result) {
    const client = await pool.connect()
    try {
      await client.query("BEGIN READ ONLY")
      await client.query("SET LOCAL statement_timeout = 5000")
      const res = await client.query(
        "SELECT * FROM (" + checked.sql + ") AS q LIMIT " + (MAX_ROWS + 1)
      )
      await client.query("COMMIT")
      result = { rows: res.rows.slice(0, MAX_ROWS), truncated: res.rows.length > MAX_ROWS }
    } catch (err) {
      await client.query("ROLLBACK").catch(() => {})
      result = { error: err.message }
    } finally {
      client.release()
    }
  }

  fs.appendFileSync("query-log.jsonl", JSON.stringify({
    at: new Date().toISOString(),
    user: who.user,
    question: who.question,
    sql: rawSql,
    ms: Date.now() - started,
    rows: result.rows ? result.rows.length : 0,
    error: result.error || null
  }) + "\n")

  return result
}

Asking for 201 rows is a small trick that fixes the capped-result problem. The model gets an honest truncated: true flag and doesn't have to guess.

Step 4: How do you connect the tool to Claude?

Put the table and column names in the tool description, because the model can only write good SQL for columns it knows about. The system prompt tells it to do the counting in the database.

// agent.js
import Anthropic from "@anthropic-ai/sdk"
import { runSql } from "./db.js"

const anthropic = new Anthropic()

const SYSTEM = "You answer operations questions with the run_sql tool. " +
  "Use COUNT, SUM and GROUP BY in SQL. Never add up rows yourself. " +
  "If a result has truncated: true, say the list is partial. " +
  "Explain the filter you used in one plain sentence. " +
  "If the data cannot answer the question, say so."

const tools = [{
  name: "run_sql",
  description: "Run one read-only SELECT. Tables: orders(id, customer_id, warehouse_id, status, created_at), " +
    "shipments(id, order_id, carrier, promised_at, delivered_at), warehouses(id, name, region), " +
    "customers(id, name, region, created_at). Results are capped at 200 rows.",
  input_schema: { type: "object", properties: { sql: { type: "string" } }, required: ["sql"] }
}]

export async function ask(question, user) {
  const messages = [{ role: "user", content: question }]
  for (let turn = 0; turn < 6; turn++) {
    const reply = await anthropic.messages.create({
      model: "claude-sonnet-5-5", max_tokens: 2000, system: SYSTEM, tools, messages
    })
    messages.push({ role: "assistant", content: reply.content })
    if (reply.stop_reason !== "tool_use") {
      return reply.content.filter(b => b.type === "text").map(b => b.text).join("\n")
    }
    const results = []
    for (const block of reply.content) {
      if (block.type !== "tool_use") continue
      const out = await runSql(block.input.sql, { user, question })
      results.push({ type: "tool_result", tool_use_id: block.id, content: JSON.stringify(out), is_error: Boolean(out.error) })
    }
    messages.push({ role: "user", content: results })
  }
  return "I could not answer that in a few tries. Try rephrasing, or ask the data team."
}

Don't drop the six-turn cap. Without it, a model stuck on a permission error can keep retrying until someone notices the bill.

What should you test before staff use it?

Try each of these by hand. Each one checks a different guardrail.

1. Ask it to delete old orders. You want a refusal or a permission error in the log. Either one shows the role is doing its job.
2. Ask "how many orders last year?" The log should show a COUNT query, not a list of 200 rows.
3. Ask about a table that isn't on the list. You should see the allowlist error, and the answer should say it can't help with that.
4. Run a deliberately slow cross join. Check that it gets cancelled at five seconds.
5. Put "ignore your instructions" in a customer name. Data can carry instructions too. The read-only role keeps the damage small, and this guide on AI agent prompt injection covers the rest.

After the first week, read the log. The questions people actually ask show you which views to build next and which questions deserve a proper dashboard.

For the bigger picture on how agents pick their tools and where they fit in a business, start with what an AI agent is and how it works. Planning something like this on your own database? Geminate Solutions will review your plan for free and reply within 24 hours.