Designing a Multi-Database MCP Server

20 min read Lesson 21 / 40 Preview

Enterprise environments rarely have a single database. They have PostgreSQL for the main application, MySQL for legacy systems, Redis for caching, and maybe MongoDB for logs. In this chapter, we build an MCP server that connects to multiple databases and gives AI a unified interface to explore them all.

Architecture

┌──────────────────────────────────────────────────────┐
│              Database Explorer MCP Server              │
│                                                        │
│  ┌─────────────┐  ┌──────────────┐  ┌──────────────┐ │
│  │ PostgreSQL   │  │    MySQL     │  │   SQLite     │ │
│  │  Adapter     │  │   Adapter    │  │   Adapter    │ │
│  └──────┬──────┘  └──────┬───────┘  └──────┬───────┘ │
│         └────────────────┼──────────────────┘         │
│                    ┌─────┴─────┐                      │
│                    │  Unified   │                      │
│                    │  Interface │                      │
│                    └─────┬─────┘                      │
│                          │                             │
│  Tools: list-databases, describe-table, query,         │
│         explain-query, export-csv                      │
│  Resources: schema://{db}/tables, schema://{db}/{tbl}  │
│  Safety: read-only, query timeout, row limits, audit   │
└──────────────────────────────────────────────────────┘

Configuration Design

// src/config.ts
interface DatabaseConfig {
  name: string;
  type: "postgresql" | "mysql" | "sqlite";
  connectionString: string;
  readOnly: boolean;
  maxRows: number;
  queryTimeout: number; // milliseconds
}

function loadConfig(): DatabaseConfig[] {
  // Load from environment or config file
  const configJson = process.env.DB_CONFIG;
  if (!configJson) {
    throw new Error("DB_CONFIG environment variable is required");
  }
  return JSON.parse(configJson);
}

// Example DB_CONFIG:
// [
//   { "name": "main", "type": "postgresql", "connectionString": "postgres://...", "readOnly": true, "maxRows": 500, "queryTimeout": 10000 },
//   { "name": "legacy", "type": "mysql", "connectionString": "mysql://...", "readOnly": true, "maxRows": 200, "queryTimeout": 5000 }
// ]

Database Adapter Interface

// src/adapters/base.ts
export interface DatabaseAdapter {
  name: string;
  type: string;
  connect(): Promise<void>;
  disconnect(): Promise<void>;
  getTableNames(): Promise<string[]>;
  getTableSchema(table: string): Promise<ColumnInfo[]>;
  executeQuery(sql: string, params?: unknown[]): Promise<QueryResult>;
  explainQuery(sql: string): Promise<string>;
}

export interface ColumnInfo {
  name: string;
  type: string;
  nullable: boolean;
  primaryKey: boolean;
  defaultValue: string | null;
}

export interface QueryResult {
  columns: string[];
  rows: Record<string, unknown>[];
  rowCount: number;
  executionTime: number; // milliseconds
}

PostgreSQL Adapter

// src/adapters/postgresql.ts
import pg from "pg";
import { DatabaseAdapter, ColumnInfo, QueryResult } from "./base.js";

export class PostgreSQLAdapter implements DatabaseAdapter {
  private pool: pg.Pool;

  constructor(
    public name: string,
    public type: string,
    private config: { connectionString: string; queryTimeout: number; maxRows: number }
  ) {
    this.pool = new pg.Pool({
      connectionString: config.connectionString,
      max: 5,
      statement_timeout: config.queryTimeout,
    });
  }

  async connect() { await this.pool.query("SELECT 1"); }
  async disconnect() { await this.pool.end(); }

  async getTableNames(): Promise<string[]> {
    const result = await this.pool.query(
      "SELECT table_name FROM information_schema.tables WHERE table_schema = 'public' ORDER BY table_name"
    );
    return result.rows.map((r) => r.table_name);
  }

  async getTableSchema(table: string): Promise<ColumnInfo[]> {
    const result = await this.pool.query(`
      SELECT c.column_name, c.data_type, c.is_nullable,
             c.column_default, EXISTS(
               SELECT 1 FROM information_schema.key_column_usage k
               WHERE k.table_name = c.table_name AND k.column_name = c.column_name
             ) as is_pk
      FROM information_schema.columns c
      WHERE c.table_schema = 'public' AND c.table_name = $1
      ORDER BY c.ordinal_position
    `, [table]);

    return result.rows.map((r) => ({
      name: r.column_name,
      type: r.data_type,
      nullable: r.is_nullable === "YES",
      primaryKey: r.is_pk,
      defaultValue: r.column_default,
    }));
  }

  async executeQuery(sql: string): Promise<QueryResult> {
    const start = Date.now();
    const result = await this.pool.query(`${sql} LIMIT ${this.config.maxRows}`);
    return {
      columns: result.fields.map((f) => f.name),
      rows: result.rows,
      rowCount: result.rowCount ?? 0,
      executionTime: Date.now() - start,
    };
  }

  async explainQuery(sql: string): Promise<string> {
    const result = await this.pool.query(`EXPLAIN ANALYZE ${sql} LIMIT 1`);
    return result.rows.map((r) => r["QUERY PLAN"]).join("\n");
  }
}

Key Takeaway

Multi-database support is what makes an MCP server enterprise-grade. By using an adapter pattern, you can support PostgreSQL, MySQL, and SQLite with the same set of tools. The AI model does not need to know which database engine is running — it just calls query and gets results.

Get Full Access