database-mcp: Instructions file for Claude Code

CLAUDE.md — @cocaxcode/database-mcp

Project Overview

MCP server for database connectivity. Multi-DB (PostgreSQL, MySQL, SQLite), connection management, schema introspection, query execution with rollback and history, database dump/restore. 27 tools, 163 tests.

Token-optimized query results (v0.3+)

execute_query, execute_mutation, explain_query accept optional params that cut context tokens 60-95%:

• verbosity: 'minimal' | 'normal' | 'full' (default 'normal')
• only_columns: string[] — client-side column projection
• max_cell_bytes: number (default 500) — per-cell byte cap for 'normal'
• max_rows_in_response: number — row cap beyond SQL LIMIT
• include_schema_context: boolean — skip schema trail when known

Every compressed result includes call_id. Use inspect_last_query({ call_id }) to recover the full result without re-executing the SQL (preserves DB load). Ring buffer 20 + ~/.database-mcp/last-queries/ with 1h TTL.

Cell truncation preserves table structure: truncated cells get a …(+NB) suffix. Much better than the old global 25KB truncation that cut trailing rows.

Stack

• TypeScript 5.x (strict mode, ESM)
• @modelcontextprotocol/sdk 1.27.x (unified package)
• Zod 3.25+ for schema validation
• Vitest for testing (InMemoryTransport + SQLite :memory:)
• tsup for building (ESM output with shebang)
• Drivers: postgres, mysql2, sql.js (optional peer deps, dynamic import)

Architecture

src/
├── index.ts              # Entry point (shebang + StdioServerTransport, --dsn flag)
├── server.ts             # createServer(storageDir?, projectDir?) factory
├── tools/                # MCP tool registration (one file per group)
│   ├── connection.ts     # conn_create/list/get/set/switch/rename/delete/duplicate/test/project_list/project_clear/export/import (13)
│   ├── schema.ts         # search_schema (1)
│   ├── query.ts          # execute_query/execute_mutation/explain_query (3)
│   ├── rollback.ts       # rollback_list/rollback_apply (2)
│   ├── history.ts        # history_list/history_clear (2)
│   ├── config.ts         # config_get/config_set (2)
│   └── dump.ts           # db_dump/db_restore/db_dump_list (3)
├── resources/
│   └── schema.ts         # MCP Resources: db://schema, db://tables/{tableName}/schema
├── services/             # Business logic with DB interaction
│   ├── connection-manager.ts  # Lazy connect, driver caching, getActiveDriver()
│   ├── schema-introspector.ts # Multi-dialect schema queries (3 detail levels)
│   ├── query-executor.ts      # Read (LIMIT injection), mutation (mode check), explain
│   ├── rollback-manager.ts    # Pre-mutation snapshots, reverse SQL generation
│   ├── history-logger.ts      # Per-project query history (5000 max)
│   └── dump-manager.ts        # Database dump/restore (SQL generation, multi-dialect)
├── drivers/              # Database adapters (dynamic import)
│   ├── interface.ts      # DatabaseDriver interface
│   ├── sqlite.ts         # sql.js adapter (:memory: + file)
│   ├── postgres.ts       # postgres.js adapter (DSN or config)
│   ├── mysql.ts          # mysql2/promise adapter (DSN or config)
│   └── registry.ts       # resolveDriver() factory
├── lib/                  # Pure logic (no DB dependency)
│   ├── types.ts          # All TypeScript interfaces
│   ├── sanitize.ts       # sanitizeName() for connection names
│   └── storage.ts        # JSON file storage in ~/.database-mcp/
├── utils/
│   ├── sql-classifier.ts     # classifySql() → read/write/ddl
│   ├── sql-parser-light.ts   # extractTableAndWhere() for rollback
│   ├── result-formatter.ts   # formatQueryResult() with 25KB truncation
│   ├── gitignore-checker.ts  # Add .database-mcp/ to .gitignore
│   └── project-storage.ts    # Lazy mkdir of {projectDir}/.database-mcp/ + gitignore
├── types/                # Module declarations for optional deps
│   ├── sql.js.d.ts
│   ├── postgres.d.ts
│   └── mysql2.d.ts
└── __tests__/
    ├── helpers.ts         # createTestClient() with InMemoryTransport
    └── *.test.ts          # 10 test files, 88 tests