AI Engineering Academy · Lesson

Exposing Database Resources via MCP

Create MCP resources that dynamically serve database content, expose query tools that the model can call, and implement pagination for large result sets.

Exposing Database Resources via MCP is a free AI Engineering Academy lesson on CoddyKit — lesson 3 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the AI Engineering Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

Why Databases Need MCP

Databases are among the most valuable data sources for AI assistants — but raw database access is too dangerous to give an LLM directly. An MCP server acts as a controlled proxy between the AI and your database, exposing only the data and operations you explicitly allow, with validation, rate limiting, and auditing built in.

Designing Your Database MCP Schema

Before writing code, decide what the AI should be able to see and do. Define: which tables are readable, which tools perform write operations (if any), what data must be filtered by user context, and what columns should be hidden (passwords, PII). This design phase prevents accidental data exposure.

Read tools: list_records, get_record, search_records
Write tools (optional, restricted): create_record, update_record
Forbidden: raw SQL execution, schema mutations, system tables

Database Connection Setup

Use asyncpg (PostgreSQL) or aiosqlite (SQLite) for async database access in your MCP server. Create a connection pool at startup so each tool call doesn't open a new connection. Store the database URL in an environment variable, never in code.

import asyncpg
import os
from mcp.server import Server
from mcp import types
import asyncio

app = Server('database-mcp-server')
pool = None  # Global connection pool

async def get_pool():
    global pool
    if pool is None:
        pool = await asyncpg.create_pool(
            os.environ['DATABASE_URL'],
            min_size=2,
            max_size=10,
            command_timeout=30
        )
    return pool

# Initialize pool at startup
async def startup():
    await get_pool()
    print('Database pool initialized', flush=False)  # stderr only via logger

Listing Records as a Tool

A list_records tool with filtering and pagination lets the AI browse database content without running arbitrary SQL. Accept filter parameters, build a parameterized query, and always apply a row limit. Return results as a formatted string the model can read and reference in its answer.

@app.list_tools()
async def list_tools():
    return [
        types.Tool(
            name='list_products',
            description='List products from the catalog with optional filtering by category and price range.',
            inputSchema={
                'type': 'object',
                'properties': {
                    'category': {'type': 'string', 'description': 'Filter by product category.'},
                    'max_price': {'type': 'number', 'description': 'Maximum price in USD.'},
                    'limit': {'type': 'integer', 'default': 20, 'maximum': 100}
                },
                'required': []
            }
        )
    ]

@app.call_tool()
async def call_tool(name: str, arguments: dict):
    if name == 'list_products':
        return await list_products_handler(arguments)

async def list_products_handler(args: dict):
    pool = await get_pool()
    query = 'SELECT id, name, category, price, stock FROM products WHERE 1=1'
    params = []

    if 'category' in args:
        params.append(args['category'])
        query += f' AND category = ${len(params)}'
    if 'max_price' in args:
        params.append(args['max_price'])
        query += f' AND price <= ${len(params)}'

    limit = min(args.get('limit', 20), 100)
    params.append(limit)
    query += f' ORDER BY name LIMIT ${len(params)}'

    async with pool.acquire() as conn:
        rows = await conn.fetch(query, *params)

    if not rows:
        return [types.TextContent(type='text', text='No products found matching your criteria.')]

    lines = ['id | name | category | price | stock']
    lines += [f'{r["id"]} | {r["name"]} | {r["category"]} | ${r["price"]} | {r["stock"]}' for r in rows]
    return [types.TextContent(type='text', text='\n'.join(lines))]

Exposing Database Rows as Resources

Individual database records can be exposed as MCP resources using structured URIs like db://products/42. Resources let the AI client cache and reference specific records without calling a tool each time. Implement list_resources to return the most relevant records and read_resource to fetch one by URI.

@app.list_resources()
async def list_resources():
    pool = await get_pool()
    async with pool.acquire() as conn:
        # List most recently updated products as resources
        rows = await conn.fetch(
            'SELECT id, name FROM products ORDER BY updated_at DESC LIMIT 50'
        )
    return [
        types.Resource(
            uri=f'db://products/{row["id"]}',
            name=row['name'],
            description=f'Product record for {row["name"]}',
            mimeType='application/json'
        )
        for row in rows
    ]

@app.read_resource()
async def read_resource(uri: str) -> str:
    import json
    if uri.startswith('db://products/'):
        product_id = int(uri.split('/')[-1])
        pool = await get_pool()
        async with pool.acquire() as conn:
            row = await conn.fetchrow(
                'SELECT id, name, category, price, description, stock FROM products WHERE id = $1',
                product_id
            )
        if row is None:
            raise ValueError(f'Product {product_id} not found')
        return json.dumps(dict(row), default=str)
    raise ValueError(f'Unknown resource URI: {uri}')

Pagination for Large Result Sets

Database tables often have thousands of rows. Implement cursor-based pagination in your tools so the AI can ask for the next page of results. Use last_id as a stable cursor (more reliable than offset-based pagination which skips rows on inserts).

async def paginated_list(table: str, limit: int = 20, after_id: int = 0) -> tuple:
    '''Returns (rows, has_more, next_cursor).'''
    pool = await get_pool()
    async with pool.acquire() as conn:
        rows = await conn.fetch(
            f'SELECT * FROM {table} WHERE id > $1 ORDER BY id LIMIT $2',
            after_id,
            limit + 1  # Fetch one extra to detect if more pages exist
        )

    has_more = len(rows) > limit
    rows = rows[:limit]
    next_cursor = rows[-1]['id'] if rows else None
    return rows, has_more, next_cursor

# In your tool result, include pagination info:
# 'Showing 20 products. To see the next page, call list_products with after_id=120'

Full-Text Search Tool

Add a search tool that uses PostgreSQL full-text search, allowing the AI to find records by natural language queries rather than exact field matches. This is especially powerful for product catalogs, documentation, and knowledge bases.

@app.list_tools()
async def list_tools():
    return [
        types.Tool(
            name='search_products',
            description='Full-text search across product names and descriptions. Use for finding products by natural language, not by category filter.',
            inputSchema={
                'type': 'object',
                'properties': {
                    'query': {'type': 'string', 'description': 'Natural language search query.'},
                    'limit': {'type': 'integer', 'default': 10, 'maximum': 50}
                },
                'required': ['query']
            }
        )
    ]

async def search_products_handler(args: dict):
    pool = await get_pool()
    async with pool.acquire() as conn:
        rows = await conn.fetch(
            '''SELECT id, name, price, ts_rank(search_vector, query) AS rank
               FROM products, plainto_tsquery('english', $1) AS query
               WHERE search_vector @@ query
               ORDER BY rank DESC
               LIMIT $2''',
            args['query'],
            args.get('limit', 10)
        )
    if not rows:
        return [types.TextContent(type='text', text=f'No products found matching "{args["query"]}".  ')]
    lines = [f'{r["id"]}: {r["name"]} — ${r["price"]}' for r in rows]
    return [types.TextContent(type='text', text='\n'.join(lines))]

Write Operations with Confirmation

If your MCP server allows write operations, build in a two-step confirmation pattern. The first tool call returns a preview of what will change. A second confirm_action(action_id) tool executes the change. This prevents the AI from making irreversible writes from a single prompt without user awareness.

import uuid
pending_actions = {}  # In-memory store; use Redis in production

async def create_order_preview(args: dict) -> list:
    action_id = str(uuid.uuid4())[:8]
    pending_actions[action_id] = {'type': 'create_order', 'data': args}
    return [types.TextContent(
        type='text',
        text=f'Preview: Create order for customer {args["customer_id"]} with {len(args["items"])} items, '
             f'total ${args["total"]}. Confirm with: confirm_action(action_id="{action_id}")')
    ]

async def confirm_action_handler(args: dict) -> list:
    action_id = args.get('action_id')
    action = pending_actions.pop(action_id, None)
    if not action:
        return [types.TextContent(type='text', text='Action expired or not found.')]
    # Execute the pending action
    result = await execute_action(action)
    return [types.TextContent(type='text', text=f'Done: {result}')]

Filtering Sensitive Data

Never expose sensitive columns like passwords, API keys, or PII unless your server explicitly needs to for the business use case. Use SELECT clauses that exclude sensitive columns, and apply row-level filtering based on the authenticated user's permissions. Your MCP server is the last line of defense before data reaches the AI.

async def get_safe_customer(customer_id: int) -> dict:
    '''Fetch customer data with sensitive fields excluded.'''
    pool = await get_pool()
    async with pool.acquire() as conn:
        row = await conn.fetchrow(
            '''SELECT id, name, created_at, country, tier
               FROM customers
               WHERE id = $1
               -- NEVER select: password_hash, api_key, full_address, payment_method_id
            ''',
            customer_id
        )
    return dict(row) if row else {}

Audit Logging for Database Access

Every database access through your MCP server should be logged for security and compliance. Record the tool called, arguments passed, rows returned, timestamp, and any session/user context available. This audit trail lets you detect anomalous patterns and satisfy compliance requirements.

import logging
import sys
import time

audit_logger = logging.getLogger('mcp.audit')
audit_logger.setLevel(logging.INFO)
handler = logging.StreamHandler(sys.stderr)
audit_logger.addHandler(handler)

async def audited_tool_call(name: str, arguments: dict, session_id: str = None) -> list:
    start = time.time()
    result = await call_tool(name, arguments)
    elapsed_ms = round((time.time() - start) * 1000)

    audit_logger.info({
        'event': 'tool_call',
        'tool': name,
        'args': {k: v for k, v in arguments.items() if k != 'password'},
        'result_count': len(result),
        'elapsed_ms': elapsed_ms,
        'session_id': session_id
    })
    return result

Testing Your Database MCP Server

Test your database MCP server at three levels: unit tests for individual query functions (using a test database or mocks), integration tests that start the full MCP server and call tools via the SDK client, and end-to-end tests that verify Claude Desktop sees the tools and calls them correctly. Automated tests catch regressions before they reach the AI.

Quick Check

Test your understanding of exposing database resources via MCP.

Lesson Recap

In this lesson you learned: MCP database servers act as controlled proxies with explicit query tools and read-only operations by default, database rows can be exposed as MCP resources with structured URIs for the AI to reference, and write operations should use a preview-then-confirm pattern to prevent unintended changes. Next up we secure our MCP server with OAuth 2.0 authentication and input validation.

Frequently asked questions

Is the “Exposing Database Resources via MCP” lesson free?

Yes — the full text of “Exposing Database Resources via MCP” is free to read here on the web, and the AI Engineering Academy course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the AI Engineering Academy course, upgrade to CoddyKit PRO.

What will I learn in “Exposing Database Resources via MCP”?

Create MCP resources that dynamically serve database content, expose query tools that the model can call, and implement pagination for large result sets. You practise AI Engineering Academy with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start AI Engineering Academy?

No prior experience is required. AI Engineering Academy on CoddyKit is structured for beginners through advanced learners; this is — lesson 3 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “Exposing Database Resources via MCP” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this AI Engineering Academy lesson?

Yes. Every AI Engineering Academy lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

← Back to AI Engineering Academy