Migrate from MongoDB to SQL

Turn collections into relational tables, as SQL files or directly in MySQL, SQL Server, PostgreSQL, Oracle or IBM DB2, with embedded objects and arrays mapped to columns or child tables.

The MongoDB → SQL migration turns collections into relational tables. A unit maps one collection or view. Its mapping tree says where each field goes: a column, columns of the same table, or a child table linked by keys. The output is either SQL files in a folder or tables created directly on a SQL server.

The MongoDB source is only read. The SQL target is written only by a run, after a confirmation that names every unit, every file that will be replaced and every table that will be dropped.

Targets

Target kind	Writes	Dialects
Directory	One SQL file per unit (`CREATE TABLE` and `INSERT INTO` statements)	MySQL, Microsoft SQL Server, PostgreSQL, Oracle, IBM DB2
Server	Tables on a saved SQL connection	MySQL, Microsoft SQL Server, PostgreSQL, Oracle, IBM DB2 (the type follows the server)

Sybase is a source of the SQL → MongoDB migration only, not a target. Oracle and IBM DB2 need their client installed (Edit › Preferences › External tools › SQL Drivers).

Run a migration

Example: write acme_store.orders to a PostgreSQL database.

Right-click orders in the tree and choose SQL Migration › MongoDB -> SQL Migration. You can also use the SQL Migration dropdown on the global toolbar. The collection becomes the first export unit.
Check MongoDB source connection: (use (Switch connection) to change it).
Next to SQL target connection/directory:, choose Server and pick the SQL connection, or choose Directory and a folder.
Add export units adds more collections. Each one is scanned for a default mapping.
In the Overview, check each unit’s Target database (or Target file), Mapping and Drop Table Mode. Untick a unit to leave it out of Run migration.
Select a unit and click Mappings to adjust its tables (next section).
Click Run migration or Run selected units, and confirm.

The migration always creates its own tables. An existing table with the same name stops the unit, unless its Drop Table Mode is Drop existing tables. That choice is named in the confirmation.

Edit a unit’s mapping

The mapping tab shows the document tree on the left and the SQL tables it produces on the right (SQL Table Preview, SQL Statement Preview).

Select a mapping and use Edit Mapping… to change its field, mapping type, relationship, table and column names, and SQL type.
Include in export (Ctrl+I) turns a field on or off.
Add child (Alt+K) and Add sibling (Alt+J) add mappings for fields the scan did not see.
Use as primary key makes _id (or another value of the main table) the primary key. Otherwise an automatic key is used.
New mapping (Ctrl+T) replaces the mappings with a new default, from a fresh scan of the documents (Number of documents to scan).
Update statement preview refreshes the statements. Auto update statement preview does it after every change.

Mapping types

Mapping Type	Use for
Column	A plain field mapped to its own column
Object Fields	An embedded object, mapped to SQL
Array Elements To Rows	An array, one row per element, with an index column
Array Elements To Columns	An array, one column per position

Relationship types

Relationship Type	Where the data goes
Embed	Columns added to the table of a parent mapping
Table, PK <- FK	A new table, with a foreign key to the parent’s primary key
Table, FK -> PK	A new table, and the parent table gets a foreign key to it
Table, No Link	A new table, without a link to the parent

Example: in orders, map items (an array of objects) as Array Elements To Rows with Table, PK <- FK. You get an orders table and an orders_items table that references it.

SQL types

Edit SQL types shows the default conversion from each MongoDB type to each SQL dialect. You can change these conversions or add custom ones. Automatically set type of known fields applies them to new mappings.

Monitored runs and Revert

On a unit’s mapping tab, Run monitored export runs that unit and keeps its log in SQL Export Log Messages. If the result is wrong, Revert … undoes the unit’s finished or interrupted run: it drops the tables it created, or deletes the file it wrote. It always asks first. View / save migration logs opens or copies a unit’s log.

Options reference

Option	Values
SQL target kind	Server, Directory
Drop Table Mode (per unit)	Do not drop (default), Drop existing tables
Number of documents to scan	How many documents a new mapping reads
Primary key	Use as primary key, or Use automatic primary key (Primary key name, Primary key initial value)
Incremental execution options	Export only the documents added since the last run of the saved task

Save mappings and Load mappings keep the units and their mappings in a file. Save task keeps the migration as a task (Automation).

Tips

Start with a Directory target and read the generated SQL before writing to a server.
Use Show insert statements for one preview document only to follow one document through every table.

Troubleshooting

Problem	What to do
A unit stops because a table exists	Choose Drop existing tables for that unit, or another target database.
A field warns about a type mismatch	Its values have several types. Change the SQL type, or untick Warn when encountering a type mismatch.
“Sybase is a SQL → MongoDB source only”	Choose another target type.
An existing file was replaced	The confirmation lists replaced files. Files are written next to the target and renamed into place only when complete.