Recently, I built a fully functional MCP (Model Control Plane) server powered by PostgreSQL—something I found incredibly useful when trying to make LLMs interact intelligently with structured data. If you've ever struggled to bolt on a REST API for every new resource or wished your AI agent could just “understand” your backend without glue code, this article is for you. Traditional APIs feel like a bottleneck when building modern, adaptive systems—especially when you’re trying to connect them to large language models or automation flows. That’s where MCP shines.

Instead of rigid endpoints and verbose documentation, MCP offers a dynamic, model-based architecture that’s inherently more compatible with how LLMs operate. In this guide, I’ll show you how to build an MCP server backed by PostgreSQL using FastMCP and psycopg2, explain the pain points it solves and walk you through the key steps to get it running—so you can focus on building smarter systems without drowning in boilerplate. Let’s dive in.

What is an MCP Server?

Model Context Protocol (MCP) is an open-source set of rules, developed by Anthropic, that establishes a standard way for applications to provide relevant context to Large Language Models (LLMs).

Envision MCP as a universal connector, much like a USB-C port for AI. Just as USB-C allows various devices to connect to a wide range of accessories in a standardized manner, MCP enables AI models to seamlessly interface with diverse data sources and tools. This standardization simplifies the process of building AI agents and complex workflows, offering pre-built integrations and the flexibility to work with different LLM providers while prioritizing data security within your own infrastructure.

Without interfaces like MCP, LLMs are limited to their built-in capabilities and training data. With MCP, they can be empowered to:

• Read files and databases

• Execute commands

• Access APIs

• Interact with local tools

• And more!

All of this happens with user oversight and permission, making it both powerful and secure.

MCP Architecture

MCP has the following components

• MCP Hosts: Programs like Claude Desktop, IDEs, or AI tools that want to access data through MCP

• MCP Clients: Protocol clients that maintain 1:1 connections with servers

• MCP Servers: Lightweight programs that each expose specific capabilities through the standardized Model Context Protocol

• Local Data Sources: Your computer’s files, databases, and services that MCP servers can securely access

• Remote Services: External systems available over the internet (e.g., through APIs) that MCP servers can connect to

Core MCP Concepts

MCP servers can provide three main types of capabilities:

1. Resources: File-like data that can be read by clients (like API responses or file contents)

2. Tools: Functions that can be called by the LLM (with user approval)

3. Prompts: Pre-written templates that help users accomplish specific tasks

MCP Resource

Resources are MCP’s way of exposing read-only data to LLMs. A resource is anything that has content that can be read, such as:

• Files on your computer

• Database records

• API responses

• Application data

• System information

Each resource has:

• A unique URI (like [file:///example.txt](file:///example.txt) or database://users/123)

• A display name

• Optional metadata (description, MIME type)

• Content (text or binary data)

MCP Tools

Tools are executable functions that LLMs can call to perform actions or retrieve dynamic information. Unlike resources, which are read-only, and prompts, which structure LLM interactions, tools allow LLMs to actively do things like calculate values, make API calls, or modify data.

Tools enable LLMs to interact with systems and perform actions.

What are MCP Prompts?

Prompts in MCP are structured templates that servers provide to standardize interactions with language models. Unlike resources which provide data, or tools which execute actions, prompts define reusable message sequences and workflows that help guide LLM behavior in consistent, predictable ways.

In this article we are going to create mcp server for postgreSQL step by step

Setting Up Your Development Environment

First, let’s install uv and set up our Python project and environment:

powershell -ExecutionPolicy ByPass -c "irm https://astral.sh/uv/install.ps1 | iex"

Make sure to restart your terminal afterwards to ensure that the uv command gets picked up.

Create Project Directory

Now, let’s create and set up our project:

# Create a new directory for our project
uv init mcp-server
cd mcp-server

# Create virtual environment and activate it
uv venv
.venv\Scripts\activate

# Install dependencies
uv add mcp[cli] httpx

# Create our server file
new-item server.py

Now let’s dive into building your server.

Building your server

Update the server.py according to the following code:

1. Import the packages

2. Database wrapper class for database connection and database queries

3. Define App Context

4. Define app lifespan and pass this lifespan to MCP instance along with MCP Server name

from contextlib import asynccontextmanager
from collections.abc import AsyncIterator
from dataclasses import dataclass

import asyncpg
from mcp.server.fastmcp import FastMCP, Context

# Async PostgreSQL wrapper using asyncpg
class Database:
    def __init__(self, pool: asyncpg.Pool):
        self.pool = pool

    @classmethod
    async def connect(cls) -> "Database":
        pool = await asyncpg.create_pool(
            user="#user",
            password="#your_password",
            database="Your_database_name",
            host="#database_host",
            port='#Port_number',
        )
        return cls(pool)

    async def disconnect(self):
        await self.pool.close()

    async def query(self, query: str) -> list:
        async with self.pool.acquire() as conn:
            try:
                return await conn.fetch(query)
            except Exception as e:
                print(f"Query error: {e}")
                return []

    async def fetch_schema(self) -> list:
        query = """
        SELECT table_name FROM information_schema.tables
        WHERE table_schema = 'public'
        """
        return await self.query(query)


@dataclass
class AppContext:
    db: Database


@asynccontextmanager
async def app_lifespan(server: FastMCP) -> AsyncIterator[AppContext]:
    db = await Database.connect()
    try:
        yield AppContext(db=db)
    finally:
        await db.disconnect()


mcp = FastMCP("PostgresMCPServer", lifespan=app_lifespan)

The FastMCP class uses Python type hints and docstrings to automatically generate tool definitions, making it easy to create and maintain MCP tools.

Implementing MCP tools

The tool execution handler is responsible for actually executing the logic of each tool. Let’s add it:

@mcp.tool("fetch_schema")
async def fetch_schema(ctx: Context) -> str:
    db = ctx.request_context.lifespan_context.db
    schema = await db.fetch_schema()
    return str([record["table_name"] for record in schema])


@mcp.tool("fetch_all_tables")
async def fetch_all_tables(ctx: Context) -> str:
    db = ctx.request_context.lifespan_context.db
    query = "SELECT * FROM information_schema.tables WHERE table_schema='public'"
    tables = await db.query(query)
    return str(tables)


@mcp.tool("run_query")
async def run_query(ctx: Context, query: str) -> str:
    db = ctx.request_context.lifespan_context.db
    result = await db.query(query)
    return str(result)

​Running the server

Finally, let’s initialize and run the server:

if __name__ == "__main__":
    # Initialize and run the server
    mcp.run()

Your server is complete! Run uv run server.py to confirm that everything’s working.

Let’s now test your server from an existing MCP host, Claude for Desktop.

Testing your server with Claude for Desktop

There are several ways to test your MCP server. One way is with Claude Desktop, and you can also use the MCP Inspector tool to test all capabilities during development.

Setting Up Claude Desktop

Here are the steps for setting up an MCP server in Claude Desktop:

1. Install Claude for Desktop if you haven’t already

2. Open Claude and access Settings

   You can access it in your [path of claude desktop installation]/claude_desktop_config.json

3. Edit configuration

    {
      "mcpServers": {
        "PostgresMCPServer": {
          "command": "uv",
          "args": [
            "run",
            "--with",
            "mcp[cli]",
            "mcp",
            "run",
            "C:\\ABSOLUTE\\PATH\\TO\\PARENT\\FOLDER\\server.py"
          ]
        }
      }
    }
   

This tells Claude for Desktop:

1. There’s an MCP server named “weather”

2. To launch it by running uv command with the following arguments

Save the file, and restart Claude for Desktop.

Test with Claude Desktop

Let’s make sure Claude for Desktop is picking up the 3 tools we’ve exposed in our PostgresMCPServer server. You can do this by looking for the setting icon

After clicking on the setting icon, you should see the MCP server listed:

After clicking the PostgresMCPServer, you should see the MCP tools listed:

If you can see the tools here, you can now test your server

We will test the server using the following commands:

1. Retrieve information on all tables within the database.

2. Use Claude Desktop to summarize the relationships between the tables.

3. Use Claude to generate a bar graph illustrating the comparison between events and registration counts.

Finally…

Building an MCP server powered by PostgreSQL offers a dynamic and efficient way to integrate large language models with structured data. By leveraging the Model Context Protocol, developers can create adaptive systems that bypass the limitations of traditional APIs, allowing AI agents to interact seamlessly with various data sources and tools. This approach not only simplifies the development process but also enhances the capabilities of AI models, enabling them to perform complex tasks with greater ease and security. By following the steps outlined in this guide, you can set up your own MCP server and unlock new possibilities for smarter, more responsive systems.