npm package: @openpets/postgres
Description: PostgreSQL integration for OpenCode - execute queries, analyze performance, and manage database health
Latest version: 1.0.3

# 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

```bash
npm install
```

### 2. Configure Environment

```bash
cp .env.example .env
```

Edit `.env` and set your PostgreSQL connection string:

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

### 3. Optional: Enable Performance Extensions

For enhanced functionality, install these PostgreSQL extensions:

```sql
-- 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:**
```bash
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:**
```bash
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:**
```bash
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:**
```bash
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:**
```bash
opencode run "explain this query: SELECT * FROM users WHERE email = 'test@example.com'"
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:**
```bash
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:**
```bash
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:**
```bash
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:**
```bash
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:**
```bash
opencode run "drop table temp_users"
opencode run "drop table old_data with cascade"
```

---

### postgres-rename-table (NEW)

Rename an existing table.

**Usage:**
```bash
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:**
```bash
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:**
```bash
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:**
```bash
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:**
```bash
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:**
```bash
opencode run "delete branch old_feature"
```

---

### postgres-rename-branch (NEW)

Rename a database branch (schema).

**Usage:**
```bash
opencode run "rename branch dev to staging"
```

---

### postgres-compare-branches (NEW)

Compare table structures between two branches.

**Usage:**
```bash
opencode run "compare dev_branch and production schemas"
```

---

### postgres-merge-branch (NEW)

Merge tables from one branch to another.

**Usage:**
```bash
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:**
```bash
opencode run "set search path to dev_branch then public"
```

---

## Connection String Examples

### Local PostgreSQL
```bash
POSTGRES_URL=postgresql://postgres:password@localhost:5432/mydb
```

### Neon (Serverless PostgreSQL)
```bash
POSTGRES_URL=postgresql://user:password@ep-cool-cloud-123456.us-east-2.aws.neon.tech/dbname
```

### Supabase
```bash
POSTGRES_URL=postgresql://postgres:password@db.projectref.supabase.co:5432/postgres
```

### Railway
```bash
POSTGRES_URL=postgresql://postgres:password@containers-us-west-123.railway.app:5432/railway
```

### AWS RDS
```bash
POSTGRES_URL=postgresql://username:password@mydb.123456789.us-east-1.rds.amazonaws.com:5432/dbname
```

### Google Cloud SQL
```bash
POSTGRES_URL=postgresql://username:password@/dbname?host=/cloudsql/project:region:instance
```

### Azure Database for PostgreSQL
```bash
POSTGRES_URL=postgresql://username@servername:password@servername.postgres.database.azure.com:5432/dbname?sslmode=require
```

## Use Cases

### 1. Schema Exploration
```bash
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
```bash
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
```bash
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
```bash
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
```bash
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)
```bash
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)
```bash
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:
```sql
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:
```sql
-- 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](https://github.com/crystaldba/postgres-mcp) 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
```bash
# 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! 🐕**