How to Set Up Model Context Protocol Server Providing Live

Introduction

When you’re building applications that interact with databases, AI coding assistants often struggle to generate accurate code because they lack visibility into your actual database structure. The Model Context Protocol (MCP) solves this problem by enabling your AI tools to query live database schema information directly. This guide walks you through setting up an MCP server that exposes your database schema to AI assistants like Claude, Cursor, or other MCP-compatible tools.

The Model Context Protocol is an open standard that allows AI systems to connect to external data sources and tools. By running an MCP server for your database, you give AI assistants the ability to ask questions about your tables, columns, relationships, and indexes in real-time.

Prerequisites

Before setting up your MCP server, ensure you have Python 3.10 or later installed. You’ll also need access to a database—PostgreSQL, MySQL, SQLite, or any other database supported by SQLAlchemy. This guide uses Python with FastMCP, which provides a straightforward way to create MCP servers.

Install the required dependencies:

uv venv
uv pip install fastmcp sqlalchemy pydantic

Create a new directory for your project and initialize it:

mkdir db-mcp-server && cd db-mcp-server
uv init

Why Live Schema Context Matters

Without live schema context, AI assistants generate code based on guesses about your database structure. They might reference columns that don’t exist, use wrong data types, miss required NOT NULL columns, or ignore your foreign key relationships entirely.

With an MCP server providing schema context, the same assistant can:

Write accurate JOIN queries using actual column names
Respect nullable constraints when generating INSERT statements
Understand your relationships and suggest correct ON DELETE behavior
Detect schema mismatches between ORM models and the actual database
Generate migration scripts that reflect the real current state

The difference in code quality is significant—particularly on databases that have evolved through many migrations and diverged from any documentation.

Building the MCP Server

Create a file named server.py in your project directory. This server will connect to your database and expose schema information through MCP tools.

from fastmcp import FastMCP
from sqlalchemy import create_engine, inspect
from typing import List, Dict, Any
import os

# Initialize FastMCP server
mcp = FastMCP("Database Schema Server")

# Database connection string from environment
DATABASE_URL = os.getenv("DATABASE_URL", "postgresql://user:password@localhost:5432/mydb")

def get_db_inspector():
    """Create a database inspector for schema queries."""
    engine = create_engine(DATABASE_URL)
    return inspect(engine)

@mcp.tool()
def get_tables() -> List[str]:
    """Get all table names in the database."""
    inspector = get_db_inspector()
    return inspector.get_table_names()

@mcp.tool()
def get_table_schema(table_name: str) -> Dict[str, Any]:
    """Get complete schema for a specific table."""
    inspector = get_db_inspector()

    columns = inspector.get_columns(table_name)
    primary_keys = inspector.get_pk_constraint(table_name)
    foreign_keys = inspector.get_foreign_keys(table_name)
    indexes = inspector.get_indexes(table_name)

    return {
        "table_name": table_name,
        "columns": columns,
        "primary_key": primary_keys,
        "foreign_keys": foreign_keys,
        "indexes": indexes
    }

@mcp.tool()
def get_all_schemas() -> Dict[str, Any]:
    """Get schema information for all tables."""
    inspector = get_db_inspector()
    tables = inspector.get_table_names()

    schemas = {}
    for table in tables:
        schemas[table] = inspector.get_columns(table)

    return schemas

if __name__ == "__main__":
    mcp.run(transport="stdio")

This server exposes three tools: get_tables lists all available tables, get_table_schema returns detailed information about a specific table including columns, keys, and indexes, and get_all_schemas provides an overview of your entire database structure.

Configuring the MCP Server

To connect your AI assistant to this server, you need to configure the MCP client. The configuration varies depending on your AI tool, but most support a mcp.json configuration file.

Create a file named mcp.json in your project:

{
  "mcpServers": {
    "db-schema": {
      "command": "python",
      "args": ["/path/to/your/db-mcp-server/server.py"],
      "env": {
        "DATABASE_URL": "postgresql://user:password@localhost:5432/mydb"
      }
    }
  }
}

The exact location for this configuration file depends on your AI assistant. For Claude Desktop, place it in ~/Library/Application Support/Claude/mcp.json. For Cursor, check the settings under the MCP configuration section.

Configuring for Multiple Environments

In practice, you’ll want different configurations for development, staging, and production databases. Use environment-specific configuration files:

{
  "mcpServers": {
    "db-schema-dev": {
      "command": "python",
      "args": ["/path/to/server.py"],
      "env": {
        "DATABASE_URL": "postgresql://dev_user:dev_pass@localhost:5432/mydb_dev"
      }
    },
    "db-schema-staging": {
      "command": "python",
      "args": ["/path/to/server.py"],
      "env": {
        "DATABASE_URL": "postgresql://staging_user:staging_pass@staging-host:5432/mydb_staging"
      }
    }
  }
}

Having both servers registered lets you ask your AI assistant “check the staging schema for this table” and get an accurate answer without manually connecting to each environment.

Using the Server with AI Assistants

Once configured, your AI assistant can now query your database schema directly. Here’s how this works in practice:

Asking about table structure:

“What columns does the users table have?”

The AI will call get_table_schema("users") and receive detailed column information including names, types, nullable status, and default values.

Finding relationships:

“What foreign keys exist in the orders table?”

The AI uses the get_table_schema tool to retrieve foreign key constraints and explain how tables relate to each other.

Generating accurate queries:

“Write a SQL query to find all orders placed by users who signed up in the last 30 days.”

The AI now understands both the users and orders table structures, enabling it to write accurate JOIN queries with correct column references.

Validating ORM models:

“Does my SQLAlchemy User model match the actual users table?”

With schema access, the AI compares your model definition against the live schema and flags any mismatches—a common source of subtle bugs after migrations.

Adding Dynamic Schema Updates

For development environments where the database schema changes frequently, you might want the schema information to stay current. Modify the server to support on-demand schema refreshing:

from functools import lru_cache

@lru_cache(maxsize=None)
def get_db_inspector_cached():
    """Cached inspector — call refresh_schema() to clear."""
    engine = create_engine(DATABASE_URL)
    return inspect(engine)

@mcp.tool()
def refresh_schema() -> str:
    """Clear cached schema to force refresh on next query."""
    get_db_inspector_cached.cache_clear()
    return "Schema cache cleared. Next query will reflect current database state."

Add the @lru_cache decorator to the get_db_inspector function to enable caching, then provide a tool to clear it when schema changes occur. After running a migration, ask the AI to “refresh the schema cache” before asking schema-dependent questions.

Extending the Server with Sample Data

Beyond schema structure, AI assistants benefit from seeing representative sample data. Add a tool that returns anonymized sample rows:

from sqlalchemy import text

@mcp.tool()
def get_sample_rows(table_name: str, limit: int = 5) -> List[Dict[str, Any]]:
    """Return sample rows from a table to illustrate data patterns."""
    engine = create_engine(DATABASE_URL)
    with engine.connect() as conn:
        result = conn.execute(
            text(f"SELECT * FROM {table_name} LIMIT :limit"),
            {"limit": limit}
        )
        rows = [dict(row._mapping) for row in result]
    return rows

Use this carefully with sensitive databases. For production data, apply column masking or only expose this tool against anonymized development databases. Sample data dramatically improves AI-generated queries for tables with enum-like string columns, date ranges, or unusual value distributions.

Security Considerations

When exposing database schema through MCP, follow these security practices:

Restrict database credentials: Use a read-only database user that can only query the information schema. Create a dedicated user for MCP access:

CREATE USER mcp_reader WITH PASSWORD 'strong_password';
GRANT CONNECT ON DATABASE mydb TO mcp_reader;
GRANT USAGE ON SCHEMA public TO mcp_reader;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO mcp_reader;

Limit network exposure: Run the MCP server locally and avoid exposing it to untrusted networks. The stdio transport keeps all communication local to your machine.

Validate table names: If your server accepts table names as parameters, validate them against the list of actual tables to prevent SQL injection through tool parameters:

@mcp.tool()
def get_table_schema(table_name: str) -> Dict[str, Any]:
    inspector = get_db_inspector()
    valid_tables = inspector.get_table_names()
    if table_name not in valid_tables:
        return {"error": f"Table '{table_name}' not found"}
    # ... proceed with safe table_name

Avoid logging credentials: Never log the DATABASE_URL or connection strings. Use environment variables exclusively and ensure your logging configuration excludes environment variable values.

Extending the Server Further

Once the basic schema server is working, consider adding these capabilities to make it more useful across your entire development workflow:

Query execution tool — Add a tool that runs read-only SELECT statements and returns results. This lets AI assistants verify their generated queries before you run them in your application.

Documentation generation — Many PostgreSQL databases store column and table comments. Expose these through an additional tool so AI assistants can explain unfamiliar columns without you needing to look up the data dictionary manually.

Relationship graph — Generate a JSON representation of all foreign key relationships. AI assistants can use this to explain the full data model and suggest correct JOIN paths for complex multi-table queries.

Schema diff tool — Compare two schemas (for example, development vs. staging) and return the differences. This helps identify missing migrations or environment drift without running migration tools manually.

@mcp.tool()
def compare_schemas(schema_a: str, schema_b: str) -> Dict[str, Any]:
    """Compare two schemas and return differences in table/column definitions."""
    # Implementation: query both schemas and diff column lists
    # Returns added tables, removed tables, changed column types
    pass

The MCP architecture makes it easy to add new tools as your needs evolve. Start with the core schema exposure tools and layer on additional capabilities as you identify gaps in your AI assistant’s database knowledge.

Built by theluckystrike — More at zovo.one