PostGIS MCP Server

Instructions

Implementation Reference

• server.ts:1248-1282 (handler)

  Handler for the 'get-table-info' tool: validates input with TableInfoSchema, loads database schema if not cached, retrieves table information from the cache, and returns detailed table info as JSON or error if table not found.
  case "get-table-info": { const { table_name } = TableInfoSchema.parse(args); if (!databaseSchema) { databaseSchema = await analyzeDatabaseSchema(client); } const tableInfo = databaseSchema.tables[table_name]; if (!tableInfo) { return { content: [ { type: "text", text: JSON.stringify( { error: `Tablo '${table_name}' bulunamadı`, available_tables: Object.keys(databaseSchema.tables), }, null, 2 ), }, ], }; } return { content: [ { type: "text", text: JSON.stringify(tableInfo, null, 2), }, ], }; }
• server.ts:606-615 (registration)

  Tool registration in ListToolsRequestSchema handler: defines name, description, and input schema for 'get-table-info'.
  name: "get-table-info", description: "Belirli bir tablo hakkında detaylı bilgi al", inputSchema: { type: "object", properties: { table_name: { type: "string", description: "Tablo adı" }, }, required: ["table_name"], }, },
• server.ts:26-28 (schema)

  Zod input schema for 'get-table-info' tool, validating the required 'table_name' parameter.
  const TableInfoSchema = z.object({ table_name: z.string(), });
• server.ts:182-338 (helper)

  Core helper function that queries PostgreSQL metadata to build detailed DatabaseSchema cache, including tables, columns (with PK/FK), geometry columns, row counts, indexes, and sample data. This cache is used by get-table-info to provide table information.
  async function analyzeDatabaseSchema(client: Client): Promise<DatabaseSchema> { const schema: DatabaseSchema = { tables: {}, spatial_tables: [], last_analyzed: new Date(), connection_info: { database: process.env.DB_NAME || "unknown", host: process.env.DB_HOST || "localhost", }, }; // 1. Get all tables with basic info const tablesResult = await client.query(` SELECT t.table_name, t.table_schema, t.table_type, obj_description(c.oid) as table_comment FROM information_schema.tables t LEFT JOIN pg_class c ON c.relname = t.table_name WHERE t.table_schema NOT IN ('information_schema', 'pg_catalog', 'pg_toast') ORDER BY t.table_name; `); // 2. Get all columns with detailed info const columnsResult = await client.query(` SELECT c.table_name, c.column_name, c.data_type, c.is_nullable::boolean, c.column_default, CASE WHEN pk.column_name IS NOT NULL THEN true ELSE false END as is_primary_key, CASE WHEN fk.column_name IS NOT NULL THEN true ELSE false END as is_foreign_key, fk.foreign_table_name, fk.foreign_column_name, col_description(pgc.oid, c.ordinal_position) as description FROM information_schema.columns c LEFT JOIN ( SELECT ku.table_name, ku.column_name FROM information_schema.table_constraints tc JOIN information_schema.key_column_usage ku ON tc.constraint_name = ku.constraint_name WHERE tc.constraint_type = 'PRIMARY KEY' ) pk ON c.table_name = pk.table_name AND c.column_name = pk.column_name LEFT JOIN ( SELECT ku.table_name, ku.column_name, ccu.table_name AS foreign_table_name, ccu.column_name AS foreign_column_name FROM information_schema.table_constraints tc JOIN information_schema.key_column_usage ku ON tc.constraint_name = ku.constraint_name JOIN information_schema.constraint_column_usage ccu ON tc.constraint_name = ccu.constraint_name WHERE tc.constraint_type = 'FOREIGN KEY' ) fk ON c.table_name = fk.table_name AND c.column_name = fk.column_name LEFT JOIN pg_class pgc ON pgc.relname = c.table_name WHERE c.table_schema NOT IN ('information_schema', 'pg_catalog') ORDER BY c.table_name, c.ordinal_position; `); // 3. Get geometry columns let geometryColumns: any[] = []; try { const geometryResult = await client.query(` SELECT f_table_name as table_name, f_geometry_column as column_name, type as geometry_type, srid FROM geometry_columns; `); geometryColumns = geometryResult.rows; } catch (error) { // PostGIS might not be installed } // 4. Get indexes const indexesResult = await client.query(` SELECT schemaname, tablename, indexname, indexdef FROM pg_indexes WHERE schemaname NOT IN ('information_schema', 'pg_catalog'); `); // 5. Build table structures for (const table of tablesResult.rows) { const tableName = table.table_name; // Get columns for this table const tableColumns = columnsResult.rows .filter((col) => col.table_name === tableName) .map((col) => ({ column_name: col.column_name, data_type: col.data_type, is_nullable: col.is_nullable, column_default: col.column_default, is_primary_key: col.is_primary_key, is_foreign_key: col.is_foreign_key, foreign_table: col.foreign_table_name, foreign_column: col.foreign_column_name, description: col.description, })); // Get geometry columns for this table const tableGeometryColumns = geometryColumns .filter((gc) => gc.table_name === tableName) .map((gc) => gc.column_name); // Get indexes for this table const tableIndexes = indexesResult.rows .filter((idx) => idx.tablename === tableName) .map((idx) => idx.indexname); // Get row count let rowCount = 0; try { const countResult = await client.query( `SELECT COUNT(*) as count FROM ${tableName}` ); rowCount = parseInt(countResult.rows[0].count); } catch (error) { // Table might be inaccessible } // Get sample data (first 3 rows) let sampleData: any[] = []; try { const sampleResult = await client.query( `SELECT * FROM ${tableName} LIMIT 3` ); sampleData = sampleResult.rows; } catch (error) { // Table might be inaccessible } schema.tables[tableName] = { table_name: tableName, table_schema: table.table_schema, table_type: table.table_type, columns: tableColumns, geometry_columns: tableGeometryColumns, row_count: rowCount, table_comment: table.table_comment, indexes: tableIndexes, sample_data: sampleData, }; if (tableGeometryColumns.length > 0) { schema.spatial_tables.push(tableName); } } return schema; }
• server.ts:145-155 (schema)

  TypeScript interface defining the structure of TableInfo returned by get-table-info.
  interface TableInfo { table_name: string; table_schema: string; table_type: string; columns: ColumnInfo[]; geometry_columns: string[]; row_count: number; table_comment?: string; indexes: string[]; sample_data?: any[]; }