How Chat2DB Import/Export Functionality Works: Architecture and Implementation

Chat2DB implements a four-layer asynchronous architecture for import/export operations, using REST controllers for HTTP endpoints, converters for domain model mapping, a transfer service for task orchestration, and pluggable strategy implementations for actual file I/O.

The import/export functionality in Chat2DB provides a robust, modular mechanism for transferring data between databases and various file formats. Implemented in the OtterMind/Chat2DB open-source project, this system handles SQL dumps, CSV files, Excel spreadsheets, and JSON through an asynchronous task pipeline that ensures non-blocking API responses while supporting long-running bulk operations.

The Four-Layer Architecture

Chat2DB's import/export functionality follows a clean separation of concerns across four distinct layers, respecting the modular boundaries defined in the repository's Maven structure.

Web Layer: REST Controllers

The entry points reside in the chat2db-community-web module. According to the source code, TaskImportController receives SQL or structured file import requests at POST /api/import/... endpoints, while TaskExportController handles SQL and other file export requests at POST /api/export/.... These controllers accept requests annotated with @Valid for automatic input validation before passing data to the conversion layer.

Conversion Layer: Domain Model Mapping

TaskWebConverter bridges the gap between API contracts and internal domain models. This converter maps SqlFileImportRequest and OtherFileImportRequest objects to unified TaskFileImportRequest instances. For export operations, it transforms SqlFileExportRequest and OtherFileExportRequest into TaskSqlFileExportRequest or TaskOtherFileExportRequest objects before delegation to the service layer.

Transfer/Scheduling Layer: Task Orchestration

TaskTransferServiceImpl serves as the central coordinator for all import/export tasks. For imports, it creates an ImportAsyncContext via createImportContext() and delegates execution to taskDataImportService.importOtherFile(). For exports, it initializes an ExportAsyncContext (or standard AsyncContext for SQL exports), then dispatches to taskExportService.exportSqlFile() or taskDataExportService.exportOtherFile(). All tasks submit to ITaskSchedulerService for asynchronous execution, allowing the API to return a task ID immediately while processing continues in the background.

Domain Services: Concrete I/O Implementations

The actual file processing occurs in strategy implementations located in chat2db-community-domain-core. ImportFactory maintains a registry mapping file extensions to concrete IImportStrategy implementations, including SQLImporter, CSVImporter, XLSImporter, XLSXImporter, and JSONImporter. Export operations utilize DmlExportDeliveryAdapter to resolve MIME types and write data streams based on the ExportTypeEnum specification.

Key Architectural Features

Asynchronous Execution Model

Every import/export operation wraps in an AsyncContext (ImportAsyncContext or ExportAsyncContext) before submission to the scheduler. This design prevents HTTP thread blocking during large CSV exports or complex SQL dumps, with clients polling or receiving webhooks for completion status using the returned task ID.

Pluggable Import Strategies

The ImportFactory class implements a strategy pattern that maps file type strings to specific IImportStrategy implementations. Located at chat2db-community-domain/chat2db-community-domain-core/src/main/java/.../imports/ImportFactory.java, this factory makes adding new formats trivial—developers implement the interface and register the mapping without modifying controller logic.

File Handling and Delivery

The taskFileService provides default export paths through defaultExportPath() and manages temporary file creation. During export, DmlExportDeliveryAdapter determines appropriate file suffixes and content types based on the ExportTypeEnum, ensuring proper HTTP responses for downloads.

Validation and Error Handling

Spring's validation framework handles basic input constraints through @Valid annotations. The service layer throws IllegalArgumentException for unsupported scopes or empty table lists, providing immediate, clear feedback to API consumers before task creation begins.

Implementation Examples

Importing SQL Files via REST API

To import a SQL file into a specific table, send a POST request to the import endpoint:

curl -X POST http://localhost:10825/api/import/sql_file \
  -H "Content-Type: application/json" \
  -d '{
        "databaseName":"test_db",
        "schemaName":"public",
        "tableName":"my_table",
        "fileName":"my_table.sql",
        "importType":"sql"
      }'

Exporting Tables to CSV Format

Export one or more tables to CSV with header rows included:

curl -X POST http://localhost:10825/api/export/other_file \
  -H "Content-Type: application/json" \
  -d '{
        "databaseName":"test_db",
        "schemaName":"public",
        "tableNames":["my_table"],
        "exportPath":"/tmp/export",
        "exportType":"CSV",
        "containsHeader":true
      }'

Programmatic Java Usage

For server-side integration or custom automation, invoke ITaskTransferService directly with a builder pattern:

ITaskTransferService transfer = SpringContext.getBean(ITaskTransferService.class);
TaskSqlFileExportRequest req = TaskSqlFileExportRequest.builder()
    .databaseName("test_db")
    .schemaName("public")
    .tableNames(List.of("my_table"))
    .exportPath("/tmp/export")
    .exportType("CSV")
    .scope("TABLE")
    .containData(false)
    .build();
Long taskId = transfer.exportSqlFile(req);
System.out.println("Export task created: " + taskId);

Summary

• Chat2DB implements import/export functionality through a four-layer architecture separating web concerns from domain logic in chat2db-community-web and chat2db-community-domain-core modules.
• All operations execute asynchronously using AsyncContext and ITaskSchedulerService to prevent API blocking during long-running tasks.
• The ImportFactory pattern supports multiple formats (SQL, CSV, Excel, JSON) through the IImportStrategy interface, with concrete implementations like SQLImporter and CSVImporter handling format-specific parsing.
• TaskTransferServiceImpl coordinates task creation and delegates to taskDataImportService or taskExportService based on operation type.
• DmlExportDeliveryAdapter manages file delivery, resolving MIME types and suffixes from ExportTypeEnum values.

Frequently Asked Questions

How does Chat2DB handle large file imports without blocking the API?

Chat2DB wraps every import task in an ImportAsyncContext submitted to ITaskSchedulerService. When TaskTransferServiceImpl receives a request, it creates the async context via createImportContext() and delegates to taskDataImportService.importOtherFile(), returning a task ID immediately. This allows the HTTP thread to release while the actual import processing occurs in a background worker thread.

What file formats does Chat2DB support for import and export?

According to the ImportFactory and IImportStrategy implementations in the source code, Chat2DB supports SQL, CSV, XLS, XLSX, and JSON for imports. For exports, the system supports these formats plus additional types defined in ExportTypeEnum, with DmlExportDeliveryAdapter handling the specific serialization logic for each format.

Where are the import/export REST controllers located in the Chat2DB repository?

The REST endpoints reside in chat2db-community-server/chat2db-community-web/src/main/java/ai/chat2db/community/web/api/controller/, specifically within TaskImportController.java and TaskExportController.java. The underlying business logic exists in chat2db-community-domain-core, primarily in TaskTransferServiceImpl.java and the imports package containing strategies like SQLImporter.java.

Can I customize the export destination path in Chat2DB?

Yes. The TaskSqlFileExportRequest and related request objects accept an exportPath parameter where you can specify custom directories. Alternatively, you can use taskFileService.defaultExportPath() to generate standard locations. The DmlExportDeliveryAdapter ensures proper file suffixes and MIME types are applied regardless of the custom path specified.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}