PostGIS MCP Server

Instructions

Implementation Reference

• server.ts:17-19 (schema)

  Zod schema definition for the 'analyze-database' tool input, including optional 'force_refresh' boolean parameter.
  const AnalyzeDatabaseSchema = z.object({ force_refresh: z.boolean().optional().default(false), });
• server.ts:572-583 (registration)

  Tool registration in the ListToolsRequestSchema handler, defining name, description, and input schema matching AnalyzeDatabaseSchema.
  name: "analyze-database", description: "Veritabanı şemasını analiz et ve tablo bilgilerini topla", inputSchema: { type: "object", properties: { force_refresh: { type: "boolean", description: "Önbelleği yenileyip yeniden analiz et", }, }, }, },
• server.ts:1126-1158 (handler)

  Handler implementation in CallToolRequestSchema switch statement. Parses input using AnalyzeDatabaseSchema, refreshes schema cache if needed by calling analyzeDatabaseSchema, generates and returns a JSON summary of the database structure.
  case "analyze-database": { const { force_refresh } = AnalyzeDatabaseSchema.parse(args); if (!databaseSchema || force_refresh) { databaseSchema = await analyzeDatabaseSchema(client); } const summary = { database: databaseSchema.connection_info.database, host: databaseSchema.connection_info.host, total_tables: Object.keys(databaseSchema.tables).length, spatial_tables: databaseSchema.spatial_tables.length, last_analyzed: databaseSchema.last_analyzed, table_summary: Object.entries(databaseSchema.tables).map( ([name, info]) => ({ name, rows: info.row_count, columns: info.columns.length, spatial: info.geometry_columns.length > 0, description: info.table_comment || "Açıklama yok", }) ), }; return { content: [ { type: "text", text: JSON.stringify(summary, null, 2), }, ], }; }
• server.ts:182-338 (helper)

  Main helper function that performs comprehensive database schema analysis. Queries PostgreSQL metadata tables to gather table info, column details (types, PK/FK, descriptions), geometry columns, indexes, row counts, sample data, and constructs a detailed DatabaseSchema object.
  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; }