pg_export_table_data
Export PostgreSQL table data to JSON or CSV files for analysis, backup, or data sharing purposes.
Instructions
Export table data to JSON or CSV format
Input Schema
TableJSON Schema
| Name | Required | Description | Default |
|---|---|---|---|
| connectionString | No | ||
| tableName | Yes | ||
| outputPath | Yes | absolute path to save the exported data | |
| where | No | ||
| limit | No | ||
| format | No | json |
Implementation Reference
- src/tools/migration.ts:30-85 (handler)Core handler function that executes the logic for exporting PostgreSQL table data to JSON or CSV format, including database query, data formatting, and file writing.async function executeExportTableData( input: ExportTableDataInput, getConnectionString: GetConnectionStringFn ): Promise<{ tableName: string; rowCount: number; outputPath: string }> { const resolvedConnectionString = getConnectionString(input.connectionString); const db = DatabaseConnection.getInstance(); const { tableName, outputPath, where, limit, format } = input; try { await db.connect(resolvedConnectionString); let query = `SELECT * FROM "${tableName}"`; // Consider quoting table name properly const params: unknown[] = []; if (where) { query += ` WHERE ${where}`; // SECURITY: Ensure 'where' is safe or validated if user-supplied } if (limit) { query += ` LIMIT ${limit}`; } const data = await db.query<Record<string, unknown>[]>(query, params); const dir = path.dirname(outputPath); // Use fs.promises.mkdir for cleaner async/await await fs.promises.mkdir(dir, { recursive: true }); if (format === 'csv') { if (data.length === 0) { await fs.promises.writeFile(outputPath, ''); } else { const headers = Object.keys(data[0]).join(','); const rows = data.map(row => Object.values(row).map(value => { const stringValue = String(value); // Ensure value is a string return typeof value === 'string' ? `"${stringValue.replace(/"/g, '""')}"` : stringValue; }).join(',') ); await fs.promises.writeFile(outputPath, [headers, ...rows].join('\n')); } } else { await fs.promises.writeFile(outputPath, JSON.stringify(data, null, 2)); } return { tableName, rowCount: data.length, outputPath }; } catch (error) { throw new McpError(ErrorCode.InternalError, `Failed to export data: ${error instanceof Error ? error.message : String(error)}`); } finally { await db.disconnect(); } }
- src/tools/migration.ts:20-28 (schema)Zod input schema defining parameters for the pg_export_table_data tool, including table name, output path, optional filters, and format.const ExportTableDataInputSchema = z.object({ connectionString: z.string().optional(), tableName: z.string(), outputPath: z.string().describe("absolute path to save the exported data"), where: z.string().optional(), limit: z.number().int().positive().optional(), format: z.enum(['json', 'csv']).optional().default('json'), }); type ExportTableDataInput = z.infer<typeof ExportTableDataInputSchema>;
- src/tools/migration.ts:87-104 (registration)Tool object definition and export, including name, description, input schema reference, and wrapper execute function that handles validation and delegates to core handler.export const exportTableDataTool: PostgresTool = { name: 'pg_export_table_data', description: 'Export table data to JSON or CSV format', inputSchema: ExportTableDataInputSchema, async execute(params: unknown, getConnectionString: GetConnectionStringFn): Promise<ToolOutput> { const validationResult = ExportTableDataInputSchema.safeParse(params); if (!validationResult.success) { return { content: [{ type: 'text', text: `Invalid input: ${validationResult.error.format()}` }], isError: true }; } try { const result = await executeExportTableData(validationResult.data, getConnectionString); return { content: [{ type: 'text', text: `Successfully exported ${result.rowCount} rows from ${result.tableName} to ${result.outputPath}` }] }; } catch (error) { const errorMessage = error instanceof McpError ? error.message : (error instanceof Error ? error.message : String(error)); return { content: [{ type: 'text', text: `Error exporting data: ${errorMessage}` }], isError: true }; } } };
- src/index.ts:225-257 (registration)Inclusion of the pg_export_table_data tool (via exportTableDataTool) in the central allTools array, which is passed to the MCP server constructor for registration and capability advertisement.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 ];