PostgreSQL MCP Server

Instructions

Implementation Reference

• src/index.ts:177-290 (handler)

  The handler function that implements the logic for the 'describe_table' tool. It queries PostgreSQL information_schema for columns, primary keys, and foreign keys, then formats a detailed textual description of the table structure.
  async ({ tableName }) => { try { // Get column information const columnsQuery = ` SELECT column_name, data_type, is_nullable, column_default, character_maximum_length, numeric_precision, numeric_scale FROM information_schema.columns WHERE table_schema = 'public' AND table_name = $1 ORDER BY ordinal_position; `; const columns = await executeQuery(columnsQuery, [tableName]); if (columns.length === 0) { return { content: [ { type: "text", text: `Table '${tableName}' not found or has no columns.`, }, ], }; } // Get primary key information const pkQuery = ` SELECT column_name FROM information_schema.table_constraints tc JOIN information_schema.key_column_usage kcu ON tc.constraint_name = kcu.constraint_name WHERE tc.table_schema = 'public' AND tc.table_name = $1 AND tc.constraint_type = 'PRIMARY KEY' ORDER BY kcu.ordinal_position; `; const primaryKeys = await executeQuery(pkQuery, [tableName]); // Get foreign key information const fkQuery = ` SELECT kcu.column_name, ccu.table_name AS referenced_table, ccu.column_name AS referenced_column FROM information_schema.table_constraints tc JOIN information_schema.key_column_usage kcu ON tc.constraint_name = kcu.constraint_name JOIN information_schema.constraint_column_usage ccu ON ccu.constraint_name = tc.constraint_name WHERE tc.table_schema = 'public' AND tc.table_name = $1 AND tc.constraint_type = 'FOREIGN KEY'; `; const foreignKeys = await executeQuery(fkQuery, [tableName]); // Format response let response = `Table: ${tableName}\n\nColumns:\n`; columns.forEach(col => { const isPk = primaryKeys.some(pk => pk.column_name === col.column_name); const fk = foreignKeys.find(fk => fk.column_name === col.column_name); response += `- ${col.column_name}: ${col.data_type}`; if (col.character_maximum_length) { response += `(${col.character_maximum_length})`; } else if (col.numeric_precision) { response += `(${col.numeric_precision}${col.numeric_scale ? `,${col.numeric_scale}` : ''})`; } response += ` ${col.is_nullable === 'YES' ? 'NULL' : 'NOT NULL'}`; if (col.column_default) { response += ` DEFAULT ${col.column_default}`; } if (isPk) { response += ' [PRIMARY KEY]'; } if (fk) { response += ` [FK -> ${fk.referenced_table}.${fk.referenced_column}]`; } response += '\n'; }); return { content: [ { type: "text", text: response, }, ], }; } catch (error) { const errorMessage = error instanceof Error ? error.message : "Unknown error"; return { content: [ { type: "text", text: `Error describing table '${tableName}': ${errorMessage}`, }, ], }; } }
• src/index.ts:174-176 (schema)

  The Zod input schema defining the 'tableName' parameter for the 'describe_table' tool.
  { tableName: z.string().describe("Name of the table to describe"), },
• src/index.ts:171-291 (registration)

  The registration of the 'describe_table' tool using server.tool(), including name, description, input schema, and handler reference.
  server.tool( "describe_table", "Get detailed information about a table's structure, columns, and constraints", { tableName: z.string().describe("Name of the table to describe"), }, async ({ tableName }) => { try { // Get column information const columnsQuery = ` SELECT column_name, data_type, is_nullable, column_default, character_maximum_length, numeric_precision, numeric_scale FROM information_schema.columns WHERE table_schema = 'public' AND table_name = $1 ORDER BY ordinal_position; `; const columns = await executeQuery(columnsQuery, [tableName]); if (columns.length === 0) { return { content: [ { type: "text", text: `Table '${tableName}' not found or has no columns.`, }, ], }; } // Get primary key information const pkQuery = ` SELECT column_name FROM information_schema.table_constraints tc JOIN information_schema.key_column_usage kcu ON tc.constraint_name = kcu.constraint_name WHERE tc.table_schema = 'public' AND tc.table_name = $1 AND tc.constraint_type = 'PRIMARY KEY' ORDER BY kcu.ordinal_position; `; const primaryKeys = await executeQuery(pkQuery, [tableName]); // Get foreign key information const fkQuery = ` SELECT kcu.column_name, ccu.table_name AS referenced_table, ccu.column_name AS referenced_column FROM information_schema.table_constraints tc JOIN information_schema.key_column_usage kcu ON tc.constraint_name = kcu.constraint_name JOIN information_schema.constraint_column_usage ccu ON ccu.constraint_name = tc.constraint_name WHERE tc.table_schema = 'public' AND tc.table_name = $1 AND tc.constraint_type = 'FOREIGN KEY'; `; const foreignKeys = await executeQuery(fkQuery, [tableName]); // Format response let response = `Table: ${tableName}\n\nColumns:\n`; columns.forEach(col => { const isPk = primaryKeys.some(pk => pk.column_name === col.column_name); const fk = foreignKeys.find(fk => fk.column_name === col.column_name); response += `- ${col.column_name}: ${col.data_type}`; if (col.character_maximum_length) { response += `(${col.character_maximum_length})`; } else if (col.numeric_precision) { response += `(${col.numeric_precision}${col.numeric_scale ? `,${col.numeric_scale}` : ''})`; } response += ` ${col.is_nullable === 'YES' ? 'NULL' : 'NOT NULL'}`; if (col.column_default) { response += ` DEFAULT ${col.column_default}`; } if (isPk) { response += ' [PRIMARY KEY]'; } if (fk) { response += ` [FK -> ${fk.referenced_table}.${fk.referenced_column}]`; } response += '\n'; }); return { content: [ { type: "text", text: response, }, ], }; } catch (error) { const errorMessage = error instanceof Error ? error.message : "Unknown error"; return { content: [ { type: "text", text: `Error describing table '${tableName}': ${errorMessage}`, }, ], }; } } );
• src/index.ts:43-62 (helper)

  Helper function 'executeQuery' used by the 'describe_table' handler to safely execute read-only SQL queries against the connected PostgreSQL database.
  async function executeQuery(query: string, params: any[] = []): Promise<any[]> { const client = await getDbConnection(); // Basic safety checks for read-only operations const normalizedQuery = query.trim().toLowerCase(); const readOnlyPrefixes = ['select', 'show', 'describe', 'explain', 'with']; const isReadOnly = readOnlyPrefixes.some(prefix => normalizedQuery.startsWith(prefix)); if (!isReadOnly) { throw new Error("Only read-only queries (SELECT, SHOW, DESCRIBE, EXPLAIN, WITH) are allowed for security."); } try { const result = await client.query(query, params); return result.rows; } catch (error) { const errorMessage = error instanceof Error ? error.message : "Unknown error occurred"; throw new Error(`Query execution failed: ${errorMessage}`); } }