/postgres-query

name: postgres-query
description: Run PostgreSQL queries for testing, debugging, and performance analysis. Use when you need to query the database directly, run EXPLAIN ANALYZE, compare query results, or test SQL optimizations. Always pass a target — `--prod` or `--dev` — because prod and dev are different databases and the default is prod. Always uses read-only connections unless explicitly directed otherwise.

PostgreSQL Query Testing

Use this skill to run ad-hoc PostgreSQL queries for testing, debugging, and performance analysis.

Running Queries

Use the included query script, and **name the target**:

node .claude/skills/postgres-query/query.mjs --prod "SELECT * FROM \"User\" LIMIT 5"
node .claude/skills/postgres-query/query.mjs --dev  "SELECT * FROM \"User\" LIMIT 5"

Targets

Pick exactly one. Two target flags in one call is an error, not a silent precedence rule, and an unrecognised option (`--devv`, `--data_packet`) is rejected rather than ignored — a swallowed typo would fall through to the default target, which is production.

| Flag | Connection string | Use when | |------|-------------------|----------| | `--prod` (default) | `DATABASE_REPLICA_URL`, or `DATABASE_URL` with `--writable` | The production main database | | `--dev` | `DEV_DATABASE_URL` | The dev cnpg database; requires SSH tunnel | | `--data-packet` | `DATABASE_DATA_PACKET_URL` | The DataPacket replica (read-only) | | `--notifications` | `NOTIFICATION_DB_REPLICA_URL` | notifications-db (read-only); requires SSH tunnel |

Omitting the target still runs against prod, so old commands keep working — but the run prints `No target flag given, defaulted to --prod`. Pass the flag.

Every run prints which database answered

Before connecting, the script writes a line to **stderr** naming the target, the access mode, the `user@host:port/database` **pg itself resolved**, and the env var it came from:

Target: PROD (read-only) -> <user>@<host>:25061/civitai [DATABASE_REPLICA_URL, timeout 30s]

It prints under `--quiet` and `--json` too, and before every exit path that reaches a database — including a blocked write, so you can always see which database you just aimed a `DELETE` at. It is on stderr, so `--json` piped to a file is still clean JSON.

The host and port come from the client's own resolved connection parameters rather than a re-parse of the string, so the line cannot disagree with where the query actually went.

🔴 **Read that line before you trust a result.** This skill's `.env` and the app's root `.env` both define `DATABASE_URL`, and **they name different databases** — the skill's is production, the root one is the dev snapshot the dev server uses. The skill's `.env` wins: it is loaded first, and `loadEnv` only fills in keys that are not already **present** (an empty value counts as present, so a bare `DATABASE_REPLICA_URL=` left in this file does not hand that key to the root `.env` — the target falls back to this file's `DATABASE_URL` instead, and the banner says `read-only, but pointed at the primary`). So a bare `query.mjs` answers from production while your dev server answers from dev. Comparing a value read here against one read by the running app is comparing two different databases unless both lines say the same host and port. That cost an hour and produced a confidently wrong root cause on 2026-08-16: `limit: 8` from the app's DB and `limit: 40` from this skill's, reported as a config bug that did not exist.

Options

| Flag | Description | |------|-------------| | `--explain` | Run EXPLAIN ANALYZE on the query | | `--writable` | Use the primary connection instead of the read replica (requires user permission) | | `--timeout <s>`, `-t` | Query timeout in seconds (default: 30) | | `--file`, `-f` | Read query from a file | | `--json` | Output results as JSON | | `--quiet`, `-q` | Minimal output, only results |

`--writable` combined with `--data-packet` or `--notifications` is an error — both are read-only replicas, so the combination can only mean a mistake.

Examples

# Simple query
node .claude/skills/postgres-query/query.mjs --prod "SELECT id, username FROM \"User\" LIMIT 5"

# Same query against the dev database
node .claude/skills/postgres-query/query.mjs --dev "SELECT id, username FROM \"User\" LIMIT 5"

# Check query performance
node .claude/skills/postgres-query/query.mjs --prod --explain "SELECT * FROM \"Model\" WHERE id = 1"

# Override default 30s timeout for longer queries
node .claude/skills/postgres-query/query.mjs --prod --timeout 60 "SELECT ... (complex query)"

# Query the notifications-db
node .claude/skills/postgres-query/query.mjs --notifications "SELECT count(*) FROM \"Notification\""

# Query from file
node .claude/skills/postgres-query/query.mjs --dev -f my-query.sql

# JSON output for processing (banner goes to stderr, stdout stays valid JSON)
node .claude/skills/postgres-query/query.mjs --prod --json "SELECT id, username FROM \"User\" LIMIT 3"

There is a second skill with this name

`apps/event-engine/.claude/skills/postgres-query/` is a separate copy with its own `.env`, and it has **none** of the above — no target flag, no banner. Skills are directory-scoped, so work under `apps/event-engine` resolves to that one. Check which script you are invoking by path before trusting its output.

Querying the dev database (cnpg)

The dev database is not reachable directly — it needs an SSH tunnel to an internal host. **Ask an infra owner for the connection recipe**; the specifics are not documented here because this repository is public (see the Security section of `CLAUDE.md`).

Once the tunnel is up, set `DEV_DATABASE_URL` in `.claude/skills/postgres-query/.env` to point at your local forwarded port.

Running dev queries

# Read-only (default — writes are blocked client-side)
node .claude/skills/postgres-query/query.mjs --dev "SELECT count(*) FROM \"User\""

# Writable DML (needs user permission)
node .cla