PostgreSQL MCP Server

Instructions

Implementation Reference

• src/index.ts:65-115 (registration)

  Registration of the 'connect_database' tool using server.tool(). Includes tool name, description, input schema defined with Zod, and inline asynchronous handler function.

  server.tool(
    "connect_database",
    "Connect to a PostgreSQL database",
    {
      host: z.string().describe("Database host (default: localhost)").optional(),
      port: z.number().describe("Database port (default: 5432)").optional(),
      database: z.string().describe("Database name"),
      username: z.string().describe("Database username"),
      password: z.string().describe("Database password").optional(),
      ssl: z.boolean().describe("Use SSL connection (default: false)").optional(),
    },
    async ({ host, port, database, username, password, ssl }) => {
      try {
        // Close existing connection if any
        if (dbClient) {
          await dbClient.end();
        }
  
        // Create new connection
        dbClient = new Client({
          host: host || "localhost",
          port: port || 5432,
          database,
          user: username,
          password,
          ssl: ssl || false,
        });
  
        await dbClient.connect();
  
        return {
          content: [
            {
              type: "text",
              text: `Successfully connected to PostgreSQL database '${database}' on ${host || "localhost"}:${port || 5432}`,
            },
          ],
        };
      } catch (error) {
        const errorMessage = error instanceof Error ? error.message : "Unknown connection error";
        return {
          content: [
            {
              type: "text",
              text: `Failed to connect to database: ${errorMessage}`,
            },
          ],
        };
      }
    }
  );

• src/index.ts:76-114 (handler)

  The handler function for the connect_database tool. It closes any existing database connection, creates a new PostgreSQL Client with the provided configuration (using defaults where applicable), connects to the database, and returns a success message or error details.

  async ({ host, port, database, username, password, ssl }) => {
    try {
      // Close existing connection if any
      if (dbClient) {
        await dbClient.end();
      }
  
      // Create new connection
      dbClient = new Client({
        host: host || "localhost",
        port: port || 5432,
        database,
        user: username,
        password,
        ssl: ssl || false,
      });
  
      await dbClient.connect();
  
      return {
        content: [
          {
            type: "text",
            text: `Successfully connected to PostgreSQL database '${database}' on ${host || "localhost"}:${port || 5432}`,
          },
        ],
      };
    } catch (error) {
      const errorMessage = error instanceof Error ? error.message : "Unknown connection error";
      return {
        content: [
          {
            type: "text",
            text: `Failed to connect to database: ${errorMessage}`,
          },
        ],
      };
    }
  }

• src/index.ts:69-75 (schema)

  Input schema for the connect_database tool, defined using Zod object with parameters for PostgreSQL connection: host, port, database, username, password, ssl, including descriptions and optionals.

    host: z.string().describe("Database host (default: localhost)").optional(),
    port: z.number().describe("Database port (default: 5432)").optional(),
    database: z.string().describe("Database name"),
    username: z.string().describe("Database username"),
    password: z.string().describe("Database password").optional(),
    ssl: z.boolean().describe("Use SSL connection (default: false)").optional(),
  },