Overview

CLI tool for running SQL queries and psql meta-commands against PostgreSQL databases. Each query is executed directly via the psql CLI - no persistent connection required.

Prerequisites

•bun runtime installed
•psql client installed
•PostgreSQL connection environment variables set in <git-root>/.env or exported

Environment Variables

Set these in your .env file or export them:

Variable	Required	Default	Description
`PGHOST`	Yes	-	Database host
`PGPORT`	No	`5432`	Database port
`PGDATABASE`	Yes	-	Database name
`PGUSER`	Yes	-	Database user
`PGPASSWORD`	Yes	-	Database password
`PGSSLMODE`	No	-	SSL mode (disable, require, etc.)

Example .env:

bash

PGHOST=localhost
PGPORT=5432
PGDATABASE=myapp
PGUSER=myapp
PGPASSWORD=secret
PGSSLMODE=disable

Command

Query

Run a SQL query, meta-command, or SQL file.

bash

bun .opencode/skill/psql/query.js <query> [options]
bun .opencode/skill/psql/query.js --file <path> [options]

Arguments:

•query - SQL query or meta-command to execute

Options:

•--file <path> - Execute SQL file instead of inline query
•--tuples - Tuples only output (no headers or row count)
•--timeout <ms> - Query timeout in milliseconds (default: 30000)
•--json - Wrap output in JSON
•--help - Show help

Examples:

bash

# SQL queries
bun .opencode/skill/psql/query.js "SELECT * FROM users LIMIT 5;"
bun .opencode/skill/psql/query.js "SELECT COUNT(*) FROM orders WHERE status = 'pending';"

# Meta-commands
bun .opencode/skill/psql/query.js "\dt"
bun .opencode/skill/psql/query.js "\d users"
bun .opencode/skill/psql/query.js "\di"
bun .opencode/skill/psql/query.js "\l"

# Execute SQL file
bun .opencode/skill/psql/query.js --file migrations/001_create_users.sql
bun .opencode/skill/psql/query.js --file scripts/seed_data.sql

# Tuples only (for scripting/parsing)
bun .opencode/skill/psql/query.js "SELECT id FROM users;" --tuples

# With longer timeout for slow queries
bun .opencode/skill/psql/query.js "SELECT * FROM large_table;" --timeout 60000

Common Workflows

Explore Database Schema

bash

# List all tables
bun .opencode/skill/psql/query.js "\dt"

# Describe a specific table
bun .opencode/skill/psql/query.js "\d users"

# Show indexes
bun .opencode/skill/psql/query.js "\di"

# Show foreign keys for a table
bun .opencode/skill/psql/query.js "\d+ orders"

Run Analytical Queries

bash

# Count records
bun .opencode/skill/psql/query.js "SELECT COUNT(*) FROM orders;"

# Group by aggregation
bun .opencode/skill/psql/query.js "SELECT status, COUNT(*) FROM orders GROUP BY status;"

# Recent activity
bun .opencode/skill/psql/query.js "SELECT * FROM orders WHERE created_at > NOW() - INTERVAL '1 day' ORDER BY created_at DESC LIMIT 10;"

Database Administration

bash

# Check table sizes
bun .opencode/skill/psql/query.js "SELECT relname, pg_size_pretty(pg_total_relation_size(relid)) FROM pg_catalog.pg_statio_user_tables ORDER BY pg_total_relation_size(relid) DESC LIMIT 10;"

# Check active connections
bun .opencode/skill/psql/query.js "SELECT count(*) FROM pg_stat_activity WHERE state = 'active';"

# List databases
bun .opencode/skill/psql/query.js "\l"

Run Migrations

bash

# Execute a migration file
bun .opencode/skill/psql/query.js --file migrations/001_create_users.sql

# Execute seed data
bun .opencode/skill/psql/query.js --file scripts/seed.sql

Output Behavior

•Query output is displayed directly to the user in the terminal
•Do not re-summarize or reformat query output - the user can already see it
•Use --tuples for clean output without headers (useful for piping to other tools)
•Use --json for structured output when parsing programmatically

Notes

•Each query is executed as a separate psql invocation (no persistent connection)
•Meta-commands (starting with \) work the same as SQL queries
•Long-running queries may need --timeout increased from the default 30 seconds
•The --tuples flag is useful when you need to parse output or pipe to other commands