Giving an AI Agent Database Access Without Giving It Your Database

• Book: AI That Acts
• The series: AI in TypeScript — 5 books, from your first LLM call to agents in production — all five here
• My project: Hermes IDE | GitHub — an IDE for developers who ship with Claude Code and other AI coding tools
• Me: xgabriel.com | GitHub

"Let the agent query the database" is the feature everyone wants and the tool
everyone builds wrong the first time.

const runSql = tool({
  name: "run_sql",
  schema: z.object({ sql: z.string() }),
  async run({ sql }) { return db.$queryRawUnsafe(sql); },
});

That is a database shell exposed to a text interface that will follow
instructions found in a support ticket. Prompt-level mitigation ("only write
SELECT statements") is not a control — it is a request, and the request is
adjacent to untrusted content in the same context window.

There is a version of this that is safe, and it does not involve trusting
generated SQL.

Layer 1: parameterised tools instead of a query language

The strongest thing you can do is not offer SQL at all. Most "data questions"
resolve to a small number of shapes.

const revenueByPeriod = tool({
  name: "revenue_by_period",
  description:
    "Total revenue grouped by day, week or month for a date range. " +
    "Maximum range 365 days.",
  schema: z.object({
    from: z.string().date(),
    to: z.string().date(),
    granularity: z.enum(["day", "week", "month"]),
    productId: z.string().uuid().optional(),
  }).refine((a) => days(a.from, a.to) <= 365, {
    message: "Range cannot exceed 365 days", path: ["to"],
  }),
  async run(a, ctx) {
    return db.$queryRaw`
      SELECT date_trunc(${a.granularity}, created_at) AS period,
             sum(total_cents) AS cents
      FROM orders
      WHERE tenant_id = ${ctx.tenantId}
        AND created_at >= ${a.from}::date
        AND created_at <  ${a.to}::date
        ${a.productId ? Prisma.sql`AND product_id = ${a.productId}` : Prisma.empty}
      GROUP BY 1 ORDER BY 1`;
  },
});

The query is written by you. The model chooses a shape and fills parameters,
which is exactly the amount of freedom it needs. The tenant filter is not
expressible by the model, so it cannot be omitted.

Five or six tools like this cover a startling share of what people actually
ask. Build those first and see what is left over before reaching for anything
general.

Layer 2: if you must generate SQL, never execute it directly

Sometimes the question space genuinely is open-ended. Then the rule is: the
model proposes, a validator disposes.

import { Parser } from "node-sql-parser";

const ALLOWED_TABLES = new Set(["orders", "order_items", "products"]);

export function validateSelect(sql: string, tenantCol = "tenant_id") {
  const parser = new Parser();
  let ast;
  try { ast = parser.astify(sql, { database: "postgresql" }); }
  catch { return { ok: false as const, why: "unparseable" }; }

  const stmts = Array.isArray(ast) ? ast : [ast];
  if (stmts.length !== 1) return { ok: false as const, why: "multiple statements" };

  const s = stmts[0];
  if (s.type !== "select") return { ok: false as const, why: `${s.type} not allowed` };

  const tables = (s.from ?? []).map((f: any) => f.table).filter(Boolean);
  if (!tables.length) return { ok: false as const, why: "no table" };
  for (const t of tables) {
    if (!ALLOWED_TABLES.has(t)) return { ok: false as const, why: `table ${t}` };
  }
  return { ok: true as const, ast, tables };
}

Parse to an AST — never regex. "SELECT * FROM orders; DROP TABLE users" and
"select/*x*/ * from pg_shadow" both defeat string matching and neither
survives a parser that checks statement type and table names against an
allowlist.

Rejecting anything that is not a single select also rules out CTEs that
write (WITH x AS (DELETE ...)), which is a real Postgres capability people
forget.

Layer 3: the database enforces it anyway

Validation is code, and code has bugs. The last line has to be a role that
cannot do the thing.

CREATE ROLE agent_ro NOINHERIT LOGIN PASSWORD '...';
REVOKE ALL ON ALL TABLES IN SCHEMA public FROM agent_ro;
GRANT SELECT ON orders, order_items, products TO agent_ro;

ALTER ROLE agent_ro SET statement_timeout = '5s';
ALTER ROLE agent_ro SET idle_in_transaction_session_timeout = '10s';

A separate connection pool for the agent, on that role:

export const agentDb = new Pool({
  connectionString: process.env.AGENT_RO_URL,
  max: 4,
});

Now a validator bug is a failed query rather than a data loss event. max: 4
also stops a runaway agent from starving your application pool, which is the
more likely incident of the two.

Add row-level security on top, so the agent's role physically cannot see other
tenants:

ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
CREATE POLICY agent_tenant ON orders TO agent_ro
  USING (tenant_id = current_setting('app.tenant_id')::uuid);

Layer 4: bound the result before it reaches the context

A valid, permitted, tenant-scoped query can still return two hundred thousand
rows — into a context window you are paying for on every subsequent turn.

export async function runAgentQuery(sql: string, ctx: Ctx) {
  const v = validateSelect(sql);
  if (!v.ok) return { error: `Query rejected: ${v.why}` };

  const client = await agentDb.connect();
  try {
    await client.query("BEGIN READ ONLY");
    await client.query("SET LOCAL app.tenant_id = $1", [ctx.tenantId]);
    await client.query("SET LOCAL statement_timeout = '5s'");

    const { rows } = await client.query(`SELECT * FROM (${sql}) q LIMIT 201`);
    await client.query("COMMIT");

    const truncated = rows.length > 200;
    return {
      rows: rows.slice(0, 200),
      truncated,
      note: truncated
        ? "Result truncated at 200 rows. Add aggregation or a narrower filter."
        : undefined,
    };
  } finally {
    client.release();
  }
}

BEGIN READ ONLY is a second, independent guarantee against writes. The
LIMIT 201 wrapper tells you there was more without a second count query, and
the note tells the model what to do about it rather than leaving it to infer
that the list was cut.

SET LOCAL, not SET, because these are pooled connections and a
session-level setting would leak to the next borrower.

Explain the schema, do not expose it

The model needs to know what it can query. A raw information_schema dump is
both large and revealing.

const SCHEMA_DOC = `
orders(id, created_at, total_cents, status, product_id)
  status ∈ 'pending' | 'paid' | 'refunded' | 'cancelled'
  total_cents is an integer in cents, never a float
order_items(order_id, sku, qty, unit_cents)
products(id, sku, name, category)

Notes:
- Money is always cents. Divide by 100 only when presenting.
- Deleted orders are hidden automatically; do not filter on deleted_at.
- tenant_id is applied automatically; do not include it in your query.
`.trim();

Hand-written, curated, and cached as a stable prompt prefix. It also carries
the semantics that prevent whole classes of wrong answer — the cents rule
alone prevents revenue figures that are off by a hundred.

Log the SQL, always

logger.info("agent_sql", {
  runId: ctx.runId,
  tenantId: ctx.tenantId,
  sql,
  accepted: v.ok,
  reason: v.ok ? undefined : v.why,
  rows: result.rows?.length,
  ms,
});

Rejected queries are the interesting ones. They tell you whether the model is
trying to do something it should not, and — more often, that your schema doc
is missing something and it is guessing.

The order that matters

Shaped tools first, because most questions do not need SQL. AST validation if
you must generate it. A read-only role with RLS so the validator is not the
last line. Bounded results so a valid query cannot flood the context.

Every layer assumes the one above it will eventually fail. That is the point —
"the prompt says SELECT only" is a single layer, and it is the one that fails
first.

If this was useful

AI That Acts covers tools that touch
real systems — capability scoping, validation boundaries, bounded results, and
the difference between a tool a model can use and one it can misuse.

The full series is at
xgabriel.com/ai-in-typescript.