PostgreSQL MCP Server

Instructions

Implementation Reference

• src/tools/migration.ts:223-285 (handler)

  The core handler function that implements the logic to copy table data from a source PostgreSQL database to a target database. It connects to the source DB, queries the data (with optional WHERE clause), disconnects, connects to the target DB, optionally truncates the table, and inserts the data in a transaction.

  async function executeCopyBetweenDatabases(
    input: CopyBetweenDatabasesInput,
    getConnectionString: GetConnectionStringFn
  ): Promise<{ tableName: string; rowCount: number }> {
    const { sourceConnectionString, targetConnectionString, tableName, where, truncateTarget } = input;
    
    const db = DatabaseConnection.getInstance(); // Use the singleton for both connections sequentially
  
    try {
      // --- Source Operations ---
      await db.connect(sourceConnectionString);
      
      let query = `SELECT * FROM "${tableName}"`;
      if (where) {
        query += ` WHERE ${where}`;
      }
      
      const data = await db.query<Record<string, unknown>[]>(query);
      
      if (data.length === 0) {
        await db.disconnect(); // Disconnect source if no data
        return { tableName, rowCount: 0 };
      }
      
      await db.disconnect(); // Disconnect source before connecting to target
      
      // --- Target Operations ---
      await db.connect(targetConnectionString);
      
      if (truncateTarget) {
        await db.query(`TRUNCATE TABLE "${tableName}"`);
      }
      
      let importedCount = 0;
      await db.transaction(async (client: import('pg').PoolClient) => {
        for (const record of data) {
          const columns = Object.keys(record);
          if (columns.length === 0) continue;
          const values = Object.values(record);
          const placeholders = values.map((_, i) => `$${i + 1}`).join(', ');
          
          const insertQuery = `
            INSERT INTO "${tableName}" (${columns.map(c => `"${c}"`).join(', ')})
            VALUES (${placeholders})
          `;
          await client.query(insertQuery, values);
          importedCount++;
        }
      });
      
      return { tableName, rowCount: importedCount };
    } catch (error) {
      throw new McpError(ErrorCode.InternalError, `Failed to copy data: ${error instanceof Error ? error.message : String(error)}`);
    } finally {
      // Ensure disconnection in normal flow; connect() handles prior disconnects if needed.
      // The connect method in DatabaseConnection already handles disconnecting if connected to a different DB.
      // So, a single disconnect here should be fine, assuming the last active connection was target.
      // If an error occurred mid-operation (e.g., after source connect, before target connect),
      // connect() for target would handle disconnecting from source.
      // If an error occurs after target connect, this disconnect handles target.
      await db.disconnect(); 
    }
  }

• src/tools/migration.ts:214-220 (schema)

  Zod schema defining the input parameters for the pg_copy_between_databases tool: source and target connection strings, table name, optional WHERE clause, and optional truncate flag.

  const CopyBetweenDatabasesInputSchema = z.object({
    sourceConnectionString: z.string(),
    targetConnectionString: z.string(),
    tableName: z.string(),
    where: z.string().optional(),
    truncateTarget: z.boolean().optional().default(false),
  });

• src/tools/migration.ts:287-304 (registration)

  The PostgresTool object definition that registers the tool with its name, description, input schema, and execute wrapper that validates input and calls the main handler.

  export const copyBetweenDatabasesTool: PostgresTool = {
    name: 'pg_copy_between_databases',
    description: 'Copy data between two databases',
    inputSchema: CopyBetweenDatabasesInputSchema,
    async execute(params: unknown, getConnectionString: GetConnectionStringFn): Promise<ToolOutput> {
      const validationResult = CopyBetweenDatabasesInputSchema.safeParse(params);
      if (!validationResult.success) {
        return { content: [{ type: 'text', text: `Invalid input: ${validationResult.error.format()}` }], isError: true };
      }
      try {
        const result = await executeCopyBetweenDatabases(validationResult.data, getConnectionString);
        return { content: [{ type: 'text', text: `Successfully copied ${result.rowCount} rows to ${result.tableName}` }] };
      } catch (error) {
        const errorMessage = error instanceof McpError ? error.message : (error instanceof Error ? error.message : String(error));
        return { content: [{ type: 'text', text: `Error copying data: ${errorMessage}` }], isError: true };
      }
    }
  };

• src/index.ts:225-257 (registration)

  The allTools array where the copyBetweenDatabasesTool is included among all available tools, which is then passed to the PostgreSQLServer constructor to register tools with the MCP server.

  const allTools: PostgresTool[] = [
    // Core Analysis & Debugging
    analyzeDatabaseTool,
    debugDatabaseTool,
    
    // Schema & Structure Management (Meta-Tools)
    manageSchemaTools,
    manageFunctionsTool,
    manageTriggersTools,
    manageIndexesTool,
    manageConstraintsTool,
    manageRLSTool,
    
    // User & Security Management
    manageUsersTool,
    
    // Query & Performance Management
    manageQueryTool,
    
    // Data Operations (Enhancement Tools)
    executeQueryTool,
    executeMutationTool,
    executeSqlTool,
    
    // Documentation & Metadata
    manageCommentsTool,
    
    // Data Migration & Monitoring
    exportTableDataTool,
    importTableDataTool,
    copyBetweenDatabasesTool,
    monitorDatabaseTool
  ];

• src/index.ts:22-22 (registration)

  Import statement that brings the copyBetweenDatabasesTool into the main index file for inclusion in the tools list.

  import { exportTableDataTool, importTableDataTool, copyBetweenDatabasesTool } from './tools/migration.js';