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