Lab: Build a Database MCP Server with FastMCP and SQLite

Goal

Build a Model Context Protocol (MCP) server using FastMCP that exposes a small database through:

• search
• insert
• aggregate

You must also expose the database schema as an MCP resource, test the server with Inspector or equivalent tooling, and show the server working from at least one MCP client.

Learning Outcomes

By the end of this lab, students should be able to:

• explain what MCP tools and resources are
• build a FastMCP server in Python
• connect FastMCP to a SQLite database
• safely validate database requests before executing SQL
• expose dynamic schema context through @mcp.resource(...)
• test tool schemas, normal calls, and error responses
• connect the server to an MCP client such as Claude Code, Codex, or Gemini CLI

Required Features

Part 1: MCP Server

Implement a FastMCP server that exposes exactly these tool categories:

1. search
2. insert
3. aggregate

Your server may use SQLite for the main implementation. If you want to support PostgreSQL too, design the code so the database layer can be swapped later.

Part 2: Resource

Expose database schema information as MCP resources:

• one resource for the full database schema
• one dynamic resource template for a single table schema

Suggested URIs:

• schema://database
• schema://table/{table_name}

Part 3: Validation and Error Handling

Your tools must reject unsafe or invalid requests:

• unknown table names
• unknown column names
• unsupported filter operators
• invalid aggregate requests
• empty inserts

Do not build SQL by blindly concatenating raw user input.

Part 4: Testing and Verification

Verify all of the following:

1. the server starts correctly
2. the three tools are discoverable
3. the schema resource is discoverable
4. valid tool calls return useful results
5. invalid tool calls return clear errors
6. at least one MCP client can connect and use the server

Part 5: Demo Deliverables

Prepare:

• GitHub repository
• setup instructions
• tool descriptions
• testing steps
• at least one client configuration example
• short demo video, around 2 minutes

Inspector screenshots are recommended if you use MCP Inspector.

Suggested Project Structure

implementation/
  db.py
  init_db.py
  mcp_server.py
  verify_server.py
  tests/
    test_server.py

Recommended Data Model

Use a small relational dataset so search, insert, and aggregate are easy to demo. Example:

• students
• courses
• enrollments

Example Tasks to Demonstrate

• search all students in cohort A1
• insert a new student
• count rows in a table
• compute average score by cohort
• read the full schema resource
• read schema://table/students
• show an invalid request, such as searching a missing table

FastMCP and Inspector References

• FastMCP quickstart: https://gofastmcp.com/v2/getting-started/quickstart