0Pricing
AI Engineering Academy · 课时

通过 MCP 暴露数据库资源

创建能够动态提供数据库内容的 MCP 资源，公开模型可以调用的查询工具，并为大型结果集实现分页。

通过 MCP 暴露数据库资源 是 CoddyKit 上的免费 AI Engineering Academy 课时。 这是第 3 节课，共 4 节。 你可以在下方免费阅读本课时的完整内容 — 然后在浏览器中使用内置代码编辑器和全天候 AI 导师进行实践。 这是 AI Engineering Academy 学习路径的一部分，你的进度在网页和 CoddyKit 应用中同步。 AI Engineering Academy 课程共包含 4 节课。

本课时的部分内容尚未翻译，以英文显示。

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.

常见问题解答

「通过 MCP 暴露数据库资源」课时是免费的吗？

是的 — 「通过 MCP 暴露数据库资源」的完整文本可在网页上免费阅读。要进行交互式练习（内置代码编辑器和全天候 AI 导师）并解锁 AI Engineering Academy 课程的其余内容，请升级到 CoddyKit PRO。 AI Engineering Academy 课程共包含 4 节课。

「通过 MCP 暴露数据库资源」这节课中我会学到什么？

创建能够动态提供数据库内容的 MCP 资源，公开模型可以调用的查询工具，并为大型结果集实现分页。 你通过在浏览器中直接运行的动手代码来练习 AI Engineering Academy，全天候 AI 导师会在你学习这节课的过程中回答你的问题。

学习 AI Engineering Academy 需要有经验吗？

无需任何先前经验。CoddyKit 上的 AI Engineering Academy 课程适合初学者到高级学习者，你可以从这里开始或从头开始，按照自己的节奏学习。 这是第 3 节课，共 4 节。

「通过 MCP 暴露数据库资源」课时需要多长时间？

大多数 CoddyKit 课程大约需要 5–10 分钟。每节课都很精短且互动，所以你能稳步进步，并在网页和应用中从离开的地方继续。

我能在这节 AI Engineering Academy 课中编写并运行代码吗？

能。每节 AI Engineering Academy 课都包含内置代码编辑器，你可以在浏览器中直接编写并运行真实代码，并获得即时 AI 反馈 — 无需本地设置。

此课程中的所有课时

  1. 什么是 MCP，以及它为何重要
  2. 构建您的第一个 MCP 服务器
  3. 通过 MCP 暴露数据库资源
  4. MCP 安全与身份验证
← 返回 AI Engineering Academy