MCP Server Postgres

🚀 PostgreSQL Server MCP Database Function Description

This README provides a detailed description of the database functions of the PostgreSQL Server MCP, including database connection classes, auxiliary functions, main database function functions, and exposed MCP tool APIs.

🚀 Quick Start

To use these database functions, you first need to ensure that your PostgreSQL database is properly configured and running. Then, you can use the DatabaseConnection class to establish a connection to the database and call various functions through this connection.

✨ Features

Database Connection Class

DatabaseConnection

• Purpose: Manages the connection to the PostgreSQL database.
• Constructor Parameters:
  • host: str (Default: "localhost")
  • database: str (Default: "postgres")
  • user: str (Default: "postgres")
  • password: str (Default: "codenexus")
• Methods:
  • close(): Closes the connection to the database.

Helper Functions

json_serializer(obj: Any) -> Any

• Purpose: Performs JSON serialization on special data types.
• Handling:
  • datetime/date objects → ISO format strings
  • Decimal numbers → Floating-point numbers

get_db() -> DatabaseConnection

• Purpose: Obtains a database connection using the singleton pattern.
• Returns: A global database connection instance or None (if the connection fails).

Main Database Function Functions

list_tables(db: DatabaseConnection) -> str

• Purpose: Lists all tables in the database.
• Returns: A formatted string containing a list of table names.

get_table_info(db: DatabaseConnection, table: str) -> str

• Purpose: Retrieves detailed information about a specific table.
• Returns: A formatted string containing:
  • Column information (name, type, length, nullable, default value)
  • Primary key information
  • Total number of rows

query_table(db: DatabaseConnection, table: str, limit: Optional[int] = 5) -> str

• Purpose: Queries data in a table.
• Parameters:
  • table: The name of the table to query.
  • limit: The maximum number of rows to return (Default: 5).
• Returns: A formatted string containing the query results.

execute_query(db: DatabaseConnection, query: str, params: Optional[tuple] = None) -> str

• Purpose: Executes a custom SQL query.
• Parameters:
  • query: The SQL query statement to execute.
  • params: Query parameters (optional).
• Returns: The query results or the number of affected rows.

MCP Tools (Exposed APIs)

@mcp.tool() list_tables_tool(args: dict) -> str

• Purpose: A listing interface for listing tables.
• Returns: A list of available tables or an error message.

@mcp.tool() get_table_info_tool(args: dict) -> str

• Purpose: A table information interface for retrieving detailed information.
• Required Parameters:
  • table: The name of the table to check.
• Returns: Detailed information about the table or an error message.

@mcp.tool() query_table_tool(args: dict) -> str

• Purpose: An interface for querying table data.
• Required Parameters:
  • table: The name of the table to query.
• Optional Parameters:
  • limit: The maximum number of rows to return (Default: 5).
• Returns: The query results or an error message.

@mcp.tool() execute_query_tool(args: dict) -> str

• Purpose: An interface for executing custom SQL queries.
• Required Parameters:
  • query: The query statement to execute.
• Optional Parameters:
  • params: Query parameters.
• Returns: The query results or an error message.