> ## Documentation Index
> Fetch the complete documentation index at: https://datagen.dev/llms.txt
> Use this file to discover all available pages before exploring further.

# Query a Database from Code

> Query Postgres directly from agent or app code with a read-only role, PG* vault secrets and pandas

Published [data models](/data/overview) are the recommended way to give agents
data. When code needs to query a database directly -- ad-hoc SQL, a table that
is not modeled yet, a long-running app -- connect from the sandbox with a
**read-only** role. This guide covers the role, the `PG*` secrets that carry the
connection, the packages, and the query code.

It builds on two foundation pages:

* [Vault](/foundation/vault) -- how secrets become environment variables, and Shared vs Per-user.
* [Sandbox Environments](/foundation/sandbox-environments) -- how packages are baked into an image.

## Prerequisites

<Note>
  This guide is specific to **PostgreSQL** (local, Neon, RDS, or any
  Postgres-compatible database). The read-only role SQL, the `psycopg2` driver,
  and the connection URL format are all Postgres-specific -- other engines
  (MySQL, SQL Server, BigQuery, etc.) use different roles, drivers, and syntax.
</Note>

You will need:

* A **PostgreSQL** database and a superuser or owner login to create the role.
* Access to run SQL (via `psql`, `pgAdmin`, or your provider's SQL editor).
* An agent or app in DataGen whose code will run the queries.

<Note>
  Managed databases (Neon, RDS, Supabase) are already reachable over TLS, so no
  tunnel is needed -- any sandbox with outbound network connects directly. Tunnels
  are only relevant for exposing a database that lives on your own machine.
</Note>

***

## 1. Create a read-only user

Agents connect with a role that can only `SELECT` -- never write, drop, or alter.
This bounds the blast radius: a leaked credential exposes data at worst, it can
never mutate or destroy anything.

<Tabs>
  <Tab title="SQL (any Postgres)">
    Run this **as a superuser or the database owner**, connected to `<DB>`:

    ```sql create_readonly_role.sql theme={null}
    -- Create the read-only login role.
    CREATE ROLE <RO_USER> WITH LOGIN PASSWORD '<RO_PASSWORD>';

    -- Grant read-only access to the current tables.
    GRANT CONNECT ON DATABASE <DB> TO <RO_USER>;
    GRANT USAGE ON SCHEMA public TO <RO_USER>;
    GRANT SELECT ON ALL TABLES IN SCHEMA public TO <RO_USER>;

    -- Optional: also read tables created later (e.g. by future migrations).
    ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO <RO_USER>;
    ```

    <Tip>
      The last line is optional. `GRANT SELECT ON ALL TABLES` only covers tables
      that exist today; `ALTER DEFAULT PRIVILEGES` extends read access to tables
      created later. Run the script as the role that owns the tables.
    </Tip>

    Run the script:

    <CodeGroup>
      ```bash Local Docker theme={null}
      docker exec -i <POSTGRES_CONTAINER> \
        psql -U postgres -d <DB> < create_readonly_role.sql
      ```

      ```bash Cloud (psql) theme={null}
      psql "postgresql://<OWNER_ROLE>:<owner_pw>@<HOST>:<PORT>/<DB>?sslmode=require" \
        -f create_readonly_role.sql
      ```

      ```text Neon theme={null}
      Paste the SQL into Neon Console -> SQL Editor and run it.
      ```
    </CodeGroup>
  </Tab>

  <Tab title="pgAdmin">
    <Steps>
      <Step title="Create the login role">
        In the left tree, right-click **Login/Group Roles** (under the *server*,
        not the database) and choose **Create -> Login/Group Role...**

        * **General** tab -> *Name*: `<RO_USER>`
        * **Definition** tab -> *Password*: `<RO_PASSWORD>`
        * **Privileges** tab -> *Can login?* = **Yes** (leave the others off)
        * **Save**
      </Step>

      <Step title="Grant read access">
        Run the `GRANT` statements in the Query Tool: right-click your
        **`<DB>`** database -> **Query Tool**, then paste the grants from the
        other tab and execute.
      </Step>
    </Steps>
  </Tab>
</Tabs>

**Verify it is actually read-only:**

```bash theme={null}
# Reads succeed:
psql "<connection-url>" -c 'SELECT count(*) FROM "SomeTable";'
# Writes are refused:
psql "<connection-url>" -c 'CREATE TABLE nope (id int);'
#   -> ERROR: permission denied for schema public
```

***

## 2. Store the connection as PG\* secrets

Store the connection as separate **libpq `PG*` variables**. Postgres drivers read
these automatically, so your code never assembles or parses a URL, and passwords
with special characters need no encoding.

In the [vault](/foundation/vault) bound to your agent or app, add each row below
with **Add credential** -> **Secret**, setting **Target** to the variable name and
**Type** by scope:

| Target (env var) | Type | Example value |
| - | - | - |
| `PGHOST` | **Shared with workspace** | `10.10.0.15` (private IP or internal DNS) |
| `PGPORT` | **Shared with workspace** | `5432` |
| `PGDATABASE` | **Shared with workspace** | `analytics` |
| `PGSSLMODE` | **Shared with workspace** | `prefer` (use `require` if the server has TLS) |
| `PGUSER` | **Per-user** | `jsmith_ro` -- each member's own role |
| `PGPASSWORD` | **Per-user** | each member's own password |

Host, port, database and sslmode are the same for everyone, so an admin sets
them once as **Shared**. User and password belong to each member, so they are
**Per-user**: each member clicks **Connect** on the row and enters their own.

<Note>
  Use the libpq names exactly. DataGen uppercases the Target, so `pghost` also
  arrives as `PGHOST`, but a different name such as `PSQL_PASSWORD` is never read
  by libpq.
</Note>

The **host** is the value that changes with where the code runs:

| Where the code runs | `PGHOST` | `PGPORT` |
| - | - | - |
| Your machine -> local Docker Postgres | `localhost` | `5432` (published port; check `docker ps`) |
| A container on the same Docker network | `postgres` | `5432` |
| Company DB on your private network | private IP / internal DNS (e.g. `10.10.0.15`) | `5432` |
| Anywhere -> Neon / RDS / cloud | provider hostname (e.g. `ep-...neon.tech`) | provider port |

<Tip>
  For Neon, the connection details are under **Console -> your project ->
  Connection Details**. Use the **pooled** (`-pooler`) host for agent and sandbox
  workloads.
</Tip>

<Warning>
  **Reachability.** A private address like `10.10.0.15` only resolves from a sandbox
  on the **same network**. On-prem DataGen runs the sandbox inside your network, so
  it reaches the database directly -- no tunnel. From a cloud sandbox, the database
  must be reachable via firewall allowlist, VPC peering, or a tunnel.
</Warning>

<AccordionGroup>
  <Accordion title="Alternative: a single DATABASE_URL secret">
    Prefer one value? Add a single **Secret** with **Target** `DATABASE_URL` and the
    full URL as its value, assembled from the same pieces:

    ```text theme={null}
    postgresql://<RO_USER>:<RO_PASSWORD>@<HOST>:<PORT>/<DB>
    ```

    Managed databases add TLS parameters, for example Neon:

    ```text theme={null}
    postgresql://<RO_USER>:<RO_PASSWORD>@<HOST>/<DB>?sslmode=require&channel_binding=require
    ```

    Read it with `os.environ["DATABASE_URL"]`. Set **Type** to **Per-user** when
    the URL embeds a personal credential, otherwise **Shared with workspace**.

    <Warning>
      If the password contains URL-special characters (`@ : / ? # %`), URL-encode
      them -- `@` becomes `%40`. The `PG*` approach avoids this entirely because
      libpq takes each value literally.
    </Warning>
  </Accordion>
</AccordionGroup>

***

## 3. Bake the query packages

Give the sandbox `pandas`, `sqlalchemy` and `psycopg2-binary` through a
[Sandbox Environment](/foundation/sandbox-environments). The built-in **Data
Engineering** template already includes them (plus `duckdb`, `polars` and
`pyarrow`). Create an environment from it, wait for the **ready** badge, then
select it on your agent or app.

| Manager | Packages | Why |
| - | - | - |
| `pip` | `pandas`, `sqlalchemy`, `psycopg2-binary` | the query stack -- `libpq` is bundled in the `-binary` wheel |
| `apt` | `libpq-dev` | only needed if you switch to non-binary `psycopg2` |

<Note>
  **Alpine / musl base:** `psycopg2-binary` ships only glibc wheels. On a musl
  image, use `psycopg[binary]` (psycopg 3 ships musl wheels) and connect with a
  `postgresql+psycopg://` URL instead of `postgresql+psycopg2://`.
</Note>

***

## 4. Query it

```python theme={null}
import pandas as pd
from sqlalchemy import create_engine

# Connection details come from the PG* env vars set in the vault -- nothing hardcoded.
# (For the single-secret setup instead, use create_engine(os.environ["DATABASE_URL"]).)
engine = create_engine(
    "postgresql+psycopg2://",
    pool_pre_ping=True,   # keeps pooled connections healthy across idle gaps
)

df = pd.read_sql('SELECT * FROM "SomeTable" LIMIT 100;', engine)
print(df.shape)

engine.dispose()   # release the connection pool when done
```

### Quick end-to-end check

```python theme={null}
import psycopg2
with psycopg2.connect(connect_timeout=15) as c:   # PGHOST/PGUSER/PGPASSWORD from env
    cur = c.cursor()
    cur.execute("SELECT current_user, current_database();")
    print(cur.fetchone())   # confirms WHO you are and WHERE -- catches wrong-role bugs
```

***

## Troubleshooting

<AccordionGroup>
  <Accordion title="Run fails with &#x22;Vault not ready&#x22;">
    A member the run runs as has not filled in their Per-user `PGUSER` /
    `PGPASSWORD`. They click **Connect** on each row in the vault. See
    [Vault gotchas](/foundation/vault#gotchas).
  </Accordion>

  <Accordion title="ModuleNotFoundError: sqlalchemy / psycopg2">
    The agent or app is not bound to an environment with the query packages, or
    the environment is still building. Check the badge on
    [Sandbox Environments](/foundation/sandbox-environments).
  </Accordion>

  <Accordion title="Why pool_pre_ping=True">
    A pooled connection can go stale between queries -- the server's idle timeout
    drops it, the DB restarts, or a serverless backend (Neon) suspends.
    `pool_pre_ping` sends a cheap `SELECT 1` before reusing a connection, turning a
    stale-connection crash into a transparent reconnect. Cheap insurance for any
    long-running or intermittently-idle sandbox.
  </Accordion>

  <Accordion title="First query is slow (1-3 seconds)">
    The first connection after a Neon database has been idle triggers a cold start
    while the compute wakes up. Set a generous `connect_timeout` -- it is not a
    network fault, and the next query will be fast.
  </Accordion>

  <Accordion title="Table &#x22;does not exist&#x22; errors">
    Quote PascalCase table names in raw SQL: `'SELECT * FROM "Workspace"'`.
    Unquoted, Postgres lowercases the identifier and reports the table does not
    exist.
  </Accordion>

  <Accordion title="Connection times out">
    The sandbox cannot reach the host. A private IP only works from a sandbox on
    the same network; from a cloud sandbox, allowlist the database or use a
    tunnel or peering.
  </Accordion>
</AccordionGroup>

***

## Security checklist

* The sandbox connects with the **read-only** role, never the owner.
* The connection lives in **vault secrets** (`PG*` or `DATABASE_URL`), never in code or git.
* `ALTER DEFAULT PRIVILEGES` is in place so future tables stay readable.
* (Postgres 14 and older) `CREATE` is revoked from `PUBLIC` so the role cannot write.
* Cloud databases use `sslmode=require` (add `channel_binding=require` where supported).
* [Rotate](/foundation/vault#rotate-update-and-delete) the password if it was ever pasted into a chat, log, or ticket.

## Next

<CardGroup cols={2}>
  <Card title="Files" icon="folder" href="/files/overview">
    Upload documents and spreadsheets your agents can read.
  </Card>

  <Card title="Use data in agents" icon="robot" href="/data/use">
    Attach published data models to agents and apps instead of raw SQL.
  </Card>
</CardGroup>


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.