psql

majiayu000's avatarfrom majiayu000

Execute SQL queries against the local PostgreSQL database. Accepts raw SQL or natural language queries that get converted to SQL automatically. Use for inspecting data, running queries, or managing the Kindle notes database.

⭐5stars🔀1forks📁View on GitHub🕐Updated Jan 11, 2026
[SQL and Databases]

When & Why to Use This Skill

The psql Claude skill enables seamless interaction with PostgreSQL databases by executing SQL queries and meta-commands directly through the CLI. It supports both raw SQL and natural language-to-SQL conversion, making it an essential tool for developers and data analysts to inspect schemas, manage records, and perform administrative tasks without needing a persistent connection.

Use Cases

  • Database Schema Exploration: Use meta-commands like \dt and \d to quickly list tables, describe column structures, and view indexes or foreign keys.
  • Natural Language Data Analysis: Transform plain English questions into executable SQL queries to perform data aggregations, filtering, and trend analysis.
  • Database Administration: Monitor system health by checking active connections, table sizes, and database configurations using administrative SQL queries.
  • Development and Migrations: Execute local .sql migration files or seed scripts to automate database setup and version control workflows.
  • Ad-hoc Data Inspection: Run rapid queries to verify data integrity, troubleshoot application issues, or manage specific datasets like Kindle notes databases.
namepsql
descriptionRun PostgreSQL queries and meta-commands via CLI
version1.0.0
licenseMIT
compatibilityopencode

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:

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

Command

Query

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

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:

# 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

# 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

# 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

# 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

# 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