afpsql CLI Reference

Agent-First PSQL - A PostgreSQL interface for AI agents: reliable, structured, explicit, and read-only by default.

afpsql gives agents a reliable PostgreSQL contract: structured stdout events, first-class SSH/container transports, explicit write permissions, stable pipe sessions, and machine-readable failures.

Interface Policy

default mode is canonical agent-first CLI
--mode psql is argument translation only; runtime output stays JSONL
stdout carries protocol events; stderr is not a protocol channel
native CLI and pipe mode default to read-only transactions; writes require permission
SSH/container transports keep afpsql local instead of running human psql across boundaries

Query Sources and Parameters

use --sql for inline SQL or --sql-file for a file
use repeatable --param N=value for positional binds
placeholder count is validated from prepared-statement metadata, not by SQL text scanning

Connection Sources

--dsn-secret for a PostgreSQL URI
--conninfo-secret for libpq-style conninfo
or discrete --host, --port, --user, --dbname, --password-secret
every *-secret flag has a *-secret-env partner that reads the value from a named environment variable
add --ssh user@server when PostgreSQL is reachable only from the server boundary
add --container TARGET when PostgreSQL is reachable only from inside a container boundary
use named container scope flags instead of raw driver option passthrough
use --container-driver docker|podman|nerdctl|compose|kubectl for the exec syntax
combine --ssh user@server --container TARGET for containers on an SSH host
agent-first environment fallbacks: AFPSQL_*
PostgreSQL environment fallbacks: PGHOST, PGPORT, PGUSER, PGDATABASE, PGPASSWORD, PGSSLMODE

Result Shaping

default mode buffers a bounded inline result
use --stream-rows for large result sets, with --batch-rows and --batch-bytes to tune chunk size
--output json|yaml|plain changes rendering only, not the runtime schema

Examples

afpsql --sql "select now() as now_rfc3339"
afpsql --sql-file ./query.sql
afpsql --sql 'select * from users where id = $1' --param 1=123
afpsql --dsn-secret-env DATABASE_URL --sql "select 1"
afpsql --ssh user@server --host 127.0.0.1 --port 5432 --user app --dbname appdb --sql "select 1"
afpsql --container pg-container --dsn-secret-env DATABASE_URL --sql "select 1"
afpsql --ssh root@server --container app --host host.container.internal --port 5432 --user app --dbname appdb --sql "select 1"
afpsql --mode psql -h 127.0.0.1 -p 5432 -U app -d appdb -c "select 1"
afpsql --sql "select * from big_table" --stream-rows --batch-rows 1000
afpsql --mode pipe
afpsql psql status
afpsql psql install
afpsql skill status
afpsql skill install

Exit Codes

0: query completed successfully
1: SQL error or runtime error
2: invalid CLI arguments

Usage: afpsql [OPTIONS] [COMMAND]

Commands:
  psql     Manage the local psql wrapper for afpsql --mode psql
  skill    Manage Agent-First PSQL skills for Codex, Claude Code, opencode, and Hermes
  inspect  Schema discovery: inspect databases, schemas, tables, indexes, or snapshots
  help     Print this message or the help of the given subcommand(s)

Options:
  -h, --help
          Print help. Add --recursive to expand every nested subcommand; add --output json|yaml|markdown to render this help in another format.

  -V, --version
          Print version

Query:
      --sql <SQL>
          Inline SQL string to execute

      --sql-file <SQL_FILE>
          Read SQL from a file

      --param <PARAM>
          Positional bind parameter in `N=value` form. Repeat for additional parameters

      --stream-rows
          Stream large result sets as `result_rows` batches instead of a single inline result

      --batch-rows <BATCH_ROWS>
          Maximum rows per streamed batch

      --batch-bytes <BATCH_BYTES>
          Soft byte target per streamed batch

      --statement-timeout-ms <STATEMENT_TIMEOUT_MS>
          Per-query statement timeout in milliseconds

      --lock-timeout-ms <LOCK_TIMEOUT_MS>
          Per-query lock timeout in milliseconds

      --inline-max-rows <INLINE_MAX_ROWS>
          Maximum inline rows before returning `result_too_large`

      --inline-max-bytes <INLINE_MAX_BYTES>
          Maximum inline payload bytes before returning `result_too_large`

      --permission <PERMISSION>
          Query permission. Defaults to read, ssh-read with --ssh, or container-read with --container

          [possible values: read, write, ssh-read, ssh-write, container-read, container-write]

      --dry-run
          Preview the query without executing it

      --explain
          Wrap the query in EXPLAIN (FORMAT JSON) and return the plan tree instead of executing the user's SQL

      --explain-analyze
          Wrap the query in EXPLAIN (ANALYZE, FORMAT JSON, BUFFERS). The underlying SQL actually runs; writes require the matching write permission

Connection:
      --dsn-secret <DSN_SECRET>
          PostgreSQL DSN URI. Redacted in structured output

      --dsn-secret-env <DSN_SECRET_ENV>
          Read PostgreSQL DSN URI from an environment variable

      --conninfo-secret <CONNINFO_SECRET>
          libpq-style conninfo string. Redacted in structured output

      --conninfo-secret-env <CONNINFO_SECRET_ENV>
          Read libpq-style conninfo string from an environment variable

      --host <HOST>
          PostgreSQL host

      --port <PORT>
          PostgreSQL port

      --user <USER>
          PostgreSQL user name

      --dbname <DBNAME>
          PostgreSQL database name

      --password-secret <PASSWORD_SECRET>
          PostgreSQL password. Redacted in structured output

      --password-secret-env <PASSWORD_SECRET_ENV>
          Read PostgreSQL password from an environment variable

SSH Transport:
      --ssh <SSH>
          Open an SSH transport to USER@HOST before connecting to PostgreSQL

      --ssh-via <SSH_VIA>
          SSH hop to reach before the final --ssh destination. Repeat for multiple hops

      --ssh-option <SSH_OPTIONS>
          Additional OpenSSH -o option. Repeat for multiple options

      --ssh-local-host <SSH_LOCAL_HOST>
          Local bind host for the SSH tunnel

      --ssh-local-port <SSH_LOCAL_PORT>
          Local bind port for the SSH tunnel. Defaults to an ephemeral port

      --ssh-remote-socket <SSH_REMOTE_SOCKET>
          Explicit remote PostgreSQL Unix socket path for SSH forwarding

      --ssh-sudo-user <SSH_SUDO_USER>
          Remote OS user for sudo -n Unix-socket bridge mode; requires an explicit socket

Container Transport:
      --container <CONTAINER>
          Run a container exec stdio bridge in TARGET before connecting to PostgreSQL

      --container-driver <CONTAINER_DRIVER>
          Container exec driver: docker, podman, nerdctl, compose, or kubectl

      --container-runtime <CONTAINER_RUNTIME>
          Runtime command for the selected container driver. Defaults to the driver command

      --container-user <CONTAINER_USER>
          OS user passed to drivers that support exec user selection

      --container-namespace <CONTAINER_NAMESPACE>
          Kubernetes namespace for kubectl exec

      --container-context <CONTAINER_CONTEXT>
          Docker or Kubernetes context for the selected driver

      --container-compose-file <CONTAINER_COMPOSE_FILES>
          Compose file passed before compose exec. Repeat for multiple files

      --container-compose-project <CONTAINER_COMPOSE_PROJECT>
          Compose project name passed before compose exec

      --container-pod-container <CONTAINER_POD_CONTAINER>
          Kubernetes container name for multi-container pods

Runtime:
  -o, --output <OUTPUT>
          Output format: json (default), yaml, or plain

          [default: json]

      --stdout-file <PATH>
          Redirect stdout bytes to this file

      --stderr-file <PATH>
          Redirect stderr bytes to this file

      --log <LOG>
          Diagnostic log categories (comma-separated). Categories: startup, connect, query, transport, mode; or an exact event name like `query.error`; or `all` for everything

      --mode <MODE>
          Runtime mode: canonical cli, pipe, or `psql` translation mode

          [default: cli]
          [possible values: cli, pipe, psql]

Agent-First PSQL psql - Manage the local psql wrapper for afpsql –mode psql

Usage: psql <COMMAND>

Commands:
  status     Show whether the afpsql-managed psql wrapper is installed and active
  install    Install an afpsql-managed psql wrapper
  uninstall  Remove an afpsql-managed psql wrapper
  help       Print this message or the help of the given subcommand(s)

Options:
  -h, --help
          Print help

Agent-First PSQL psql status - Show whether the afpsql-managed psql wrapper is installed and active

Usage: status [OPTIONS]

Options:
      --bin-dir <BIN_DIR>
          Directory that contains the psql wrapper. Defaults to the afpsql executable directory

  -h, --help
          Print help

Agent-First PSQL psql install - Install an afpsql-managed psql wrapper

Usage: install [OPTIONS]

Options:
      --bin-dir <BIN_DIR>
          Directory that contains the psql wrapper. Defaults to the afpsql executable directory

  -h, --help
          Print help

Agent-First PSQL psql uninstall - Remove an afpsql-managed psql wrapper

Usage: uninstall [OPTIONS]

Options:
      --bin-dir <BIN_DIR>
          Directory that contains the psql wrapper. Defaults to the afpsql executable directory

  -h, --help
          Print help

Agent-First PSQL skill - Manage Agent-First PSQL skills for Codex, Claude Code, opencode, and Hermes

Usage: skill <COMMAND>

Commands:
  status     Show whether the Agent-First PSQL skill is installed, valid, and up to date
  install    Install the Agent-First PSQL skill
  uninstall  Remove an afpsql-managed Agent-First PSQL skill
  help       Print this message or the help of the given subcommand(s)

Options:
  -h, --help
          Print help

Agent-First PSQL skill status - Show whether the Agent-First PSQL skill is installed, valid, and up to date

Usage: status [OPTIONS]

Options:
      --agent <AGENT>
          Agent to manage. Defaults to all personal skill targets

          Possible values:
          - all:         Manage every agent that supports the requested scope
          - codex:       Manage the Codex local skill under $CODEX_HOME/skills
          - claude-code: Manage the Claude Code skill under ~/.claude/skills or .claude/skills
          - opencode:    Manage the opencode skill under ~/.config/opencode/skills or .opencode/skills
          - hermes:      Manage the Hermes skill under $HERMES_HOME/skills or ~/.hermes/skills

          [default: all]

      --scope <SCOPE>
          Skill scope

          Possible values:
          - personal:  Install under the user-level skills directory
          - workspace: Install under the current workspace's skills directory

          [default: personal]

      --skills-dir <SKILLS_DIR>
          Directory that contains skill folders. Requires an explicit single --agent

  -h, --help
          Print help (see a summary with '-h')

Agent-First PSQL skill install - Install the Agent-First PSQL skill

Usage: install [OPTIONS]

Options:
      --agent <AGENT>
          Agent to manage. Defaults to all personal skill targets

          Possible values:
          - all:         Manage every agent that supports the requested scope
          - codex:       Manage the Codex local skill under $CODEX_HOME/skills
          - claude-code: Manage the Claude Code skill under ~/.claude/skills or .claude/skills
          - opencode:    Manage the opencode skill under ~/.config/opencode/skills or .opencode/skills
          - hermes:      Manage the Hermes skill under $HERMES_HOME/skills or ~/.hermes/skills

          [default: all]

      --scope <SCOPE>
          Skill scope

          Possible values:
          - personal:  Install under the user-level skills directory
          - workspace: Install under the current workspace's skills directory

          [default: personal]

      --skills-dir <SKILLS_DIR>
          Directory that contains skill folders. Requires an explicit single --agent

      --force
          Overwrite or remove an unmanaged Agent-First PSQL skill at the target path

  -h, --help
          Print help (see a summary with '-h')

Agent-First PSQL skill uninstall - Remove an afpsql-managed Agent-First PSQL skill

Usage: uninstall [OPTIONS]

Options:
      --agent <AGENT>
          Agent to manage. Defaults to all personal skill targets

          Possible values:
          - all:         Manage every agent that supports the requested scope
          - codex:       Manage the Codex local skill under $CODEX_HOME/skills
          - claude-code: Manage the Claude Code skill under ~/.claude/skills or .claude/skills
          - opencode:    Manage the opencode skill under ~/.config/opencode/skills or .opencode/skills
          - hermes:      Manage the Hermes skill under $HERMES_HOME/skills or ~/.hermes/skills

          [default: all]

      --scope <SCOPE>
          Skill scope

          Possible values:
          - personal:  Install under the user-level skills directory
          - workspace: Install under the current workspace's skills directory

          [default: personal]

      --skills-dir <SKILLS_DIR>
          Directory that contains skill folders. Requires an explicit single --agent

      --force
          Overwrite or remove an unmanaged Agent-First PSQL skill at the target path

  -h, --help
          Print help (see a summary with '-h')

Agent-First PSQL inspect - Schema discovery: inspect databases, schemas, tables, indexes, or snapshots

Usage: inspect <COMMAND>

Commands:
  databases  List databases on the connected server with size, encoding, and connection facts
  database   Summarize the connected database: schema/table/view/sequence counts and size
  schemas    List user-visible schemas
  schema     Export full schema metadata for one schema
  snapshot   Export a stable full-schema snapshot for machine consumption
  tables     List tables in a schema with owner, estimated rows, and size
  views      List views (regular and materialized) in a schema with owner
  indexes    List indexes with definitions, size, validity, and optional usage stats
  table      Describe a table's columns: types, nullability, defaults, primary key, comments
  help       Print this message or the help of the given subcommand(s)

Options:
  -h, --help
          Print help

Agent-First PSQL inspect databases - List databases on the connected server with size, encoding, and connection facts

Usage: databases [OPTIONS]

Options:
      --all
          Include template databases (template0/template1) in the listing

  -h, --help
          Print help

Agent-First PSQL inspect database - Summarize the connected database: schema/table/view/sequence counts and size

Usage: database

Options:
  -h, --help
          Print help

Agent-First PSQL inspect schemas - List user-visible schemas

Usage: schemas

Options:
  -h, --help
          Print help

Agent-First PSQL inspect schema - Export full schema metadata for one schema

Usage: schema [OPTIONS]

Options:
      --schema <SCHEMA>
          Schema to inspect. Defaults to `public`

          [default: public]

      --like <LIKE>
          Optional `LIKE` pattern matched against relation names (use `%` as wildcard)

  -h, --help
          Print help

Agent-First PSQL inspect snapshot - Export a stable full-schema snapshot for machine consumption

Usage: snapshot [OPTIONS]

Options:
      --schema <SCHEMA>
          Schema to inspect. Defaults to `public`

          [default: public]

      --like <LIKE>
          Optional `LIKE` pattern matched against relation names (use `%` as wildcard)

  -h, --help
          Print help

Agent-First PSQL inspect tables - List tables in a schema with owner, estimated rows, and size

Usage: tables [OPTIONS]

Options:
      --schema <SCHEMA>
          Schema to filter on. Defaults to `public`

          [default: public]

      --like <LIKE>
          Optional `LIKE` pattern matched against the table name (use `%` as wildcard)

  -h, --help
          Print help

Agent-First PSQL inspect views - List views (regular and materialized) in a schema with owner

Usage: views [OPTIONS]

Options:
      --schema <SCHEMA>
          Schema to filter on. Defaults to `public`

          [default: public]

      --like <LIKE>
          Optional `LIKE` pattern matched against the view name (use `%` as wildcard)

  -h, --help
          Print help

Agent-First PSQL inspect indexes - List indexes with definitions, size, validity, and optional usage stats

Usage: indexes [OPTIONS]

Options:
      --schema <SCHEMA>
          Schema to filter on. Defaults to `public`

          [default: public]

      --table <TABLE>
          Optional table name to filter on. Accepts `schema.table` to override --schema

      --stats
          Include PostgreSQL's built-in pg_stat_user_indexes usage counters

  -h, --help
          Print help

Agent-First PSQL inspect table - Describe a table’s columns: types, nullability, defaults, primary key, comments

Usage: table [OPTIONS] <NAME>

Arguments:
  <NAME>
          Table name. Accepts `schema.table`; defaults to `public.NAME` when unqualified

Options:
      --full
          Include relation, constraints, indexes, triggers, and sequence/default metadata

  -h, --help
          Print help