# How DBX Handles Full Database Export and Cross-Engine Data Transfer

> DBX exports full databases via an async pipeline, discovering schema, sorting dependencies, and generating SQL dumps for seamless transfer between MySQL, PostgreSQL, Oracle, and Dameng.

- Repository: [skyler/dbx](https://github.com/t8y2/dbx)
- Tags: how-to-guide
- Published: 2026-07-04

---

**DBX exports full databases by orchestrating an async pipeline that discovers schema objects, sorts them by foreign key dependencies, and generates dialect-specific SQL dumps containing DDL and batched INSERT statements, enabling seamless data transfer between different database engines like MySQL, PostgreSQL, Oracle, and Dameng.**

The DBX database client (t8y2/dbx) provides a robust solution for full database export and cross-engine data transfer through its Rust-based core library. By leveraging engine-specific adapters and streaming SQL generation, DBX can migrate entire database schemas and data between heterogeneous systems while handling dialect differences like identifier quoting, temporal literal formats, and INSERT batching limitations.

## The Export Pipeline Architecture

DBX's export functionality resides in the `dbx_core::database_export` crate, coordinating between Tauri commands and core Rust implementations to produce portable SQL dumps.

### Request Handling and Orchestration

When initiating a full database export, the front-end (desktop, web, or CLI) constructs a `DatabaseExportRequest` struct defined in [`crates/dbx-core/src/database_export.rs`](https://github.com/t8y2/dbx/blob/main/crates/dbx-core/src/database_export.rs) (lines 20‑30). This request is passed to the Tauri command `export_database_sql` located in [`src-tauri/src/commands/database_export.rs`](https://github.com/t8y2/dbx/blob/main/src-tauri/src/commands/database_export.rs) (lines 12‑27), which spawns an async task executing `export_database_sql_core`.

The core routine first retrieves the connection's `DatabaseType` from the application state (lines 894‑901) to determine which dialect-specific rules for quoting, pagination, and INSERT generation apply throughout the export process.

### Configuration and Object Discovery

Before generating SQL, DBX must inventory the database objects and establish a safe export order. The system calls `schema::list_tables_core` (lines 706‑718) to enumerate tables and views, while Postgres-specific objects like sequences, procedures, and functions are fetched via `list_postgres_export_sequences`.

Critical to data integrity, tables are sorted by foreign key dependencies using `transfer::sort_tables_by_fk_dependency` (lines 662‑674). This ensures parent tables are exported before child tables, preventing constraint violations during re-import.

### Structure Export

For each discovered table, DBX retrieves the complete DDL via `schema::get_table_ddl_core` (lines 880‑894) and writes it to the target dump file. If the `drop_table_if_exists` flag is enabled, the system generates `DROP TABLE IF EXISTS` statements through `drop_table_if_exists_sql` (lines 1198‑1200).

Engine-specific preparations occur during file initialization. For MySQL targets, foreign key checks are temporarily disabled with `SET FOREIGN_KEY_CHECKS = 0` (lines 730‑733) to allow schema creation without constraint conflicts.

### Data Export and Batching

Data extraction proceeds through a memory-efficient streaming pipeline. Column metadata (name, type, and extra attributes) is obtained via `schema::get_columns_core`, followed by a row count query using `transfer::count_sql` (lines 928‑935) to support progress tracking.

Data is fetched in paged batches using `transfer::pagination_sql` (lines 958‑966) respecting the user-specified `batch_size`. For each batch, `transfer::generate_insert_typed` creates typed INSERT statements (lines 981‑989). The `build_export_insert_statements` function (lines 666‑735) handles per-engine quirks, enforcing single-row inserts for Oracle (`uses_single_row_insert_statements`), formatting MySQL BIT literals via `format_mysql_bit_literal` (lines 278‑325), and normalizing temporal values through `format_export_temporal_literal`.

### Progress Reporting and Cancellation

After processing each object (table, view, sequence), DBX emits an `ExportProgress` event (lines 1109‑1122) to update the UI or CLI. The system supports graceful cancellation through a global `EXPORT_CANCELLED` set (lines 15‑17). The `cancel_database_export` command marks the export ID for termination, which the running export checks after each batch (lines 440‑447) to abort cleanly.

Post-export, MySQL foreign key checks are re-enabled via `SET FOREIGN_KEY_CHECKS = 1` (lines 1168‑1170), and a final `ExportProgress` event with status `Done` is emitted (lines 1173‑1179).

## Cross-Engine Data Transfer

DBX enables data transfer between different database engines by generating portable SQL dumps that account for dialect differences. The export produces standard SQL containing DDL and INSERT statements that can be fed into DBX's import flow or executed directly via native clients like `psql`, `mysql`, or `sqlcmd`.

Engine-specific adaptations are handled through the `transfer` module:

- **Identifier Quoting**: `quote_table_identifier` and `quote_identifier` automatically switch between MySQL backticks (`` `name` ``) and PostgreSQL double quotes (`"name"`).
- **INSERT Batching**: Oracle's single-row limitation is enforced by `uses_single_row_insert_statements`, while MySQL supports multi-row batches.
- **Data Type Mapping**: `format_mysql_bit_literal` converts BIT columns to `b'1010'` syntax, while `format_export_temporal_literal` normalizes timestamps to RFC-3339 or engine-specific formats.
- **Identity Columns**: Dameng requires `SET IDENTITY_INSERT` toggles, wrapped by `wrap_dameng_identity_insert_sql` (lines 200‑207).
- **Sequence Handling**: Postgres sequences are exported as `CREATE SEQUENCE` and `SETVAL` statements via `generate_postgres_sequence_create_ddl`, `generate_postgres_sequence_owner_ddl`, and `generate_postgres_sequence_setval_sql` (lines 440‑560).

## Implementation Examples

### CLI Export Command

Export an entire database using the bundled `dbx` CLI:

```bash
dbx export database \
  --connection-id mydb \
  --output /tmp/mydb_export.sql \
  --include-structure \
  --include-data \
  --batch-size 500

```

Behind the scenes, this marshals a `DatabaseExportRequest` and invokes the Tauri `export_database_sql` command.

### Programmatic Rust Usage

Integrate DBX export functionality directly into Rust applications:

```rust
use dbx_core::database_export::{DatabaseExportRequest, export_database_sql_core};
use dbx_core::transfer::sort_tables_by_fk_dependency;

// Build the request
let request = DatabaseExportRequest {
    export_id: "run-001".into(),
    connection_id: "mydb".into(),
    database: "mydb".into(),
    schema: "public".into(),
    file_path: "/tmp/mydb_export.sql".into(),
    selected_tables: vec![],               // empty → export all tables
    include_structure: true,
    include_data: true,
    include_objects: true,
    drop_table_if_exists: false,
    batch_size: 1000,
};

// `state` is the shared AppState from Tauri/DBX
tokio::spawn(async move {
    let _ = export_database_sql_core(&state, &request, |progress| {
        println!("Export progress: {:#?}", progress);
    }).await;
});

```

### Cancelling an Export

Terminate a running export using the export ID:

```bash
dbx export cancel --export-id run-001

```

This invokes `set_export_cancelled` (line 51 in [`database_export.rs`](https://github.com/t8y2/dbx/blob/main/database_export.rs)) to mark the operation for graceful termination.

## Summary

- **DBX exports full databases** through an async pipeline in `dbx_core::database_export` that handles request validation, object discovery, and SQL generation.
- **Foreign key dependencies** are resolved using `sort_tables_by_fk_dependency` to ensure tables export in the correct order for referential integrity.
- **Memory efficiency** is achieved through paginated batching via `pagination_sql` and `generate_insert_typed`, with configurable `batch_size` limits.
- **Cross-engine compatibility** is managed through dialect-specific adapters in [`transfer.rs`](https://github.com/t8y2/dbx/blob/main/transfer.rs) that handle identifier quoting, literal formatting, and engine-specific syntax like MySQL BIT literals or Dameng identity columns.
- **Cancellation support** allows users to abort exports gracefully via the `EXPORT_CANCELLED` set, which the core export loop checks between batches.

## Frequently Asked Questions

### Can DBX export databases between different engine types?

Yes, DBX supports cross-engine data transfer by generating dialect-specific SQL dumps that can be imported into different target engines. The system automatically adapts identifier quoting, INSERT batching strategies, and data type literals according to the source and target `DatabaseType` configurations.

### How does DBX handle large table exports without memory issues?

DBX implements streaming pagination using `transfer::count_sql` to determine total row counts and `transfer::pagination_sql` to fetch data in configurable batches. The `build_export_insert_statements` function processes these batches incrementally, writing results to disk rather than holding entire tables in memory.

### What happens if an export is interrupted or cancelled?

Exports can be cancelled gracefully through the `cancel_database_export` command, which sets a flag in the global `EXPORT_CANCELLED` set. The export loop checks this flag after processing each object and batch (lines 440‑447), allowing the operation to terminate cleanly and re-enable any temporarily disabled constraints like MySQL foreign key checks.

### Does DBX preserve foreign key relationships during export?

Yes, DBX explicitly sorts tables by foreign key dependencies using `sort_tables_by_fk_dependency` (lines 662‑674) before exporting data. This ensures parent tables are dumped before child tables, and the generated SQL includes the full DDL with constraints, allowing the database schema to be recreated with relationships intact on the target system.