·

PostgreSQL MCP With OpenCode

Set up PostgreSQL MCP in OpenCode so your AI agent can query, inspect, and manage databases right from your editor.

OpenCode's MCP support is solid but younger than Claude Code's, and the gaps show up specifically around long-running tool calls and how much plan-tree output it's willing to keep in context across turns. This topic covers wiring postgres-mcp into OpenCode's config, the schema/index exploration workflow, a full migration-script example, and the specific limitations worth knowing before you lean on it for anything production-critical.

Installing and Connecting Postgres MCP to OpenCode

OpenCode reads MCP server definitions from opencode.json (project root) or the global ~/.config/opencode/opencode.json. Project-level config wins for anything with a real connection string:

{
  "$schema": "https://opencode.ai/config.json",
  "mcp": {
    "postgres": {
      "type": "local",
      "command": [
        "uvx",
        "postgres-mcp",
        "--access-mode=restricted",
        "{env:POSTGRES_MCP_URI}"
      ],
      "enabled": true
    }
  }
}

{env:VAR} interpolation pulls from the shell environment at launch time, so the actual connection string never touches the checked-in config file:

export POSTGRES_MCP_URI="postgresql://mcp_agent:CHANGE_ME@localhost:5432/app_dev"
opencode

Confirm it loaded from inside a session with /mcp:

/mcp
postgres: connected (3 tools: execute_sql, list_objects, get_object_details ...)

If the server type or path is wrong, /mcp usually shows postgres: error with the stderr tail from the failed uvx launch — check that uv/uvx is actually on the PATH OpenCode inherits, which on some macOS setups differs from your interactive shell's PATH if OpenCode was launched from a GUI app launcher rather than a terminal.

which uvx
uvx postgres-mcp --version

Tips
- Use {env:VAR} interpolation in opencode.json, not a literal connection string — the file is meant to be committed.
- /mcp inside a running session is the fastest way to confirm a server is actually connected versus just declared in config.
- If OpenCode was launched from Finder/Spotlight rather than a terminal on macOS, it may not inherit your shell's PATH — launch from a terminal during setup to rule that out as a failure cause.

Querying Schemas, Tables, and Indexes from OpenCode

The exploration workflow is the same tool set as any other client, but OpenCode's default context window handling means you get better results being more surgical about what you ask for in one turn versus Claude Code, which tolerates broader "map everything" requests more gracefully.

Prompt:
List tables in the public schema via postgres MCP. Then, for the
`subscriptions` table specifically, show me its columns, all indexes,
and any foreign keys — I need to know if there's already an index
covering (customer_id, status) before I add one.

Tool: list_objects  { "schema_name": "public", "object_type": "table" }
Tool: get_object_details  { "schema_name": "public", "object_type": "table", "object_name": "subscriptions" }

Typical output for the index check:

Indexes on subscriptions:
  subscriptions_pkey            UNIQUE btree (id)
  idx_subscriptions_customer_id btree (customer_id)
  idx_subscriptions_status      btree (status)

No composite index on (customer_id, status) exists. Two single-column
indexes exist separately; Postgres can bitmap-AND them for a query
filtering on both, but a composite index would be more efficient for
this specific access pattern if it's frequent.

That's a correct and useful answer, but it's also exactly the kind of thing worth double-checking with explain_query on the actual query you care about rather than accepting the general index-theory statement — bitmap AND of two single-column indexes is often close enough in practice that a new composite index isn't worth the write-amplification cost. Ask the agent to prove it with a real plan, not just describe the general principle.

EXPLAIN ANALYZE
SELECT * FROM subscriptions WHERE customer_id = 4821 AND status = 'active';

Tips
- Scope schema questions to specific tables in OpenCode rather than "map the whole database" — the smaller the ask, the more reliably the tool-call loop completes in one pass.
- Push past a general index-theory answer to an actual EXPLAIN ANALYZE on your real query before deciding a composite index is worth adding.
- get_object_details output format is stable across the crystaldba/postgres-mcp versions I've tested (0.2.x-0.3.x), so prompts written against it don't need much rework across upgrades.

Practical Example: Generating a Data Migration Script in OpenCode

A realistic ask: backfilling a new column from existing data, safely, on a table too large for a naive single-transaction UPDATE.

Prompt:
I added a nullable `region` column to the `customers` table (varchar).
I want to backfill it from `customers.billing_country` using this
mapping: US/CA -> "NA", GB/DE/FR/ES/IT -> "EU", everything else ->
"OTHER". The table has ~6M rows. Check the table size and current
load via postgres MCP, then write a batched backfill script safe to
run against production, plus the down migration.

The agent first checks scale before proposing an approach:

SELECT count(*) FROM customers;                     -- 6,142,003
SELECT pg_size_pretty(pg_total_relation_size('customers'));  -- 3812 MB

Then a batched backfill using ctid-range or PK-range chunking rather than a single UPDATE ... WHERE region IS NULL, which would hold row locks across the whole table for the transaction's duration:

-- migration: 0047_backfill_customer_region.sql
-- Batched backfill, 5,000 rows per transaction, safe to run against
-- a live production table. Re-runnable: only touches rows where
-- region IS NULL, so a partial run can simply be resumed.

DO $$
DECLARE
  rows_updated integer;
BEGIN
  LOOP
    WITH batch AS (
      SELECT id FROM customers
      WHERE region IS NULL
      ORDER BY id
      LIMIT 5000
      FOR UPDATE SKIP LOCKED
    )
    UPDATE customers c
    SET region = CASE
      WHEN c.billing_country IN ('US', 'CA') THEN 'NA'
      WHEN c.billing_country IN ('GB', 'DE', 'FR', 'ES', 'IT') THEN 'EU'
      ELSE 'OTHER'
    END
    FROM batch
    WHERE c.id = batch.id;

    GET DIAGNOSTICS rows_updated = ROW_COUNT;
    EXIT WHEN rows_updated = 0;

    COMMIT;
    PERFORM pg_sleep(0.1);  -- brief pause between batches to ease replication lag
  END LOOP;
END $$;

-- down migration: 0047_backfill_customer_region_down.sql
UPDATE customers SET region = NULL WHERE region IS NOT NULL;

The FOR UPDATE SKIP LOCKED plus explicit COMMIT inside the loop and a small pg_sleep between batches are the parts that actually matter for a 6M-row table on a live system — they keep any single transaction short and avoid piling up replication lag on a read replica. An agent asked generically to "write a migration" without being told the table's real size will often skip straight to the single-statement UPDATE, which is why running the row-count and size check first, and telling it to, matters.

Tips
- Always have the agent check actual table size/row count via MCP before it chooses a migration strategy — a single-statement UPDATE is fine for 10K rows and dangerous for 6M.
- FOR UPDATE SKIP LOCKED plus batching in explicit transactions is the pattern to ask for by name if the agent doesn't propose it on its own for a large-table backfill.
- Always ask for the down migration in the same response — it's easy to get the forward script and forget the reverse until you actually need to roll back at 2am.

Known Limitations for Postgres MCP in OpenCode

A few things worth knowing going in, from actually running this combination:

  • Long EXPLAIN ANALYZE output on complex plans gets truncated more aggressively than in Claude Code's CLI. For a plan tree with 15+ nodes (common on a query joining 5+ tables), OpenCode sometimes shows only the top-level nodes in the rendered tool-call summary even though the full JSON was returned to the model. If the agent's diagnosis seems to be missing a nested sub-plan, ask it to paste the raw JSON plan directly rather than relying on the tool-call summary view.
  • No built-in equivalent to Claude Code's --scope project vs --scope user split. OpenCode's config precedence (project opencode.json overrides global) achieves the same practical outcome, but there's no CLI subcommand for adding/listing servers — you're editing JSON by hand, which is more error-prone for a team onboarding new members.
  • Session-to-session tool-call history isn't preserved the way Claude Code's --resume carries forward MCP context. Starting a new OpenCode session means the agent has forgotten what it learned about your schema last time; expect to re-run schema introspection at the start of each significant session rather than assuming persistent memory of your database structure.
  • Version churn. OpenCode's MCP client implementation has changed enough between recent releases that a config block working on one version occasionally needs a type: "local" vs type: "stdio" key rename on upgrade — check the changelog if /mcp starts showing a connected server as erroring after an OpenCode update.

None of these are blockers, but they mean OpenCode currently suits shorter, more targeted database sessions better than the long, exploratory multi-hour sessions Claude Code handles comfortably.

Tips
- For complex multi-join EXPLAIN ANALYZE diagnosis, ask for the raw JSON plan explicitly rather than trusting the summarized tool-call view.
- Re-run schema introspection at the start of each OpenCode session on a schema-heavy task — don't assume it remembers last session's findings.
- Pin your postgres-mcp server version in opencode.json (or your uvx invocation) if you hit a config-format break after an OpenCode upgrade, then upgrade both deliberately together.

Tips

Tips
- {env:VAR} interpolation in opencode.json keeps connection strings out of version control while still letting the server config itself be committed and shared.
- Keep OpenCode database sessions scoped and shorter than you might in Claude Code — ask for specific tables and specific queries rather than open-ended schema mapping.
- For any migration script it drafts, insist on both the batched-safe forward migration and the down migration in the same response, and validate table scale via MCP before accepting either.

Next PostgreSQL MCP With Gemini CLI