pg_import_table_data
Import JSON or CSV file data into PostgreSQL tables using specified connection parameters and file paths.
Instructions
Import data from JSON or CSV file into a table
Input Schema
TableJSON Schema
| Name | Required | Description | Default |
|---|---|---|---|
| connectionString | No | ||
| tableName | Yes | ||
| inputPath | Yes | absolute path to the file to import | |
| truncateFirst | No | ||
| format | No | json | |
| delimiter | No |
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';