PostgreSQL MCP Server

Instructions

Implementation Reference

• src/tools/migration.ts:118-192 (handler)

  Core implementation of the pg_import_table_data tool logic: reads JSON/CSV file, parses data, optionally truncates table, and inserts rows into PostgreSQL table using transactions.

  async function executeImportTableData(
    input: ImportTableDataInput,
    getConnectionString: GetConnectionStringFn
  ): Promise<{ tableName: string; rowCount: number }> {
    const resolvedConnectionString = getConnectionString(input.connectionString);
    const db = DatabaseConnection.getInstance();
    const { tableName, inputPath, truncateFirst, format, delimiter } = input;
    
    try {
      await db.connect(resolvedConnectionString);
      
      const fileContent = await fs.promises.readFile(inputPath, 'utf8');
      
      let dataToImport: Record<string, unknown>[];
      
      if (format === 'csv') {
        const csvDelimiter = delimiter || ',';
        const lines = fileContent.split('\n').filter(line => line.trim()); // Use \n consistently
        
        if (lines.length === 0) {
          return { tableName, rowCount: 0 };
        }
        
        const headers = lines[0].split(csvDelimiter).map(h => h.trim().replace(/^"|"$/g, '')); // Remove surrounding quotes from headers
        
        dataToImport = lines.slice(1).map(line => {
          // Basic CSV parsing, might need a more robust library for complex CSVs
          const values = line.split(csvDelimiter).map(val => val.trim().replace(/^"|"$/g, '').replace(/""/g, '"'));
          return headers.reduce((obj, header, index) => {
            obj[header] = values[index] !== undefined ? values[index] : null;
            return obj;
          }, {} as Record<string, unknown>);
        });
      } else {
        dataToImport = JSON.parse(fileContent);
      }
      
      if (!Array.isArray(dataToImport)) {
        throw new Error('Input file does not contain an array of records');
      }
      
      if (truncateFirst) {
        await db.query(`TRUNCATE TABLE "${tableName}"`); // Consider quoting
      }
      
      let importedCount = 0;
      if (dataToImport.length > 0) {
        await db.transaction(async (client: import('pg').PoolClient) => {
          for (const record of dataToImport) {
            const columns = Object.keys(record);
            if (columns.length === 0) continue; // Skip empty records
            const values = Object.values(record);
            const placeholders = values.map((_, i) => `$${i + 1}`).join(', ');
            
            const query = `
              INSERT INTO "${tableName}" (${columns.map(c => `"${c}"`).join(', ')})
              VALUES (${placeholders})
            `;
            
            await client.query(query, values);
            importedCount++;
          }
        });
      }
      
      return {
          tableName,
          rowCount: importedCount
      };
    } catch (error) {
      throw new McpError(ErrorCode.InternalError, `Failed to import data: ${error instanceof Error ? error.message : String(error)}`);
    } finally {
      await db.disconnect();
    }
  }

• src/tools/migration.ts:108-115 (schema)

  Zod input schema defining parameters for pg_import_table_data tool: connection string (optional), table name, input file path, truncate option, format (json/csv), delimiter.

  const ImportTableDataInputSchema = z.object({
    connectionString: z.string().optional(),
    tableName: z.string(),
    inputPath: z.string().describe("absolute path to the file to import"),
    truncateFirst: z.boolean().optional().default(false),
    format: z.enum(['json', 'csv']).optional().default('json'),
    delimiter: z.string().optional(),
  });

• src/tools/migration.ts:194-211 (registration)

  Tool object definition exporting the pg_import_table_data handler with name, description, schema, and execute method that validates input and calls the core handler.

  export const importTableDataTool: PostgresTool = {
    name: 'pg_import_table_data',
    description: 'Import data from JSON or CSV file into a table',
    inputSchema: ImportTableDataInputSchema,
    async execute(params: unknown, getConnectionString: GetConnectionStringFn): Promise<ToolOutput> {
      const validationResult = ImportTableDataInputSchema.safeParse(params);
      if (!validationResult.success) {
        return { content: [{ type: 'text', text: `Invalid input: ${validationResult.error.format()}` }], isError: true };
      }
      try {
        const result = await executeImportTableData(validationResult.data, getConnectionString);
        return { content: [{ type: 'text', text: `Successfully imported ${result.rowCount} rows into ${result.tableName}` }] };
      } catch (error) {
        const errorMessage = error instanceof McpError ? error.message : (error instanceof Error ? error.message : String(error));
        return { content: [{ type: 'text', text: `Error importing data: ${errorMessage}` }], isError: true };
      }
    }
  };

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

  Inclusion of importTableDataTool in the allTools array, which populates the MCP server's available tools list.

  importTableDataTool,

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

  Import statement bringing importTableDataTool into the main index file for server registration.

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