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;
  }