# Directory Structure
```
├── .gitignore
├── cmd
│ └── postgres-mcp
│ └── main.go
├── db.go
├── go.mod
├── go.sum
├── query.go
├── README.md
├── resource_mgr.go
├── server.go
└── Taskfile.yml
```
# Files
--------------------------------------------------------------------------------
/.gitignore:
--------------------------------------------------------------------------------
```
# If you prefer the allow list template instead of the deny list, see community template:
# https://github.com/github/gitignore/blob/main/community/Golang/Go.AllowList.gitignore
#
# Binaries for programs and plugins
*.exe
*.exe~
*.dll
*.so
*.dylib
# Test binary, built with `go test -c`
*.test
# Output of the go coverage tool, specifically when used with LiteIDE
*.out
# Dependency directories (remove the comment below to include it)
# vendor/
# Go workspace file
go.work
go.work.sum
# env file
.env
.vscode/
```
--------------------------------------------------------------------------------
/README.md:
--------------------------------------------------------------------------------
```markdown
# Postgres MCP (Research Project)
A Model Control Protocol (MCP) server implementation for PostgreSQL databases. This project provides a simple HTTP interface to interact with PostgreSQL databases through MCP, allowing you to execute read-only queries and explore database resources.
## Features
- MCP-compliant HTTP server
- Read-only SQL query execution
- Database resource listing
- Environment variable support through `.env` files
- Built with Go and Gin web framework
## Prerequisites
- Go 1.23.6 or later
- PostgreSQL database
- Git
## Installation
```bash
git clone https://github.com/ipfans/postgres-mcp.git
cd postgres-mcp
go mod download
```
## Configuration
You can configure the database connection in two ways:
1. Using command-line flags:
```bash
go run cmd/postgres-mcp/main.go -db "postgres://user:password@localhost:5432/dbname"
```
2. Using environment variables:
- Create a `.env` file in the project root
- Add your database URL:
```
DATABASE_URL=postgres://user:password@localhost:5432/dbname
```
## Usage
1. Start the server:
```bash
go run cmd/postgres-mcp/main.go
```
The server will start on port 8080 by default.
2. Interact with the MCP endpoints:
- List database resources:
```
POST http://localhost:8080/mcp
Content-Type: application/json
{
"type": "function",
"name": "resources"
}
```
- Execute a read-only query:
```
POST http://localhost:8080/mcp
Content-Type: application/json
{
"type": "function",
"name": "query",
"arguments": {
"query": "SELECT * FROM your_table LIMIT 10"
}
}
```
## License
This project is open source and available under the MIT License.
## Contributing
Contributions are welcome! Please feel free to submit a Pull Request.
```
--------------------------------------------------------------------------------
/Taskfile.yml:
--------------------------------------------------------------------------------
```yaml
# https://taskfile.dev
version: "3"
tasks:
default:
cmds:
- go run cmd/postgres-mcp/main.go -db "postgres://postgres:postgres@localhost:5432/postgres"
```
--------------------------------------------------------------------------------
/db.go:
--------------------------------------------------------------------------------
```go
package postgresmcp
import (
"context"
"github.com/jackc/pgx/v5"
)
// DatabaseQuerier 定义数据库查询接口
type DatabaseQuerier interface {
Query(ctx context.Context, sql string, args ...interface{}) (pgx.Rows, error)
Begin(ctx context.Context) (pgx.Tx, error)
}
```
--------------------------------------------------------------------------------
/cmd/postgres-mcp/main.go:
--------------------------------------------------------------------------------
```go
package main
import (
"flag"
"fmt"
"os"
postgresmcp "github.com/ipfans/postgres-mcp"
"github.com/joho/godotenv"
)
func main() {
var dbString string
flag.StringVar(&dbString, "db", "", "Database URL. (e.g. postgres://postgres:postgres@localhost:5432/postgres )")
flag.Parse()
if dbString == "" {
godotenv.Load()
dbString = os.Getenv("DATABASE_URL")
if dbString == "" {
fmt.Print("Please provide a database URL, DATABASE_URL environment variable or a dotenv file.\n\n")
flag.PrintDefaults()
return
}
}
postgresmcp.Server(dbString)
}
```
--------------------------------------------------------------------------------
/resource_mgr.go:
--------------------------------------------------------------------------------
```go
package postgresmcp
import (
"context"
"fmt"
mcp_golang "github.com/metoro-io/mcp-golang"
)
// ResourceManager 处理资源相关操作
type ResourceManager struct {
db DatabaseQuerier
baseURL string
}
func NewResourceManager(db DatabaseQuerier, baseURL string) *ResourceManager {
return &ResourceManager{
db: db,
baseURL: baseURL,
}
}
func (rm *ResourceManager) ListResources(ctx context.Context) ([]*mcp_golang.Content, error) {
rows, err := rm.db.Query(ctx, `
SELECT
t.table_name,
array_agg(
c.column_name || ' ' || c.data_type
ORDER BY c.ordinal_position
) as columns
FROM information_schema.tables t
JOIN information_schema.columns c
ON c.table_name = t.table_name
AND c.table_schema = t.table_schema
WHERE t.table_schema = 'public'
GROUP BY t.table_name`)
if err != nil {
return nil, err
}
defer rows.Close()
var resources []*mcp_golang.Content
for rows.Next() {
var tableName string
var columns []string
if err := rows.Scan(&tableName, &columns); err != nil {
return nil, err
}
schemaInfo := fmt.Sprintf("表名: %s\n字段:\n", tableName)
for _, col := range columns {
schemaInfo += fmt.Sprintf("- %s\n", col)
}
content := mcp_golang.NewTextContent(schemaInfo)
resources = append(resources, content)
}
return resources, rows.Err()
}
```
--------------------------------------------------------------------------------
/query.go:
--------------------------------------------------------------------------------
```go
package postgresmcp
import (
"context"
"encoding/json"
"fmt"
"log"
"github.com/jackc/pgx/v5"
mcp_golang "github.com/metoro-io/mcp-golang"
)
// QueryExecutor 处理数据库查询操作
type QueryExecutor struct {
db DatabaseQuerier
}
func NewQueryExecutor(db DatabaseQuerier) *QueryExecutor {
return &QueryExecutor{db: db}
}
func (qe *QueryExecutor) ExecuteReadOnlyQuery(ctx context.Context, query string) (*mcp_golang.ToolResponse, error) {
tx, err := qe.db.Begin(ctx)
if err != nil {
return nil, err
}
defer func() {
if err := tx.Rollback(ctx); err != nil && err != pgx.ErrTxClosed {
log.Printf("Could not rollback transaction: %v", err)
}
}()
if _, err := tx.Exec(ctx, "BEGIN TRANSACTION READ ONLY"); err != nil {
return nil, err
}
rows, err := tx.Query(ctx, query)
if err != nil {
return nil, err
}
defer rows.Close()
results := []map[string]interface{}{}
columnNames := rows.FieldDescriptions()
for rows.Next() {
values, err := rows.Values()
if err != nil {
return nil, err
}
rowMap := make(map[string]interface{})
for i, col := range columnNames {
rowMap[col.Name] = values[i]
}
results = append(results, rowMap)
}
if err := rows.Err(); err != nil {
return nil, err
}
jsonResult, err := json.MarshalIndent(results, "", " ")
if err != nil {
return nil, fmt.Errorf("failed to marshal query results to JSON: %w", err)
}
if err := tx.Commit(ctx); err != nil {
return nil, err
}
content := mcp_golang.NewTextContent(string(jsonResult))
return &mcp_golang.ToolResponse{
Content: []*mcp_golang.Content{content},
}, nil
}
```
--------------------------------------------------------------------------------
/server.go:
--------------------------------------------------------------------------------
```go
package postgresmcp
import (
"context"
"fmt"
"log"
"github.com/gin-gonic/gin"
"github.com/jackc/pgx/v5/pgxpool"
mcp_golang "github.com/metoro-io/mcp-golang"
"github.com/metoro-io/mcp-golang/transport/http"
)
// MCPServer 处理MCP服务器配置和启动
type MCPServer struct {
server *mcp_golang.Server
transport *http.GinTransport
resources *ResourceManager
queryExec *QueryExecutor
}
type QueryArguments struct {
Query string `json:"query" jsonschema:"required,description=The sql query to execute"`
}
func NewMCPServer(dbString string, baseURL string) (*MCPServer, error) {
dbpool, err := pgxpool.New(context.Background(), dbString)
if err != nil {
return nil, fmt.Errorf("unable to connect to database: %w", err)
}
transport := http.NewGinTransport()
server := mcp_golang.NewServer(transport)
resources := NewResourceManager(dbpool, baseURL)
queryExec := NewQueryExecutor(dbpool)
return &MCPServer{
server: server,
transport: transport,
resources: resources,
queryExec: queryExec,
}, nil
}
func (s *MCPServer) registerHandlers() error {
// 注册资源处理器
if err := s.server.RegisterResource(
"resources",
"Database Resources",
"List all database tables",
"application/json",
s.resources.ListResources,
); err != nil {
return err
}
// 注册查询工具
if err := s.server.RegisterTool(
"query",
"Run a read-only SQL query",
func(ctx context.Context, req QueryArguments) (*mcp_golang.ToolResponse, error) {
return s.queryExec.ExecuteReadOnlyQuery(ctx, req.Query)
},
); err != nil {
return err
}
return nil
}
func (s *MCPServer) Start() error {
if err := s.registerHandlers(); err != nil {
return err
}
router := gin.Default()
router.POST("/mcp", s.transport.Handler())
return router.Run(":8080")
}
// Server 是主入口函数
func Server(dbString string) {
baseURL := "http://localhost:8080/resources"
server, err := NewMCPServer(dbString, baseURL)
if err != nil {
log.Fatalf("Failed to create server: %v", err)
return
}
if err := server.Start(); err != nil {
log.Fatalf("Failed to start server: %v", err)
}
}
```