Postgres MCP Server

Instructions

Implementation Reference

• src/index.ts:92-154 (schema)

  Input schema and description for the 'tables' MCP tool, defining supported actions (list, info, create, drop, etc.) and parameters.

  {
    name: 'tables',
    description: 'Table management: list, create, alter, drop tables and get detailed table information',
    inputSchema: {
      type: 'object',
      properties: {
        action: {
          type: 'string',
          enum: ['list', 'info', 'create', 'drop', 'add_column', 'drop_column', 'rename'],
          description: 'Action: list (all tables), info (table details), create (new table), drop (remove table), add_column (add column), drop_column (remove column), rename (rename table)'
        },
        schemaName: {
          type: 'string',
          description: 'Schema name (default: public)',
          default: 'public'
        },
        tableName: {
          type: 'string',
          description: 'Table name (required for info, create, drop, add_column, drop_column, rename)'
        },
        columns: {
          type: 'array',
          items: {
            type: 'object',
            properties: {
              name: { type: 'string' },
              type: { type: 'string' },
              nullable: { type: 'boolean', default: true },
              defaultValue: { type: 'string' },
              primaryKey: { type: 'boolean', default: false }
            },
            required: ['name', 'type']
          },
          description: 'Column definitions for create action'
        },
        columnName: {
          type: 'string',
          description: 'Column name (required for add_column, drop_column)'
        },
        dataType: {
          type: 'string',
          description: 'Data type (required for add_column)'
        },
        newName: {
          type: 'string',
          description: 'New name (required for rename action)'
        },
        options: {
          type: 'object',
          properties: {
            includeViews: { type: 'boolean', default: false },
            includeSystemTables: { type: 'boolean', default: false },
            ifNotExists: { type: 'boolean', default: false },
            ifExists: { type: 'boolean', default: true },
            cascade: { type: 'boolean', default: false },
            temporary: { type: 'boolean', default: false }
          },
          description: 'Action-specific options'
        }
      },
      required: ['action']
    }
  },

• src/index.ts:789-843 (handler)

  Primary handler for 'tables' tool execution. Parses arguments, validates inputs, dispatches to TablesAPIClient methods based on action, and formats MCP response.

  private async handleTables(args: any) {
    const { action, tableName, schemaName = 'public', columns, columnName, dataType, newName, options = {} } = args;
  
    switch (action) {
      case 'list':
        return {
          content: [{
            type: 'text',
            text: JSON.stringify(await this.tablesClient.listTables(schemaName, options.includeViews, options.includeSystemTables), null, 2)
          }]
        };
  
      case 'info':
        ParameterValidator.validateRequired(tableName, 'tableName');
        return {
          content: [{
            type: 'text',
            text: JSON.stringify(await this.tablesClient.getTableInfo(tableName, schemaName), null, 2)
          }]
        };
  
      case 'create':
        ParameterValidator.validateRequired(tableName, 'tableName');
        ParameterValidator.validateRequired(columns, 'columns');
        return {
          content: [{
            type: 'text',
            text: JSON.stringify(await this.tablesClient.createTable(tableName, columns, { schema: schemaName, ...options }), null, 2)
          }]
        };
  
      case 'drop':
        ParameterValidator.validateRequired(tableName, 'tableName');
        return {
          content: [{
            type: 'text',
            text: JSON.stringify(await this.tablesClient.dropTable(tableName, schemaName, options.cascade, options.ifExists), null, 2)
          }]
        };
  
      case 'add_column':
        ParameterValidator.validateRequired(tableName, 'tableName');
        ParameterValidator.validateRequired(columnName, 'columnName');
        ParameterValidator.validateRequired(dataType, 'dataType');
        return {
          content: [{
            type: 'text',
            text: JSON.stringify(await this.tablesClient.addColumn(tableName, columnName, dataType, schemaName, options), null, 2)
          }]
        };
  
      default:
        throw new Error(`Unknown tables action: ${action}`);
    }
  }

• src/index.ts:634-636 (registration)

  Registers all tool definitions (including 'tables') by returning toolDefinitions array in response to ListToolsRequestSchema.

  this.server.setRequestHandler(ListToolsRequestSchema, async () => ({
    tools: toolDefinitions,
  }));

• src/api/domains/tables-api.ts:36-461 (helper)

  TablesAPIClient class implements core table operations (listTables, getTableInfo, createTable, dropTable, addColumn) using direct PostgreSQL queries via DatabaseConnectionManager.

  export class TablesAPIClient {
    constructor(private dbManager: DatabaseConnectionManager) {}
  
    /**
     * List all tables in the database
     */
    async listTables(
      schemaName?: string,
      includeViews: boolean = false,
      includeSystemTables: boolean = false
    ): Promise<TableInfo[]> {
      let sql = `
        SELECT 
          t.table_schema as schema_name,
          t.table_name,
          t.table_type,
          pg_catalog.pg_get_userbyid(c.relowner) as owner,
          c.relhasindex as has_indexes,
          c.relhasrules as has_rules,
          c.relhastriggers as has_triggers,
          pg_catalog.pg_relation_size(c.oid) as size_bytes
        FROM information_schema.tables t
        JOIN pg_catalog.pg_class c ON c.relname = t.table_name
        JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace AND n.nspname = t.table_schema
        WHERE 1=1
      `;
  
      const params: any[] = [];
      let paramIndex = 1;
  
      if (schemaName) {
        const validatedSchema = ParameterValidator.validateSchemaName(schemaName);
        sql += ` AND t.table_schema = $${paramIndex}`;
        params.push(validatedSchema);
        paramIndex++;
      } else if (!includeSystemTables) {
        sql += ` AND t.table_schema NOT IN ('information_schema', 'pg_catalog', 'pg_toast')`;
      }
  
      if (!includeViews) {
        sql += ` AND t.table_type = 'BASE TABLE'`;
      }
  
      sql += ` ORDER BY t.table_schema, t.table_name`;
  
      const result = await this.dbManager.query(sql, params, { readOnly: true });
  
      return result.rows.map((row: any) => ({
        schemaName: row.schema_name,
        tableName: row.table_name,
        tableType: row.table_type,
        owner: row.owner,
        hasIndexes: row.has_indexes,
        hasRules: row.has_rules,
        hasTriggers: row.has_triggers,
        sizeBytes: parseInt(row.size_bytes) || 0
      }));
    }
  
    /**
     * Get detailed information about a specific table
     */
    async getTableInfo(tableName: string, schemaName: string = 'public'): Promise<{
      table: TableInfo;
      columns: ColumnInfo[];
      indexes: any[];
      constraints: any[];
      statistics: any;
    }> {
      const validatedTable = ParameterValidator.validateTableName(tableName);
      const validatedSchema = ParameterValidator.validateSchemaName(schemaName);
  
      // Get table info
      const tableInfo = await this.getTableBasicInfo(validatedTable, validatedSchema);
      
      // Get columns
      const columns = await this.getTableColumns(validatedTable, validatedSchema);
      
      // Get indexes
      const indexes = await this.getTableIndexes(validatedTable, validatedSchema);
      
      // Get constraints
      const constraints = await this.getTableConstraints(validatedTable, validatedSchema);
      
      // Get statistics
      const statistics = await this.getTableStatistics(validatedTable, validatedSchema);
  
      return {
        table: tableInfo,
        columns,
        indexes,
        constraints,
        statistics
      };
    }
  
    /**
     * Create a new table
     */
    async createTable(
      tableName: string,
      columns: Array<{
        name: string;
        type: string;
        nullable?: boolean;
        defaultValue?: string;
        primaryKey?: boolean;
      }>,
      options: CreateTableOptions = {}
    ): Promise<{ success: boolean; message: string }> {
      const validatedTable = ParameterValidator.validateTableName(tableName);
      
      // Validate columns
      if (!columns || columns.length === 0) {
        throw new Error('At least one column is required');
      }
  
      for (const col of columns) {
        ParameterValidator.validateColumnName(col.name);
        ParameterValidator.validateDataType(col.type);
      }
  
      const schema = options.schema ? ParameterValidator.validateSchemaName(options.schema) : 'public';
      const fullTableName = `${schema}.${validatedTable}`;
  
      // Build CREATE TABLE statement
      let sql = 'CREATE';
      
      if (options.temporary) sql += ' TEMPORARY';
      if (options.unlogged) sql += ' UNLOGGED';
      
      sql += ' TABLE';
      
      if (options.ifNotExists) sql += ' IF NOT EXISTS';
      
      sql += ` ${fullTableName} (`;
  
      const columnDefinitions = columns.map(col => {
        let def = `${col.name} ${col.type}`;
        
        if (col.nullable === false) def += ' NOT NULL';
        if (col.defaultValue) def += ` DEFAULT ${col.defaultValue}`;
        if (col.primaryKey) def += ' PRIMARY KEY';
        
        return def;
      }).join(', ');
  
      sql += columnDefinitions + ')';
  
      try {
        await this.dbManager.query(sql);
        
        logger.info('Table created successfully', { 
          tableName: fullTableName,
          columns: columns.length 
        });
        
        return {
          success: true,
          message: `Table ${fullTableName} created successfully with ${columns.length} columns`
        };
      } catch (error) {
        logger.error('Failed to create table', { 
          tableName: fullTableName,
          error: error instanceof Error ? error.message : error 
        });
        throw error;
      }
    }
  
    /**
     * Drop a table
     */
    async dropTable(
      tableName: string,
      schemaName: string = 'public',
      cascade: boolean = false,
      ifExists: boolean = true
    ): Promise<{ success: boolean; message: string }> {
      const validatedTable = ParameterValidator.validateTableName(tableName);
      const validatedSchema = ParameterValidator.validateSchemaName(schemaName);
      const fullTableName = `${validatedSchema}.${validatedTable}`;
  
      let sql = 'DROP TABLE';
      if (ifExists) sql += ' IF EXISTS';
      sql += ` ${fullTableName}`;
      if (cascade) sql += ' CASCADE';
  
      try {
        await this.dbManager.query(sql);
        
        logger.info('Table dropped successfully', { tableName: fullTableName });
        
        return {
          success: true,
          message: `Table ${fullTableName} dropped successfully`
        };
      } catch (error) {
        logger.error('Failed to drop table', { 
          tableName: fullTableName,
          error: error instanceof Error ? error.message : error 
        });
        throw error;
      }
    }
  
    /**
     * Add a column to an existing table
     */
    async addColumn(
      tableName: string,
      columnName: string,
      dataType: string,
      schemaName: string = 'public',
      options: {
        nullable?: boolean;
        defaultValue?: string;
        ifNotExists?: boolean;
      } = {}
    ): Promise<{ success: boolean; message: string }> {
      const validatedTable = ParameterValidator.validateTableName(tableName);
      const validatedColumn = ParameterValidator.validateColumnName(columnName);
      const validatedType = ParameterValidator.validateDataType(dataType);
      const validatedSchema = ParameterValidator.validateSchemaName(schemaName);
      
      const fullTableName = `${validatedSchema}.${validatedTable}`;
  
      let sql = `ALTER TABLE ${fullTableName} ADD`;
      if (options.ifNotExists) sql += ' IF NOT EXISTS';
      sql += ` COLUMN ${validatedColumn} ${validatedType}`;
      
      if (options.nullable === false) sql += ' NOT NULL';
      if (options.defaultValue) sql += ` DEFAULT ${options.defaultValue}`;
  
      try {
        await this.dbManager.query(sql);
        
        logger.info('Column added successfully', { 
          tableName: fullTableName,
          columnName: validatedColumn 
        });
        
        return {
          success: true,
          message: `Column ${validatedColumn} added to ${fullTableName} successfully`
        };
      } catch (error) {
        logger.error('Failed to add column', { 
          tableName: fullTableName,
          columnName: validatedColumn,
          error: error instanceof Error ? error.message : error 
        });
        throw error;
      }
    }
  
    /**
     * Get table columns
     */
    private async getTableColumns(tableName: string, schemaName: string): Promise<ColumnInfo[]> {
      const sql = `
        SELECT 
          c.column_name,
          c.data_type,
          c.is_nullable = 'YES' as is_nullable,
          c.column_default as default_value,
          c.character_maximum_length as max_length,
          c.numeric_precision as precision,
          c.numeric_scale as scale,
          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
        FROM information_schema.columns c
        LEFT JOIN (
          SELECT ku.column_name
          FROM information_schema.table_constraints tc
          JOIN information_schema.key_column_usage ku 
            ON tc.constraint_name = ku.constraint_name
            AND tc.table_schema = ku.table_schema
          WHERE tc.constraint_type = 'PRIMARY KEY'
            AND tc.table_name = $1
            AND tc.table_schema = $2
        ) pk ON pk.column_name = c.column_name
        LEFT JOIN (
          SELECT ku.column_name
          FROM information_schema.table_constraints tc
          JOIN information_schema.key_column_usage ku 
            ON tc.constraint_name = ku.constraint_name
            AND tc.table_schema = ku.table_schema
          WHERE tc.constraint_type = 'FOREIGN KEY'
            AND tc.table_name = $1
            AND tc.table_schema = $2
        ) fk ON fk.column_name = c.column_name
        WHERE c.table_name = $1 AND c.table_schema = $2
        ORDER BY c.ordinal_position
      `;
  
      const result = await this.dbManager.query(sql, [tableName, schemaName], { readOnly: true });
      
      return result.rows.map((row: any) => ({
        columnName: row.column_name,
        dataType: row.data_type,
        isNullable: row.is_nullable,
        defaultValue: row.default_value,
        isPrimaryKey: row.is_primary_key,
        isForeignKey: row.is_foreign_key,
        maxLength: row.max_length,
        precision: row.precision,
        scale: row.scale
      }));
    }
  
    /**
     * Get table basic info
     */
    private async getTableBasicInfo(tableName: string, schemaName: string): Promise<TableInfo> {
      const sql = `
        SELECT 
          t.table_schema as schema_name,
          t.table_name,
          t.table_type,
          pg_catalog.pg_get_userbyid(c.relowner) as owner,
          c.relhasindex as has_indexes,
          c.relhasrules as has_rules,
          c.relhastriggers as has_triggers,
          pg_catalog.pg_relation_size(c.oid) as size_bytes,
          c.reltuples::bigint as row_count
        FROM information_schema.tables t
        JOIN pg_catalog.pg_class c ON c.relname = t.table_name
        JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace AND n.nspname = t.table_schema
        WHERE t.table_name = $1 AND t.table_schema = $2
      `;
  
      const result = await this.dbManager.query(sql, [tableName, schemaName], { readOnly: true });
      
      if (result.rows.length === 0) {
        throw new Error(`Table ${schemaName}.${tableName} not found`);
      }
  
      const row = result.rows[0];
      return {
        schemaName: row.schema_name,
        tableName: row.table_name,
        tableType: row.table_type,
        owner: row.owner,
        hasIndexes: row.has_indexes,
        hasRules: row.has_rules,
        hasTriggers: row.has_triggers,
        sizeBytes: parseInt(row.size_bytes) || 0,
        rowCount: parseInt(row.row_count) || 0
      };
    }
  
    /**
     * Get table indexes
     */
    private async getTableIndexes(tableName: string, schemaName: string): Promise<any[]> {
      const sql = `
        SELECT 
          indexname,
          indexdef,
          schemaname
        FROM pg_indexes 
        WHERE tablename = $1 AND schemaname = $2
        ORDER BY indexname
      `;
  
      const result = await this.dbManager.query(sql, [tableName, schemaName], { readOnly: true });
      return result.rows;
    }
  
    /**
     * Get table constraints
     */
    private async getTableConstraints(tableName: string, schemaName: string): Promise<any[]> {
      const sql = `
        SELECT 
          tc.constraint_name,
          tc.constraint_type,
          kcu.column_name,
          ccu.table_name AS foreign_table_name,
          ccu.column_name AS foreign_column_name
        FROM information_schema.table_constraints tc 
        LEFT JOIN information_schema.key_column_usage kcu
          ON tc.constraint_name = kcu.constraint_name
          AND tc.table_schema = kcu.table_schema
        LEFT JOIN information_schema.constraint_column_usage ccu
          ON ccu.constraint_name = tc.constraint_name
          AND ccu.table_schema = tc.table_schema
        WHERE tc.table_name = $1 AND tc.table_schema = $2
        ORDER BY tc.constraint_type, tc.constraint_name
      `;
  
      const result = await this.dbManager.query(sql, [tableName, schemaName], { readOnly: true });
      return result.rows;
    }
  
    /**
     * Get table statistics
     */
    private async getTableStatistics(tableName: string, schemaName: string): Promise<any> {
      const sql = `
        SELECT 
          schemaname,
          tablename,
          seq_scan,
          seq_tup_read,
          idx_scan,
          idx_tup_fetch,
          n_tup_ins,
          n_tup_upd,
          n_tup_del,
          n_tup_hot_upd,
          n_live_tup,
          n_dead_tup,
          last_vacuum,
          last_autovacuum,
          last_analyze,
          last_autoanalyze
        FROM pg_stat_user_tables 
        WHERE tablename = $1 AND schemaname = $2
      `;
  
      const result = await this.dbManager.query(sql, [tableName, schemaName], { readOnly: true });
      return result.rows[0] || {};
    }
  }

• src/index.ts:551-552 (helper)

  Instantiates TablesAPIClient for use in the 'tables' tool handler.

  this.tablesClient = new TablesAPIClient(this.dbManager);
  this.schemaClient = new SchemaAPIClient(this.dbManager, this.cache);