Understanding `mcp_main.py` and `functions.py` in the MCP PostgreSQL Ops Architecture

functions.py serves as the low-level data-access layer handling PostgreSQL connections and query execution, while mcp_main.py acts as the high-level presentation layer that exposes these capabilities as MCP tools via a FastMCP server.

The call518/mcp-postgresql-ops repository implements a clean layered architecture for PostgreSQL database operations through the Model Context Protocol (MCP). At the heart of this MCP PostgreSQL Ops architecture are two critical files that separate database logic from protocol handling. Understanding their distinct responsibilities is essential for extending the server or troubleshooting database interactions.

Database Abstractions: The Role of functions.py

Located at src/mcp_postgresql_ops/functions.py, this module encapsulates all direct PostgreSQL interactions. It abstracts the raw asyncpg driver behind reusable async utilities, acting as the service layer for the entire application.

Core Query Execution and Formatting

The file provides execute_query() (lines 58-81) for parameterized query execution and format_table_data() (lines 30-68) for result presentation. These functions handle connection pooling, error handling, and version-aware query construction through helpers like get_server_version() and sanitize_connection_info(). The module collaborates with version_compat.py to provide version-specific query strings for different PostgreSQL versions.

Security and Connection Utilities

Beyond execution, the module includes security-aware utilities such as password masking in connection strings. This ensures sensitive credentials never leak into logs or error messages while maintaining efficient connection reuse across the application lifecycle.

from mcp_postgresql_ops.functions import execute_query

# Direct database interaction bypassing the MCP layer

rows = await execute_query(
    "SELECT pid, usename, query FROM pg_stat_activity WHERE state = $1",
    ["active"]
)

MCP Tool Definitions: The Role of mcp_main.py

Located at src/mcp_postgresql_ops/mcp_main.py, this file defines the public API surface that MCP clients consume. It bridges the gap between low-level database operations and the MCP protocol, and can be launched via python -m mcp_postgresql_ops which delegates to this module through __main__.py.

FastMCP Server Bootstrapping

Lines 84-87 instantiate FastMCP("mcp-postgresql-ops") and configure critical infrastructure including logging handlers, static-token authentication middleware, and the prompt template loaded from prompt_template.md.

Tool Registration and Orchestration

The @mcp.tool() decorator (first implemented at line 98) registers async functions as RPC-callable endpoints. Each tool orchestrates calls to functions.py helpers. For example, get_database_list (lines 58-61) builds a SQL query and delegates execution to await execute_query(query), while get_lock_monitoring provides real-time lock statistics using the same abstraction pattern.

# In src/mcp_postgresql_ops/mcp_main.py

@mcp.tool()
async def get_custom_metric() -> str:
    query = "SELECT count(*) AS total FROM my_schema.my_table"
    result = await execute_query(query)
    return format_table_data(result, "Custom Metric")

Architectural Execution Flow

When an MCP client invokes a tool, the request flows through distinct architectural layers:

1. Request Reception – The FastMCP server in mcp_main.py receives the RPC call and routes it to the appropriate decorated tool function.
2. Input Validation – The tool validates and filters input parameters, building the necessary SQL query string.
3. Database Delegation – The tool calls helpers from functions.py, such as await execute_query(query, params) (lines 58-81), isolating database-specific logic from the tool definition.
4. Result Formatting – Raw database results are processed through format_table_data() (lines 30-68) to generate human-readable tables.
5. Response Delivery – The formatted string returns to the MCP client, completing the request lifecycle.

This separation allows functions.py to focus purely on "how to talk to PostgreSQL" while mcp_main.py determines "what capabilities we expose to users."

Summary

• functions.py at src/mcp_postgresql_ops/functions.py provides the data-access layer, abstracting asyncpg operations including connection management, query execution, and result formatting.
• mcp_main.py at src/mcp_postgresql_ops/mcp_main.py serves as the presentation layer, bootstrapping the FastMCP server (lines 84-87) and registering tools via the @mcp.tool() decorator.
• The execute_query function (lines 58-81) and format_table_data (lines 30-68) enable consistent, reusable database interactions across all MCP tools.
• Security utilities like sanitize_connection_info ensure credential safety without compromising functionality.
• Static-token authentication and logging configuration reside exclusively in the MCP layer, keeping database utilities protocol-agnostic.

Frequently Asked Questions

What is the relationship between functions.py and mcp_main.py?

functions.py provides the data-access layer that handles raw PostgreSQL interactions through the asyncpg driver, while mcp_main.py consumes these utilities to implement MCP-compliant tool endpoints. This separation allows database logic to be tested independently of the MCP transport layer, following clean architecture principles.

How do I add a new database tool to the MCP server?

Add a new async function to src/mcp_postgresql_ops/mcp_main.py decorated with @mcp.tool(), then implement the database logic by calling helpers like execute_query() from functions.py. Return the formatted result using format_table_data() to maintain consistent output styling across all tools.

Where is the FastMCP server instantiated in the codebase?

The FastMCP instance is created at lines 84-87 of src/mcp_postgresql_ops/mcp_main.py with the server identifier "mcp-postgresql-ops". This initialization sequence also configures logging infrastructure and static-token authentication before any tools are registered.

Can I use functions.py independently of the MCP server?

Yes, the utilities in src/mcp_postgresql_ops/functions.py can be imported and used directly in other Python applications requiring PostgreSQL connectivity. These modules abstract the asyncpg driver without dependencies on the MCP protocol or FastMCP framework, making them reusable in standalone scripts or other services.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}