Querying Postgres from an AI Agent

There is a question that every developer asks eventually: "Can I connect ChatGPT to my production database?"

And then a little voice in their head screams: "NO!"

The fear is justified. LLMs are unpredictable. If you give an LLM raw SQL access (SELECT * FROM...), it is only a matter of time before it hallucinates a query that scans 10 million rows and crashes your server, or worse, tries to DROP TABLE users.

But the need is real. Your database holds the truth—customer orders, inventory levels, user counts. An AI agent that can't see this data is flying blind.

The Solution: The "Stored Procedure" Model

We solve this not by giving the AI a SQL terminal, but by giving it functions.

We build specific, read-only tools that execute pre-defined queries. The AI provides the parameters (like a user's email), but we provide the SQL.

This is effectively a modern version of "Stored Procedures." The AI knows what it can ask, but it has no control over how the database is touched.

The Implementation

We will use the postgres driver for Deno. This allows us to connect directly to Supabase, Neon, RDS, or any Postgres instance.

import { Client } from "https://deno.land/x/postgres/mod.ts";

/**
 * Searches for users by email domain.
 * 
 * Description for LLM: "Find users belonging to a specific company domain (e.g., 'acme.com')."
 * Note: We don't say "Run a SQL query". We say "Find users". 
 * We abstract the database away from the AI.
 */

type Input = {
  domain: string;
};

export default async function searchUsersByDomain({ domain }: Input) {
  // 1. Connection Security
  // The database URL (with password) is stored in the vault.
  const dbUrl = Deno.env.get("DATABASE_URL");
  if (!dbUrl) throw new Error("Missing DATABASE_URL");

  // We create a client for just this execution.
  // Ideally, this DB user should have READ-ONLY permissions.
  const client = new Client(dbUrl);
  await client.connect();

  try {
    // 2. The Parameterized Query
    // This is the most important line of code.
    // We do NOT construct the string manually like `WHERE email LIKE '%${domain}'`.
    // That would allow SQL Injection.
    // Instead, we use the driver's parameterization features.
    // The driver treats the input as a literal string, rendering injection impossible.
    const result = await client.queryObject`
      SELECT id, email, created_at, plan_tier
      FROM users 
      WHERE email LIKE ${`%@${domain}`}
      ORDER BY created_at DESC
      LIMIT 10
    `;

    // 3. Return the Rows
    return { users: result.rows };
  } finally {
    // Always clean up your connection.
    await client.end();
  }
}

Why This is Safe

Let's say a malicious user tricks the AI. User: "Find users with domain acme.com'; DROP TABLE users; --"

If we were just concatenating strings, this would destroy our database. But because we use parameterized queries, the database sees this:

SELECT ... FROM users WHERE email LIKE '%@acme.com''; DROP TABLE users; --'

It looks for a user whose email ends with that weird literal string. It finds nothing. The command fails harmlessly. The DROP TABLE is never executed as a command.

The Execution Story

User: "Do we have any customers from NVIDIA? And what plan are they on?"

Agent Action: Calls search_users_by_domain

Input:

{ "domain": "nvidia.com" }

Output:

{
  "users": [
    { "id": 42, "email": "jensen@nvidia.com", "plan_tier": "enterprise" },
    { "id": 55, "email": "engineer@nvidia.com", "plan_tier": "pro" }
  ]
}

Agent: "Yes, we have a few. Jensen is on the Enterprise plan, and there is an engineer on the Pro plan."

The AI answered a specific business question using live production data, without ever having the ability to break the database.