Content
# **AI-Powered Query Interface for MCP Servers**
[](https://www.oracle.com/java/technologies/javase-jdk21-downloads.html)
[](https://spring.io/projects/spring-boot)
[](LICENSE)
---
## **Overview**
This project allows users to make natural language queries, like "Show products under $1000," and interact with
PostgreSQL databases using Spring Boot and AI-powered translation. It seamlessly converts user input into SQL
queries through MCP, then returns the results in structured JSON with easy-to-understand
explanations.
To learn more about MCP, visit: Model Context Protocol Servers,
visit: [Model Context Protocol Servers](https://github.com/modelcontextprotocol/servers).
---
## **Configuration (`application.yml`)**
The `application.yml` file contains all the settings needed to connect AI model with the MCP server. Below is a detailed
breakdown of each section.
---
### **1. Connecting to Ollama**
Ollama is a platform that allows users to run open-source LLMs locally on their devices. You need to configure its connection
details in the
`application.yml` file.
```yaml
spring:
ai:
ollama:
base-url: http://host:11434
chat:
model: model-name #(e.g., llama3.2)
```
#### **Explanation:**
- **`base-url`**: The URL where the Ollama server is running.
- **`model`**: Specifies the AI model. In this case.
---
### **2. Connecting to the MCP Server**
The MCP Server acts as the bridge between the application and the PostgreSQL database. It processes SQL queries
generated by the AI model and returns results.
```yaml
spring:
ai:
mcp:
client:
stdio:
connection-id: server-postgres
connections:
server-postgres:
command: npx
args:
- "-y"
- "@modelcontextprotocol/server-postgres"
- "postgresql://postgres:password@localhost:5432/inventory_db"
```
#### **Explanation**
To configure the MCP server, you need to specify the `connection-id`, `command`, and `args`. These details depend on the
specific MCP server you are using. For the exact `command` and `args` for your server, refer to
the [Model Context Protocol Servers](https://github.com/modelcontextprotocol/servers) repository.
- **`connection-id`**:
- Identifies the MCP server you're connecting to. In this example, it's `server-postgres` (PostgreSQL MCP Server).
- Replace this with the appropriate identifier for your chosen server.
- **`command`**:
- Specifies the command used to start the MCP server.
- For example, `npx` is used here to run the PostgreSQL MCP Server.
- Refer to the [Model Context Protocol Servers](https://github.com/modelcontextprotocol/servers) repository for the
correct command for your server.
- **`args`**:
- Arguments passed to the `command`. These vary depending on the MCP server you are using.
- Common arguments include:
- `-y`: Automatically confirms prompts (useful for non-interactive setups).
- `@modelcontextprotocol/server-postgres`: Specifies the type of MCP server (e.g., PostgreSQL).
- `postgresql://...`: The database connection string.
- For the exact arguments required for your server, check the documentation in
the [Model Context Protocol Servers](https://github.com/modelcontextprotocol/servers) repository.
---
### **System Prompt**
The `systemPrompt` provides instructions to the AI model on how to process natural language queries and generate
responses. It defines the behavior of the AI, including how to generate queries, process results, and format outputs.
```yaml
nlq:
server-postgres:
systemPrompt: |
You are a PostgreSQL expert assistant. Follow these instructions:
1. Generate accurate SQL queries using the 'query' tool.
2. Wait for and process actual PostgreSQL database results.
3. Return clean JSON formatted output.
4. Provide a concise explanation of the results.
```
#### **Explanation**:
- The `systemPrompt` tells the AI model how to interpret user input and what to do with it.
- **Adaptability**: Depending on the server you're using (e.g., Airbnb, Atlassian, MySQL), you can modify the
`systemPrompt` to match the query language and expected behavior.
---
## **How It Works Together**
1. ### Resource Discovery
The AI model (e.g., **llama**) leverages the **MCP server** to access essential resources such as database schemas,
metadata, or documentation. This process helps the model understand the structure of the PostgreSQL database before
generating queries.
2. ### Natural Language to SQL Conversion
The AI model uses the schema information retrieved via MCP to construct a precise SQL query tailored for the
PostgreSQL database.
3. ### Query Execution via MCP Server
The generated SQL query is sent to the **MCP server**, which safely executes it against the PostgreSQL database.
4. ### Structured Result Retrieval
The PostgreSQL server executes the query and returns the structured data results to the MCP server for further
processing.
5. ### Response Formatting with System Prompt
A **System Prompt** instructs the AI to format the output into clean, structured **JSON**, including a human-readable
explanation of the results.
#### Example Output:
```json
{
"results": [ ... ],
"explanation": "The query retrieved data from the 'sales' table where..."
}
```
---
## **Running the Application**
#### **Step 1: Build the Project**
```bash
mvn clean install
```
#### **Step 2: Start the Application**
```bash
mvn spring-boot:run
```
#### **Step 3: Test the API**
Send a POST request to the `/api/natural-language-query/process` endpoint with a natural language query:
##### Example Request:
```text
POST /api/natural-language-query/process
Content-Type: application/json
{
"prompt": "Show me all products under 1000"
}
```
##### Example Response:
```json
{
"results": [
{
"product_id": 1,
"name": "Laptop",
"price": 950.00
},
{
"product_id": 2,
"name": "Office Chair",
"price": 150.00
}
],
"explanation": "Retrieved all products with a price less than 1000."
}
```
---
## **Deployment**
### **Local Deployment**
Follow the steps in the **Running the Application** section to deploy the application locally.
### **Docker Deployment**
If you want to containerize the application, create a `Dockerfile` and build the image:
```dockerfile
FROM openjdk:21-jdk-slim
COPY target/ai-postgresql-query-interface-1.0.0-SNAPSHOT.jar app.jar
ENTRYPOINT ["java", "-jar", "/app.jar"]
```
Build and run the Docker container:
```bash
docker build -t ai-postgresql-query-interface .
docker run -p 8080:8080 ai-postgresql-query-interface
```
---
## **Contact**
For questions or feedback, feel free to reach out:
- **Author**: Daagi Saber
- **Email**: saber.daagi.perso@gmail.com
