How to Connect an AI Agent to a Database (Text-to-SQL, Safely)

Intermediate ~4 min read Last tested October 2026

Updated 2026-10-15

The pattern behind every “chat with your database” product is simple: give the model the schema, let it write SQL, run that SQL on a read-only connection, and feed the rows back. This guide does it in plain Python with SQLite (zero setup) and shows the Postgres changes.

Prerequisites

Python 3.10+, pip install openai
The agent loop from Build your first AI agent in Python

Steps

1. Create a sample database

# seed.py
import sqlite3
db = sqlite3.connect("shop.db")
db.executescript("""
CREATE TABLE customers(id INTEGER PRIMARY KEY, name TEXT, country TEXT);
CREATE TABLE orders(id INTEGER PRIMARY KEY, customer_id INT, total REAL, created DATE);
INSERT INTO customers VALUES (1,'Ana','PT'),(2,'Ben','US'),(3,'Chen','SG');
INSERT INTO orders VALUES (1,1,120,'2026-09-02'),(2,1,80,'2026-09-20'),(3,2,300,'2026-10-01');
""")
db.commit()

2. Open the database read-only

# db_tools.py
import sqlite3, json
conn = sqlite3.connect("file:shop.db?mode=ro", uri=True)   # writes raise errors
conn.row_factory = sqlite3.Row

3. Write schema and query tools

def get_schema() -> str:
    rows = conn.execute("SELECT sql FROM sqlite_master WHERE type='table'").fetchall()
    return "\n".join(r["sql"] for r in rows)

def run_sql(sql: str) -> str:
    if not sql.lstrip().lower().startswith(("select", "with")):
        return "error: only SELECT queries are allowed"
    try:
        rows = conn.execute(sql).fetchmany(100)
        return json.dumps([dict(r) for r in rows], default=str)
    except sqlite3.Error as e:
        return f"error: {e}"          # the model reads this and fixes its SQL

The prefix check is a convenience, not security. The read-only connection is what actually protects you.

4. Register the tools and instruct the model

SYSTEM = ("You answer questions about a shop database. Always call get_schema first, "
          "then write one SQLite SELECT query with run_sql. Never guess numbers.")
FUNCS = {"get_schema": get_schema, "run_sql": run_sql}
TOOLS = [
  {"type": "function", "function": {"name": "get_schema", "description": "Return CREATE TABLE statements.",
     "parameters": {"type": "object", "properties": {}}}},
  {"type": "function", "function": {"name": "run_sql", "description": "Run a read-only SQLite SELECT.",
     "parameters": {"type": "object", "properties": {"sql": {"type": "string"}}, "required": ["sql"]}}},
]

Plug these into the loop from the first-agent guide and ask: “Which customer spent the most in September 2026?”

5. Switch to Postgres

Use psycopg, connect as a role with only SELECT grants, and set conn.read_only = True plus a statement_timeout. The full role setup is in MCP server for Postgres. Swap the schema query for information_schema.columns.

Tips for accurate answers

Add a short description of non-obvious columns to the schema text (“total is in USD, including tax”).
For big schemas, give a list_tables tool and a describe_table(name) tool instead of dumping everything.
Return errors as text instead of raising. Models are good at fixing their own SQL.

Common errors

attempt to write a readonly database: the model tried an INSERT/UPDATE. Read-only mode blocked it.

no such column: the model guessed a column name. Make sure the system prompt forces get_schema first.

Wrong totals: usually a join that duplicates rows. Ask the model to show its SQL, and add an example query to the prompt.

MCP server for Postgres
Build an agent that runs on a schedule (daily sales summary)