postgres-query · civitai — Claude Code skill: what it does, how to install
postgres-query — a skill for Claude Code that executes PostgreSQL queries during debugging and performance analysis. It targets specific database environments with --prod or --dev flags under read-only mode.
Apache-2.0 105 installs ★ 7,327 updated 2026-10-25
npx skills add civitai/civitai --skill postgres-query -a claude-codeWhat postgres-query does
Runs SQL commands through a bundled Node.js script query.mjs using read-only connections. It accepts explicit target flags such as --prod, --dev, --data-packet, and --notifications. Specifying multiple targets triggers an error, and unrecognized options are rejected immediately.
Every query prints the resolved host, port, and origin environment variable to stderr before executing. Dev connections require an active SSH tunnel to internal hosts. Target URLs are loaded from .claude/skills/postgres-query/.env, and any non-dev target exits if a connection string originates from the root project .env.
Available options include --explain for running EXPLAIN ANALYZE, --json for machine-readable output, and -f to read queries from disk. Write operations demand --writable, though read-only targets reject writable flags outright.
What it helps with
- run
EXPLAIN ANALYZEdirectly against PostgreSQL databases - inspect production replica records without risking accidental writes
- execute DDL and schema modifications on dev using
--writable - run complex queries directly from local
.sqlfiles
Example prompts for the agent
- Run EXPLAIN ANALYZE on production:
node .claude/skills/postgres-query/query.mjs --prod --explain "SELECT * FROM \"User\" LIMIT 5" - Execute SQL from a local file against dev:
node .claude/skills/postgres-query/query.mjs --dev -f my-query.sql - Query records in JSON format with a custom timeout:
node .claude/skills/postgres-query/query.mjs --prod --json -t 60 "SELECT id FROM \"Model\" LIMIT 10"
How to install postgres-query
1Skills CLI (Vercel)
npx skills add civitai/civitai --skill postgres-query -a claude-code2Manually: put the files in place
~/.claude/skills/postgres-query/SKILL.mdCopy the whole skill folder; for one project use .claude/skills/postgres-query/
1Skills CLI (Vercel)
npx skills add civitai/civitai --skill postgres-query -a codex2Manually: put the files in place
.agents/skills/postgres-query/SKILL.mdFor all projects use ~/.agents/skills/
1Skills CLI (Vercel)
npx skills add civitai/civitai --skill postgres-query -a cursor2Manually: put the files in place
.cursor/skills/postgres-query/SKILL.mdCursor also reads .agents/skills/ and .claude/skills/
1GitHub CLI
gh skill install civitai/civitai .claude/skills/postgres-query2Manually: put the files in place
.github/skills/postgres-query/SKILL.md1Skills CLI (Vercel)
npx skills add civitai/civitai --skill postgres-query -a gemini-cli2Platform CLI
gemini skills install https://github.com/civitai/civitai --path .claude/skills/postgres-queryManually: put the files in place
.devin/skills/postgres-query/SKILL.mdThe legacy .windsurf/skills/ path still works
Skills CLI (Vercel)
npx skills add civitai/civitai --skill postgres-query -a opencodeClaude.ai
Zip the folder and upload it in Settings → Capabilities → Skills
Step by step, with commands for each platform: How to install skills in Claude Code: five ways
Questions about postgres-query
How to install postgres-query in Claude Code?
Run npx skills add civitai/civitai --skill postgres-query -a claude-code.
What does postgres-query do?
It routes SQL queries through query.mjs, targeting specific PostgreSQL replicas or primary databases while verifying environment isolation.
What is required to run postgres-query?
You need Node.js, valid connection strings in .claude/skills/postgres-query/.env, and an active SSH tunnel for the --dev target.
Source text
SKILL.md · Apache-2.0
--- 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**: ```bash 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. Two checks make the wrong database fail loudly instead of answering: - **Every target except `--dev` refuses a URL that came from the root `.env`.** A checkout with no skill `.env` exits 1 and names the file it would have read. A URL set in the process environment is still accepted: that is a deliberate choice. - **`--prod` (read-only) checks that it reached a hot standby.** After connecting it prints `Server: <addr>, in recovery: <bool>` and exits 1 if the server is not in recovery, since a primary or a local copy is not the replica. ### 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 ```bash # 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" ``` ## 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 ```bash # 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 .claude/skills/postgres-query/query.mjs --dev --writable "UPDATE ..." ``` `--dev` uses `DEV_DATABASE_URL` either way — unlike `--prod`, there is no separate replica credential, so `--writable` only lifts the client-side guard. Whether a write lands is decided by the role in that URL. **DDL does work through this credential** — verified 2026-08-17 on both dev and the prod primary as role `civitai`: `ALTER TABLE ... ADD COLUMN`, `ADD CONSTRAINT`, `CREATE INDEX` and a `CREATE TABLE`/`DROP TABLE` round trip all succeeded. Migrations can be applied with `--writable`. This section previously said the opposite — that a `ddl_command_end` event trigger reassigning object ownership made any `CREATE TABLE` roll back with `must be able to SET ROLE "<schema owner>"`. Nobody has observed that, and it is contradicted by the run above. It cost an escalation to an infra owner that was not needed. If you do hit an ownership error, record what you ran and correct this section again rather than restoring the blanket claim. ⚠️ **A multi-statement script needs `--writable` even when its first statement is harmless.** The client-side write guard checks every statement's leading keyword, so a migration opening with `SET lock_timeout` / `BEGIN` is still refused on account of the `ALTER`s underneath. That is deliberate. ## Querying the notifications-db The notifications database is not reachable directly — it needs an SSH tunnel to an internal host, and access has to be granted first. **Ask an infra owner for access and the connection recipe.** The bastion host, the forward target, and where the credentials live are deliberately not documented here, because this repository is public — see the Security section of `CLAUDE.md`. Once you have the tunnel open, set `NOTIFICATION_DB_REPLICA_URL` in `.claude/skills/postgres-query/.env` to point at your local forwarded port. ### Running queries ```bash # With the tunnel open in another terminal: node .claude/skills/postgres-query/query.mjs --notifications \ "SELECT count(*) FROM \"Notification\"" node .claude/skills/postgres-query/query.mjs --notifications --explain \ "SELECT * FROM \"UserNotification\" WHERE \"userId\" = 12345 ORDER BY \"createdAt\" DESC LIMIT 50" ``` ### Available tables (read-only) - `Notification` — canonical notifications - `UserNotification` — per-user fanout (largest table) - `PendingNotification` — processing queue (often empty) The role `notifications_readonly` only has `SELECT`. Writes are also rejected at the pooler level (replica routing). ## Safety Features 1. **Read-only by default**: `--prod` uses `DATABASE_REPLICA_URL` to prevent accidental writes 2. **Write protection**: Blocks INSERT/UPDATE/DELETE/DROP unless `--writable` flag is used 3. **Replica targets refuse `--writable`**: `--notifications` and `--data-packet` error on the flag, and their roles/poolers reject writes anyway 4. **Explicit permission required**: Before using `--writable`, you MUST ask the user for permission 5. **The target is printed on every run**: no result can be attributed to the wrong database by accident ⚠️ **The write guard is a typo-catcher, not a sandbox.** It matches the query's leading keyword (after stripping leading comments) and the `(INSERT|UPDATE|DELETE|…` inside a leading `WITH`. A write buried deeper than that gets through the client. What actually stops writes is the **role** you connect as — which is why `--prod` defaults to the read-only replica credential. Do not treat a missing `--writable` as proof a query cannot write. ## When to Use --writable Only use the `--writable` flag when: - The user explicitly requests write access - You need to test write operations - You're verifying transaction behavior **IMPORTANT**: Always ask the user for permission before running with `--writable`. ## Comparing Query Performance To compare two query approaches — same target on both runs, or the comparison is meaningless: ```bash # Run first approach node .claude/skills/postgres-query/query.mjs --prod --explain "SELECT ... (approach 1)" # Run second approach node .claude/skills/postgres-query/query.mjs --prod --explain "SELECT ... (approach 2)" # Compare actual results node .claude/skills/postgres-query/query.mjs --prod --json "SELECT ... (approach 1)" > /tmp/q1.json node .claude/skills/postgres-query/query.mjs --prod --json "SELECT ... (approach 2)" > /tmp/q2.json ``` ## Verifying Index Usage Run with `--explain` and look for: - **Good**: "Index Scan", "Bitmap Index Scan", "Index Only Scan" - **Bad**: "Seq Scan" on large tables (indicates missing or unused index) ```bash node .claude/skills/postgres-query/query.mjs --prod --explain "SELECT * FROM \"Account\" WHERE provider = 'discord'" ```
© civitai · Apache-2.0 · civitai/civitai
Similar skills
All skills for Data and databases →postgres-query by other authors comes first.