How to migrate from MySQL to MongoDB

What actually changes

The vocabulary maps almost one to one, which is exactly what makes people underestimate the job:

The two rows that matter are FOREIGN KEY and JOIN. Everything else is a rename. Those two are a redesign, because MongoDB does not enforce referential integrity and its join operator is deliberately limited. If your migration plan is "copy each table into a collection of the same name", you will end up with a relational database that has lost its constraints and gained nothing — the worst of both. That anti-pattern has a name in the MongoDB community: a relational schema in a document store.

The genuine gain is that a single document can hold everything one screen of your application needs, so a page that took four joins becomes one read. You only get that gain if you redesign for it.

Step 1 — Start from your queries, not your tables

In MySQL you normalise first and query second. In MongoDB you do the opposite: the read patterns dictate the shape of the documents. So the first deliverable of a migration is not a schema, it is a list.

Go through the application and write down, for each screen or endpoint:

• What is fetched, and by which key
• How often, relative to everything else
• Whether it reads or writes
• How much data comes back — one item, twenty, thousands

The fastest way to build that list without guessing is to ask MySQL itself. Enable the slow query log with a threshold of zero for a representative period, or read performance_schema:

Those forty rows are your real workload. Order the migration around them. A table that is written constantly but read once a month deserves a completely different treatment from one behind your busiest page.

Do this before touching anything else. Every later decision — embed or reference, which indexes, which collections — falls out of this list. Teams that skip it end up migrating twice.

Step 2 — Embed or reference: the actual decision

This is the one design decision that determines whether the migration was worth doing. For each relationship in your MySQL schema you choose to nest the related data inside the parent document, or to keep it in a separate collection and store a reference.

Embed when

• The child is almost always read together with the parent
• The number of children is bounded and small — think dozens, not thousands
• The child has little meaning on its own (an address, a line item, a set of preferences)

Reference when

• The child list grows without a natural ceiling (events, log lines, messages)
• The child is queried independently of the parent
• The child changes far more often than the parent, or is shared by many parents

A widely used rule of thumb splits relationships into one-to-few (embed), one-to-many (reference, or embed an array of references), and one-to-very-many (reference from the child side, storing the parent id on each child).

Take a typical orders schema. In MySQL:

An order always displays with its items, and the item count is bounded — so items get embedded. A customer can have unlimited orders and you list orders by date without loading the customer — so orders stay a separate collection referencing the customer:

Note the deliberate duplication: the customer's name and the product names are copied into the order. That would be a normalisation error in MySQL. Here it is the point — the order screen becomes a single read, and it is also historically correct, because an order should show the price and name as they were at purchase time, not as they are today.

The cost is real and you should price it in before committing: when a product is renamed, existing orders keep the old name. For an order that is correct behaviour. For a customer's display name it may not be, and you will need a background job to fan the update out. Decide this per field, not per collection.

The 16 MB document size limit is a hard ceiling, and it exists to stop exactly the design where an unbounded array is embedded. If a document could conceivably grow without limit, that relationship must be referenced.

Step 3 — Map the data types

Most conversions are obvious. The two that cause real production incidents are decimals and dates.

Money

A DECIMAL(10,2) exported to JSON and re-imported naively becomes a Double, and Doubles cannot represent 0.1 exactly. Totals then drift by fractions of a cent, which surfaces months later in a reconciliation report. Use NumberDecimal explicitly, or store integer cents.

Dates

MongoDB stores dates as UTC milliseconds since the epoch. MySQL DATETIME carries no timezone, and TIMESTAMP is converted using the session timezone. Decide what the stored values actually mean before exporting, and convert once, at export time — not later, in application code, one caller at a time.

NULL versus missing

In MySQL a column is always present and may hold NULL. In MongoDB a field may hold null or may not exist at all, and those are different states. It matters when you query: { deleted_at: null } matches documents where the field is null and documents where it is absent, while { deleted_at: { $exists: false } } matches only the second group. Pick one convention — usually "omit the field when there is no value" — and enforce it at import, or your queries will quietly return different result sets depending on which import batch a document came from.

Step 4 — Move the data

Three approaches, in increasing order of control.

MongoDB Relational Migrator

MongoDB's own tool, free, with connectors for MySQL, PostgreSQL, SQL Server, Oracle, Sybase and DB2. You map the relational schema to a document schema visually, then run the migration — snapshot, or continuous with change data capture so you can cut over with little downtime. It also converts SQL queries and stored procedures using generative AI.

This is the right default for a schema of any size. Its weakness is that the mapping UI encourages a table-to-collection default, so it will happily reproduce your normalised model unless you actively design against it.

Export and import

For a handful of tables, SELECT ... INTO OUTFILE plus mongoimport is quick and fully predictable:

The --columnsHaveTypes flag is what saves you: without it every column lands as a string and you spend the next day writing repair scripts.

A custom script

When the target document shape differs substantially from the source tables — which, if you did Step 2 properly, it does — a script is often the shortest path. Read the parent rows in batches, fetch their children, assemble the document, and write with insertMany() using an unordered bulk write so one bad record does not abort the batch.

Whichever route you take, migrate in this order: a sample of a few thousand documents first, verify counts and spot-check values, index, then run the full load. Building indexes after the bulk load is significantly faster than maintaining them during it.

Step 5 — Translate the queries

With the documents designed, the query layer is mostly mechanical:

Aggregations follow the same logic, with GROUP BY becoming a $group stage and HAVING a second $match placed after it. You can paste any of these into the converter on the home page to see the mapping clause by clause.

Three things do not translate mechanically:

• JOINs. If Step 2 went well, most of them no longer exist because the data is embedded. What remains becomes a $lookup stage, which is genuinely slower than a relational join — it is a tool for reporting queries, not for your hot path.
• Transactions. MongoDB supports multi-document ACID transactions, but a single-document write is already atomic. If your design needs multi-document transactions for ordinary operations, that is usually a signal the documents were drawn along the wrong boundaries.
• Constraints. NOT NULL, CHECK and ENUM have no equivalent in the query language. Recreate them with JSON schema validation on the collection. Do not skip this because "the application validates it" — a migration script or an ad-hoc shell command will eventually write around the application.

Indexes

Port every index you had, then reconsider it against your document design. Compound index field order follows the same equality-sort-range principle as MySQL's leftmost-prefix rule: fields matched by equality first, then the field you sort on, then range conditions. Embedded arrays index transparently — an index on items.product._id works on the orders document above without any special syntax.

The five mistakes that cost the most

1. Copying the schema table by table. You inherit every join, lose the constraints that made them safe, and conclude MongoDB is slow. It is the single most common failed migration.
2. Embedding something unbounded. Comments on a post, events on a device, messages in a thread. It works in testing and hits the 16 MB wall in production, at which point the fix is another migration.
3. Money as a Double. Silent, cumulative, and discovered by the finance team rather than by you.
4. No schema validation. "Flexible schema" is read as "no schema", and eighteen months later one collection holds four different document shapes with no record of why.
5. Migrating everything at once. Move one bounded context, run it in production, learn, then move the next. A big-bang cutover across a whole application removes your ability to roll back.

A useful sanity check before you commit: if, after designing the document model, your application still needs a $lookup on its busiest query path, the model is probably still relational. Go back to Step 2.

Frequently asked questions