Postgresql & DuckDB

🚀 MCP Database Project Documentation

This project documentation provides a comprehensive guide to the MCP database project, including installation instructions, available tools and resources, development guidelines, and other important considerations.

🚀 Quick Start

To get started with the MCP database project, follow the installation steps below.

📦 Installation

1. Install Dependencies

npm install github:dwarvesf/mcp-db # Install the main package
npm install duckdb # Install the DuckDB extension
npx duckdb-install # Install DuckDB binary files (if required)

2. Configure Environment Variables

The project relies on the following environment variables:

3. Initialize the Database

To initialize DuckDB, run the following command:

npx github:dwarvesf/duckdb-insert --help # View help information

✨ Features

Available Tools

• duckdb_insert: Execute INSERT statements on a PostgreSQL database using DuckDB. Only INSERT type queries are allowed.
• duckdb_query: Execute read-only SQL queries directly on a PostgreSQL database (postgres_db) using DuckDB's postgres_query function. Automatically prefixes unbounded table names (e.g., my_table becomes postgres_db.public.my_table).
• duckdb_read_parquet: Query Parquet files on GCS using DuckDB (if GCS is configured).
• duckdb_update: Execute UPDATE statements on a PostgreSQL database using DuckDB.

Available Resources

• mcp://gcs/objects: List all objects in a specified GCS bucket.
• mcp://db/tables: List all tables and their column information in the configured PostgreSQL database.

📚 Documentation

Development Guide: Integrating New Tools or Resources

This project uses the mcp-framework. To add new tools or resources, follow these steps:

1. Create a New Class

• Create a new .ts file in the src/tools or src/resources directory.
• Define a class that extends MCPTool or MCPResource.
• Implement the required properties (name, description, schema for tools) and methods (execute method for tools or read method for resources).
• Use Zod for input validation in the schema property (for tools only).
• Initialize any dependencies (such as database connections or GCS clients) in the class constructor, typically obtaining configurations from src/services/ or 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 of parameter 1"),
});

type MyToolInput = z.infer<typeof MyToolInputSchema>;

export class MyTool extends MCPTool<MyToolInput> {
  name = "my_tool";
  description = "Describe the functionality of my tool.";
  schema = MyToolInputSchema;

  async execute(args: MyToolInput) {
    // Implement specific business logic here
    return formatSuccessResponse({
      message: `Successfully executed my_tool with parameters: ${args.param1}`,
    });
  };
}

// Export the tool instance (optional)
export const myTool = new MyTool();

2. Register the New Tool or Resource

Create or modify the src/tools/index.ts or src/resources/index.ts file in the project root directory to export the new tool or resource:

// Example: index.ts file in the tools directory
export { myTool } from "./my_tool";

3. Use the New Tool or Resource

The new tool or resource will be automatically registered when the application starts and can be called via the MCP framework's API.

⚠️ Important Note

• Ensure that all environment variables are correctly configured, especially in a production environment.
• When handling database connections, use appropriate error handling and connection pool management.
• For GCS operations, ensure that the Google Cloud SDK is installed and configured, and that the correct credentials are set.