Brief Summary
This video introduces the Model Context Protocol (MCP), a universal connector that enables AI assistants to interact with external tools and databases. It explains MCP's components (host, client, server), its creation by Anthropic to overcome AI isolation, and demonstrates building an MCP server to connect to a SQLite database. The tutorial covers gathering database metadata, creating a SQLite database, and implementing MCP server tools for querying and modifying the database. Finally, it shows how to integrate the MCP server with Visual Studio Code's Copilot in agent mode to interact with the database using natural language.
- MCP is a universal connector for AI assistants to interact with external tools and databases.
- The video demonstrates building an MCP server for a SQLite database.
- The integration with Visual Studio Code's Copilot allows natural language interaction with the database.
Introduction to MCP
The video introduces MCP (Model Context Protocol) as a universal connector that allows AI assistants to communicate with external tools and databases, similar to a universal USB port for AI. MCP enables plug-and-play compatibility and bidirectional communication, allowing AI to both read data and perform actions. Anthropic created MCP to address the issue of AI assistants being isolated from live data and external tools, which previously required building each connection from scratch.
MCP Components
MCP consists of three main components: the host, client, and server. The host is the AI brain, such as Cloud or GitHub Copilot, that wants to use tools and data. The client is the application we interact with, like Visual Studio Code or a cloud chat interface, that connects everything together. The server is the actual tool or database that provides the functionality. The video uses GitHub Copilot in Visual Studio Code as an example, where Copilot understands the user's request, and the MCP server interacts with a SQLite database to perform the requested actions.
Building a SQLite MCP Server: Package References and Setup
The goal is to build an MCP server that connects to a SQLite database, acting as a smart database assistant. This assistant will help with tasks such as chatting with the database structure, writing queries, explaining table relationships, viewing triggers, adding new tables or columns, and serving as an onboarding tool for new team members. The necessary package references include Microsoft.Data.SQLite for SQLite database interaction, Dapper for easier query writing, YAML.NET for presenting data to the AI in a hierarchical format similar to markdown, the official Model Context Protocol NuGet package, and Microsoft.Extensions.Hosting for the MCP server. The database path is set in the app settings.
Gathering SQLite Metadata
The main part of the project involves gathering information from SQLite to enable interaction. This includes retrieving table names, relationships, foreign keys, primary keys, views, and triggers. A SQLite schema service is created, which accepts a connection string and uses simple queries to extract this metadata. The service includes methods for getting all tables and their information (triggers, columns, primary keys, foreign keys, and indexes), retrieving specific tables by name, and fetching view details. Models are defined to represent the schema, such as SQLiteColumn (name, type, not null, primary key) and table relationships (from table, from column, to table, to column).
Implementing Database Modification Methods
The schema service includes methods for creating tables and adding columns via prompts. The AI can get the table name and columns (name and type) to create a table. Feedback is provided to the AI in text format, sharing successful outputs or exceptions. The add table column method takes the table name, column name, and column type, using ALTER TABLE to add the column. Exceptions are shared with the AI. All necessary information is gathered with specific methods for each task, using the database path from the app settings.
Creating the SQLite Database
A new SQLite database named "bookcraft" is created. A provided SQL script is used to create a library management system database with tables such as payment, fines, loan, books, and book copies. The design of the database is not critical; the focus is on enabling interaction and information retrieval. The script is executed in a SQL editor, and the tables, views, triggers, and indexes are verified.
Preparing Data for AI Consumption
The application reads the database path from app settings and creates a SQLite connection string, which is passed to the SQLite schema service. The data is formatted for AI consumption, categorizing information with titles and hierarchical order using YAML. An extension method is created to convert objects to YAML using the YamlDotNet library. The YAML output includes table details (audit log, loans), column information (name, type, nullability), primary keys, foreign keys, indexes, triggers (with SQL source code), view information (name, SQL query), and foreign key relations.
Building the MCP Server
The Microsoft.Extensions.Hosting package is used to build the MCP server. An empty application builder is created, and the AddMCP Server method is called with a stdio server transport and tools from the assembly. The BookcraftDBMCPServer class is created, passing the configuration to access the connection string. The MCP server is defined using the [MCP ServerToolType] attribute, which includes a description that tells the AI what the class is about and when to call it. The description specifies that the server is for exploring and modifying the Bookcraft SQLite database, retrieving schema information, and inspecting tables and views, with all operations based on SQLite syntax.
Implementing MCP Server Tools
Static methods are created and categorized as MCP server tools using the [MCP ServerTool] attribute. Each tool has a description that explains when the AI should call it. Tools include: GetGeneralInfo (general overview of the database), GetTablesInfo (list of tables and schema), GetTableInfo (information about a specific table), GetTableRelation (table relations and foreign keys), GetViews (information about views), AddNewColumn (adds a new column to a table), and CreateTable (creates a new table). The descriptions are crucial for the AI to understand when to call each method based on the prompt. For the AddNewColumn tool, a rule is specified to convert invalid types to the closest SQLite type (e.g., string to text).
Configuring Visual Studio Code for MCP
To run Copilot in agent mode and use the tools, an .vscode folder is created with an mcp.json file. The file specifies the server command using stdio and the path to the CS project. The server can be started, stopped, or restarted, and it detects the seven static methods created as tools. To ensure Copilot only uses the defined tools, an empty Visual Studio Code instance is used with only the mcp.json file.
Interacting with the Database via Copilot
Copilot is used in agent mode to interact with the database. Questions are asked to test the AI's understanding and ability to call the correct tools. Examples include: explaining the database structure, listing tables and connections, writing queries to find members who haven't returned books, creating CTE queries, and generating complex queries with multiple joins. The AI correctly calls the appropriate methods and provides accurate results.
Performing Actions and Validating Results
The AI is used to perform actions such as creating a new table (key-value store) and adding a new column (purpose). The AI correctly interprets the prompts, converts data types (string to text), and executes the actions. The database is then checked to verify that the table and column have been created successfully. The demonstration shows how MCP facilitates the interaction with the database, requiring minimal effort beyond gathering information and providing well-formed data.
