# Test Text-to-SQL Queries Against a Real Postgres

> Run Postgres in a Sandboxer microVM with Docker, load your schema and sample rows, and save it as a named snapshot. Give the agent a run_sql tool that runs each candidate query as a read-only role, inside a read-only transaction with a statement timeout, and returns the rows or the exact Postgres error.

Source: https://sandboxer.com/guides/test-text-to-sql-queries-against-a-real-postgres. Updated 2026-10-27.

**Short answer:** Run Postgres in a Sandboxer microVM with Docker, load your schema and sample rows, and save it as a named snapshot. Give the agent a run_sql tool that runs each candidate query as a read-only role, inside a read-only transaction with a statement timeout, and returns the rows or the exact Postgres error.

## At a glance

| Decision | What this guide does | Why |
| - | - | - |
| Database | `postgres:16` in Docker, inside a `default` sandbox | Docker is preinstalled, so there is nothing to set up on the VM |
| Data | A small seed file: 4 tables, 27 rows | The agent sees real column names, types and edge cases (a refunded order) |
| Who the query runs as | `agent_ro`: `SELECT` only, read-only by default, 5-second statement timeout | A write fails even if the model switches read-only off |
| Tool output | JSON: columns and up to 50 rows, or the Postgres error text | The model can fix a wrong column from the error message |
| Fresh state | A named snapshot of the seeded database; one sandbox per session | Every session starts from the same rows |

## What we ran, and what we didn't

We have no LLM key on the test account, so there is no model in this guide. We called the `run_sql` tool directly with the queries a model might write: one correct, one with a wrong column, two writes and one runaway query. Everything else is real: the sandboxes, Postgres, the snapshot and the outputs below all ran on production (`sandboxer-sdk` 0.1.1, Python 3.12, PostgreSQL 16.15). To use a model, register `run_sql` as a tool in your agent framework. The tool doesn't change.

## 1. Start Postgres in a sandbox

```bash
sbx new --name sql-seed --no-env
id=sb_v9na1yk0k4    # the id that sbx new printed

sbx exec $id --timeout 300 "docker run -d --name pg --restart unless-stopped \
  -e POSTGRES_PASSWORD=pw -p 127.0.0.1:5432:5432 postgres:16"
sbx exec $id "docker exec pg pg_isready -U postgres"
```

```text
Status: Downloaded newer image for postgres:16
/var/run/postgresql:5432 - accepting connections
```

The image pull and start took about 8 seconds (single run, October 27, 2026). `--restart unless-stopped` matters later: it brings the container back when a sandbox boots from the snapshot. The port is bound to `127.0.0.1` only, so nothing outside the VM can reach it.

## 2. Load a schema, sample data and a read-only role

Save this as `seed.sql`. Use your real schema and a few hundred representative rows.

```sql
CREATE DATABASE shop;
\c shop

CREATE TABLE customers (id int PRIMARY KEY, name text NOT NULL, country text NOT NULL, created_at date NOT NULL);
CREATE TABLE products (id int PRIMARY KEY, name text NOT NULL, category text NOT NULL, price_cents int NOT NULL);
CREATE TABLE orders (
  id int PRIMARY KEY, customer_id int NOT NULL REFERENCES customers(id), ordered_at date NOT NULL,
  status text NOT NULL CHECK (status IN ('paid', 'refunded'))
);
CREATE TABLE order_items (
  order_id int NOT NULL REFERENCES orders(id), product_id int NOT NULL REFERENCES products(id),
  quantity int NOT NULL, PRIMARY KEY (order_id, product_id)
);

INSERT INTO customers VALUES (1, 'Ada', 'US', '2026-01-04'), (2, 'Bruno', 'DE', '2026-02-11'),
  (3, 'Chen', 'US', '2026-03-02'), (4, 'Dalia', 'FR', '2026-05-19'), (5, 'Emeka', 'DE', '2026-07-23');
INSERT INTO products VALUES (1, 'Keyboard', 'hardware', 8900), (2, 'Mouse', 'hardware', 2900),
  (3, 'Monitor', 'hardware', 24900), (4, 'IDE seat', 'software', 12000), (5, 'VPN year', 'software', 6000);
INSERT INTO orders VALUES (101, 1, '2026-08-02', 'paid'), (102, 2, '2026-08-15', 'paid'),
  (103, 1, '2026-09-01', 'refunded'), (104, 3, '2026-09-09', 'paid'), (105, 4, '2026-09-21', 'paid'),
  (106, 5, '2026-10-03', 'paid'), (107, 2, '2026-10-12', 'paid');
INSERT INTO order_items VALUES (101, 1, 1), (101, 2, 2), (102, 3, 2), (103, 4, 1), (104, 4, 3),
  (104, 5, 1), (105, 2, 1), (106, 3, 1), (106, 1, 1), (107, 5, 4);

-- The role the agent's queries run as: it can read, nothing else.
CREATE ROLE agent_ro LOGIN PASSWORD 'ro';
REVOKE ALL ON SCHEMA public FROM PUBLIC;
GRANT CONNECT ON DATABASE shop TO agent_ro;
GRANT USAGE ON SCHEMA public TO agent_ro;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO agent_ro;
ALTER ROLE agent_ro SET default_transaction_read_only = on;
ALTER ROLE agent_ro SET statement_timeout = '5s';
```

Copy it in and load it:

```bash
sbx scp ./seed.sql $id:/home/user/
sbx exec $id "docker exec -i pg psql -U postgres -v ON_ERROR_STOP=1 < seed.sql"
```

```text
CREATE DATABASE
You are now connected to database "shop" as user "postgres".
CREATE TABLE
…
INSERT 0 10
CREATE ROLE
…
ALTER ROLE
```

## 3. The run_sql tool

Save this as `sqltool.py`. It writes the query to a file in the sandbox, wraps it in a read-only transaction that is always rolled back, and runs it with `psql` as `agent_ro`.

```python
import json

PSQL = "docker exec -i pg psql -X -q --csv -v ON_ERROR_STOP=1 -U agent_ro -d shop"
MAX_ROWS = 50


def make_run_sql(sandbox):
    """Return a run_sql tool bound to one sandbox that has the seeded Postgres."""

    def run_sql(query: str) -> str:
        """Run one SQL query against the shop database, read-only.
        Returns {"ok": true, "columns": [...], "rows": [CSV lines]} or {"ok": false, "error": "..."}."""
        sandbox.files.write("query.sql", f"BEGIN TRANSACTION READ ONLY;\n{query}\n;\nROLLBACK;\n")
        r = sandbox.exec(f"{PSQL} < query.sql", timeout_seconds=30)
        if r.timed_out:
            return json.dumps({"ok": False, "error": "query took longer than 30 seconds"})
        if r.exit_code != 0:
            return json.dumps({"ok": False, "error": r.stderr.strip()})
        lines = r.stdout.strip().splitlines()
        result = {"ok": True, "columns": lines[0].split(",") if lines else [], "rows": lines[1 : MAX_ROWS + 1]}
        if len(lines) - 1 > MAX_ROWS:
            result["truncated"] = True
        return json.dumps(result)

    return run_sql
```

The query goes in a file, not on the command line, so quotes and semicolons in the model's SQL need no escaping. Inside the Postgres container, local connections don't ask for a password, so `-U agent_ro` is enough. Return the error text as it is: Postgres errors name the bad column and point at its position, which is what the model needs to fix the query.

Give the model the schema in its first prompt too. The same tool can fetch it:

```python
run_sql("""SELECT table_name, string_agg(column_name || ' ' || data_type, ', ' ORDER BY ordinal_position)
           FROM information_schema.columns WHERE table_schema = 'public'
           GROUP BY table_name ORDER BY table_name""")
```

```json
{"ok": true, "columns": ["table_name", "columns"], "rows": ["customers,\"id integer, name text, country text, created_at date\"", "order_items,\"order_id integer, product_id integer, quantity integer\"", "orders,\"id integer, customer_id integer, ordered_at date, status text\"", "products,\"id integer, name text, category text, price_cents integer\""]}
```

## 4. Save the seeded database as a named snapshot

```bash
sbx exec $id "docker exec pg psql -U postgres -c CHECKPOINT"
sbx snapshot $id sql-seeded
```

The `CHECKPOINT` flushes Postgres's data files to disk before the snapshot is taken. The save took about 2 seconds in our run. A named snapshot is independent of its source, so you can delete the seed sandbox now and still start from `sql-seeded`.

To check that copies start clean, we deleted every order in the seed sandbox (as `postgres`) after saving, then started a new sandbox from the snapshot. The new one had all 7 orders.

## 5. One clean database per session

Save this as `session.py`. It starts a sandbox from the snapshot, waits for Postgres, runs the candidate queries, and deletes the sandbox at the end.

```python
import time

from sandboxer import Sandboxer
from sqltool import make_run_sql

client = Sandboxer()  # reads SANDBOXER_API_KEY

t0 = time.monotonic()
sandbox = client.create(from_="sql-seeded", no_env=True, ttl_seconds=900)
sandbox.exec("until docker exec pg pg_isready -q; do sleep 0.5; done", timeout_seconds=60, check=True)
print(f"{sandbox.id} ready with Postgres in {time.monotonic() - t0:.1f}s")

try:
    run_sql = make_run_sql(sandbox)
    candidates = {
        "correct": """
            SELECT c.country, round(sum(p.price_cents * oi.quantity) / 100.0, 2) AS revenue
            FROM orders o
            JOIN customers c    ON c.id = o.customer_id
            JOIN order_items oi ON oi.order_id = o.id
            JOIN products p     ON p.id = oi.product_id
            WHERE o.status = 'paid'
            GROUP BY c.country
            ORDER BY revenue DESC""",
        "wrong column": "SELECT c.country, sum(o.total) FROM orders o JOIN customers c ON c.id = o.customer_id GROUP BY 1",
        "write": "DELETE FROM orders WHERE status = 'refunded'",
        "write, read-only switched off": "SET transaction_read_only = off; DELETE FROM orders WHERE status = 'refunded'",
        "runaway": "SELECT count(*) FROM generate_series(1, 1e12)",
    }
    for label, sql in candidates.items():
        print(f"{label}: {run_sql(sql)}")
finally:
    sandbox.delete()
    print(f"deleted {sandbox.id}")
```

```text
sb_6cwh96wicm ready with Postgres in 2.6s
correct: {"ok": true, "columns": ["country", "revenue"], "rows": ["DE,1076.00", "US,567.00", "FR,29.00"]}
wrong column: {"ok": false, "error": "ERROR:  column o.total does not exist\nLINE 1: SELECT c.country, sum(o.total) FROM orders o JOIN customers ...\n                              ^"}
write: {"ok": false, "error": "ERROR:  cannot execute DELETE in a read-only transaction"}
write, read-only switched off: {"ok": false, "error": "ERROR:  permission denied for table orders"}
runaway: {"ok": false, "error": "ERROR:  canceling statement due to statement timeout"}
deleted sb_6cwh96wicm
```

In this single run (October 27, 2026), from `create` to Postgres accepting connections took 2.6 seconds. Each successful query took about 0.3 seconds; the runaway one stopped at the 5-second timeout. The correct answer matches the data: Germany has 1,076.00 in paid orders, and the refunded order 103 is left out.

A sandbox started from a named snapshot boots fresh from the saved disk. It doesn't restore memory, so Postgres isn't "still running": Docker starts the container again because of its restart policy, and the `pg_isready` loop waits for it. If you need to keep a session's database between turns instead, stop the sandbox and resume it later. A resume restores memory and the Postgres process with it when it lands on a machine with the same CPU type; if none is free within 2 minutes it boots from disk, so the data files are kept and the restart policy brings Postgres back (see [snapshots](/docs/snapshots#resume)).

## Why two layers of read-only

| Attempt | What stops it |
| - | - |
| `DELETE`, `INSERT`, `DROP` | The transaction is read-only (`BEGIN TRANSACTION READ ONLY`, plus the role default) |
| `SET transaction_read_only = off; DELETE …` | `agent_ro` has only `SELECT` grants: `permission denied` |
| A query that never ends | `statement_timeout = '5s'` on the role |
| `SET statement_timeout = 0` first | The `exec` call's own `timeout_seconds=30` |
| Anything that escapes all of that | It only touches a copy in a throwaway VM; the next session starts from the snapshot |

A read-only transaction alone isn't enough, because any role can turn it off. The grants are what make writes impossible. The sandbox is what makes a mistake cheap.

## FAQ

### Can I use apt instead of Docker?

Yes. Each sandbox is a full Ubuntu 24.04 VM with passwordless `sudo`, so you can install Postgres with `apt` instead. We tested only the Docker route: the `postgres:16` image pins the version and starts in one command.

### Should the agent query my production database instead?

Test against a copy first. Load your real schema and a sample of rows into the sandbox, let the agent iterate there, and only run the final query against production, with your own read-only credentials. The sandbox never needs a production password.

### Why one sandbox per session and not one shared database?

A shared database collects whatever earlier sessions did, such as temporary tables or a superuser's test writes. Starting each session from the named snapshot gives every question the same data. Delete the sandbox when the session ends; billing is per second while it runs.

### What API key should the agent's backend use?

One that can create and delete sandboxes but not change your account, such as `sbx api-key create sql-agent --preset ci`. The model itself never sees a key: it only sees the `run_sql` tool.

### How do I update the seed data?

Start a sandbox from the snapshot, change the data as `postgres`, run `CHECKPOINT`, and save it again under the same name. Reusing a name replaces the snapshot.

## Related

- [Snapshots](/docs/snapshots)
- [Turn a sandbox into a reusable template](/guides/turn-a-sandbox-into-a-reusable-template)
- [Docker and KVM inside an agent sandbox](/guides/docker-and-kvm-inside-an-agent-sandbox)
- [Run OpenAI Agents SDK tools in a cloud VM](/guides/run-openai-agents-sdk-tools-in-a-cloud-vm)