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.

Pain	SqlJam
Dialect differences	Types, identifiers, defaults, identity and literals translated by the dialect of each target
Old versions	Version subclasses: Oracle11gDialect, PostgreSQL9Dialect, MySQL56Dialect, SQLServer2008Dialect …
Missing keys & indexes	Primary keys, unique/regular indexes, foreign keys, comments, sequences, partitions of every kind (range, list, hash, interval, composite, reference, nested), shown with their partitioning in the tree
Huge scripts	Data files split by size: SQL and CSV at 10 MB (orders.sql, orders_2.sql …), Parquet at 256 MB (orders.parquet, orders_2.parquet …), imported part by part in order
LOBs	Stored as files, restored by primary key
Scripts without context	manifest.json: source, target, options, SHA-256 per file, row counts
Analytics needs Parquet	Parquet packages through an embedded DuckDB, Parquet files of other tools loaded into any table
Spreadsheets need CSV	CSV packages with your delimiter, quote, NULL text, header, encoding (UTF-8 with BOM, GBK and more) and date format, exact types kept in manifest.json, CSV files of other tools loaded too
An import went wrong	One transaction by default, rolled back on any error, or commit every N rows. Skip rows with errors goes on and logs each skipped row. Confirm before commit reviews a successful import first
Rows exist already	Update or insert by key or skip existing by key: the primary key or the columns of a unique index match the rows, insert is the default
Warehouses apart from OLTP	DuckDB and ClickHouse data sources, grouped as OLAP databases
Only part of a table	Data viewer query (columns, WHERE, GROUP BY, ORDER BY) and export of the rows of the query
Rows of a join or a report	SQL Query editor with syntax highlighting, the first rows are shown and all rows of the query are exported as a table
A copy next to the original	Copies in the same schema with {table}_copy, keys and indexes renamed, source tables protected
Custom and modern types	PostgreSQL enums and domains, vectors, unions, maps, tuples and variants

3. Quick Start

git clone [e-mail]: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).

Platform	Runnable jar	Launcher	Supported
Windows 10 / 11 (x64)	sqljam-<version>-win.jar	sqljam.bat	✅
macOS Apple Silicon	sqljam-<version>-mac-aarch64.jar	sqljam.sh	✅
macOS Intel	sqljam-<version>-mac.jar	sqljam.sh	✅
Linux x64 (GTK 3)	sqljam-<version>-linux.jar	sqljam.sh	✅

Database	Version
MySQL	5.5 to 9.x
PostgreSQL	9.x to 16
Oracle	11g to 23ai
SQL Server	2008 to 2022
MariaDB	10.3 to 11.x
H2 / SQLite	2.x / 3.x
DuckDB (OLAP)	1.x
ClickHouse (OLAP)	24.x to 26.x

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

Source column	→ PostgreSQL	→ Oracle	→ SQL Server
MySQL bigint unsigned	numeric(20, 0)	NUMBER(20,0)	decimal(20,0)
PostgreSQL jsonb	same	CLOB	nvarchar(max)
SQL Server datetime2(7)	timestamp	TIMESTAMP(7)	same
PostgreSQL enum / domain	same (created in the target schema)	VARCHAR2 / base type	varchar / base type
MySQL / Oracle VECTOR	text	same	nvarchar(max)
ClickHouse Array(T) / Map(K, V)	text	CLOB	nvarchar(max)

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.

Property	Default	Description
sqljam.export.max-file-size	10485760	Max bytes of a SQL or CSV data file
sqljam.export.parquet-max-file-size	268435456	Max bytes of a Parquet file, Parquet reads best in large files
sqljam.export.csv-delimiter	,	Delimiter of CSV files
sqljam.import.confirm-commit.reminder-minutes	10	Reminder for an import still waiting for commit
sqljam.export.data-file-strategy	SINGLE_FILE	Single file or one file per table
sqljam.export.lob-separated	true	Write LOBs as files
sqljam.export.page-size	5000	Rows per page, tuned by benchmark
sqljam.export.lob-page-size	100	Rows per page for tables with LOB columns
sqljam.banner.mode	console	Startup banner with the version: console, log or off
sqljam.import.batch-size	1000	INSERT statements per batch when importing a package
sqljam.ui.theme	Primer Dark	7 themes
sqljam.pool.maximum-size	10	Connection pool size

8. Performance

Scenario (200,000 rows × 6 columns)	Time	Rows/s
H2 → Oracle 23ai	2.1 s	95.3k
H2 → export package (32 MB, 4 files)	2.9 s	69.4k
H2 → PostgreSQL 16	3.2 s	62.4k
H2 → MySQL 9.6	3.3 s	60.7k
H2 → SQL Server 2022	5.0 s	40.1k
Export package → PostgreSQL	4.5 s	44.5k

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.