157 lines
5.0 KiB
Markdown
157 lines
5.0 KiB
Markdown
# Spring AI Model Context Protocol - SQLite Chatbot
|
|
|
|
A demo application showcasing the integration of Spring AI with SQLite databases using the Model Context Protocol (MCP). This application enables natural language interactions with your SQLite database through a command-line interface.
|
|
|
|
It uses the [SQLite MCP-Server](https://github.com/modelcontextprotocol/servers/tree/main/src/sqlite) to enable running SQL queries, analyzing business data, and automatically generating business insight memos.
|
|
|
|
The demo starts a simple chatbot where your can ask qustions about the data stored in the database.
|
|
|
|
For example:
|
|
> Can you connect to my SQLite database and tell me what products are available, and their prices?
|
|
|
|
or perform some data aggreagation on the fly:
|
|
|
|
> What's the average price of all products in the database?
|
|
|
|
run annalysis
|
|
|
|
> Can you analyze the price distribution and suggest any pricing optimizations?
|
|
|
|
or even create an new table:
|
|
|
|
> Could you help me design and create a new table for storing customer orders?
|
|
|
|
## Features
|
|
|
|
- Natural language querying of SQLite databases
|
|
- Interactive chat mode for dynamic database interactions
|
|
- Seamless integration with OpenAI's language models
|
|
- Built on Spring AI and Model Context Protocol
|
|
|
|
## Prerequisites
|
|
|
|
- Java 17 or higher
|
|
- Maven 3.6+
|
|
- uvx package manager
|
|
- Git
|
|
- OpenAI API key
|
|
- SQLite (optional, for database modifications)
|
|
|
|
## Installation
|
|
|
|
1. Install uvx (Universal Package Manager):
|
|
```bash
|
|
# Follow installation instructions at:
|
|
https://docs.astral.sh/uv/getting-started/installation/
|
|
```
|
|
|
|
2. Clone the repository:
|
|
```bash
|
|
git clone https://github.com/spring-projects/spring-ai-examples.git
|
|
cd model-context-protocol/sqlite/chatbot
|
|
```
|
|
|
|
3. Set up your OpenAI API key:
|
|
```bash
|
|
export OPENAI_API_KEY='your-api-key-here'
|
|
```
|
|
|
|
## Sample SQLite database
|
|
|
|
SQLite database files are portable across operating systems. This repository contains a sample database file named `test.db`.
|
|
|
|
It has a `PRODUCTS` table and was created using the script `create-database.sh`
|
|
|
|
## Running the Application
|
|
|
|
### Interactive Chat
|
|
Enables real-time conversation with your database:
|
|
|
|
```bash
|
|
./mvnw spring-boot:run -Dspring-boot.run.profiles=chat
|
|
```
|
|
|
|
## Architecture Overview
|
|
|
|
### MCP Client Configuration
|
|
|
|
The application uses a synchronous MCP client to communicate with the SQLite database:
|
|
|
|
```java
|
|
@Bean(destroyMethod = "close")
|
|
public McpSyncClient mcpClient() {
|
|
|
|
var stdioParams = ServerParameters.builder("uvx")
|
|
.args("mcp-server-sqlite", "--db-path", getDbPath())
|
|
.build();
|
|
|
|
var mcpClient = McpClient.sync(new StdioServerTransport(stdioParams),
|
|
Duration.ofSeconds(10), new ObjectMapper());
|
|
|
|
var init = mcpClient.initialize();
|
|
|
|
System.out.println("MCP Initialized: " + init);
|
|
|
|
return mcpClient;
|
|
}
|
|
```
|
|
|
|
This configuration:
|
|
1. Creates a stdio-based transport layer that communicates with the `uvx` MCP server
|
|
2. Specifies SQLite as the backend database and its location
|
|
3. Sets a 10-second timeout for operations
|
|
4. Uses Jackson for JSON serialization
|
|
5. Initializes the connection to the MCP server
|
|
|
|
The `destroyMethod = "close"` annotation ensures proper cleanup when the application shuts down.
|
|
|
|
### Function Callbacks
|
|
|
|
The application registers MCP tools with Spring AI using function callbacks:
|
|
|
|
```java
|
|
@Bean
|
|
public List<McpFunctionCallback> functionCallbacks(McpSyncClient mcpClient) {
|
|
return mcpClient.listTools(null)
|
|
.tools()
|
|
.stream()
|
|
.map(tool -> new McpFunctionCallback(mcpClient, tool))
|
|
.toList();
|
|
}
|
|
```
|
|
|
|
#### Purpose
|
|
|
|
This bean is responsible for:
|
|
1. Discovering available MCP tools from the client
|
|
2. Converting each tool into a Spring AI function callback
|
|
3. Making these callbacks available for use with the ChatClient
|
|
|
|
|
|
#### How It Works
|
|
|
|
1. `mcpClient.listTools(null)` queries the MCP server for all available tools
|
|
- The `null` parameter represents a pagination cursor
|
|
- When null, returns the first page of results
|
|
- A cursor string can be provided to get results after that position
|
|
2. `.tools()` extracts the tool list from the response
|
|
3. Each tool is transformed into a `McpFunctionCallback` using `.map()`
|
|
4. These callbacks are collected into an array using `.toArray(McpFunctionCallback[]::new)`
|
|
|
|
#### Usage
|
|
|
|
The registered callbacks enable the ChatClient to:
|
|
- Access MCP tools during conversations
|
|
- Handle function calls requested by the AI model
|
|
- Execute tools against the MCP server (e.g., SQLite database)
|
|
|
|
|
|
## Documentation references
|
|
|
|
You can find out more about this sample application following the this quickstart link to a specific verison in github.
|
|
|
|
Unfortunately, on December 10th 2024, the quickstart was changed from SQLite to be a weather retrieval example.
|
|
|
|
However, here is the [link](https://github.com/modelcontextprotocol/docs/blob/1024e03f83aa0b8badde9b50dfee4d2e4e7f9446/quickstart.mdx) to the docs before the change if you want to read up on some details.
|
|
|
|
For example, you may want to create other tables and install SQLite |