# Implementing Custom MCP Tools in the MCP-PostgreSQL-Ops Framework: A Complete Guide

> Learn to implement custom MCP tools in MCP-PostgreSQL-Ops. Decorate async functions, use helper functions for database ops, and register your tool for LLM discovery.

- Repository: [JungJungIn/mcp-postgresql-ops](https://github.com/call518/mcp-postgresql-ops)
- Tags: how-to-guide
- Published: 2026-02-26

---

**To implement custom MCP tools in MCP-PostgreSQL-Ops, create an async function decorated with `@mcp.tool()` in [`mcp_main.py`](https://github.com/call518/mcp-postgresql-ops/blob/main/mcp_main.py), use helper functions from [`functions.py`](https://github.com/call518/mcp-postgresql-ops/blob/main/functions.py) for database operations, and register the tool in [`prompt_template.md`](https://github.com/call518/mcp-postgresql-ops/blob/main/prompt_template.md) for LLM discovery.**

The MCP-PostgreSQL-Ops framework provides a FastMCP-based server for PostgreSQL monitoring and operations. Implementing custom MCP tools allows you to extend the server's capabilities with organization-specific monitoring queries while maintaining the framework's read-only safety guarantees and version-aware SQL generation.

## Understanding the MCP-PostgreSQL-Ops Architecture

Before implementing custom MCP tools, understand the core components that handle tool registration and execution.

### Core Architectural Components

The framework relies on several key files:

- **[`src/mcp_postgresql_ops/mcp_main.py`](https://github.com/call518/mcp-postgresql-ops/blob/main/src/mcp_postgresql_ops/mcp_main.py)**: Central registry where all tools are defined and decorated with `@mcp.tool()`.
- **[`src/mcp_postgresql_ops/functions.py`](https://github.com/call518/mcp-postgresql-ops/blob/main/src/mcp_postgresql_ops/functions.py)**: Database connection helpers (`get_db_connection`, `execute_query`, `execute_single_query`) and formatting utilities (`format_table_data`, `format_bytes`).
- **[`src/mcp_postgresql_ops/version_compat.py`](https://github.com/call518/mcp-postgresql-ops/blob/main/src/mcp_postgresql_ops/version_compat.py)**: Version-aware query builders (`PostgreSQLVersion`, `VersionAwareQueries`) that adapt SQL to different PostgreSQL versions.
- **[`src/mcp_postgresql_ops/prompt_template.md`](https://github.com/call518/mcp-postgresql-ops/blob/main/src/mcp_postgresql_ops/prompt_template.md)**: Human-readable catalog of available tools shown to the LLM for discovery.
- **[`pyproject.toml`](https://github.com/call518/mcp-postgresql-ops/blob/main/pyproject.toml)**: Declares package dependencies including `fastmcp` and `asyncpg`.

## Step-by-Step Process for Implementing Custom MCP Tools

Follow this workflow to add new monitoring capabilities to the framework.

### 1. Define the Tool Purpose and Signature

Determine what PostgreSQL metrics your tool will expose. Define an async function with primitive parameters (strings, ints, bools) that can be supplied from LLM prompts.

### 2. Implement the Tool in mcp_main.py

Add your async function to [`src/mcp_postgresql_ops/mcp_main.py`](https://github.com/call518/mcp-postgresql-ops/blob/main/src/mcp_postgresql_ops/mcp_main.py) and decorate it with `@mcp.tool()`:

```python
@mcp.tool()
async def get_large_tables(min_size_mb: int = 100, database_name: str = None) -> str:
    """
    [Tool Purpose]: List tables whose total size exceeds *min_size_mb* megabytes.
    """
    query = """
        SELECT
            schemaname,
            tablename,
            pg_total_relation_size(format('%I.%I', schemaname, tablename)) / 1024 / 1024 AS size_mb
        FROM pg_catalog.pg_tables
        WHERE pg_total_relation_size(format('%I.%I', schemaname, tablename)) >= $1 * 1024 * 1024
        ORDER BY size_mb DESC
    """
    rows = await execute_query(query, [min_size_mb], database=database_name)
    return format_table_data(rows, f"Tables > {min_size_mb} MB")

```

Key implementation details:

- Use **`execute_query`** from [`functions.py`](https://github.com/call518/mcp-postgresql-ops/blob/main/functions.py) so connection handling and logging remain consistent.
- Return **`str`**; FastMCP expects a string payload for LLM consumption.
- Keep the implementation **read-only** — execute only `SELECT` statements.

### 3. Handle Version Compatibility

For features requiring specific PostgreSQL versions, use helpers from [`version_compat.py`](https://github.com/call518/mcp-postgresql-ops/blob/main/version_compat.py):

```python
from .version_compat import PostgreSQLVersion, get_postgresql_version

@mcp.tool()
async def get_replication_stats(database_name: str = None) -> str:
    pg_version = await get_postgresql_version()
    if not pg_version.is_modern:
        return "Error: This tool requires PostgreSQL 12 or newer."
    # Proceed with version-specific query...

```

### 4. Update the Prompt Template

Register the tool in [`src/mcp_postgresql_ops/prompt_template.md`](https://github.com/call518/mcp-postgresql-ops/blob/main/src/mcp_postgresql_ops/prompt_template.md) so LLMs can discover it:

```markdown
34. **get_large_tables**: List tables larger than a size threshold (e.g., `get_large_tables(min_size_mb=500)`).

```

### 5. Test the Implementation

Start the server to verify tool registration:

```bash
python -m mcp_postgresql_ops

```

Look for the log message *"Registering tool: get_large_tables"* to confirm FastMCP has loaded your custom tool.

## Best Practices for Custom MCP Tool Development

Follow these guidelines to maintain framework integrity when implementing custom MCP tools:

- **Maintain read-only semantics**: Custom tools must execute only `SELECT` statements. Never perform DDL or DML operations to preserve safety guarantees when LLMs invoke these tools.
- **Use framework helpers**: Always route database calls through `execute_query` or `execute_single_query` from [`functions.py`](https://github.com/call518/mcp-postgresql-ops/blob/main/functions.py) to ensure consistent connection pooling, error handling, and logging.
- **Validate parameters**: FastMCP converts parameters from strings; implement early validation and return clear error messages for out-of-range values.
- **Respect version compatibility**: Use `PostgreSQLVersion` checks or `VersionAwareQueries` when accessing features unavailable in older PostgreSQL releases.
- **Optimize performance**: Add `LIMIT` clauses and avoid full-table scans unless necessary; remember that these tools may be invoked frequently by automated LLM workflows.
- **Document thoroughly**: Include inline docstrings with `[Tool Purpose]` tags and update [`prompt_template.md`](https://github.com/call518/mcp-postgresql-ops/blob/main/prompt_template.md) so the LLM understands when to invoke your tool.

## Complete Code Example: Building a Custom Monitoring Tool

Here is a production-ready example implementing a version-aware table size inspector:

```python

# src/mcp_postgresql_ops/mcp_main.py

from .functions import execute_query, format_table_data
from .version_compat import PostgreSQLVersion, get_postgresql_version

@mcp.tool()
async def get_large_tables(min_size_mb: int = 100, database_name: str = None) -> str:
    """
    [Tool Purpose]: List tables whose total size exceeds *min_size_mb* megabytes.
    """
    # Ensure the server supports pg_total_relation_size (PostgreSQL 12+)

    pg_version: PostgreSQLVersion = await get_postgresql_version()
    if not pg_version.is_modern:
        return "Error: This tool requires PostgreSQL 12 or newer."

    query = """
        SELECT
            schemaname,
            tablename,
            pg_total_relation_size(format('%I.%I', schemaname, tablename)) / 1024 / 1024 AS size_mb
        FROM pg_catalog.pg_tables
        WHERE pg_total_relation_size(format('%I.%I', schemaname, tablename)) >= $1 * 1024 * 1024
        ORDER BY size_mb DESC
    """
    rows = await execute_query(query, [min_size_mb], database=database_name)
    return format_table_data(rows, f"Tables > {min_size_mb} MB")

```

After adding this code and updating **[`prompt_template.md`](https://github.com/call518/mcp-postgresql-ops/blob/main/prompt_template.md)**, the tool becomes available to any LLM client connected to your MCP-PostgreSQL-Ops server.

## Summary

- **Implementing custom MCP tools** requires creating async functions decorated with `@mcp.tool()` in [`src/mcp_postgresql_ops/mcp_main.py`](https://github.com/call518/mcp-postgresql-ops/blob/main/src/mcp_postgresql_ops/mcp_main.py).
- Use helper functions from [`src/mcp_postgresql_ops/functions.py`](https://github.com/call518/mcp-postgresql-ops/blob/main/src/mcp_postgresql_ops/functions.py) (`execute_query`, `format_table_data`) to maintain consistent database access and formatting.
- Respect **read-only semantics** by executing only `SELECT` statements and avoiding DDL/DML operations.
- Handle **version compatibility** using `PostgreSQLVersion` and `VersionAwareQueries` from [`version_compat.py`](https://github.com/call518/mcp-postgresql-ops/blob/main/version_compat.py) when targeting features unavailable in older PostgreSQL releases.
- Register tools in [`prompt_template.md`](https://github.com/call518/mcp-postgresql-ops/blob/main/prompt_template.md) so LLMs can discover and invoke them correctly.
- Test implementations by starting the server and verifying the "Registering tool" log message appears.

## Frequently Asked Questions

### What programming language is used for implementing custom MCP tools in this framework?

The MCP-PostgreSQL-Ops framework is built using **Python** and leverages the **FastMCP** library for tool registration. All custom tools must be implemented as Python async coroutines decorated with `@mcp.tool()` and placed in the appropriate module files.

### Can custom MCP tools modify data in PostgreSQL databases?

No. According to the framework's design principles, custom tools must maintain **read-only semantics** and execute only `SELECT` statements. The framework explicitly prohibits DDL (Data Definition Language) and DML (Data Manipulation Language) operations to ensure safety when LLMs invoke these tools autonomously.

### How does the framework handle different PostgreSQL versions when implementing custom tools?

The framework provides version-aware utilities in [`src/mcp_postgresql_ops/version_compat.py`](https://github.com/call518/mcp-postgresql-ops/blob/main/src/mcp_postgresql_ops/version_compat.py). You should use the `PostgreSQLVersion` class and `VersionAwareQueries` to detect the server version and adapt SQL queries accordingly, ensuring compatibility with both legacy and modern PostgreSQL releases.

### Where should I register a new custom tool so that LLMs can discover it?

After implementing the tool in [`mcp_main.py`](https://github.com/call518/mcp-postgresql-ops/blob/main/mcp_main.py), you must add a description entry to **[`src/mcp_postgresql_ops/prompt_template.md`](https://github.com/call518/mcp-postgresql-ops/blob/main/src/mcp_postgresql_ops/prompt_template.md)**. This markdown file serves as the human-readable catalog that LLMs use to understand available tools and their invocation patterns, making your custom tool discoverable by AI agents.