Postgresql MCP Server Wt6

🚀 PostgreSQL MCP Server

This is a Model Context Protocol (MCP) server that provides an interface for interacting with a PostgreSQL database. It allows you to execute queries, perform database operations, and manage transactions through a standard API.

🚀 Quick Start

This server offers an interface for interacting with a PostgreSQL database, enabling you to execute queries, perform database operations, and manage transactions via a standard API.

✨ Features

• Execute parameterized SQL queries to prevent SQL injection.
• Perform insert, update, and delete operations.
• Execute multiple statements within a transaction.
• Retrieve database schema information.
• Securely handle parameters to prevent SQL injection.

📦 Installation

1. Clone the repository:

git clone https://github.com/yourusername/postgres-mcp.git
cd postgres-mcp

2. Download dependencies:

go mod download

3. Configure the database connection:

cp .env.example .env

Edit the .env file with your PostgreSQL database credentials.

4. Build the server:

go build -o postgres-mcp

5. Run the server:

./postgres-mcp

💻 Usage Examples

Basic Usage

The server exposes the following MCP tools:

1. execute_tool

Execute a single SQL statement.

Request:

{
  "statement": "INSERT INTO users(name, email) VALUES($1, $2)",
  "arguments": ["Jane Doe", "jane@example.com"]
}

Response:

{
  "rows affected": 1
}

2. query_tool

Execute a SELECT query and return the results.

Request:

{
  "statement": "SELECT * FROM users WHERE id = $1",
  "arguments": [1]
}

Response:

{
  "rows": [
    {
      "id": 1,
      "name": "John Doe",
      "email": "john@example.com"
    }
  ],
  "columns": ["id", "name", "email"],
  "rowCount": 1
}

3. transaction_tool

Execute multiple statements within a transaction.

Request:

{
  "statements": [
    "INSERT INTO users(name, email) VALUES($1, $2)",
    "INSERT INTO user_roles(user_id, role) VALUES($1, $2)"
  ],
  "arguments": [
    ["Alice", "alice@example.com"],
    [1, "admin"]
  ]
}

Response:

{
  "results": [
    {
      "statement": 0,
      "rowsAffected": 1
    },
    {
      "statement": 1,
      "rowsAffected": 1
    }
  ]
}

4. schema_tool

Retrieve table schema information.

Request:

{
  "table_name": "users"
}

Response:

{
  "table": "users",
  "columns": [
    {
      "name": "id",
      "type": "integer",
      "nullable": false,
      "default": "nextval('users_id_seq'::regclass)"
    },
    {
      "name": "name",
      "type": "text",
      "nullable": true
    }
  ]
}

Advanced Usage

Here is an example of using Go to call the query_tool:

package main

import (
	"bytes"
	"encoding/json"
	"fmt"
	"io/ioutil"
	"net/http"
)

type QueryRequest struct {
	Statement string   `json:"statement"`
	Arguments []string `json:"arguments"`
}

func main() {
	query := &QueryRequest{
		Statement: "SELECT * FROM users WHERE id = $1",
		Arguments: []string{strconv.Itoa(1)},
	}

	jsonReq, err := json.Marshal(query)
	if err != nil {
		panic(err)
	}

	resp, err := http.Post("http://localhost:8080/query", "application/json", bytes.NewBuffer(jsonReq))
	if err != nil {
		panic(err)
	}

	defer resp.Body.Close()
	body, err := ioutil.ReadAll(resp.Body)
	if err != nil {
		panic(err)
	}

	var result map[string]interface{}
	json.Unmarshal(body, &result)
	fmt.Printf("%#v\n", result)
}

📚 Documentation

Security Precautions

• Use parameterized queries to prevent SQL injection.
• Limit the permissions of database users.
• Enable SSL/TLS encryption in a production environment.
• Avoid exposing sensitive information in queries.
• Regularly audit and monitor database activities.

📄 License

This project is licensed under the MIT License.