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

> Discover the roles of mcp_main.py and functions.py in the MCP PostgreSQL Ops architecture. functions.py handles data access while mcp_main.py provides MCP tools via FastMCP.

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

---

**[`functions.py`](https://github.com/call518/mcp-postgresql-ops/blob/main/functions.py) serves as the low-level data-access layer handling PostgreSQL connections and query execution, while [`mcp_main.py`](https://github.com/call518/mcp-postgresql-ops/blob/main/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`](https://github.com/call518/mcp-postgresql-ops/blob/main/functions.py)

Located at [`src/mcp_postgresql_ops/functions.py`](https://github.com/call518/mcp-postgresql-ops/blob/main/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`](https://github.com/call518/mcp-postgresql-ops/blob/main/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.

```python
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`](https://github.com/call518/mcp-postgresql-ops/blob/main/mcp_main.py)

Located at [`src/mcp_postgresql_ops/mcp_main.py`](https://github.com/call518/mcp-postgresql-ops/blob/main/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`](https://github.com/call518/mcp-postgresql-ops/blob/main/__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`](https://github.com/call518/mcp-postgresql-ops/blob/main/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`](https://github.com/call518/mcp-postgresql-ops/blob/main/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.

```python

# 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`](https://github.com/call518/mcp-postgresql-ops/blob/main/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`](https://github.com/call518/mcp-postgresql-ops/blob/main/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`](https://github.com/call518/mcp-postgresql-ops/blob/main/functions.py) to focus purely on "how to talk to PostgreSQL" while [`mcp_main.py`](https://github.com/call518/mcp-postgresql-ops/blob/main/mcp_main.py) determines "what capabilities we expose to users."

## Summary

- **[`functions.py`](https://github.com/call518/mcp-postgresql-ops/blob/main/functions.py)** at [`src/mcp_postgresql_ops/functions.py`](https://github.com/call518/mcp-postgresql-ops/blob/main/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`](https://github.com/call518/mcp-postgresql-ops/blob/main/mcp_main.py)** at [`src/mcp_postgresql_ops/mcp_main.py`](https://github.com/call518/mcp-postgresql-ops/blob/main/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`](https://github.com/call518/mcp-postgresql-ops/blob/main/functions.py) and [`mcp_main.py`](https://github.com/call518/mcp-postgresql-ops/blob/main/mcp_main.py)?

[`functions.py`](https://github.com/call518/mcp-postgresql-ops/blob/main/functions.py) provides the data-access layer that handles raw PostgreSQL interactions through the `asyncpg` driver, while [`mcp_main.py`](https://github.com/call518/mcp-postgresql-ops/blob/main/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`](https://github.com/call518/mcp-postgresql-ops/blob/main/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`](https://github.com/call518/mcp-postgresql-ops/blob/main/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`](https://github.com/call518/mcp-postgresql-ops/blob/main/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`](https://github.com/call518/mcp-postgresql-ops/blob/main/functions.py) independently of the MCP server?

Yes, the utilities in [`src/mcp_postgresql_ops/functions.py`](https://github.com/call518/mcp-postgresql-ops/blob/main/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.