Overview

afpsql is a PostgreSQL reliability contract for AI agents.

It is not trying to be an interactive psql clone, an ORM, a database UI, or a high-throughput connection pooler. It gives agents a predictable way to run SQL: structured events on stdout, explicit permission boundaries, stable session state when requested, and machine-readable failures.

Supported platforms: macOS, Linux, Windows.

For agent behavior rules, see the Agent Skill. For exact fields, see the Protocol Reference.

The contract: stdout events, SQLSTATE, and explicit write boundaries

Agents can depend on these semantics:

Connection reuse exists to make session state reliable for agents. It is not a promise of pooler-level throughput or workload balancing.

Install it where the agent runs, not on every database server

Install afpsql on the machine where the agent runs. The database server or container does not need afpsql installed; use SSH or container transport when PostgreSQL is reachable only from that boundary.

brew install agentfirstkit/tap/afpsql   # macOS/Linux
scoop bucket add agentfirstkit https://github.com/agentfirstkit/scoop-bucket && scoop install afpsql  # Windows
cargo install agent-first-psql          # any platform

Install or load the Agent Skill so the agent keeps choosing structured database access instead of human-oriented psql:

afpsql skill status
afpsql skill install
afpsql skill status

The default skill target installs personal skills for Codex and Claude Code. Use afpsql skill install --agent claude-code --scope workspace when a Claude Code skill should live in the current repository under .claude/skills.

Suggested agent instruction:

Use local afpsql for non-interactive PostgreSQL work. Prefer read-only queries. Ask before writes and use explicit permission. Use afpsql --ssh user@server when PostgreSQL is only reachable from the server itself, and afpsql --container CONTAINER when PostgreSQL is only reachable from inside a container. For containers on a remote SSH host, combine --ssh user@server with --container CONTAINER. Do not SSH or run container exec commands just to run human psql unless I ask for that. Do not run afpsql --help as routine preflight before known query forms.

Choose the mode by the reliability property you need

Native CLI: one agent action

Use native CLI mode for a single query or command. Output is one structured event or a structured error.

afpsql --dsn-secret-env DATABASE_URL \
  --sql 'select id, status from jobs where id = $1' \
  --param 1=123

Pipe: long agent session

Use pipe mode when an agent needs multiple ordered operations, cancellation, streaming, or PostgreSQL session state such as temp tables or set local/GUC behavior.

afpsql --mode pipe --dsn-secret-env DATABASE_URL

Each input line is one JSON object. Queries in the same session are queued FIFO. Different sessions are isolated.

{"code":"query","id":"q1","session":"work","sql":"select current_database() as db"}

Pipe mode also accepts begin/commit/rollback for explicit multi-statement transactions. While a transaction is open, queries bypass the implicit BEGIN..COMMIT wrap; per-query failures are isolated by a savepoint so a single bad statement does not abort the whole tx. See docs/reference.md for the input shape.

Schema discovery and plan inspection

Run afpsql inspect <databases|database|schemas|schema|snapshot|tables|views|indexes|table> instead of writing information_schema / pg_catalog queries by hand. Use inspect schema or inspect snapshot for a full schema metadata export, and inspect indexes --stats for built-in pg_stat_user_indexes counters plus index size/definition metadata. inspect databases returns size, encoding, collate/ctype, and connection facts per database (--all includes templates); inspect database summarizes the connected database’s object counts and total size. Wrap any query in --explain (EXPLAIN (FORMAT JSON)) or --explain-analyze (also runs the statement) to receive the plan tree as a normal code:"result" row.

Pre-flight a query with --dry-run: afpsql opens a connection, runs PREPARE inside a transaction that is rolled back, and emits a dry_run event carrying the inferred param_types and output columns — no rows are scanned and no side effects occur.

psql compatibility: scripts only

Use --mode psql or the managed wrapper for non-interactive scripts that already call psql -c, psql -f, or psql -l.

afpsql --mode psql -h 127.0.0.1 -p 5432 -U app -d appdb -c "select 1"

psql mode is only argument translation into the same structured runtime. It preserves psql’s writable default for script compatibility and intentionally does not expose native afpsql permission flags. Prefer native afpsql mode for transport-specific agent work.

Out of scope for psql mode:

Permission is the write boundary

Native CLI and pipe mode are read-only by default:

TransportDefaultWrite permission
direct PostgreSQL connectionreadwrite
afpsql SSH transportssh-readssh-write
afpsql container transportcontainer-readcontainer-write

read, ssh-read, and container-read run the SQL inside a PostgreSQL read-only transaction. Writes fail with SQLSTATE 25006 unless the agent explicitly requests the right write permission.

Direct write:

afpsql --permission write \
  --sql 'update jobs set checked_at = now() where id = $1' \
  --param 1=123

Pipe write:

{"code":"query","id":"q1","sql":"update jobs set checked_at = now() where id = $1","params":[123],"options":{"permission":"write"}}

SSH write:

afpsql --permission ssh-write --ssh user@server \
  --host 127.0.0.1 --port 5432 \
  --user app --dbname appdb \
  --password-secret-env PGPASSWORD \
  --sql 'update jobs set checked_at = now() where id = $1' \
  --param 1=123

Container write:

afpsql --permission container-write --container pg-container \
  --dsn-secret-env DATABASE_URL \
  --sql 'update jobs set checked_at = now() where id = $1' \
  --param 1=123

Permission mismatches are rejected before execution with an invalid_request error and a corrective hint. For example, --permission write --ssh ... tells the agent to use ssh-read or ssh-write; --permission ssh-write without SSH tells the agent to use read or write; container sessions similarly require container-read or container-write.

Parameters are data, not SQL text

Dynamic values should be bound with $1..$N placeholders and params / --param. Do not concatenate values into SQL text.

afpsql --sql 'select * from users where id = $1 and status = $2' \
  --param 1=123 \
  --param 2=active

Prepared-statement metadata validates parameter count and local binding shape. Client-side parameter shape or local binding conversion failures return invalid_params; PostgreSQL server conversion and execution failures remain sql_error events with the original SQLSTATE.

Unsupported by design:

In psql compatibility mode, numeric -v N=value entries can be translated into positional parameters. Non-numeric interpolation variables are not supported.

Output is a protocol, not terminal formatting

Common output events:

Connection-stage PostgreSQL rejections use code:"error" with error_code:"connect_failed" and, when PostgreSQL provides them, sqlstate, message, detail, and an actionable hint. Agents can tell a missing role from a password failure, missing database, server startup state, or connection capacity problem without parsing terminal prose.

Large result handling is explicit. By default, small results are returned inline. Use streaming when the agent expects a large result set:

afpsql --sql "select * from big_table" --stream-rows --batch-rows 1000

If streaming is off and inline limits are exceeded, afpsql soft-truncates the result: the agent receives a code:"result" event with the first N rows and truncated:true (plus the cap that fired). The underlying SQL still executed in full; only the projection returned to the agent is capped. Either narrow the query with WHERE/LIMIT or switch to --stream-rows to see everything.

SSH transport makes the remote boundary explicit

Use --ssh when PostgreSQL is reachable from the server but not directly from the agent machine. afpsql stays local, starts OpenSSH, connects through the forwarded path, and tears down the transport with the process/session.

Remote TCP PostgreSQL is the preferred path when the server can run a normal password-authenticated local connection:

export PGPASSWORD='...'
afpsql --ssh user@server \
  --host 127.0.0.1 --port 5432 \
  --user app --dbname appdb \
  --password-secret-env PGPASSWORD \
  --sql "select now()"

For bastion or two-hop access, keep the final PostgreSQL host in --ssh and pass the jump host through OpenSSH, for example --ssh-option ProxyJump=bastion; do not replace afpsql transport with an external tunnel unless the user explicitly provides one.

Remote Unix socket without sudo, useful when the SSH login user can peer-auth to PostgreSQL:

afpsql --ssh user@server \
  --host /var/run/postgresql \
  --user user --dbname appdb \
  --sql "select current_user"

If the only working manual command is sudo -u postgres psql, prefer changing PostgreSQL roles/authentication over using sudo from an agent. When necessary, afpsql has an explicit, non-interactive sudo bridge:

afpsql --ssh user@server \
  --ssh-sudo-user postgres \
  --ssh-remote-socket /path/to/.s.PGSQL.5432 \
  --user postgres --dbname postgres \
  --sql "select current_user"

This bridge uses sudo -n and fails instead of prompting. It requires an exact socket path or a socket directory in --host/PGHOST; afpsql does not guess socket locations.

Supported SSH options:

SSH transport expects discrete connection fields. Prefer --host/--port/--user/--dbname/--password-secret-env over --dsn-secret or --conninfo-secret when --ssh is active.

Container transport reaches container-local PostgreSQL

Use --container TARGET when PostgreSQL listens only from inside a container. This replaces docker exec CONTAINER psql ... and similar Podman, nerdctl, Compose, or Kubernetes exec calls for agent work. afpsql stays local, starts a no-TTY exec bridge through the selected driver, and runs a small stdio bridge in the container; the container does not need afpsql or psql.

Container-local TCP:

afpsql --container pg-container \
  --dsn-secret 'postgresql://app:pw@127.0.0.1:5432/appdb' \
  --sql "select now()"

Container-local Unix socket:

afpsql --container pg-container \
  --host /var/run/postgresql --port 5432 \
  --user app --dbname appdb \
  --sql "select current_user"

If the socket uses peer auth, add --container-user OSUSER so the container OS user matches the requested PostgreSQL role.

Supported container options:

Use --container-runtime docker-compose with --container-driver compose for Compose v1. The default Compose driver form is Docker Compose v2 (docker compose exec).

For Kubernetes pods with more than one container, select the pod as --container POD and the inner container with --container-pod-container CTR; afpsql emits kubectl exec POD -c CTR -i -- ....

Container bridge prerequisites: the target container must provide sh plus one of python3, python, or perl. For distroless or scratch containers, attach a small sidecar with a supported interpreter or connect through PostgreSQL’s host network path instead.

The PostgreSQL host/port or socket path is interpreted inside the container. When --container is used alone, the container exec command runs locally.

For containers on a remote SSH host, combine --ssh and --container. This is one local afpsql transport chain, not “SSH in, then run psql.” It runs the container exec command on the SSH host, then bridges from inside the container:

afpsql --ssh root@server --container app-container \
  --container-driver docker \
  --host postgres --port 5432 \
  --user app --dbname appdb \
  --password-secret-env PGPASSWORD \
  --sql "select 1"

Use host.docker.internal only when the remote Docker environment provides it (Docker Desktop, or Linux configured with host-gateway).

The permission family is still container: reads default to container-read, and writes require --permission container-write.

Connection inputs keep secrets out of shell history

Canonical connection fields:

CLI secret values can be read from environment variables so they do not appear in shell history:

afpsql --dsn-secret-env DATABASE_URL --sql "select 1"
afpsql --password-secret-env PGPASSWORD --host localhost --sql "select 1"

Environment fallback also reads standard PostgreSQL variables:

Managed psql wrapper is script compatibility, not a new runtime

Install the wrapper only when existing non-interactive scripts should call psql and receive structured afpsql output:

afpsql psql status
afpsql psql install
afpsql psql status

Use --bin-dir for a custom location:

afpsql psql install --bin-dir ~/.local/bin
export PATH="$HOME/.local/bin:$PATH"
afpsql psql status --bin-dir ~/.local/bin

Check active_in_path: true. The wrapper is managed only when it contains the afpsql marker; unmanaged system psql binaries are not overwritten.

Non-goals: not psql, not an ORM, not a pooler

afpsql deliberately does not provide:

License

MIT