pg_export_table_data
Export PostgreSQL table data to JSON or CSV for analysis or migration. Specify table name, output path, and optional filters like 'where' clause or row limit to customize data extraction.
Instructions
Export table data to JSON or CSV format
Input Schema
TableJSON Schema
| Name | Required | Description | Default |
|---|---|---|---|
| connectionString | No | ||
| format | No | json | |
| limit | No | ||
| outputPath | Yes | absolute path to save the exported data | |
| tableName | Yes | ||
| where | No |
Implementation Reference
- src/tools/migration.ts:30-85 (handler)Core execution logic for exporting table data: connects to DB, queries data with optional WHERE/LIMIT, writes JSON or CSV to outputPath, returns row count.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-27 (schema)Zod input schema defining parameters for the tool: optional connectionString, required tableName and outputPath, optional where clause, limit, and format (json/csv).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'), });
- src/tools/migration.ts:87-104 (registration)Defines and exports the PostgresTool object for 'pg_export_table_data', including wrapper execute that validates input and calls 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)Central allTools array collects all imported tools including exportTableDataTool (line 253), passed to PostgreSQLServer constructor for registration in MCP capabilities.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 ];