# 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:
--------------------------------------------------------------------------------
```
1 | # If you prefer the allow list template instead of the deny list, see community template:
2 | # https://github.com/github/gitignore/blob/main/community/Golang/Go.AllowList.gitignore
3 | #
4 | # Binaries for programs and plugins
5 | *.exe
6 | *.exe~
7 | *.dll
8 | *.so
9 | *.dylib
10 |
11 | # Test binary, built with `go test -c`
12 | *.test
13 |
14 | # Output of the go coverage tool, specifically when used with LiteIDE
15 | *.out
16 |
17 | # Dependency directories (remove the comment below to include it)
18 | # vendor/
19 |
20 | # Go workspace file
21 | go.work
22 | go.work.sum
23 |
24 | # env file
25 | .env
26 | .vscode/
27 |
```
--------------------------------------------------------------------------------
/README.md:
--------------------------------------------------------------------------------
```markdown
1 | # Postgres MCP (Research Project)
2 |
3 | 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.
4 |
5 | ## Features
6 |
7 | - MCP-compliant HTTP server
8 | - Read-only SQL query execution
9 | - Database resource listing
10 | - Environment variable support through `.env` files
11 | - Built with Go and Gin web framework
12 |
13 | ## Prerequisites
14 |
15 | - Go 1.23.6 or later
16 | - PostgreSQL database
17 | - Git
18 |
19 | ## Installation
20 |
21 | ```bash
22 | git clone https://github.com/ipfans/postgres-mcp.git
23 | cd postgres-mcp
24 | go mod download
25 | ```
26 |
27 | ## Configuration
28 |
29 | You can configure the database connection in two ways:
30 |
31 | 1. Using command-line flags:
32 |
33 | ```bash
34 | go run cmd/postgres-mcp/main.go -db "postgres://user:password@localhost:5432/dbname"
35 | ```
36 |
37 | 2. Using environment variables:
38 | - Create a `.env` file in the project root
39 | - Add your database URL:
40 | ```
41 | DATABASE_URL=postgres://user:password@localhost:5432/dbname
42 | ```
43 |
44 | ## Usage
45 |
46 | 1. Start the server:
47 |
48 | ```bash
49 | go run cmd/postgres-mcp/main.go
50 | ```
51 |
52 | The server will start on port 8080 by default.
53 |
54 | 2. Interact with the MCP endpoints:
55 |
56 | - List database resources:
57 |
58 | ```
59 | POST http://localhost:8080/mcp
60 | Content-Type: application/json
61 |
62 | {
63 | "type": "function",
64 | "name": "resources"
65 | }
66 | ```
67 |
68 | - Execute a read-only query:
69 |
70 | ```
71 | POST http://localhost:8080/mcp
72 | Content-Type: application/json
73 |
74 | {
75 | "type": "function",
76 | "name": "query",
77 | "arguments": {
78 | "query": "SELECT * FROM your_table LIMIT 10"
79 | }
80 | }
81 | ```
82 |
83 | ## License
84 |
85 | This project is open source and available under the MIT License.
86 |
87 | ## Contributing
88 |
89 | Contributions are welcome! Please feel free to submit a Pull Request.
90 |
```
--------------------------------------------------------------------------------
/Taskfile.yml:
--------------------------------------------------------------------------------
```yaml
1 | # https://taskfile.dev
2 |
3 | version: "3"
4 |
5 | tasks:
6 | default:
7 | cmds:
8 | - go run cmd/postgres-mcp/main.go -db "postgres://postgres:postgres@localhost:5432/postgres"
9 |
```
--------------------------------------------------------------------------------
/db.go:
--------------------------------------------------------------------------------
```go
1 | package postgresmcp
2 |
3 | import (
4 | "context"
5 |
6 | "github.com/jackc/pgx/v5"
7 | )
8 |
9 | // DatabaseQuerier 定义数据库查询接口
10 | type DatabaseQuerier interface {
11 | Query(ctx context.Context, sql string, args ...interface{}) (pgx.Rows, error)
12 | Begin(ctx context.Context) (pgx.Tx, error)
13 | }
14 |
```
--------------------------------------------------------------------------------
/cmd/postgres-mcp/main.go:
--------------------------------------------------------------------------------
```go
1 | package main
2 |
3 | import (
4 | "flag"
5 | "fmt"
6 | "os"
7 |
8 | postgresmcp "github.com/ipfans/postgres-mcp"
9 | "github.com/joho/godotenv"
10 | )
11 |
12 | func main() {
13 | var dbString string
14 | flag.StringVar(&dbString, "db", "", "Database URL. (e.g. postgres://postgres:postgres@localhost:5432/postgres )")
15 | flag.Parse()
16 |
17 | if dbString == "" {
18 | godotenv.Load()
19 | dbString = os.Getenv("DATABASE_URL")
20 | if dbString == "" {
21 | fmt.Print("Please provide a database URL, DATABASE_URL environment variable or a dotenv file.\n\n")
22 | flag.PrintDefaults()
23 | return
24 | }
25 | }
26 |
27 | postgresmcp.Server(dbString)
28 | }
29 |
```
--------------------------------------------------------------------------------
/resource_mgr.go:
--------------------------------------------------------------------------------
```go
1 | package postgresmcp
2 |
3 | import (
4 | "context"
5 | "fmt"
6 |
7 | mcp_golang "github.com/metoro-io/mcp-golang"
8 | )
9 |
10 | // ResourceManager 处理资源相关操作
11 | type ResourceManager struct {
12 | db DatabaseQuerier
13 | baseURL string
14 | }
15 |
16 | func NewResourceManager(db DatabaseQuerier, baseURL string) *ResourceManager {
17 | return &ResourceManager{
18 | db: db,
19 | baseURL: baseURL,
20 | }
21 | }
22 |
23 | func (rm *ResourceManager) ListResources(ctx context.Context) ([]*mcp_golang.Content, error) {
24 | rows, err := rm.db.Query(ctx, `
25 | SELECT
26 | t.table_name,
27 | array_agg(
28 | c.column_name || ' ' || c.data_type
29 | ORDER BY c.ordinal_position
30 | ) as columns
31 | FROM information_schema.tables t
32 | JOIN information_schema.columns c
33 | ON c.table_name = t.table_name
34 | AND c.table_schema = t.table_schema
35 | WHERE t.table_schema = 'public'
36 | GROUP BY t.table_name`)
37 | if err != nil {
38 | return nil, err
39 | }
40 | defer rows.Close()
41 |
42 | var resources []*mcp_golang.Content
43 | for rows.Next() {
44 | var tableName string
45 | var columns []string
46 | if err := rows.Scan(&tableName, &columns); err != nil {
47 | return nil, err
48 | }
49 |
50 | schemaInfo := fmt.Sprintf("表名: %s\n字段:\n", tableName)
51 | for _, col := range columns {
52 | schemaInfo += fmt.Sprintf("- %s\n", col)
53 | }
54 | content := mcp_golang.NewTextContent(schemaInfo)
55 | resources = append(resources, content)
56 | }
57 | return resources, rows.Err()
58 | }
59 |
```
--------------------------------------------------------------------------------
/query.go:
--------------------------------------------------------------------------------
```go
1 | package postgresmcp
2 |
3 | import (
4 | "context"
5 | "encoding/json"
6 | "fmt"
7 | "log"
8 |
9 | "github.com/jackc/pgx/v5"
10 | mcp_golang "github.com/metoro-io/mcp-golang"
11 | )
12 |
13 | // QueryExecutor 处理数据库查询操作
14 | type QueryExecutor struct {
15 | db DatabaseQuerier
16 | }
17 |
18 | func NewQueryExecutor(db DatabaseQuerier) *QueryExecutor {
19 | return &QueryExecutor{db: db}
20 | }
21 |
22 | func (qe *QueryExecutor) ExecuteReadOnlyQuery(ctx context.Context, query string) (*mcp_golang.ToolResponse, error) {
23 | tx, err := qe.db.Begin(ctx)
24 | if err != nil {
25 | return nil, err
26 | }
27 | defer func() {
28 | if err := tx.Rollback(ctx); err != nil && err != pgx.ErrTxClosed {
29 | log.Printf("Could not rollback transaction: %v", err)
30 | }
31 | }()
32 |
33 | if _, err := tx.Exec(ctx, "BEGIN TRANSACTION READ ONLY"); err != nil {
34 | return nil, err
35 | }
36 |
37 | rows, err := tx.Query(ctx, query)
38 | if err != nil {
39 | return nil, err
40 | }
41 | defer rows.Close()
42 |
43 | results := []map[string]interface{}{}
44 | columnNames := rows.FieldDescriptions()
45 | for rows.Next() {
46 | values, err := rows.Values()
47 | if err != nil {
48 | return nil, err
49 | }
50 | rowMap := make(map[string]interface{})
51 | for i, col := range columnNames {
52 | rowMap[col.Name] = values[i]
53 | }
54 | results = append(results, rowMap)
55 | }
56 |
57 | if err := rows.Err(); err != nil {
58 | return nil, err
59 | }
60 |
61 | jsonResult, err := json.MarshalIndent(results, "", " ")
62 | if err != nil {
63 | return nil, fmt.Errorf("failed to marshal query results to JSON: %w", err)
64 | }
65 |
66 | if err := tx.Commit(ctx); err != nil {
67 | return nil, err
68 | }
69 |
70 | content := mcp_golang.NewTextContent(string(jsonResult))
71 | return &mcp_golang.ToolResponse{
72 | Content: []*mcp_golang.Content{content},
73 | }, nil
74 | }
75 |
```
--------------------------------------------------------------------------------
/server.go:
--------------------------------------------------------------------------------
```go
1 | package postgresmcp
2 |
3 | import (
4 | "context"
5 | "fmt"
6 | "log"
7 |
8 | "github.com/gin-gonic/gin"
9 | "github.com/jackc/pgx/v5/pgxpool"
10 | mcp_golang "github.com/metoro-io/mcp-golang"
11 | "github.com/metoro-io/mcp-golang/transport/http"
12 | )
13 |
14 | // MCPServer 处理MCP服务器配置和启动
15 | type MCPServer struct {
16 | server *mcp_golang.Server
17 | transport *http.GinTransport
18 | resources *ResourceManager
19 | queryExec *QueryExecutor
20 | }
21 |
22 | type QueryArguments struct {
23 | Query string `json:"query" jsonschema:"required,description=The sql query to execute"`
24 | }
25 |
26 | func NewMCPServer(dbString string, baseURL string) (*MCPServer, error) {
27 | dbpool, err := pgxpool.New(context.Background(), dbString)
28 | if err != nil {
29 | return nil, fmt.Errorf("unable to connect to database: %w", err)
30 | }
31 |
32 | transport := http.NewGinTransport()
33 | server := mcp_golang.NewServer(transport)
34 | resources := NewResourceManager(dbpool, baseURL)
35 | queryExec := NewQueryExecutor(dbpool)
36 |
37 | return &MCPServer{
38 | server: server,
39 | transport: transport,
40 | resources: resources,
41 | queryExec: queryExec,
42 | }, nil
43 | }
44 |
45 | func (s *MCPServer) registerHandlers() error {
46 | // 注册资源处理器
47 | if err := s.server.RegisterResource(
48 | "resources",
49 | "Database Resources",
50 | "List all database tables",
51 | "application/json",
52 | s.resources.ListResources,
53 | ); err != nil {
54 | return err
55 | }
56 |
57 | // 注册查询工具
58 | if err := s.server.RegisterTool(
59 | "query",
60 | "Run a read-only SQL query",
61 | func(ctx context.Context, req QueryArguments) (*mcp_golang.ToolResponse, error) {
62 | return s.queryExec.ExecuteReadOnlyQuery(ctx, req.Query)
63 | },
64 | ); err != nil {
65 | return err
66 | }
67 |
68 | return nil
69 | }
70 |
71 | func (s *MCPServer) Start() error {
72 | if err := s.registerHandlers(); err != nil {
73 | return err
74 | }
75 |
76 | router := gin.Default()
77 | router.POST("/mcp", s.transport.Handler())
78 | return router.Run(":8080")
79 | }
80 |
81 | // Server 是主入口函数
82 | func Server(dbString string) {
83 | baseURL := "http://localhost:8080/resources"
84 | server, err := NewMCPServer(dbString, baseURL)
85 | if err != nil {
86 | log.Fatalf("Failed to create server: %v", err)
87 | return
88 | }
89 |
90 | if err := server.Start(); err != nil {
91 | log.Fatalf("Failed to start server: %v", err)
92 | }
93 | }
94 |
```