Simple PostgreSQL MCP Server

Instructions

Implementation Reference

• src/tools/query.ts:36-69 (handler)

  The core handler function that executes the SQL query against the PostgreSQL database using DatabaseConnection, enforces read-only restrictions, measures execution time, and returns structured results.
  export async function executeQuery( connectionString: string, query: string, readOnly: boolean = false ): Promise<QueryResult> { const db = DatabaseConnection.getInstance(); const startTime = Date.now(); try { // Check if query is allowed in readOnly mode if (readOnly && !isReadOnlyQuery(query)) { throw new Error('Only read-only queries are allowed when readOnly is set to true'); } await db.connect(connectionString); // Execute the query const result = await db.query(query); const executionTime = Date.now() - startTime; return { success: true, rowCount: result.rowCount || 0, rows: result.rows || [], command: result.command || 'UNKNOWN', executionTime }; } catch (error) { const executionTime = Date.now() - startTime; throw new Error(`Query execution failed: ${error instanceof Error ? error.message : String(error)} (execution time: ${executionTime}ms)`); } finally { await db.disconnect(); } }
• src/index.ts:15-32 (schema)

  The tool definition including name, description, and input schema for validating arguments to execute_query.
  name: 'execute_query', description: 'Execute SQL queries on PostgreSQL database', inputSchema: { type: 'object', properties: { query: { type: 'string', description: 'SQL query to execute' }, readOnly: { type: 'boolean', description: 'If true, only allows read-only queries (SELECT, EXPLAIN, etc.)', default: false } }, required: ['query'] } }
• src/index.ts:50-53 (registration)

  Registration of the execute_query tool in the MCP server capabilities.
  capabilities: { tools: { execute_query: TOOL_DEFINITIONS[0] },
• src/index.ts:78-96 (registration)

  Request handler dispatch for execute_query tool, which extracts parameters, enforces server mode, calls the executeQuery handler, and formats the response.
  case 'execute_query': { const { query, readOnly = false } = request.params.arguments as { query: string; readOnly?: boolean; }; // Enforce readonly mode if server is in readonly mode const enforceReadOnly = this.serverMode === 'readonly' || readOnly; const result = await executeQuery(this.connectionString, query, enforceReadOnly); return { content: [ { type: 'text', text: JSON.stringify(result, null, 2) } ] }; }
• src/tools/query.ts:16-34 (helper)

  Helper function to determine if a SQL query is read-only by checking starting command and excluding certain modifiers.
  function isReadOnlyQuery(query: string): boolean { // Normalize query - trim whitespace and convert to uppercase for comparison const normalizedQuery = query.trim().toUpperCase(); // Check if query starts with SELECT, EXPLAIN, SHOW, etc. const readOnlyCommands = [ 'SELECT', 'EXPLAIN', 'SHOW', 'WITH', // CTE that ends with SELECT 'ANALYZE', 'DESCRIBE' ]; return readOnlyCommands.some(cmd => normalizedQuery.startsWith(cmd)) && !normalizedQuery.includes('INTO') && // Exclude SELECT INTO !normalizedQuery.includes('FOR UPDATE') && // Exclude locking queries !normalizedQuery.includes('FOR SHARE'); }