db-mcp — Read-Only Database MCP Server

A Model Context Protocol server that lets AI assistants safely explore a PostgreSQL database — schema, tables, columns, relationships, indexes, constraints, DDL, and sample data — without ever modifying it.

Safety model

Read-only is enforced at three independent layers, so a failure in any one layer is still caught by the others:

1. Least-privilege database role. You connect with a role that only has CONNECT / USAGE / SELECT. The database itself rejects anything else.
2. Read-only transaction. Every query runs inside BEGIN TRANSACTION READ ONLY on a session forced to default_transaction_read_only = on with a statement_timeout. The query is always rolled back.
3. Application-level validation. Arbitrary SQL (the run_readonly_query tool) is parsed with libpg-query — the real PostgreSQL parser compiled to wasm — and rejected unless it is a single read-only statement. This catches multi-statement input, DDL/DML, EXPLAIN ANALYZE, SELECT INTO, and data-modifying CTEs (WITH x AS (INSERT ... RETURNING ...) SELECT ...).

Additional guards: a maximum query length, a hard row cap on every result, a sample-row cap (default 50), and secrets are never logged (config is redacted before any diagnostic output).

Tools

Tool	Description
list_databases	Databases the read-only role can connect to.
list_schemas	Schemas visible to the role (system schemas excluded).
list_tables	Tables/views in a schema: type, row estimate, comment.
describe_table	Columns, PK, FKs, unique/check constraints, indexes.
get_table_ddl	Reconstructed CREATE TABLE DDL (+ indexes).
get_relationships	Foreign keys, referenced tables, cardinality hint.
get_indexes	Index names, columns, order, uniqueness, type, predicate.
sample_rows	A small sample of rows (capped at 50).
run_readonly_query	Validated read-only SQL; returns columns, rows, timing.
search_schema	Search names + comments across tables/columns/constraints/indexes.

Requirements

• Node.js >= 18.17
• A reachable PostgreSQL database and a read-only role (see below).

Setup

npm install
npm run build

Create a least-privilege read-only role

Run as a superuser or the database owner:

CREATE ROLE readonly_user LOGIN PASSWORD 'CHANGE_ME';
GRANT CONNECT ON DATABASE your_db TO readonly_user;
GRANT USAGE ON SCHEMA public TO readonly_user;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly_user;
-- Optional extra safety:
ALTER ROLE readonly_user SET default_transaction_read_only = on;

Configure

Copy .env.example and fill in your connection. Configuration is read from the environment (no .env is loaded automatically — export the vars, or have your MCP client pass them in its env block).

Variable	Default	Purpose
DATABASE_URL	—	Full connection string (wins over PG*).
PGHOST / PGPORT / PGDATABASE / PGUSER / PGPASSWORD	—	Discrete connection settings.
PGSSLMODE	disable	require/verify-* enable TLS.
DB_MCP_DRIVER	postgres	Database driver.
DB_MCP_MAX_ROWS	1000	Hard cap for run_readonly_query.
DB_MCP_SAMPLE_MAX_ROWS	50	Hard cap for sample_rows.
DB_MCP_STATEMENT_TIMEOUT_MS	5000	Per-statement timeout.
DB_MCP_MAX_QUERY_LENGTH	10000	Reject longer queries.
DB_MCP_DEFAULT_SCHEMA	public	Schema used when omitted.

Running locally

export DATABASE_URL="postgres://readonly_user:***@localhost:5432/your_db"
npm start

The server speaks MCP over stdio. To poke at it interactively, use the MCP Inspector:

npx @modelcontextprotocol/inspector node dist/server.js

Connecting from Claude Code

Add an entry to your MCP config (e.g. .mcp.json in your project, or the global Claude Code config):

{
  "mcpServers": {
    "db-mcp": {
      "command": "node",
      "args": ["/absolute/path/to/db-mcp/dist/server.js"],
      "env": {
        "DATABASE_URL": "postgres://readonly_user:***@localhost:5432/your_db"
      }
    }
  }
}

Then ask Claude to, e.g., "list the tables in the public schema" or "show me the DDL for the orders table".

Tests

npm test

Tests are pure unit tests (no live database required): exhaustive SQL-validation cases (allowed reads vs. rejected mutations and bypass attempts), identifier quoting, DDL reconstruction, and config parsing/redaction.

Extending to other databases

Tools depend only on the DatabaseDriver interface (src/db/driver.ts). To add MySQL, SQLite, or Snowflake:

1. Implement DatabaseDriver for the new engine under src/db/<engine>/.
2. Register it in createDriver (src/db/factory.ts).
3. Provide an engine-appropriate read-only enforcement (read-only role + read-only session/transaction) and SQL validation.

Project layout

src/
  server.ts                 MCP server setup + stdio transport
  config.ts                 env parsing + secret redaction
  errors.ts                 typed error classes
  types.ts                  shared result types
  validation/
    validateReadOnly.ts     libpg-query-based read-only enforcement
  db/
    driver.ts               DatabaseDriver interface
    factory.ts              driver selection
    postgres/
      pool.ts               read-only pool, transactions, row-capped cursor
      identifiers.ts        safe identifier quoting
      introspection.ts      catalog/information_schema queries
      ddl.ts                CREATE TABLE reconstruction (pure)
      PostgresDriver.ts     DatabaseDriver implementation
  tools/                    one module per MCP tool + registry
tests/                      pure unit tests