Postgres MCP Server

Instructions

Implementation Reference

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

  Schema definition for the 'tables' MCP tool, defining input parameters and actions (list, info, create, drop, add_column, etc.)
  { 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 function for executing 'tables' tool actions, dispatching to TablesAPIClient methods and formatting MCP responses
  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)

  Registration of the 'tables' tool (among others) with the MCP server via ListToolsRequestSchema handler using toolDefinitions array
  this.server.setRequestHandler(ListToolsRequestSchema, async () => ({ tools: toolDefinitions, }));
• src/index.ts:646-647 (handler)

  Tool dispatcher case in CallToolRequestSchema handler that routes 'tables' calls to handleTables function
  case 'tables': return await this.handleTables(args);
• src/api/domains/tables-api.ts:42-93 (helper)

  Core helper method listTables in TablesAPIClient used by 'tables' tool for listing tables
  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 })); }