Migrate from SQL to MongoDB

The SQL → MongoDB migration reads tables or queries from a relational database and writes documents into MongoDB collections. A unit maps one table or query to one collection. Its mapping can embed related rows as sub-documents (one-to-one) or arrays (one-to-many).

The SQL source is only read. Sessions open read-only on the server, and a custom query must be one reading statement (SELECT, WITH, VALUES or TABLE). Writes go only to the MongoDB target, after a confirmation.

Supported SQL databases

Oracle, IBM DB2 and Sybase clients are not part of the product. Install the client, then open Edit › Preferences › External tools › SQL Drivers: the page shows what was found and lets you point to the Oracle Instant Client folder, or to the DB2 and Sybase driver folder or library. These clients run in a separate helper process, so a crash or a hang in a client library never takes the app down. The next call starts a new helper.

Save a SQL connection

1. Open File › Manage SQL Connections.
2. Click New connection, choose the Database type, then fill in Server, Port, Database, User name and Password (tick Save password to keep it in the system’s credential store).
3. Optional: set TLS (with a CA file and a client certificate and key), or tick Use SSH tunnel to connect.
4. Click Test Connection, then Save.

You can export SQL connections to a file and import them on another machine, or share them through a team shared folder (Team).

Run a migration

Example: move the customers and orders tables of a PostgreSQL shop database into acme_store.

1. Click SQL Migration on the global toolbar and choose SQL -> MongoDB Migration. You can also right-click a connection, database or collection in the tree and choose SQL Migration › SQL -> MongoDB Migration: the target is then taken from the selection.
2. The first time, a short tutorial explains the steps. Click Get Started.
3. Next to Source SQL connection:, click Click here to connect to a server and pick the SQL connection.
4. Next to Target MongoDB connection:, pick the MongoDB connection ((Switch connection) changes it).
5. Click Add from source table and tick public.customers and public.orders in Select tables to import. Each table becomes a unit, writing to a collection of the same name. Use Add from custom SQL query to add a unit from a query instead.
6. In the Overview, check each unit’s Target database, Target collection and Insertion mode, and untick the units you don’t want to run.
7. Select a unit and click Mappings to shape its documents (next section).
8. Click Run migration (every included unit) or Run selected units, and confirm.

The confirmation names every unit, and every insertion mode that replaces, drops or clears data. The result of each unit appears under Last run.

Shape documents with mappings

Mappings opens one tab per unit:

• The Schema view lists the fields of the document. Each field takes a column of a dataset (the table, or a related table), with a type. Edit / Rename changes the name, column or type, Move up and Move down reorder fields, and Remove selected fields drops them. Add object field adds an embedded document.
• The Datasets view lists the tables in use and how they are joined.
• JSON output preview shows the first documents as they will be written. Tick Limit length of arrays in preview for long arrays.

Embed related rows

Both dialogs offer Get relationship from foreign key, which uses a foreign key found in the database. Specify relationship manually lets you give the parent and child columns yourself. Reuse Dataset adds fields from a dataset already joined, and Clean up unused datasets removes the joins that no field uses any more.

Example: in the customers unit, add an array field orders from public.orders on customers.id = orders.customer_id, with Object with a field per column. Each customer document then holds its orders.

Custom queries

For a unit made from a query, edit it in SQL Query. Create default mapping for query rebuilds the schema from the columns the query returns now.

Mask fields

A unit’s tab has a Data Masking section. Open field masking editor lists the fields of the first documents the unit builds. Set a technique on each field to hide, as in Mask data. OK keeps the rules for the unit, and Cancel drops your edits. The run masks every document before writing it, and the SQL database is not changed. A value that a rule cannot mask is left out, never written unmasked.

Options reference

Save mappings writes the units and their mappings to a JSON file, and Load mappings adds the units of such a file. Save task keeps the whole migration as a task to run again or on a schedule (Automation).

Tips

• Migrate into a new database first (acme_store_staging), check the documents, then run again against the real target.
• To migrate a single table with a simple column-to-field mapping, the Import wizard’s SQL database format is quicker (Import data).

Troubleshooting