Skip to content
AI Developer Toolkit

Find your level — free Buy the Toolkit Buy Toolkit Buy the Toolkit Buy Toolkit Open the guides Read
Navigation menu
Buy the Toolkit Find your level — free Buy the Toolkit Open the guides Sign in

Database Design and Queries

PL

Level 3 How-to Updated

A database MCP server connects Claude Code, Codex, or Cursor to a live database through a read-only role. The agent then designs schema changes, reads real execution plans, and plans zero-downtime migrations against the actual schema instead of a pasted dump. The agent writes migration files; CI gates and a disposable database copy prove them before anyone approves the rollout.

This guide is for developers who own a service’s data layer and for tech leads who approve its migrations. The situation it solves: a column change on a table with tens of millions of rows, a foreign-key graph nobody has drawn in years, and an agent that returns confident SQL naming columns that do not exist because it only saw half the schema.

What you get from agent-driven database work

Section titled “What you get from agent-driven database work”
  • A maintained database MCP server connected to your agent through a role that cannot write.
  • Four copy-paste prompts: schema design, slow-query diagnosis, a zero-downtime migration plan, and a verification harness.
  • A migration gate you can put in CI (lint, apply up and down on a copy, invariant queries) so you approve evidence, not SQL line by line.
  • The failure modes that hit first, with the recovery for each.

The per-tool pages go deeper on each agent’s specifics: database work in Claude Code, database work in Codex, and database design in Cursor.

Which database MCP server should you connect?

Section titled “Which database MCP server should you connect?”

Pick the server by database engine and by what the agent must do. Every option below runs in Claude Code, Codex, and Cursor; the database MCP catalogue covers each one in depth.

New to MCP? Start with MCP servers.

ServerEnginesPick it whenRead-only control
Postgres MCP Pro (postgres-mcp, PyPI)PostgreSQLYou tune queries: it has explain_query with hypothetical indexes, get_top_queries, index recommendations, and analyze_db_health--access-mode=restricted (read-only transactions plus an execution-time limit)
DBHub (@bytebase/dbhub, npm)PostgreSQL, MySQL, MariaDB, SQL Server, Oracle, SQLiteYou have several engines, or want a small tool surface (two tools by default)readonly = true per tool in dbhub.toml
Supabase MCP (remote)Supabase PostgresYour database lives in Supabaseread_only=true URL parameter
MongoDB MCP Server (mongodb-mcp-server, npm)MongoDB, AtlasDocument stores and aggregation pipelines--readOnly flag

Two trade-offs to know before you choose. Postgres MCP Pro gives the deepest tuning tools, but its last PyPI release is 0.3.0 from 2025-05-16 (checked 2026-09-26), so pin the version and watch the repository. Tool definitions still cost context: Claude Code and Codex load them on demand through tool search, but Cursor and other clients may load every tool up front, so DBHub’s two-tool default keeps long sessions lean everywhere; see reducing MCP token cost.

Create a read-only role before you connect anything

Section titled “Create a read-only role before you connect anything”

The server’s own read-only switch is the second lock. The first is a database role that cannot write, so a generated UPDATE fails even if a flag is wrong. Run this as an administrator on a development or staging database:

CREATE ROLE mcp_readonly WITH LOGIN;
GRANT CONNECT ON DATABASE app_dev TO mcp_readonly;
GRANT USAGE ON SCHEMA public TO mcp_readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO mcp_readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO mcp_readonly;
-- Lets the agent read pg_stat_statements; query texts from all roles become visible and may contain literal values
GRANT pg_read_all_stats TO mcp_readonly;
ALTER ROLE mcp_readonly SET statement_timeout = '30s';

Set the role’s password with \password mcp_readonly in psql and store it in ~/.pgpass (mode 0600), not in any MCP config. Postgres MCP Pro connects through libpq, which reads ~/.pgpass, so the connection string you give the agent carries no secret and is safe to commit.

For query tuning, Postgres MCP Pro also needs the pg_stat_statements and hypopg extensions on the target database. On managed Postgres (RDS, Cloud SQL, Azure) both are usually available through CREATE EXTENSION; on self-managed Postgres, pg_stat_statements must be in shared_preload_libraries first.

Connect the server in Claude Code, Codex, and Cursor

Section titled “Connect the server in Claude Code, Codex, and Cursor”

The server and its arguments are identical in all three tools; only the config location differs.

Run in your terminal, in the repository root. -s project writes .mcp.json so the whole team shares the entry:

Terminal window
claude mcp add postgres -s project \
  -e DATABASE_URI=postgresql://mcp_readonly@localhost:5432/app_dev \
  -- uvx postgres-mcp --access-mode=restricted
claude mcp list

A teammate who pulls the repository sees the server as pending approval in claude mcp list until they approve it in a session. .mcp.json also expands ${VAR} and ${VAR:-default}, so each developer can point DATABASE_URI at their own database.

For another engine, swap the command. DBHub reads its settings from a dbhub.toml that you name with --config, and interpolates ${VAR} so the password stays in your environment:

[[sources]]
id = "app_dev"
dsn = "postgres://mcp_readonly:${DB_PASSWORD}@localhost:5432/app_dev"


[[tools]]
name = "execute_sql"
source = "app_dev"
readonly = true
max_rows = 1000


[[tools]]
name = "search_objects"
source = "app_dev"


[[tools]]
name = "explain_sql"
source = "app_dev"

Then register npx @bytebase/dbhub@latest --transport stdio --config ./dbhub.toml as the command, the same way as above (checked against @bytebase/dbhub 1.4.0). DBHub loads dbhub.toml only when --config names it; it never picks the file up from the working directory. For MongoDB, the command is npx -y mongodb-mcp-server@latest --readOnly with the connection string in MDB_MCP_CONNECTION_STRING.

Design a schema change that fits the schema you have

Section titled “Design a schema change that fits the schema you have”

The value of the connection is that the agent reads your conventions (key types, naming, timestamp columns) before it proposes anything. Ask for a migration file in your project’s migration tool, never for DDL run through the MCP server.

A grounded result matches users.id (here a uuid) instead of guessing bigint, and enforces the business rule in the database:

CREATE TABLE subscriptions (
  id                 uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id            uuid NOT NULL REFERENCES users (id) ON DELETE CASCADE,
  plan_id            uuid NOT NULL REFERENCES plans (id),
  status             text NOT NULL
                     CHECK (status IN ('trialing', 'active', 'past_due', 'canceled')),
  current_period_end timestamptz NOT NULL,
  created_at         timestamptz NOT NULL DEFAULT now()
);


-- One active subscription per user, enforced by the database
CREATE UNIQUE INDEX subscriptions_one_active_per_user
  ON subscriptions (user_id) WHERE status = 'active';
CREATE INDEX subscriptions_user_id_idx ON subscriptions (user_id);
CREATE INDEX subscriptions_plan_id_idx ON subscriptions (plan_id);

Review the list of constraints and business rules, not the DDL. If a rule you care about has no constraint next to it, the design is incomplete.

Diagnose a slow query from its real plan

Section titled “Diagnose a slow query from its real plan”

Index advice without a plan is a guess. Postgres MCP Pro reads pg_stat_statements to find the expensive queries and uses hypopg to simulate an index before anyone creates it, so the agent can show a before-and-after plan without touching the database.

A typical fix pre-aggregates a one-to-many join that was multiplying rows, adds a covering index on reviews, and adds one index for the filter:

-- Migration: indexes, created without blocking writes.
-- Each CREATE INDEX CONCURRENTLY goes in its own non-transactional migration file.
CREATE INDEX CONCURRENTLY reviews_product_id_rating_idx
  ON reviews (product_id) INCLUDE (rating);


-- Separate migration file
CREATE INDEX CONCURRENTLY products_created_at_price_idx
  ON products (created_at, price);


-- Query rewrite: aggregate reviews once per product, then join
WITH product_stats AS (
  SELECT product_id, avg(rating) AS avg_rating, count(*) AS review_count
  FROM reviews
  GROUP BY product_id
)
SELECT p.id, p.name, p.price, ps.avg_rating, ps.review_count
FROM products p
LEFT JOIN product_stats ps ON ps.product_id = p.id
WHERE p.price BETWEEN 100 AND 500
  AND p.created_at > now() - interval '2 years'
ORDER BY ps.avg_rating DESC NULLS LAST;

If the agent proposes a partial index such as ON products (price) WHERE created_at > now() - interval '2 years', reject it: PostgreSQL requires index predicates to be IMMUTABLE and now() is STABLE, so it refuses to create the index. The now() in the query above is fine, because it sits in a WHERE clause, not in an index. Agents do propose such indexes; the up-down-up run on a disposable copy (see the verification gates below) fails on it before review.

Plan a zero-downtime migration with expand and contract

Section titled “Plan a zero-downtime migration with expand and contract”

Large-table changes go out as small, reversible steps across several deploys. The agent is good at producing the phased plan and the lock analysis; the plan is what you review.

  1. Expand. Add the nullable column. This is a metadata-only change, but it still needs a brief ACCESS EXCLUSIVE lock, so set lock_timeout first. If the lock is not granted in time, the statement fails instead of queueing behind a long transaction and blocking every query behind it; have your deploy script retry the migration when it fails with a lock_timeout error.

    SET lock_timeout = '3s';
    ALTER TABLE users ADD COLUMN email_normalized text;

  2. Dual-write. Deploy application code that writes email_normalized on every insert and update.

  3. Backfill in committed batches. Run this from a script in a loop, committing after each batch, until it updates zero rows. One big UPDATE, or a loop that never commits, holds every row lock until the end. The email IS NOT NULL filter keeps the loop from picking up the same rows forever; decide what rows with a NULL email get (a default value, or exclusion from the constraint) before step 4, or the NOT NULL validation fails.

    UPDATE users SET email_normalized = lower(trim(email))
    WHERE id IN (
      SELECT id FROM users
      WHERE email_normalized IS NULL AND email IS NOT NULL
      ORDER BY id LIMIT 10000
    );

  4. Enforce without blocking writes. Check for duplicates first; a unique index build fails on the first one it finds. If the build fails, it leaves an INVALID index; drop it with DROP INDEX CONCURRENTLY before you retry. Keep the three parts in separate migration files: the concurrent index build cannot run inside a transaction, and if ADD CONSTRAINT ... NOT VALID and VALIDATE share a transaction, the validation scan runs under the ACCESS EXCLUSIVE lock and blocks writes.

    -- Migration A (non-transactional): duplicate check + CREATE UNIQUE INDEX CONCURRENTLY
    SELECT email_normalized, count(*) FROM users
    GROUP BY 1 HAVING count(*) > 1;
    CREATE UNIQUE INDEX CONCURRENTLY users_email_normalized_key
      ON users (email_normalized);
    

    -- Migration B: brief ACCESS EXCLUSIVE lock; commit before validating
    SET lock_timeout = '3s';
    ALTER TABLE users ADD CONSTRAINT users_email_normalized_nn
      CHECK (email_normalized IS NOT NULL) NOT VALID;
    

    -- Migration C: VALIDATE scans without blocking writes; SET NOT NULL locks only briefly
    SET lock_timeout = '3s';
    ALTER TABLE users VALIDATE CONSTRAINT users_email_normalized_nn;
    -- PostgreSQL 12+ uses the validated check and skips the full-table scan
    ALTER TABLE users ALTER COLUMN email_normalized SET NOT NULL;
    ALTER TABLE users DROP CONSTRAINT users_email_normalized_nn;

  5. Contract. Switch reads to the new column, and remove old code paths in a later release, after the new column has served production traffic.

How do you verify an agent-written migration without reading every line?

Section titled “How do you verify an agent-written migration without reading every line?”

You approve evidence. Define the checks before the agent writes the migration, keep them in a directory the agent cannot edit (see protecting the oracle), and let CI run them on every migration file.

GateWhat it catchesWhere it runsWho owns it
Migration lint with Squawk (squawk-cli, npm and PyPI)Blocking index builds, unsafe NOT NULL, table rewrites, and other lock hazardsCI, on every migration fileTech lead sets .squawk.toml
Up, down, up on a disposable copyMigrations that fail, rollbacks that do not restore the schemaCI, in a throwaway Postgres container restored from pg_dump --schema-onlyAuthor
Schema diffUnintended changes: a dropped index, a changed defaultCI: diff pg_dump --schema-only before and afterReviewer reads the diff, not the SQL
Invariant queriesOrphaned rows, duplicates, lost data after a backfillCI and after each production phase: each query must return zero rowsTech lead or DBA writes them first
Plan evidenceA “fix” that does not change the planBefore-and-after explain_query output pasted into the pull requestAuthor
Integration tests against a real databaseApplication code that breaks on the new schemaCITeam

Squawk is one command in CI (version 2.66.0 on npm, checked 2026-09-26):

Terminal window
npx --package squawk-cli squawk migrations/*.sql

Sign-off works like this: a human owner, the tech lead or the DBA, approves the phased plan, the lock report, and a green gate, and then CI applies each migration in its deploy. Nobody applies DDL through the MCP server, and nobody points an agent with write access at production.

Enforce tenant isolation with row-level security

Section titled “Enforce tenant isolation with row-level security”

Row-level security (RLS) is high-stakes because it fails open in quiet ways: on a table where RLS was never enabled, for a table owner when FORCE is missing, or for a role with BYPASSRLS, every tenant’s rows are visible. Ask the agent for the policies and the tests that prove them together.

Two details decide whether the policy works. Table owners bypass RLS unless the table also has ALTER TABLE ... FORCE ROW LEVEL SECURITY, and a transaction-mode connection pooler does not keep a session-level SET, so the tenant has to be set per transaction.

Add Postgres judgement with a skill

Section titled “Add Postgres judgement with a skill”

An MCP server gives the agent access; a skill gives it rules to apply. Supabase publishes supabase-postgres-best-practices, which covers Postgres performance and RLS, and works for any Postgres database, not only Supabase. Codex and Cursor use the same skills CLI command.

Terminal window
claude plugin marketplace add supabase/agent-skills
claude plugin install postgres-best-practices@supabase-agent-skills

Use the skill with the MCP server: the server supplies facts about your schema, and the skill supplies the review checklist. See installing and managing skills for scopes and updates.

What breaks when agents work on databases?

Section titled “What breaks when agents work on databases?”

Where to go next with database work

Section titled “Where to go next with database work”
All 577 guides Free resources: Compare tools Glossary By role

Written by

See what's new on the platform.

Privacy Policy Terms of Service Cookie settings llms.txt pricing.md RSS