MCP Database Server

MCP Database Server

A Model Context Protocol server that provides tools for interacting with databases, including PostgreSQL, DuckDB, and Google Cloud Storage Parquet files.

Category
Visit Server

README

MCP Database Server

A Model Context Protocol (MCP) server built with mcp-framework that provides tools and resources for interacting with databases (PostgreSQL via DuckDB) and Google Cloud Storage (GCS).

Prerequisites

  • Node.js 22 or higher
  • TypeScript
  • PostgreSQL (required for database features)
  • Google Cloud credentials (optional, for GCS features)
  • Devbox (for local development using make commands)

Project Structure

.
├── docs
│   ├── assets
│   │   └── etl.png
│   ├── etl-workflow.md
│   └── setup-with-claude-desktop.md
├── migrations
│   ├── 1743322886782_initial-schema.cjs
│   └── 1743323460433_continuous-aggregates.cjs
├── scripts
│   └── setup-continuous-aggregates.sql
├── src
│   ├── resources       # MCP Resource definitions
│   │   ├── gcs_objects.ts
│   │   └── sql_tables.ts
│   ├── services        # Service initializers (DB connections, GCS client)
│   │   ├── duckdb.ts
│   │   ├── gcs.ts
│   │   └── postgres.ts
│   ├── tools           # MCP Tool definitions
│   │   ├── duckdb_insert.ts
│   │   ├── duckdb_query.ts
│   │   ├── duckdb_read_parquet.ts
│   │   └── gcs_directory_tree.ts
│   ├── utils           # Utility functions (logging, formatting)
│   │   ├── index.ts
│   │   └── logger.ts
│   ├── config.ts       # Configuration loading and validation
│   ├── index.ts        # Main server entry point
│   └── utils.ts        # Deprecated utils? (Consider removing if unused)
├── .env.example        # Example environment variables
├── .gitignore
├── CLAUDE.md
├── Dockerfile
├── MIGRATION.md
├── Makefile            # Development commands
├── README.md
├── database.json       # Migration configuration
├── devbox.json         # Devbox configuration
├── devbox.lock
├── docker-compose.yml  # Docker setup for DBs
├── fly.toml            # Fly.io deployment config
├── package-lock.json
├── package.json
└── tsconfig.json

Installation

  1. Clone the repository:

    git clone <repository-url>
    cd mcp-db
    
  2. Install dependencies (using Devbox is recommended for consistency):

    devbox install
    # Or using npm directly if not using Devbox
    # npm install
    
  3. Copy .env.example to .env and fill in your environment variables.

    cp .env.example .env
    # Edit .env with your details
    
  4. Build the project:

    # Using make (requires Devbox)
    make build
    # Or using npm directly
    # npm run build
    

Configuration

Environment Variables

Configure the server using these environment variables (or command-line arguments):

  • DATABASE_URL: PostgreSQL connection string (required unless running with supergateway).
  • DATABASE_URLS: Comma-separated list of alias=url pairs for multiple database connections (alternative to DATABASE_URL).
  • LOG_LEVEL: Logging level (debug, info, error). Default: info.
  • GCS_BUCKET: Default Google Cloud Storage bucket name (optional).
  • GCP_SERVICE_ACCOUNT: Base64 encoded Google Cloud service account key JSON (optional, for GCS authentication).
  • GCS_KEY_ID / GCS_SECRET: Alternative GCS credentials specifically for DuckDB's httpfs extension (optional).
  • TRANSPORT: Transport type (stdio or sse). Default: stdio.
  • PORT: Port number for SSE transport. Default: 3001.
  • HOST: Hostname for SSE transport. Default: localhost.
  • API_KEY: Optional API key for securing the server (if set, clients must provide it in the Authorization: Bearer <key> header).

Command-line arguments (e.g., --port 8080, --gcs-bucket my-bucket) override environment variables. See src/config.ts for details.

Database Migrations

The project uses node-pg-migrate for managing PostgreSQL schema changes. See the "Database Migrations" section in the original README content above for details on running and creating migrations.

Note: The npm run setup:db command mentioned previously might need review or updates based on the current setup.

Running the Server

Use the Makefile for convenient development commands (requires Devbox):

# Run in development mode (builds and starts with nodemon for auto-restarts)
# Uses SSE transport by default on port 3001
make dev

# Run tests (if configured)
# make test

# Build for production
# make build

To run without make (after npm run build):

# Run with stdio transport
node dist/index.js --transport stdio

# Run with SSE transport on default port 3001
node dist/index.js --transport sse

# Run with SSE on a different port
node dist/index.js --transport sse --port 8080

Client Configuration

To connect your MCP client (e.g., mcp-cli, Claude Desktop) to the local server:

For SSE Transport (e.g., on port 3001):

{
  "mcpServers": {
    "mcp-db-local": {
      "command": "node",
      "args": [
        "/path/to/mcp-db/dist/index.js", // Adjust path if needed
        "--transport", "sse",
        "--port", "3001" // Match the port the server is running on
      ],
      // Add "env" if API_KEY is set
      // "env": { "API_KEY": "your-secret-key" }
    }
  }
}

(Note: The Docker/supergateway example from the previous README might be outdated or specific to a different deployment setup.)

For Stdio Transport:

{
  "mcpServers": {
    "mcp-db-local": {
      "command": "node",
      "args": [
        "/path/to/mcp-db/dist/index.js", // Adjust path if needed
        "--transport", "stdio"
      ],
      // Add "env" if API_KEY is set
      // "env": { "API_KEY": "your-secret-key" }
    }
  }
}

Running with npx from GitHub

You can run the server directly using npx (requires build step in package):

# Ensure required env vars are set
export DATABASE_URL="postgresql://user:password@localhost:5432/db"
export GCS_BUCKET="my-bucket"

npx github:dwarvesf/mcp-db --transport sse --port 3001

Available Tools

  • duckdb_insert: Executes an INSERT statement on the attached PostgreSQL database via DuckDB. Only INSERT queries are allowed.
  • duckdb_query: Executes a read-only SQL query directly on the attached PostgreSQL database (postgres_db) using DuckDB's postgres_query function. Automatically prefixes unqualified table names (e.g., my_table becomes postgres_db.public.my_table).
  • duckdb_read_parquet: Queries Parquet files using DuckDB (likely from GCS if configured).
  • gcs_directory_tree: Fetches the directory tree structure from a GCS bucket with pagination support.

Available Resources

  • mcp://gcs/objects (gcs_objects): Lists objects in the configured GCS bucket.
  • mcp://db/tables (sql_tables): Lists all tables and their columns in the configured PostgreSQL database.

Development: Integrating a New Tool/Resource

This project uses mcp-framework. To add a new tool or resource:

  1. Create the Class:

    • Create a new .ts file in src/tools/ or src/resources/.
    • Define a class that extends MCPTool or MCPResource.
    • Implement the required properties (name, description, schema for tools) and methods (execute for tools, read for resources).
    • Use Zod in the schema property for input validation (tools).
    • Initialize any dependencies (like DB connections or GCS clients) within the class, often in the constructor, potentially using services from src/services/ or configuration from src/config.ts.

    Example Tool (src/tools/my_tool.ts):

    import { MCPTool } from "mcp-framework";
    import { z } from "zod";
    import { formatSuccessResponse } from "../utils.js";
    import { getDuckDBConnection } from "../services/duckdb.js"; // Example dependency
    
    const MyToolInputSchema = z.object({
      param1: z.string().describe("Description for parameter 1"),
    });
    type MyToolInput = z.infer<typeof MyToolInputSchema>;
    
    export class MyTool extends MCPTool<MyToolInput> {
      name = "my_tool";
      description = "Description of what my tool does.";
      schema = { // Matches Zod schema structure
        param1: { type: z.string(), description: "Description for parameter 1" },
      };
    
      async execute(args: MyToolInput): Promise<any> {
        console.error(`Handling tool request: ${this.name}`);
        const duckDBConn = getDuckDBConnection(); // Get dependency
        // ... implement logic using args and duckDBConn ...
        const result = { message: `Processed ${args.param1}` };
        return formatSuccessResponse(result);
      }
    }
    export default MyTool; // Ensure default export
    
  2. Automatic Discovery:

    • mcp-framework automatically discovers and registers tool/resource classes that are default-exported from files within the src/tools and src/resources directories.
    • Ensure your new class is the default export in its file.
  3. Test:

    • Run the server (make dev).
    • Check the startup logs to ensure your new tool/resource is listed.
    • Use an MCP client (like mcp-cli or the MCP Inspector) to call the tool or read the resource and verify its functionality.

Best Practices

  • Define clear input schemas using Zod for tools.
  • Handle errors gracefully within execute/read and return formatted error responses using formatErrorResponse (or throw errors).
  • Use the centralized configuration (src/config.ts) via getConfig() where needed.
  • Leverage the service initializers in src/services/ for dependencies like database connections.
  • Add logging (console.error) for visibility.

Recommended Servers

playwright-mcp

playwright-mcp

A Model Context Protocol server that enables LLMs to interact with web pages through structured accessibility snapshots without requiring vision models or screenshots.

Official
Featured
TypeScript
Magic Component Platform (MCP)

Magic Component Platform (MCP)

An AI-powered tool that generates modern UI components from natural language descriptions, integrating with popular IDEs to streamline UI development workflow.

Official
Featured
Local
TypeScript
Audiense Insights MCP Server

Audiense Insights MCP Server

Enables interaction with Audiense Insights accounts via the Model Context Protocol, facilitating the extraction and analysis of marketing insights and audience data including demographics, behavior, and influencer engagement.

Official
Featured
Local
TypeScript
VeyraX MCP

VeyraX MCP

Single MCP tool to connect all your favorite tools: Gmail, Calendar and 40 more.

Official
Featured
Local
graphlit-mcp-server

graphlit-mcp-server

The Model Context Protocol (MCP) Server enables integration between MCP clients and the Graphlit service. Ingest anything from Slack to Gmail to podcast feeds, in addition to web crawling, into a Graphlit project - and then retrieve relevant contents from the MCP client.

Official
Featured
TypeScript
Kagi MCP Server

Kagi MCP Server

An MCP server that integrates Kagi search capabilities with Claude AI, enabling Claude to perform real-time web searches when answering questions that require up-to-date information.

Official
Featured
Python
E2B

E2B

Using MCP to run code via e2b.

Official
Featured
Neon Database

Neon Database

MCP server for interacting with Neon Management API and databases

Official
Featured
Exa Search

Exa Search

A Model Context Protocol (MCP) server lets AI assistants like Claude use the Exa AI Search API for web searches. This setup allows AI models to get real-time web information in a safe and controlled way.

Official
Featured
Qdrant Server

Qdrant Server

This repository is an example of how to create a MCP server for Qdrant, a vector search engine.

Official
Featured