数据库查询服务器
示例MCP服务器-Go（数据库查询服务器）-根据这些构建 规格
如何运行项目
配置
.env
在部署此设置之前，您需要在中配置以下值 .env 文件。
- POSTGRES_USER
- POSTGRES_PW
- POSTGRES_DB（可以是默认值）
- PG管理员默认电子邮件
- PGADMIN_DEFAULT_PASSWORD
使用docker compose进行部署
部署此设置时，pgAdmin web界面将在端口5050处可用（例如。http://localhost:5050).
$ docker compose up
Starting postgres ... done
Starting pgadmin ... done将postgres数据库添加到pgAdmin
使用.env文件的凭据登录后，您可以将数据库添加到pgAdmin。
- 右键单击左上角的“服务器”，然后选择“创建”->“服务器…”
- 命名您的连接
- 切换到“连接”选项卡并添加连接详细信息：
- 主机名：“postgres”（这通常是您的postgres数据库的IP地址-但是，docker可以通过其名称解析此容器的IP）
- 端口：“5432”
- 维护数据库：$POSTGRES_DB（请参阅.env）
- 用户名：$POSTGRES_USER（请参阅.env）
- 密码：$POSTGRES_PW（请参阅.env）
预期结果
检查容器是否正在运行：
$ docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
849c5f48f784 postgres:latest "docker-entrypoint.s…" 9 minutes ago Up 9 minutes 0.0.0.0:5432->5432/tcp, :::5432->5432/tcp postgres
d3cde3b455ee dpage/pgadmin4:latest "/entrypoint.sh" 9 minutes ago Up 9 minutes 443/tcp, 0.0.0.0:5050->80/tcp, :::5050->80/tcp pgadmin用以下方式停止容器
$ docker compose down
# To delete all data run:
$ docker compose down -v使用示例
执行一个简单的SELECT查询
{
"jsonrpc": "2.0",
"id": 1,
"method": "tools/call",
"params": {
"name": "execute_query",
"arguments": {
"database": "primary",
"query": "SELECT id, name, email FROM users WHERE active = $1",
"parameters": {"1": true},
"format": "json",
"limit": 100
}
}
}安全执行准备好的报表
{
"jsonrpc": "2.0",
"id": 1,
"method": "tools/call",
"params": {
"name": "execute_prepared",
"arguments": {
"database": "primary",
"StatementName": "SELECT id, name, email FROM users WHERE active = $1",
"parameters": {"1": true},
"format": "json"
}
}
}执行一个简单的SELECT查询
{
"jsonrpc": "2.0",
"id": 1,
"method": "tools/call",
"params": {
"name": "execute_query",
"arguments": {
"database": "primary",
"query": "SELECT id, name, email FROM users WHERE active = $1",
"parameters": {"1": true},
"format": "json",
"limit": 100
}
}
}获取表架构
echo '{
"jsonrpc": "2.0",
"id": 1,
"method": "tools/call",
"params": {
"name": "get_schema",
"arguments": {
"database": "primary",
"tables": ["users", "orders"],
"detailed": true
}
}
}' 获取连接状态
echo '{
"jsonrpc": "2.0",
"id": 1,
"method": "tools/call",
"params": {
"name": "get_connection_status",
"arguments": {
"database": "primary"//name of the DB you want to retrieve stats for
}
}
}' 示例用法
# Basic JSON-RPC request
curl -X POST http://localhost:8080/mcp \
-H "Content-Type: application/json" \
-H "Accept: application/json" \
-H "MCP-Protocol-Version: 2024-11-05" \
-d '{
"jsonrpc": "2.0",
"id": 1,
"method": "tools/call",
"params": {
"name": "execute_query",
"arguments": {
"database": "primary",
"query": "SELECT id, name, email FROM users WHERE active = $1",
"parameters": {"1": true},
"format": "json",
"limit": 100
}
}
}'初始化
curl -v -X POST http://localhost:8080/mcp \
-H "Content-Type: application/json" \
-d '{
"jsonrpc": "2.0",
"id": 1,
"method": "initialize",
"params": {
"protocolVersion": "2025-03-26",
"capabilities": {
"tools": {},
"resources": {},
"prompts": {}
},
"clientInfo": {
"name": "curl-client",
"version": "1.0"
}
}
}'用测试数据填充数据库
进步
通过使用以下SQL查询调用此MCP服务器，您可以轻松地用测试数据填充PostgreSQL数据库：
- 创建
Customers桌子
CREATE TABLE IF NOT EXISTS Customers (
id SERIAL PRIMARY KEY,
CustomerName VARCHAR(200),
ContactName VARCHAR(250),
Address VARCHAR(500),
City VARCHAR(250),
PostalCode VARCHAR(150),
Country VARCHAR(250),
created_at TIMESTAMP
)- 用数据更新表格
INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country)
VALUES
('Cardinal', 'Tom B. Erichsen', 'Skagen 21', 'Stavanger', '4006', 'Norway'),
('Greasy Burger', 'Per Olsen', 'Gateveien 15', 'Sandnes', '4306', 'Norway'),
('Tasty Tee', 'Finn Egan', 'Streetroad 19B', 'Liverpool', 'L1 0AA', 'UK');- 查询数据库
SELECT * FROM customers或者（别忘了提供所需的参数）
SELECT CustomerName, Address FROM customers WHERE Country =$1 AND City LIKE $2示例
{
"jsonrpc": "2.0",
"id": 1,
"method": "tools/call",
"params": {
"name": "execute_query",
"arguments": {
"database": "primary",
"query": "SELECT CustomerName, Address FROM customers WHERE Country =$1 AND City LIKE $2",
"parameters": {"1": "UK", "2": "L%"},
"format": "json",
"limit": 100
}
}
}