--- name: psql description: Run PostgreSQL queries and meta-commands via CLI version: 1.0.0 license: MIT compatibility: opencode --- ## 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](https://bun.sh) runtime installed - [psql](https://www.postgresql.org/docs/current/app-psql.html) client installed - PostgreSQL connection environment variables set in `/.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 [options] bun .opencode/skill/psql/query.js --file [options] ``` **Arguments:** - `query` - SQL query or meta-command to execute **Options:** - `--file ` - Execute SQL file instead of inline query - `--tuples` - Tuples only output (no headers or row count) - `--timeout ` - 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 --- Source attribution: aiskillstore Original project: https://github.com/aiskillstore/marketplace/blob/main/skills/bind/psql/SKILL.md License: MIT License information: https://opensource.org/license/mit Original upstream instructions; downloaded from Dave AI. ## Upstream license notice MIT License Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated documentation files (the "Software"), to deal in the Software without restriction, including without limitation the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, and to permit persons to whom the Software is furnished to do so, subject to the following conditions: The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software. THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE. # Dave AI resource metadata Creator / publisher: aiskillstore Original project: https://github.com/aiskillstore/marketplace/blob/main/skills/bind/psql/SKILL.md Attribution basis: Source repository record Fields of work: Data & Analytics, Development & Engineering, Operations Roles: Backend Developer, Business Process Analyst, Data Analyst, Data Scientist, Database Administrator Specialties: API, Database Tools: PostgreSQL Country applicability: Country not specified Classification basis: Source catalog labels Instruction SHA-256: 46ef472f53a6d70bbd4bde218642370ac4a4d89d54d93037c0f95f8150543291