PostgreSQL

A CLI tool for exploring and debugging PostgreSQL databases with JSON-first output designed for AI agents.

CLI Discovery

The CLI is located at ./scripts/pgtool-cli/ relative to this SKILL.md file.

Platform Script

Unix/Linux/macOS pgtool

Windows pgtool.ps1

Claude Code: Use ${CLAUDE_PLUGIN_ROOT}/skills/pgtool/scripts/pgtool-cli/pgtool (or pgtool.ps1 on Windows).

For setup instructions, see SETUP.md in this directory.

Important

• Always use pgtool-cli for all database operations. Do NOT use psql directly.

• If pgtool-cli encounters an error or limitation, report the issue to the user and stop. Do not fall back to psql or other tools.

• Always add LIMIT to SELECT queries to avoid fetching excessive data.

Commands

List Schemas

pgtool schemas

Output: {"ok":true,"schemas":[{"name":"public","owner":"postgres"}]}

List Tables

Tables in default schema

pgtool tables

Tables in a specific schema

pgtool tables auth

Output: {"ok":true,"schema":"public","tables":[{"name":"users","type":"table","rowEstimate":1000,"sizeHuman":"256 KB"}]}

Describe Table

Get column details with primary key and foreign key information.

Table in default schema

pgtool describe users

Table in specific schema

pgtool describe auth.users

Output includes column types, nullability, defaults, PK/FK info, and foreign key references.

List Indexes

pgtool indexes users

Output: {"ok":true,"indexes":[{"name":"users_pkey","unique":true,"primary":true,"columns":["id"],"type":"btree"}]}

List Constraints

pgtool constraints users

Output includes PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK, and EXCLUDE constraints.

List Relationships

Get all foreign key relationships in a schema.

pgtool relationships pgtool relationships auth

Output: {"ok":true,"relationships":[{"fromTable":"orders","fromColumns":["user_id"],"toTable":"users","toColumns":["id"]}]}

Execute Query

pgtool query "SELECT * FROM users WHERE active = true LIMIT 100"

Output: {"ok":true,"rows":[...],"rowCount":5,"fields":["id","name","email"]}

Best Practices:

• Always add LIMIT to SELECT queries to avoid fetching excessive data

• DML statements (INSERT, UPDATE, DELETE) with RETURNING are fully supported

• Use parameterized values in WHERE clauses to avoid SQL injection

Sample Table Rows

Get sample data from a table quickly.

Default: 5 rows

pgtool sample users

Custom limit

pgtool sample users --limit 10

Specific schema

pgtool sample auth.users

Output: {"ok":true,"schema":"public","table":"users","rows":[...],"rowCount":5,"columns":["id","name","email"]}

Count Table Rows

Get exact row count (not estimate).

pgtool count users pgtool count auth.sessions

Output: {"ok":true,"schema":"public","table":"users","count":12345}

Plain: public.users: 12,345 rows

Search Tables and Columns

Find tables and columns matching a pattern.

pgtool search email pgtool search user

Output:

{ "ok": true, "pattern": "email", "matches": { "tables": [{ "schema": "public", "name": "emails" }], "columns": [ { "schema": "public", "table": "users", "column": "email", "type": "varchar(255)" }, { "schema": "public", "table": "contacts", "column": "email_address", "type": "text" } ] } }

Schema Overview

Compact ERD-like view showing tables, primary keys, and relationships.

pgtool overview pgtool overview auth

Output (plain):

Schema: public (3 tables)

users (1.2k rows) PK: id → orders.user_id, profiles.user_id

orders (45k rows) PK: id FK: user_id → users.id → order_items.order_id

products (500 rows) PK: id

Explain Query Plan

Analyze query execution plan.

With ANALYZE (executes query)

pgtool explain "SELECT * FROM users WHERE email = 'x'"

Without ANALYZE (plan only)

pgtool explain "SELECT * FROM users WHERE id = 1" --no-analyze

Output: {"ok":true,"query":"SELECT...","plan":["Seq Scan on users...","..."]}

Error Responses

All errors follow a consistent JSON format with ok: false , an error code, and a helpful hint:

Configuration not found:

{ "ok": false, "error": "Configuration file not found", "code": "CONFIG_NOT_FOUND", "hint": "Create a .pgtool.json file in your project root with database connection details." }

Connection failed:

{ "ok": false, "error": "Could not connect to database server: connect ECONNREFUSED 127.0.0.1:5432", "code": "CONNECTION_FAILED", "hint": "Verify host and port in .pgtool.json and ensure PostgreSQL is running" }

Authentication failed:

{ "ok": false, "error": "Authentication failed", "code": "PERMISSION_DENIED", "hint": "Check your username and password in .pgtool.json" }

Table not found:

{ "ok": false, "error": "relation "nonexistent_table" does not exist", "code": "TABLE_NOT_FOUND", "hint": "Check that the table exists and you have permission to access it" }

Error codes: CONFIG_NOT_FOUND , CONFIG_INVALID , CONNECTION_FAILED , QUERY_FAILED , TABLE_NOT_FOUND , SCHEMA_NOT_FOUND , PERMISSION_DENIED , TIMEOUT

Common Usage Patterns

Exploring a new database:

• pgtool schemas

• See available schemas

• pgtool overview

• Quick view of tables and relationships

• pgtool tables <schema>

• List tables with sizes

• pgtool describe <table>

• Understand table structures

• pgtool sample <table>

• See example data

Finding data:

• pgtool search <pattern>

• Find tables/columns by name

• pgtool sample <table>

• Quick data preview

• pgtool count <table>

• Get exact row count

• pgtool query "SELECT..."

• Custom queries

Debugging data issues:

• pgtool describe <table>

• Verify column types

• pgtool sample <table>

• Check actual data

• pgtool explain "SELECT..."

• Analyze query performance

• pgtool indexes <table>

• Check index coverage

Understanding relationships:

• pgtool overview

• Visual relationship map

• pgtool relationships

• Get all FK relationships

• pgtool constraints <table>

• See specific table constraints