simpliq-mcp-server-dev-test
Advanced tools
Database Management via Model Context Protocol (MCP)
This POC demonstrates a Model Context Protocol (MCP) server for managing database connections using SQLAlchemy. The server implements the JSON-RPC 2.0 protocol for VS Code Copilot integration, reads database connection strings from a YAML configuration file, and provides tools to query and manage database connections.
mcp_server.py: The main Flask server script with REST API endpoints and MCP tools.config.yml: YAML configuration file for the database connection string.The server now supports two authentication strategies configured via authentication.type in config.yml:
| Mode | How Identity Is Resolved | Order of Resolution | Use Case | Security |
|---|---|---|---|---|
mcp | Validates JWT token with User Manager API | Bearer token → session file | Production, normal multi-user | Strong (token verified) |
client | Trusts headers (X-Client-Username, optional email/org) | Header → Bearer token → session file | Local dev, behind trusted reverse proxy | Weak (no token validation) |
Header passthrough (client mode) example:
GET /config HTTP/1.1
Host: localhost:8000
X-Client-Username: dev_user
X-Client-Email: dev_user@example.com
X-Client-Org: demo-org
Add to config.yml:
authentication:
type: client # or mcp
require_auth_for_all: false
If require_auth_for_all: true is set, even discovery endpoints will require a resolved identity.
Português (resumo):
O modo client aceita identidade via cabeçalhos (X-Client-Username, X-Client-Email, X-Client-Org) sem validar token. Use somente em ambiente confiável (localhost, proxy autenticado). Para produção, mantenha type: mcp.
Startup logs show both the declared and effective mode; valores desconhecidos fazem fallback para mcp.
test_client.py: Script to test all server endpoints.test_validation.py: Script to test connection string validation (interactive & suite). ⭐ NEW!create_test_db.py: Script to create a sample SQLite database for testing.requirements.txt: Python dependencies (Flask, SQLAlchemy, PyYAML).example.db: SQLite database file (created by create_test_db.py).mcp_cli_client.py: Interactive CLI client for SimpliqData MCP server.gemini_mcp_client.py: Google Gemini AI integration example.docs/GEMINI_CLI_INTEGRATION.md: Step-by-step guide for using with Google Gemini CLI.docs/VALIDATION_GUIDE.md: Complete guide for connection string validation. ⭐ NEW!docs/TEST_README.md: Testing guide for all test scripts. ⭐ NEW!docs/QUICK_START_CONNECT_MONITOR.md: Quick start for connect & monitor test. ⭐ NEW!# Navigate to the poc/db directory
cd C:\Users\gerso\source\repo\gerson\simpliq\pocs\db
# Create and activate virtual environment (if not already done at repo root)
python -m venv .venv
.\.venv\Scripts\Activate.ps1
# Install dependencies
pip install -r requirements.txt
# This creates example.db with sample tables (users, products, orders)
python create_test_db.py
# Start the Flask server on http://127.0.0.1:8000
python mcp_server.py
Tip: use an alternate config file
# CLI flag (relative or absolute path)
python mcp_server.py --config .\config.dev.yml
python mcp_server.py -c C:\envs\simpliq\config.prod.yml
# Or via environment variable
$env:SIMPLIQ_CONFIG = ".\config.dev.yml"; python mcp_server.py
# (Also supported: SIMPLIQ_CONFIG_FILE)
The server prints the selected file at startup as:
Using configuration file: C:\full\path\to\config.yml
The easiest way to interact with SimpliqData:
# Activate the virtual environment
cd C:\Users\gerso\source\repo\gerson\simpliq\pocs\db
.\.venv\Scripts\Activate.ps1
# Run the interactive CLI client
python mcp_cli_client.py
This provides a user-friendly menu with all available tools.
For programmatic testing:
# Run the test client
python test_client.py
For AI-powered database interactions as a client, see GEMINI_CLI_INTEGRATION.md for step-by-step instructions on using SimpliqData with Google Gemini CLI.
You can choose which LLM provider the server uses to translate natural language into SQL.
Via environment variables (highest priority):
SIMPLIQ_NL2SQL_PROVIDER=gemini (or openai, anthropic, mock)GEMINI_API_KEY (or GOOGLE_API_KEY)
SIMPLIQ_GEMINI_MODEL (default: gemini-1.5-pro)SIMPLIQ_GEMINI_BASE_URL (default: https://generativelanguage.googleapis.com)OPENAI_API_KEY, optional SIMPLIQ_OPENAI_MODEL, SIMPLIQ_OPENAI_BASE_URLANTHROPIC_API_KEY, optional SIMPLIQ_ANTHROPIC_MODEL, SIMPLIQ_ANTHROPIC_BASE_URLVia config.yml (lower priority than env):
nl2sql:
provider: gemini # mock | openai | anthropic | gemini
gemini:
api_key: "AIza..."
model: "gemini-1.5-pro"
base_url: "https://generativelanguage.googleapis.com"
temperature: 0.1
max_tokens: 800
After setting the provider and keys, restart the server, then try:
# Example natural language query via MCP tool
python mcp_cli_client.py
# Choose: natural_query → "liste todos os usuários"
O servidor agora disponibiliza um conjunto ampliado de ferramentas MCP (contagem dinâmica). Além das originais de conexão, foram adicionadas ferramentas multi-usuário, organizações, chaves de API, inspeção de esquema e execução de SQL (somente leitura). A contagem exata é calculada em tempo de execução.
Principais destaques novos:
execute_sql / alias run_query: execução de consultas SELECT, com timeout, limit e include_metadata.describe_table, get_table_relationships.how_to_query (guia rápido de uso).Ferramentas base (exemplo simplificado):
| Tool | Descrição (PT-BR) | Parâmetros principais |
|---|---|---|
get_config | Retorna configuração atual e info do banco | — |
update_config | Atualiza a connection string | connection_string (obrigatório) |
check_status | Verifica status da conexão | — |
list_objects | Lista schemas, tabelas e views | — |
connect | Conecta usando a config atual | — |
disconnect | Desconecta o engine | — |
execute_sql | Executa SELECT somente leitura | sql (obrig.), timeout, limit, include_metadata |
run_query | Alias de execute_sql | Mesmos parâmetros de execute_sql |
describe_table | Descreve colunas, PK, FKs, índices de uma tabela | table_name (obrig.), schema (opcional) |
get_table_relationships | Lista relacionamentos (FKs) | schema (opcional) |
user_login | Autentica usuário | username, password |
whoami | Retorna usuário autenticado | — |
list_users | Lista usuários | — |
add_connection | Adiciona uma conexão para o usuário | name, connection_string (obrig.) |
list_connections | Lista conexões do usuário | — |
get_active_connection | Retorna conexão ativa | — |
activate_connection | Ativa uma conexão específica | connection_id (obrig.) |
create_api_key | Cria chave de API | name (obrig.), description, expires_in_days |
list_my_api_keys | Lista chaves do usuário | — |
revoke_api_key | Revoga chave de API | key_id (obrig.) |
Observação: outras ferramentas de organização (create/update/delete/list) também estão disponíveis; consultar tools/list para a lista completa.
how_to_queryDisponível via:
curl -X POST http://127.0.0.1:8000/ -H "Content-Type: application/json" \
-d '{"jsonrpc":"2.0","id":10,"method":"prompts/list","params":{}}'
curl -X POST http://127.0.0.1:8000/ -H "Content-Type: application/json" \
-d '{"jsonrpc":"2.0","id":11,"method":"prompts/get","params":{"name":"how_to_query"}}'
Resumo do conteúdo do prompt (PT-BR): autenticar → configurar/connect → consultar com run_query ou execute_sql.
Exemplo JSON-RPC para consulta:
curl -X POST http://127.0.0.1:8000/ -H "Content-Type: application/json" \
-d '{"jsonrpc":"2.0","id":20,"method":"tools/call","params":{"name":"run_query","arguments":{"sql":"SELECT * FROM users LIMIT 5"}}}'
Se receber erro de conexão ausente:
curl -X POST http://127.0.0.1:8000/ -H "Content-Type: application/json" \
-d '{"jsonrpc":"2.0","id":21,"method":"tools/call","params":{"name":"update_config","arguments":{"connection_string":"sqlite:///example.db"}}}'
curl -X POST http://127.0.0.1:8000/ -H "Content-Type: application/json" \
-d '{"jsonrpc":"2.0","id":22,"method":"tools/call","params":{"name":"connect","arguments":{}}}'
Depois repita a consulta.
python mcp_cli_client.py
# Then select from menu: 2 (Get Config), 4 (Connect), 5 (List Objects), etc.
python gemini_mcp_client.py "What database am I connected to?"
See GEMINI_CLI_INTEGRATION.md for complete setup instructions.
Once configured in .vscode/mcp.json, use the /list command to see available tools:
/list
# Shows: mcp_SimpliqData_get_config, mcp_SimpliqData_connect, etc.
Note: VS Code Copilot integration is functional for tool discovery, but @ symbol reference may not work in all cases. For reliable usage, use the CLI client or Gemini integration.
MCP server discovery endpoint. Returns server information and available tools.
Response:
{
"name": "Database MCP POC Server",
"version": "1.0.0",
"description": "A minimal MCP server for database connection management",
"protocol": "mcp",
"capabilities": {
"tools": true,
"prompts": false,
"resources": false
},
"tools": [...]
}
MCP tool execution endpoint using JSON-RPC 2.0 protocol. This endpoint handles MCP protocol initialization, tool listing, and tool execution as required by VS Code Copilot.
Supported JSON-RPC 2.0 Methods:
initialize - Protocol HandshakeRequest:
{
"jsonrpc": "2.0",
"id": 1,
"method": "initialize",
"params": {
"protocolVersion": "2025-06-18",
"capabilities": {},
"clientInfo": {
"name": "Visual Studio Code",
"version": "1.105.1"
}
}
}
Response:
{
"jsonrpc": "2.0",
"id": 1,
"result": {
"protocolVersion": "2025-06-18",
"capabilities": {
"tools": {},
"prompts": {},
"resources": {},
"logging": {}
},
"serverInfo": {
"name": "Database MCP POC Server",
"version": "1.0.0"
}
}
}
tools/list - List Available ToolsRequest:
curl -X POST http://127.0.0.1:8000/ -H "Content-Type: application/json" \
-d '{"jsonrpc":"2.0","id":2,"method":"tools/list","params":{}}'
Response:
{
"jsonrpc": "2.0",
"id": 2,
"result": {
"tools": [
{
"name": "get_config",
"description": "Get current database configuration and connection information",
"inputSchema": {"type": "object", "properties": {}}
},
...
]
}
}
The server also implements these optional MCP protocol methods for full compatibility:
prompts/list - Returns empty list (prompts feature not implemented)
curl -X POST http://127.0.0.1:8000/ -H "Content-Type: application/json" \
-d '{"jsonrpc":"2.0","id":10,"method":"prompts/list","params":{}}'
# Response: {"jsonrpc":"2.0","id":10,"result":{"prompts":[]}}
resources/list - Returns empty list (resources feature not implemented)
curl -X POST http://127.0.0.1:8000/ -H "Content-Type: application/json" \
-d '{"jsonrpc":"2.0","id":11,"method":"resources/list","params":{}}'
# Response: {"jsonrpc":"2.0","id":11,"result":{"resources":[]}}
logging/setLevel - Accepts logging requests (no-op implementation)
curl -X POST http://127.0.0.1:8000/ -H "Content-Type: application/json" \
-d '{"jsonrpc":"2.0","id":12,"method":"logging/setLevel","params":{"level":"info"}}'
# Response: {"jsonrpc":"2.0","id":12,"result":{}}
JSON-RPC Notifications - Accepts notifications (id=null, no response required)
curl -X POST http://127.0.0.1:8000/ -H "Content-Type: application/json" \
-d '{"jsonrpc":"2.0","id":null,"method":"notifications/initialized","params":{}}'
# Response: HTTP 204 No Content (successful notification received)
Note: Notifications are JSON-RPC messages with
"id": null. They don't require a response. Common notifications includenotifications/initialized,notifications/cancelled, etc.
tools/call - Execute a ToolExample - Get Config:
curl -X POST http://127.0.0.1:8000/ -H "Content-Type: application/json" \
-d '{"jsonrpc":"2.0","id":3,"method":"tools/call","params":{"name":"get_config","arguments":{}}}'
Example - Connect to Database:
curl -X POST http://127.0.0.1:8000/ -H "Content-Type: application/json" \
-d '{"jsonrpc":"2.0","id":4,"method":"tools/call","params":{"name":"connect","arguments":{}}}'
Example - List Database Objects:
curl -X POST http://127.0.0.1:8000/ -H "Content-Type: application/json" \
-d '{"jsonrpc":"2.0","id":5,"method":"tools/call","params":{"name":"list_objects","arguments":{}}}'
Tool Call Response Format:
{
"jsonrpc": "2.0",
"id": 3,
"result": {
"content": [
{
"type": "text",
"text": "{\"connection_string\": \"sqlite:///example.db\", \"db_info\": {...}}"
}
]
}
}
MCP protocol information endpoint.
Response:
{
"mcpVersion": "1.0",
"serverInfo": {
"name": "Database MCP POC Server",
"version": "1.0.0"
},
"capabilities": {
"tools": true
}
}
Retrieve the current database configuration and connection information.
Response:
{
"connection_string": "sqlite:///example.db",
"db_info": {
"database_type": "sqlite",
"database": "example.db",
"driver": null,
"host": null,
"port": null
}
}
Update the database connection string.
Request body:
{
"connection_string": "sqlite:///new_database.db"
}
Check if the database is accessible and connected.
Response:
{
"status": "connected",
"message": "Database is accessible"
}
List all database objects (schemas, tables, views) visible to the connected user.
Response:
{
"schemas": [],
"tables": ["users", "products", "orders"],
"views": ["order_details"],
"note": "User information is not available through SQLAlchemy inspection"
}
Connect or reconnect to the database using the current configuration.
Response:
{
"message": "Successfully connected to the database."
}
Close the current database connection.
Response:
{
"message": "Successfully disconnected from the database."
}
Ensure the server is running on http://127.0.0.1:8000:
python mcp_server.py
Ctrl+Alt+I)A configuration panel will appear
Enter the following information:
database-mcp-pochttp://127.0.0.1:8000Database connection and query toolClick Save or Add
Alternative: Manual Configuration
You can also manually add the server to .vscode/mcp.json:
{
"servers": {
"database-mcp-poc": {
"url": "http://127.0.0.1:8000",
"type": "http"
}
},
"inputs": []
}
@database-mcp-poc or similar (depending on how VS Code names it)You can now ask Copilot questions like:
If the chat interface doesn't show the tools option:
Ctrl+,)http://127.0.0.1:8000/configIf you can't find the "Configure Tools" option:
If the tool doesn't connect:
http://127.0.0.1:8000 in your browser (should return JSON server info)"jsonrpc": "2.0" messages)If you see HTTP 400 or 405 errors:
.vscode/mcp.json is exactly http://127.0.0.1:8000 (no /config or other paths)Protocol Information:
The server implements the Model Context Protocol (MCP) using JSON-RPC 2.0:
2025-06-18initialize, tools/list, tools/callprompts/list, resources/list, logging/setLevel (implemented but return empty/no-op){"content": [{"type": "text", "text": "..."}]}Test the MCP protocol that VS Code Copilot uses:
# 1. Initialize handshake
$body = '{"jsonrpc":"2.0","id":1,"method":"initialize","params":{"protocolVersion":"2025-06-18"}}'
curl -X POST http://127.0.0.1:8000/ -H "Content-Type: application/json" -d $body
# 2. List available tools
$body = '{"jsonrpc":"2.0","id":2,"method":"tools/list","params":{}}'
curl -X POST http://127.0.0.1:8000/ -H "Content-Type: application/json" -d $body
# 3. Call get_config tool
$body = '{"jsonrpc":"2.0","id":3,"method":"tools/call","params":{"name":"get_config","arguments":{}}}'
curl -X POST http://127.0.0.1:8000/ -H "Content-Type: application/json" -d $body
# 4. Connect to database
$body = '{"jsonrpc":"2.0","id":4,"method":"tools/call","params":{"name":"connect","arguments":{}}}'
curl -X POST http://127.0.0.1:8000/ -H "Content-Type: application/json" -d $body
# 5. List database objects
$body = '{"jsonrpc":"2.0","id":5,"method":"tools/call","params":{"name":"list_objects","arguments":{}}}'
curl -X POST http://127.0.0.1:8000/ -H "Content-Type: application/json" -d $body
You can also test the REST endpoints directly:
# Get configuration
curl http://127.0.0.1:8000/config
# Check status
curl http://127.0.0.1:8000/status
# Connect to database
curl -X POST http://127.0.0.1:8000/connect
# List database objects
curl http://127.0.0.1:8000/objects
# Update configuration
curl -X POST http://127.0.0.1:8000/config -H "Content-Type: application/json" -d '{\"connection_string\":\"sqlite:///test.db\"}'
# Disconnect
curl -X POST http://127.0.0.1:8000/disconnect
The create_test_db.py script creates the following structure:
Tables:
users: id, username, email, created_atproducts: id, name, description, price, stockorders: id, user_id, product_id, quantity, order_dateViews:
order_details: Joins orders with users and products informationThis server implements the Model Context Protocol (MCP) specification:
2025-06-18┌─────────────────┐ JSON-RPC 2.0 ┌──────────────────┐
│ VS Code │ ◄──────────────────────► │ Flask Server │
│ Copilot │ initialize, tools/* │ (mcp_server.py) │
└─────────────────┘ └──────────────────┘
│
▼
┌──────────────────┐
│ SQLAlchemy │
│ (DB Abstraction)│
└──────────────────┘
│
▼
┌──────────────────┐
│ SQLite/ │
│ PostgreSQL/etc │
└──────────────────┘
config.yml with postgresql://user:password@localhost:5432/dbnameconfig.yml with mysql+pymysql://user:password@localhost:3306/dbnameInstalling drivers for other databases:
# PostgreSQL
pip install psycopg2-binary
# MySQL
pip install pymysql
# SQL Server
pip install pyodbc
Current setup (Development):
For production use, implement:
✅ Servidor MCP Corrigido - Suporte POST Implementado! 🔧 Problema Resolvido: O erro 405 Method Not Allowed no POST / foi corrigido. O servidor agora suporta:
GET / - Descoberta do servidor (lista de ferramentas) POST / - Execução de ferramentas MCP ✨ Mudanças Implementadas: Endpoint raiz atualizado - Agora aceita GET e POST
Roteamento de ferramentas - POST processa as seguintes ferramentas:
get_config - Retorna configuração do banco update_config - Atualiza connection string check_status - Verifica status da conexão list_objects - Lista tabelas, views e schemas connect - Conecta ao banco disconnect - Desconecta do banco Formato de requisição MCP:
{ "method": "tool_name", "params": { }} 🧪 Testes Realizados: ✅ POST / {"method":"get_config"} → Retornou configuração ✅ POST / {"method":"check_status"} → Status: disconnected ✅ POST / {"method":"connect"} → Conectado com sucesso ✅ POST / {"method":"list_objects"} → Listou tabelas: users, products, orders, view: order_details
🚀 Próximos Passos: Recarregue o VS Code - Pressione Ctrl+Shift+P → "Reload Window" Teste no Copilot Chat: "@database-mcp-poc get config" "@database-mcp-poc list tables" "Using the database tool, show me what's in the database" O servidor agora está 100% compatível com o protocolo MCP e pronto para uso no VS Code Copilot! 🎉
Uso no chat Copilot VsCode
Vou verificar o status da conexão e também obter a configuração:
Ran check_status
Completed with input: {}
Ran get_config
Completed with input: {}
Perfeito! 🎉 As ferramentas MCP do SimpliqData estão funcionando no Claude Desktop!
sqlite:///example.dbQuer que eu:
#mcp_simpliqdata_connectExemplo para conectar:
#mcp_simpliqdata_connect
Depois de conectar, posso listar todas as tabelas e views disponíveis! 😊
FAQs
Simpliq Data Proxy MCP Server
We found that simpliq-mcp-server-dev-test demonstrated a healthy version release cadence and project activity because the last version was released less than a year ago. It has 1 open source maintainer collaborating on the project.
Did you know?
Socket for GitHub automatically highlights issues in each pull request and monitors the health of all your open source dependencies. Discover the contents of your packages and block harmful activity before you install or update your dependencies.
Security News
A misconfiguration gave three Anthropic models internet access, and one, believing it was in a simulation, shipped a credential-stealing package to PyPI.
Security News
/Company News
Socket has joined the new Composer and Packagist sponsorship program as a launch sponsor, supporting the team that keeps PHP's package ecosystem secure.
Research
/Security News
Benign-looking npm packages split malicious functionality across a dependency chain that deploys a cross-platform RAT targeting Alibaba developers.