🐾 OpenPets
Submit
PO

postgres

Open Source DatabaseDevelopment

PostgreSQL integration for OpenCode - execute queries, analyze performance, and manage database health

postgres postgresql database sql
View Source NPM Package Website

Quick Install

npm install @openpets/postgres
pnpm add @openpets/postgres

Required Environment Variables

  • DATABASE_URL

    PostgreSQL connection string (format: postgresql://user:password@host:port/database)

Available Tools (7)

These tools are available when you install the postgres plugin in your AI assistant.

postgres-list-schemas

List all database schemas in the PostgreSQL instance, including schema owner and type (System/User).

postgres-list-objects

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

postgres-get-object-details

Get detailed information about a specific database object, including columns, constraints, and indexes for tables/views.

postgres-execute-sql

Execute a SQL query against the PostgreSQL database. Returns query results for SELECT statements, or execution status for other statements.

postgres-explain-query

Get the query execution plan for a SQL query, showing how PostgreSQL will execute it with detailed cost estimates.

postgres-get-top-queries

Get the slowest or most resource-intensive queries from pg_stat_statements extension. Requires pg_stat_statements to be installed and enabled.

postgres-analyze-health

Analyze database health including index health, connection utilization, buffer cache hit rates, vacuum health, and more.

Example Queries

list all database schemas
list all tables in the public schema
show details of the users table
execute SELECT version()
list all database branches excluding system schemas
create a table called test_users with columns: id serial primary key, email varchar(255), created_at timestamp
create a development branch copying from public schema
compare dev_branch and public schemas
analyze database health
show top 10 slowest queries

Usage Scenarios

basic query

  1. 1 list all database schemas
  2. 2 list all tables in the public schema
  3. 3 show the structure of a table in public schema
  4. 4 execute SELECT current_database()

performance analysis

  1. 1 analyze database health
  2. 2 show connection utilization
  3. 3 check cache hit rates
  4. 4 find unused indexes
  5. 5 show top 10 slowest queries

branch workflow

  1. 1 list all database branches
  2. 2 create a branch called dev_test copying from public
  3. 3 create a table products in dev_test with columns: id serial, name varchar(200), price decimal(10,2)
  4. 4 compare dev_test and public schemas
  5. 5 list tables in dev_test schema
  6. 6 delete branch dev_test

table management

  1. 1 create a table orders with columns: id serial primary key, user_id integer, total decimal(10,2), status varchar(20)
  2. 2 add column notes text to table orders
  3. 3 show details of the orders table
  4. 4 copy table orders to orders_backup
  5. 5 truncate table orders_backup
  6. 6 drop table orders_backup

advanced branching

  1. 1 create branch staging copying from public with data
  2. 2 create table new_feature in staging with columns: id serial, data jsonb, created_at timestamp default now()
  3. 3 list all tables in staging schema
  4. 4 compare staging and public schemas
  5. 5 merge staging to public with skip strategy
  6. 6 delete branch staging

schema exploration

  1. 1 list all schemas with their owners
  2. 2 list all tables in public schema
  3. 3 list all views in public schema
  4. 4 list installed PostgreSQL extensions
  5. 5 show detailed structure of a specific table

health monitoring

  1. 1 analyze overall database health
  2. 2 check for duplicate indexes
  3. 3 find unused indexes
  4. 4 check vacuum health
  5. 5 analyze table bloat
  6. 6 show connection statistics

Optional Configuration

Variable Description
PG_HOST PostgreSQL host (overrides DATABASE_URL host)
PG_PORT PostgreSQL port (overrides DATABASE_URL port)
PG_USER PostgreSQL username (overrides DATABASE_URL user)
PG_PASSWORD PostgreSQL password (overrides DATABASE_URL password)
PG_DATABASE PostgreSQL database name (overrides DATABASE_URL database)
PG_SSL Enable SSL connection (true/false)

Use postgres with Your AI Assistant

This open source plugin works with OpenCode-compatible AI assistants including ChatGPT, Claude, Perplexity, and other LLM-powered coding tools. Install the plugin and start using natural language to interact with postgres.

Works with ChatGPT Works with Claude Works with Perplexity MCP Compatible

Related Plugins

🤖

llm-models

LLM model discovery and comparison plugin - search and compare models from models.dev across multiple providers
GI

gitlab

GitLab integration plugin for OpenCode - manage projects, issues, merge requests, and more
LI

linear

Linear plugin for OpenCode - manage issues, projects, cycles, and teams with full CRUD operations