Data transfer between connections: copy tables, schemas, and databases across servers (and engines)

[datlechin]

Problem

There is no way to move data or structure between two connections inside TablePro. Whatever the unit (one table, several tables, a whole schema, or an entire database), the only path today is manual: export to a SQL or CSV file on the source connection, then import that file on the destination. That is slow for large data, easy to get wrong (type mapping, encoding, NULL handling, constraints), and there is no single action to send objects from one server to another.

This started from a user asking "When will you add copy tables from one server to another?", but the real need is broader: a proper data transfer between connections, across databases and, where possible, across engines.

Proposed solution

A transfer feature that copies database objects from a source connection to a destination connection, at several scope levels:

• Scope: a single table, a multi-table selection, a whole schema/database, or a filtered/queried result set.
• What to copy: structure only, data only, or both.
• On conflict: create new, drop and recreate, or append into an existing object.
• Same-engine first (MySQL to MySQL, PostgreSQL to PostgreSQL), where structure and types map cleanly. Cross-engine as a later phase, using each plugin's type mapping with clear lossy-conversion warnings.
• Same-server fast path: when source and destination are the same server (different database/schema), use server-native INSERT ... SELECT instead of round-tripping through the app.
• Stream data in batches (parameterized bulk insert, keyset pagination on the source) so large tables do not load into memory, with a progress bar and Cancel.
• Reuse the existing export/import building blocks (ExportService, ImportService, the SQL export/import plugins) rather than a parallel code path.

Entry points: right-click a table or selection in the sidebar ("Copy to..."), or a transfer panel that picks source objects and a destination connection + database/schema.

Architecture

Keep a clean, testable split:

• TableTransferPlan (pure, unit-testable): the resolved description of what to copy, the options, the generated DDL, and the column/type mappings. Shown in a preview / dry run before anything runs.
• TableTransferExecutor (side-effecting): runs the plan against the source and destination drivers from DatabaseManager's pool, in phases, with progress and cancel.
• Go through plugin-provided capabilities and type mapping, not an engine switch. DatabaseType is an open struct (CLAUDE.md), so every decision needs a sane default: that emits portable SQL and warns on unsupported features.

Hard problems (most of the cost)

1. Cross-engine type mapping. ENUM, JSON/JSONB, UUID, arrays, spatial/vector, TIMESTAMPTZ, AUTO_INCREMENT vs SERIAL/IDENTITY. Needs a real per-plugin mapping layer and honest lossy warnings.
2. Value encoding across drivers. Dates/timezones, binary/BLOB, NULL vs empty, numeric precision, charset/collation, large text. Values are PluginCellValue and must re-bind correctly on the destination.
3. Large data. Batch sizing, throughput, keyset pagination (needs a stable order / PK), and cancel mid-stream without leaving a half-written object.
4. Conflict and atomicity. create / drop-recreate / append; behavior when a row fails (rollback vs partial); destination permissions; identifier quoting and reserved words per engine.
5. Constraint and dependency ordering. Create indexes and foreign keys after the data load; order multi-table and whole-schema copies by dependency.
6. Object coverage. Generated columns, views, partitioned tables, triggers, sequences/identity reset, default expressions that do not translate.

Phasing

• Phase 1: same-engine, single table, structure+data, create-new only. Ships the UI shell, the plan/executor split, and the preview.
• Phase 2: conflict modes, batched streaming with progress + cancel, large-table keyset paging, dry run.
• Phase 3: cross-engine type and value mapping, with lossy-conversion warnings. Highest risk.
• Phase 4: multi-table and whole-schema/database transfer with dependency ordering; same-server INSERT ... SELECT fast path.

Alternatives considered

• Export to a SQL/CSV file then import on the other connection. Works today but is manual, slow, and lossy across engines.
• Hand-written transfer SQL. Only works same-engine, same session, and for trivial tables.

Related database type

N/A / General