Postgres MCP Server

Instructions

Implementation Reference

• src/index.ts:285-317 (schema)

  Schema definition for the 'transactions' MCP tool, including input parameters and actions (begin, commit, rollback, status, etc.)

  {
    name: 'transactions',
    description: 'Transaction management: begin, commit, rollback, savepoints',
    inputSchema: {
      type: 'object',
      properties: {
        action: {
          type: 'string',
          enum: ['begin', 'commit', 'rollback', 'savepoint', 'rollback_to', 'release', 'status'],
          description: 'Action: begin (start transaction), commit (commit transaction), rollback (rollback transaction), savepoint (create savepoint), rollback_to (rollback to savepoint), release (release savepoint), status (transaction status)'
        },
        transactionId: {
          type: 'string',
          description: 'Transaction ID (required for commit, rollback, and operations within transaction)'
        },
        savepointName: {
          type: 'string',
          description: 'Savepoint name (required for savepoint, rollback_to, release)'
        },
        readOnly: {
          type: 'boolean',
          description: 'Start read-only transaction (for begin action)',
          default: false
        },
        isolationLevel: {
          type: 'string',
          enum: ['READ UNCOMMITTED', 'READ COMMITTED', 'REPEATABLE READ', 'SERIALIZABLE'],
          description: 'Transaction isolation level (for begin action)'
        }
      },
      required: ['action']
    }
  },

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

  Registration of the 'transactions' tool via the toolDefinitions array returned in ListToolsRequestSchema handler

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

• src/index.ts:1082-1126 (handler)

  Primary handler function for 'transactions' tool calls, handling begin/commit/rollback/status actions by delegating to DatabaseConnectionManager

  private async handleTransactions(args: any) {
    const { action, transactionId, readOnly, isolationLevel } = args;
  
    switch (action) {
      case 'begin':
        const txId = await this.dbManager.beginTransaction(readOnly);
        return {
          content: [{
            type: 'text',
            text: JSON.stringify({ transactionId: txId, status: 'started' }, null, 2)
          }]
        };
  
      case 'commit':
        ParameterValidator.validateRequired(transactionId, 'transactionId');
        await this.dbManager.commitTransaction(transactionId);
        return {
          content: [{
            type: 'text',
            text: JSON.stringify({ transactionId, status: 'committed' }, null, 2)
          }]
        };
  
      case 'rollback':
        ParameterValidator.validateRequired(transactionId, 'transactionId');
        await this.dbManager.rollbackTransaction(transactionId);
        return {
          content: [{
            type: 'text',
            text: JSON.stringify({ transactionId, status: 'rolled_back' }, null, 2)
          }]
        };
  
      case 'status':
        return {
          content: [{
            type: 'text',
            text: JSON.stringify(this.dbManager.getOperationalStats(), null, 2)
          }]
        };
  
      default:
        throw new Error(`Unknown transaction action: ${action}`);
    }
  }

• src/database/connection-manager.ts:158-193 (helper)

  Core beginTransaction helper method that creates a new transaction context, supports read-only mode, and tracks active transactions

  async beginTransaction(readOnly: boolean = false): Promise<string> {
    const transactionId = uuidv4();
    const client = await this.pool.connect();
    
    try {
      if (readOnly) {
        await client.query('BEGIN READ ONLY');
      } else {
        if (this.config.isReadOnlyMode()) {
          throw new Error('Write transactions are not allowed in read-only mode');
        }
        await client.query('BEGIN');
      }
      
      const context: TransactionContext = {
        id: transactionId,
        client,
        startTime: Date.now(),
        readOnly
      };
      
      this.activeTransactions.set(transactionId, context);
      this.connectionStats.totalTransactions++;
      
      logConnection('transaction_started', { 
        transactionId, 
        readOnly,
        activeTransactions: this.activeTransactions.size 
      });
      
      return transactionId;
    } catch (error) {
      client.release();
      throw error;
    }
  }

• src/database/connection-manager.ts:235-253 (helper)

  Core commitTransaction helper method that commits the transaction and cleans up the context

  async commitTransaction(transactionId: string): Promise<void> {
    const context = this.activeTransactions.get(transactionId);
    if (!context) {
      throw new Error(`Transaction ${transactionId} not found`);
    }
  
    try {
      await context.client.query('COMMIT');
      const duration = Date.now() - context.startTime;
      
      logConnection('transaction_committed', { 
        transactionId, 
        duration: `${duration}ms` 
      });
    } finally {
      context.client.release();
      this.activeTransactions.delete(transactionId);
    }
  }

• src/database/connection-manager.ts:258-276 (helper)

  Core rollbackTransaction helper method that rolls back the transaction and cleans up the context

  async rollbackTransaction(transactionId: string): Promise<void> {
    const context = this.activeTransactions.get(transactionId);
    if (!context) {
      throw new Error(`Transaction ${transactionId} not found`);
    }
  
    try {
      await context.client.query('ROLLBACK');
      const duration = Date.now() - context.startTime;
      
      logConnection('transaction_rolled_back', { 
        transactionId, 
        duration: `${duration}ms` 
      });
    } finally {
      context.client.release();
      this.activeTransactions.delete(transactionId);
    }
  }

• src/database/connection-manager.ts:297-302 (helper)

  getOperationalStats helper method providing transaction status and statistics used by 'status' action

  getOperationalStats() {
    return {
      ...this.connectionStats,
      activeTransactions: this.activeTransactions.size,
      poolStats: this.getPoolStats()
    };