Postgres Pup Plugin for OpenCode

A comprehensive PostgreSQL integration for OpenCode that provides AI agents with tools to execute queries, analyze performance, manage database health, and optimize your PostgreSQL databases.

Features

• 🔍 Schema Discovery - List schemas, tables, views, sequences, and extensions
• 📊 Query Execution - Execute SQL queries with detailed results
• ⚡ Performance Analysis - Get query execution plans and analyze slow queries
• 🔧 Health Monitoring - Check index health, connections, vacuum status, cache hit rates, and bloat
• 📈 Query Statistics - Identify slowest queries using pg_stat_statements
• 🛠️ Table Management (NEW) - Create, alter, drop, rename, copy, and truncate tables
• 🌿 Database Branching (NEW) - Create schema branches for isolated development and testing

Prerequisites

• Node.js 18+ with npm or bun
• PostgreSQL database (local or cloud-hosted)
• Database connection string

Setup

1. Install Dependencies

npm install

2. Configure Environment

cp .env.example .env

Edit .env and set your PostgreSQL connection string:

POSTGRES_URL=postgresql://username:password@host:port/database

3. Optional: Enable Performance Extensions

For enhanced functionality, install these PostgreSQL extensions:

-- Enable query statistics tracking
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- For index tuning (optional)
CREATE EXTENSION IF NOT EXISTS hypopg;

Available Tools

postgres-list-schemas

List all database schemas with owner information.

Usage:

opencode run "list all postgres schemas"
opencode run "show me the database schemas"

Returns:

• Schema name
• Schema owner
• Schema type (System/User)

postgres-list-objects

List database objects (tables, views, sequences, or extensions) within a schema.

Parameters:

• schema (required): Schema name to list objects from
• type (optional): Object type - 'table', 'view', 'sequence', or 'extension' (default: 'table')

Usage:

opencode run "list all tables in the public schema"
opencode run "show me views in the analytics schema"
opencode run "list sequences in public schema"
opencode run "show installed postgres extensions"

postgres-get-object-details

Get detailed information about a database object including columns, constraints, and indexes.

Parameters:

• schema (required): Schema name
• name (required): Object name
• type (optional): Object type - 'table', 'view', 'sequence', or 'extension' (default: 'table')

Usage:

opencode run "get details for the users table in public schema"
opencode run "describe the orders table structure"
opencode run "show me the columns and indexes for products table"

Returns for tables/views:

• Column definitions (name, type, nullable, default)
• Constraints (primary keys, foreign keys, unique, check)
• Indexes (name and definition)

postgres-execute-sql

Execute any SQL query against the PostgreSQL database.

Parameters:

• sql (required): SQL query to execute

Usage:

opencode run "execute: SELECT * FROM users LIMIT 10"
opencode run "count rows in orders table"
opencode run "create a table called products with id and name columns"

Returns:

• Row count
• Query results (for SELECT)
• Execution status (for INSERT/UPDATE/DELETE)

postgres-explain-query

Get the query execution plan showing how PostgreSQL will execute a query.

Parameters:

• sql (required): SQL query to explain
• analyze (optional): When true, actually runs the query for real statistics (default: false)

Usage:

opencode run "explain this query: SELECT * FROM users WHERE email = '[email protected]'"
opencode run "analyze execution plan for my slow query"

Returns:

• Detailed execution plan in JSON format
• Cost estimates
• Row estimates
• Actual timing (when analyze=true)

postgres-get-top-queries

Get the slowest or most resource-intensive queries from pg_stat_statements.

Prerequisites: Requires pg_stat_statements extension to be installed.

Parameters:

• sortBy (optional): 'total_time', 'mean_time', or 'calls' (default: 'total_time')
• limit (optional): Number of queries to return (default: 10)

Usage:

opencode run "show me the slowest postgres queries"
opencode run "find top 20 most frequently executed queries"
opencode run "get queries sorted by average execution time"

Returns:

• Query text
• Execution statistics (calls, times, rows)
• Mean, min, max, and stddev execution times

postgres-analyze-health

Analyze database health across multiple dimensions.

Parameters:

• checks (optional): Comma-separated list - 'index', 'connection', 'vacuum', 'cache', 'bloat', or 'all' (default: 'all')

Usage:

opencode run "analyze postgres database health"
opencode run "check postgres index health"
opencode run "show connection and cache health"

Health Checks:

1. Index Health

   • Duplicate indexes (wasting space)
   • Unused indexes (never scanned)

2. Connection Health

   • Max connections vs used connections
   • Available connection slots

3. Cache Health

   • Index hit rate (should be > 0.95)
   • Table hit rate (should be > 0.95)

4. Vacuum Health

   • Tables with high dead tuple count
   • Vacuum frequency

5. Bloat Analysis

   • Tables with significant bloat
   • Bloat size estimates

postgres-create-table (NEW)

Create a new table with full schema definition support.

Parameters:

• schema (optional): Schema name (default: 'public')
• tableName (required): Name of the table to create
• columns (required): Array of column definitions
  • name: Column name
  • type: PostgreSQL data type (VARCHAR(255), INTEGER, TIMESTAMP, etc.)
  • nullable: Whether column can be NULL
  • default: Default value expression
  • primaryKey: Whether this is the primary key
  • unique: Whether values must be unique

Usage:

opencode run "create a table users with columns: id serial primary key, email varchar(255) unique not null, name varchar(100), created_at timestamp default current_timestamp"

postgres-alter-table (NEW)

Modify an existing table structure.

Operations: add_column, drop_column, rename_column, alter_column_type

Usage:

opencode run "add column bio text to table users"
opencode run "drop column temp_field from users table"
opencode run "rename column old_name to new_name in users table"
opencode run "change type of age column to bigint in users table"

postgres-drop-table (NEW)

Drop (delete) a table from the database.

Usage:

opencode run "drop table temp_users"
opencode run "drop table old_data with cascade"

postgres-rename-table (NEW)

Rename an existing table.

Usage:

opencode run "rename table old_users to users_archive"

postgres-copy-table (NEW)

Create a copy of an existing table, optionally including its data.

Usage:

opencode run "copy table users to users_backup"
opencode run "copy table users to users_backup including data"

postgres-truncate-table (NEW)

Remove all rows from a table quickly while preserving the table structure.

Usage:

opencode run "truncate table logs"
opencode run "truncate table sessions and restart identity"

postgres-create-branch (NEW)

Create a new database branch (schema) for isolated development.

Usage:

opencode run "create a branch called dev_branch"
opencode run "create branch testing copying from public"
opencode run "create branch staging copying from public with data"

postgres-list-branches (NEW)

List all database branches (schemas) with table counts and ownership.

Usage:

opencode run "list all branches"
opencode run "show all database branches"

postgres-delete-branch (NEW)

Delete a database branch (schema) and all its objects.

Usage:

opencode run "delete branch old_feature"

postgres-rename-branch (NEW)

Rename a database branch (schema).

Usage:

opencode run "rename branch dev to staging"

postgres-compare-branches (NEW)

Compare table structures between two branches.

Usage:

opencode run "compare dev_branch and production schemas"

postgres-merge-branch (NEW)

Merge tables from one branch to another.

Usage:

opencode run "merge dev_branch to production"
opencode run "merge dev_branch to production with overwrite strategy"

postgres-set-search-path (NEW)

Set the default schema search path for the current session.

Usage:

opencode run "set search path to dev_branch then public"

Connection String Examples

Local PostgreSQL

POSTGRES_URL=postgresql://postgres:password@localhost:5432/mydb

Neon (Serverless PostgreSQL)

POSTGRES_URL=postgresql://user:[email protected]/dbname

Supabase

POSTGRES_URL=postgresql://postgres:[email protected]:5432/postgres

Railway

POSTGRES_URL=postgresql://postgres:[email protected]:5432/railway

AWS RDS

POSTGRES_URL=postgresql://username:[email protected]:5432/dbname

Google Cloud SQL

POSTGRES_URL=postgresql://username:password@/dbname?host=/cloudsql/project:region:instance

Azure Database for PostgreSQL

POSTGRES_URL=postgresql://username@servername:[email protected]:5432/dbname?sslmode=require

Use Cases

1. Schema Exploration

opencode run "explore my postgres database structure"
opencode run "what tables exist in the public schema?"
opencode run "show me the structure of the users table"

2. Query Development

opencode run "select all users created in the last 7 days"
opencode run "count orders by status"
opencode run "find top 10 customers by order value"

3. Performance Optimization

opencode run "why is my users query slow?"
opencode run "show me the execution plan for this query"
opencode run "find the slowest queries in my database"

4. Health Monitoring

opencode run "check if my database is healthy"
opencode run "are there any unused indexes?"
opencode run "what's my cache hit rate?"

5. Data Analysis

opencode run "analyze user signup trends over the last month"
opencode run "find tables that need vacuuming"
opencode run "show me connection utilization"

6. Table Management (NEW)

opencode run "create a products table with id, name, and price columns"
opencode run "add a description column to the products table"
opencode run "copy the users table to users_backup including data"
opencode run "rename table old_products to products_archive"

7. Development Branching (NEW)

opencode run "create a development branch copying from public"
opencode run "create table new_feature in dev_branch with columns: id serial, data jsonb"
opencode run "compare dev_branch and public schemas"
opencode run "merge dev_branch to public when ready"
opencode run "delete branch dev_branch after merging"

Best Practices

Security

• Never commit .env files to version control
• Use read-only database credentials when possible
• Limit permissions to only what's needed

Performance

• Enable pg_stat_statements for query tracking
• Run EXPLAIN ANALYZE on slow queries
• Monitor cache hit rates (should be > 95%)
• Look for unused indexes

Maintenance

• Regularly check vacuum status
• Monitor connection utilization
• Watch for table bloat
• Remove duplicate indexes

Troubleshooting

"Missing required environment variable: POSTGRES_URL"

Make sure you've created a .env file with your connection string.

"pg_stat_statements extension is not installed"

Install the extension:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

You may also need to add it to shared_preload_libraries in postgresql.conf and restart PostgreSQL.

Connection timeout errors

Check:

• Database is running and accessible
• Firewall rules allow connections
• Connection string is correct
• SSL settings match your database requirements

"permission denied" errors

Ensure your database user has appropriate permissions:

-- For read-only access
GRANT CONNECT ON DATABASE mydb TO myuser;
GRANT USAGE ON SCHEMA public TO myuser;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO myuser;

-- For read-write access
GRANT ALL PRIVILEGES ON SCHEMA public TO myuser;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO myuser;

Inspired By

This plugin is inspired by Postgres MCP Pro by Crystal DBA, which provides comprehensive PostgreSQL tools including index tuning and health checks for Model Context Protocol (MCP) servers.

Repository Structure

postgres/
├── index.ts                 # Main plugin entry point
├── package.json            # Dependencies and metadata
├── README.md              # This file
├── .env.example           # Environment template
├── src/                   # Source code
│   ├── utils.ts          # Shared utilities
│   ├── tables.ts         # Table management tools
│   └── branches.ts       # Branch management tools
├── tests/                # Test suite
│   ├── README.md         # Test documentation
│   ├── scripts/          # Test scripts (9 files)
│   └── docs/             # Test results (7 files)
└── docs/                 # Documentation
    ├── README.md         # Doc index
    ├── PLUGIN_FEATURES.md
    ├── CRUD_OPERATIONS.md
    ├── ADVANCED_FEATURES.md
    ├── IMPLEMENTATION_SUMMARY.md
    └── FINAL_SUMMARY.md

Testing

Comprehensive test suite with 100% command coverage (20/20 commands tested).

Quick Start

# Run quick validation (10 tests, ~60s)
./tests/scripts/quick-test.sh

# Run comprehensive tests (35+ tests, ~5-10 min)
./tests/scripts/comprehensive-test.sh

# Run complex scenarios (8 tests, ~14s)
node tests/scripts/complex-scenario-tests.js

See tests/README.md for complete testing documentation.

Documentation

• Quick Start: This README
• Complete Reference: See docs/ folder
• Test Results: See tests/docs/ folder

Key documentation:

• docs/PLUGIN_FEATURES.md - All 20 commands documented
• docs/CRUD_OPERATIONS.md - CRUD reference guide
• docs/ADVANCED_FEATURES.md - Advanced features guide
• tests/docs/COMPLETE_TEST_SUMMARY.md - Test coverage summary

Development

Commands Implemented: 20

• 7 Original Tools: Schema discovery, SQL execution, performance analysis
• 6 Table Management: Create, alter, drop, rename, copy, truncate
• 7 Branch Management: Create, list, delete, rename, compare, merge, set-path

All 20 commands have been tested and verified (100% coverage).

License

MIT

Contributing

Contributions are welcome! Please feel free to submit issues or pull requests.

Happy database querying! 🐕