MCP For Postgres Db Demo

An MCP for your Postgres DB

This repo accompanies the talk "An MCP for your Postgres DB" at Posette 2026.

It contains:

• Four FastMCP servers that expose the same PostgreSQL bee observation database with progressively stricter tool designs — from free-form SQL to fully typed, annotated tools.
• A PydanticAI agent that connects to any server over Streamable HTTP, with tool filtering for evaluation.
• An evaluation harness that runs test cases and measures tool selection accuracy.

Table of contents

• Setup
• Database setup
• Run the MCP servers
• Run agents
• Run evaluations
• Deployment

Server levels

Setup

The easiest way to get a local PostgreSQL + PostGIS instance is to use the included dev container, which starts a postgis/postgis container automatically:

1. Open this repo in GitHub Codespaces or VS Code Dev Containers (requires Docker Desktop).
2. The dev container provides PostgreSQL on localhost:5432 with user admin / password postgres.
3. Continue with the steps below inside the dev container terminal.

Prerequisites

If not using the dev container, install these manually:

• Python 3.12+
• PostgreSQL 14+ with PostGIS
• uv (recommended) or pip

Install dependencies

uv sync

Environment variables

Copy .env.sample to .env and fill in the values:

cp .env.sample .env

Required for servers:

Required for agents:

Database setup

1. Create the database and enable PostGIS:

   uv run scripts/setup_postgres_database.py
   

2. Ingest the bee observations CSV:

   uv run scripts/ingest_observations.py --csv data/observations.csv
   

Run the MCP servers

Each server runs independently on port 8000. Start one at a time:

# Level 1: Free-form SQL (maximum flexibility, maximum risk)
uv run servers/level1_freeform.py

# Level 2: Read-only SQL (parsed with pglast, non-SELECT rejected)
uv run servers/level2_readonly.py

# Level 3: Scoped WHERE clause (server controls SELECT/FROM)
uv run servers/level3_scoped.py

# Level 4: Fully typed tools (no SQL surface, MCP annotations)
uv run servers/level4_typed.py

All servers expose a Streamable HTTP endpoint at http://localhost:8000/mcp.

Run agents

Start a server first, then run the Copilot SDK agent:

uv run agents/copilotsdk_agent.py --query "What bees are active near San Francisco in March?"

# Show tool calls and reasoning:
uv run agents/copilotsdk_agent.py --show-tool-calls --show-reasoning --query "Has anyone seen a sweat bee near Austin?"

Agent options

Run evaluations

Start a level 4 server, then run the eval harness:

uv run servers/level4_typed.py &
uv run evals/runner.py

Run specific cases:

uv run evals/runner.py --cases species_by_common_name,observations_near_location

Results are saved to evals/runs/results.json.

Deployment

This project supports deployment to Azure using the Azure Developer CLI.

1. Sign in:

   azd auth login
   

2. Provision and deploy:

   azd up
   

This deploys the app on Azure Container Apps with Azure PostgreSQL Flexible Server.

Resources

• Video recording: An MCP for your Postgres DB - Accompanying presentation for this repo
• Model Context Protocol — the MCP specification and documentation
• Tool Annotations as Risk Vocabulary — MCP blog post on what annotation hints can and can't do
• MCP Toolbox for Databases — Google's open-source MCP server for databases, with built-in connection pooling, authentication, and tool construction from SQL templates
• Designing SQL Tools for AI Agents — Arcade's taxonomy of Exploratory vs Operational SQL tools
• Context Engineering for MCP Servers — MotherDuck's Posette talk on MCP server design lessons