A Model Context Protocol server that provides tools for interacting with databases, including PostgreSQL, DuckDB, and Google Cloud Storage Parquet files.
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).
make commands).
├── 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.jsonClone the repository:
git clone <repository-url>
cd mcp-dbInstall dependencies (using Devbox is recommended for consistency):
devbox install
# Or using npm directly if not using Devbox
# npm installCopy .env.example to .env and fill in your environment variables.
cp .env.example .env
# Edit .env with your detailsBuild the project:
# Using make (requires Devbox)
make build
# Or using npm directly
# npm run buildConfigure 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.
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.
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 buildTo 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 8080To 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" }
}
}
}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" }
}
}
}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 3001duckdb_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.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.This project uses mcp-framework. To add a new tool or resource:
Create the Class:
.ts file in src/tools/ or src/resources/.MCPTool or MCPResource.name, description, schema for tools) and methods (execute for tools, read for resources).schema property for input validation (tools).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 exportAutomatic Discovery:
mcp-framework automatically discovers and registers tool/resource classes that are default-exported from files within the src/tools and src/resources directories.default export in its file.Test:
make dev).mcp-cli or the MCP Inspector) to call the tool or read the resource and verify its functionality.execute/read and return formatted error responses using formatErrorResponse (or throw errors).src/config.ts) via getConfig() where needed.src/services/ for dependencies like database connections.console.error) for visibility.