SqlJam: Move Tables Across Databases with Schema, Data and All

Tables, indexes, constraints, sequences, partitions and rows, in one go.

1. Overview

SqlJam: Move tables across databases. Schema, data and all.

A Java 17 + JavaFX desktop tool that copies tables between relational and OLAP databases (MySQL, MariaDB, PostgreSQL, Oracle, SQL Server, Db2, H2, SQLite, DuckDB, ClickHouse and more) in any direction:
import directly into a target database or into the same schema as copies, or save a self-describing export package of SQL, Parquet or CSV files and import it later. Imports into a database run in one transaction which an error rolls back, and rows are inserted, updated or skipped by key.

2. What Problem Does It Solve?

The hard part of a cross-database migration is not INSERT, it is the details: type names, identity columns, default value syntax, bigint unsigned overflow, timestamps shifted by time zones, BLOBs inflating scripts, and old servers rejecting new syntax. Hand-written scripts are slow and miss things.

SqlJam reads one metadata tree and generates SQL with a dialect chosen by target type and version. Export packages carry a manifest.json, so an import knows the target, checks completeness and verifies every file.

3. Quick Start

git clone [email protected]:paganini2008/sqljam.git && cd sqljam
./mvnw -DskipTests package       # Windows: mvnw.cmd, output goes to bin/
bin/sqljam.sh                    # Windows: bin\sqljam.bat, or double click the jar of your platform

bin/
├── sqljam-1.0.0-SNAPSHOT-win.jar           # runnable jar of each platform,
├── sqljam-1.0.0-SNAPSHOT-mac-aarch64.jar   # JavaFX and all JDBC drivers inside
├── sqljam-1.0.0-SNAPSHOT-mac.jar
├── sqljam-1.0.0-SNAPSHOT-linux.jar
├── sqljam.sh / sqljam.bat                  # launchers, pick the jar of the platform
├── sqljam.properties                       # configuration, edit and restart
├── sqljam.vmoptions                        # JVM options, one per line
├── sqljam.png                              # Dock icon of macOS
└── sqljam-tutorial.mp4                     # tutorial video, export and import in under 3 minutes

sqljam.properties and sqljam.vmoptions sit next to the runnable jar. Edit them and restart, no rebuild needed.

A splash screen shows the loading of the configuration, the data sources and the JDBC drivers:

The first start brings an Example Shop (H2) data source with a few e-commerce tables (categories, customers, products, orders, order_items) to try everything right away. Example on the login page or Help → Restore Example Database brings it back at any time.

Right-click a data source to Connect, and once it is connected the same menu offers Disconnect, which also closes its table tabs. When an import or export needs a target data source, the + button next to it creates one on the spot. Exit sits at the right end of the toolbar. Help → About SqlJam shows the version, the home page, the repository and how to reach the author, and the GitHub icon at the bottom right opens the repository.

SQL Query on the toolbar opens an editor for the selected data source. Ctrl+Enter (⌘+Enter on macOS) runs the statement at the caret, Ctrl+Shift+Enter runs them all, and Export… sends all rows of the query to SQL, Parquet, CSV or another database:

A short tutorial video walks through the example database: query rows, export into files, import the package into PostgreSQL, and copy the tables straight into SQL Server and Oracle.

1. Log in to a data source	2. Export wizard	3. Progress

4. Data query	5. Package viewer	6. Parquet files

4. Requirements

Only Java 17+ is needed to run SqlJam. JavaFX and the JDBC drivers are inside the runnable jar, and building needs no Maven installation thanks to the Maven Wrapper (./mvnw).

All JDBC drivers are bundled in the fat jar.

5. How It Works

flowchart LR
    S[(Source DB)] --> T[Metadata tree]
    T --> D[Target dialect<br/>type + version subclass]
    D --> I[Import directly] --> TD[(Target DB)]
    D --> E[Export package<br/>schema.sql · data*.sql · lob/ · manifest.json]
    E --> SI[ScriptImporter] --> TD
    D --> P[Parquet or CSV package<br/>DuckDB workspace · data/*.parquet or *.csv]
    P --> PI[ParquetImporter · CsvImporter] --> TD

• Read metadata once: XxxMetaDataOperations completes comments, identity, generated columns, partitions and sequences per database.
• Generate for the target: DbType.createDialect(major, minor) picks the version subclass.
• Stream rows: count rows (percentage progress) → page by primary key → normalize values → batch insert or SQL literals.
• Parquet and CSV: rows pass an embedded DuckDB workspace, COPY ... TO writes them, read_parquet or read_csv with the column types of manifest.json loads them back.
• Transactions: an import runs in one transaction, committed when it succeeds or after your confirmation. MySQL, MariaDB, Oracle and H2 commit around DDL, so keys and identity values are set after the commit.
• Upserts and skips: ON CONFLICT, ON DUPLICATE KEY UPDATE or MERGE of each database, kept tables, indexes and keys are skipped.
• Skipped errors: a failed page is written row by row under savepoints, each bad row is logged with its key and the others are kept.

6. Code Examples

Example 1: Export as a PostgreSQL package

Input: MySQL database shop

ScriptExporter exporter = new ScriptExporter(new File("export"), DataFileStrategy.FILE_PER_TABLE, 10 * 1024 * 1024);
Exporter.ExportConfiguration config = exporter.getConfiguration();
config.setDbType(DbType.MYSQL);
config.setUrl(DbType.MYSQL.getUrl("localhost", 3306, "shop"));
config.setUsername("root");
config.setPassword("secret");
config.setIdReused(true);
exporter.setTargetDbType(DbType.POSTGRESQL);
exporter.exportDdlAndData();

Output

export/
├── schema.sql   ├── data/orders.sql   ├── data/orders_2.sql
├── lob/         ├── lob-manifest.json ├── constraints.sql
└── manifest.json

The same tables as Parquet files:

ParquetExporter parquet = new ParquetExporter(new File("export-parquet"));
parquet.setTargetDbType(DbType.POSTGRESQL);
parquet.setCompression("ZSTD");
parquet.exportDdlAndData();   // schema.sql, data/orders.parquet, constraints.sql, manifest.json

CsvExporter csv = new CsvExporter(new File("export-csv"));
csv.setDelimiter(";");
csv.exportDdlAndData();       // data/orders.csv, data/orders_2.csv ...

Example 2: Oracle → SQL Server directly (schema created)

ImportExporter importer = new ImportExporter();
Exporter.ExportConfiguration source = importer.getExportConfiguration();
source.setDbType(DbType.ORACLE);
source.setUrl(DbType.ORACLE.getUrl("ora-host", 1521, "ORCL"));
source.setUsername("hr");
source.setPassword("secret");
source.setIncludedSchemaNames(new String[]{"HR"});
ImportExportHandler.ImportConfiguration target = importer.getImportConfiguration();
target.setDbType(DbType.SQLSERVER);
target.setUrl(DbType.SQLSERVER.getUrl("mssql-host", 1433, "demo"));
target.setUsername("sa");
target.setPassword("secret");
target.setTargetSchemaName("hr");
importer.exportDdlAndData();

Output: tables, keys, indexes, foreign keys, comments and sequences in demo.hr. Identities continue from the imported max value.

Rows of a query and copies in the same schema:

source.getTableQueries().put("EMPLOYEES", new TableQuery(List.of("EMPLOYEE_ID", "NAME", "SALARY"),
        "SALARY > 1000", null, "SALARY DESC"));   // target table created with these columns
target.setTableNamePattern("{table}_copy");       // EMPLOYEES_COPY next to EMPLOYEES

7. Configuration

All settings live in bin/sqljam.properties next to the runnable jar. Settings changed in the UI are saved to ~/.sqljam/sqljam.properties and win over the file.

8. Performance

Environment: Apple M2 Max, 32 GB, JDK 17. MySQL/PostgreSQL local, Oracle/SQL Server in Docker. Default settings.

Quality: a full cross-database import matrix with every column type of every database, SQL, Parquet and CSV package round trips, commit and roll back verified on every database, copies in the same schema, old-version SQL executed on real servers, UI and boundary tests.

9. Design Trade-offs

• Table-level focus: tables, keys, indexes, constraints, sequences, partitions and comments: the objects data migration depends on.
• Rows paged by primary key: stable, deterministic reads on every database.
• Tables copied one by one: predictable load on production servers.
• Plain SQL in packages: readable, editable and portable. Direct import uses batched inserts for speed.
• Version-specific SQL: older servers get the syntax they understand instead of a lowest common denominator.
• Parquet and CSV through DuckDB: one embedded engine writes and reads both, no Hadoop libraries.
• One transaction by default: an import is all or nothing, confirm before commit when you want to look first.
• Grouped rows are for viewing: exports carry real table rows, a GROUP BY result is a view of them.
• Copies need their own names: a copy in the same schema takes a table name such as {table}_copy, so the originals are never replaced.

10. Summary

1. Any-to-any copies. Every cross-database pair passes automated tests.
2. Dialects are chosen by type + version. Old versions are subclasses.
3. Tables, indexes, constraints, sequences, partitions and comments move together.
4. Export package = SQL + LOB files + manifest.json. Export and import are paired.
5. Data files split by size (SQL and CSV 10 MB, Parquet 256 MB) and imported in order. LOBs stored separately.
6. Timestamps, unsigned numbers, bits, money, UUID and JSON survive the trip.
7. Pooled connections and batch inserts: 40k to 95k rows/s for direct imports.
8. A mainstream dark UI with 7 themes, percentage progress and cancel.
9. Parquet and CSV packages and files of other tools, DuckDB and ClickHouse as OLAP data sources, imports in one transaction and rows updated by key.
10. A SQL Query editor exports the rows of any query through the same exits, columns of tables keep their types.
11. Query the rows of a table, export just those rows, or copy tables inside the same schema.