Migrating from SQL

Requires Pro (MigrateData). See Plans and Pro features.

Moving a relational database into MongoDB, with a mapping you can read and correct before anything is written.

#Which sources are supported

SQLite is the only supported source.

The four unsupported sources are not "coming soon" and have no dates. They are implementations of an interface that exists, which is a statement about the code and not a promise about a release.

#Why a table-per-collection copy is not a migration

Copying each table into a collection of the same name produces a MongoDB database that is worse than the one you started with: every query becomes a $lookup, and the conclusion people draw is that MongoDB was the problem.

So TableCore reads your foreign keys and your row counts, and for each relationship it proposes one of three answers: embed it, reference it, or do not express the relationship at all.

#The plan is data

The mapping plan serialises to JSON and back. You can review it, save it, correct it and re-run it without the database in front of you — and the wizard edits exactly that object. The same schema proposes the same plan every time; the only thing that changes it is your decision.

Every decision carries the reason it was made and the number it came from, so a proposal you disagree with is a proposal you can correct.

#How relationships are decided

In this order:

#Identity

• A single primary key of a type that compares exactly becomes _id.
• A composite key — or a type that cannot be an _id — stays as fields and gets a unique index, because _id is one value.
• A table with no key gets a generated ObjectId.
• An embedded document loses its own identity; its parent's _id addresses it.
• A double primary key does not become _id: two source values differing in a digit double cannot hold would become one document.

#Types

• Exact decimal becomes Decimal128, never double. Money that stops adding up to its own line items is precisely the failure this avoids.
• A native UUID becomes binary subtype 4, never text. Text cannot be indexed as the same value the source indexed.
• Integers become Int32 up to nine digits of declared precision, Int64 above that. A value that does not fit is not a rounding error, it is a different number.
• BLOB/bytea/varbinary become Binary; JSON/JSONB become a document; boolean becomes Boolean.
• Time of day has no BSON equivalent and travels as text (hh:mm:ss.fffffff, culture-independent), because as a Date it would gain a day nobody chose. It is flagged for review, as is any type no source classified.
• A timestamp without a time zone is SQL's one ambiguity that MongoDB does not have. An explicit policy says whether to read it as UTC (the default) or at a given offset. A date with no time is midnight at the same offset, so a date and a timestamp from one row do not drift apart by hours. A timestamp carrying its own zone ignores the option.

#NULL, and the absence of a field

SQL has one kind of absence and MongoDB has two, and the choice changes the meaning of every future query: { field: null } matches both, and { field: { $exists: false } } matches only the missing one.

This is an explicit migration option with a stated default (skip the field), not an implementation detail.

#Names

Names do not change by themselves. The convention — preserve, camelCase or snake_case — is your choice, separately for fields and for collections, and Preserve hands the name back untouched including its punctuation. Applying a convention twice gives the same result as applying it once, so re-running a plan does not rename things in circles.

#What the transfer does

• One batch in flight, never a table. The tables worth migrating are exactly the ones that do not fit in memory.
• Embedding is a merge join, not a query per parent. The child table is read once, ordered by its foreign key, and the two ordered streams are merged. The obvious implementation — asking the child table for each parent's children — turns a ten-minute migration into an overnight one.
• Because of that, the parent has to arrive in the order the child's foreign key sorts in. Ordered any other way, every child would look like an orphan and every parent childless with no error at all — so it is a refusal, not a warning.
• An orphan is counted, not lost. A child whose parent is not in the parent table goes nowhere, and the difference between a migration that lost rows and one that says which rows is the whole point.
• A parent with no children gets an empty array, not a missing field: a missing field does not match { field: { $size: 0 } }, so the two spellings mean different things to every future query.
• An embedded child does not repeat the foreign key that put it there — that would be the parent's _id in every array element. A dissolved junction table becomes an array of the other side's keys, not of pair documents.
• The target is not silently cleared or silently appended to. A collection that already holds something stops the run before anything is written; consenting is an explicit option. All refusals are decided before the first write, because a run interrupted half way through the first table after you changed the target is the state nobody can reason about.
• Duplicate keys are reported, not swallowed. Writes go unordered — with ordered writes one rejected document would abandon the rest of the batch and the report would understate what was actually written. An error that is not a duplicate key (document too large, disk full) is raised rather than reported as a skipped row.
• A value that cannot be converted arrives as what it is, and is counted. Nothing the migration read is allowed to fail to arrive silently: a counter saying the mapping was optimistic somewhere is fixable, a missing value is not.
• Indexes are created after the data, and only after a run that completed in full. An index that could not be translated is named rather than disappearing — an index that vanished quietly is a query that gets slow six months later with nothing to point at. A foreign key left as a reference gets an index: without it, the join the application now does itself is a collection scan, which is the shape of migration everybody blames MongoDB for.
• Progress counts rows, not tables. Tables differ by four orders of magnitude.

#Verify before you delete anything

Verification is a button, not something that happens automatically: it counts every table exactly, which on a large source is real work.

• The arithmetic differs per mapping kind, and that is most of the work. A table mapped to a collection is a document count; an embedded table is the sum of array lengths in the parent collection; a dissolved junction table is the elements of the key array; an excluded table is not checked, with the reason given. Comparing an embedded table against a document count would report every one of its rows as lost.
• The server does the summing, with an aggregation, because a collection worth migrating does not fit in memory.
• "Not checked" is part of the report, not its absence. A verification that quietly skips a table reads as passed, and the next thing after passed is deleting the database it was about. A verified result requires both nothing differs and nothing was skipped.
• Rows the run itself said it did not place — an orphan, a key already present in the target — are subtracted before the comparison, so the report does not say the same thing twice.
• The sample check compares a document the same mapping would have built, field by field and type by type. Type is half of it: a Decimal128 stored as a Double reads as the same number and is not the same value. The sample is the first rows in key order, and the report says so rather than implying a spread.
• References are checked against the values in the built documents, not the raw rows. BsonInt32(1) and BsonInt64(1) are not equal, so checking with the source's type would report every reference as dangling.
• The source is read as it currently is. If it is still taking writes, the numbers will differ for reasons that are not defects — the panel says so, and recommends migrating from a quiesced copy.

#A working order

1. Take a quiesced copy of the source, or accept that the counts will move.
2. Connect the source in the wizard and read the proposed mapping.
3. Correct it. Look hardest at the embed/reference decisions and at anything flagged for review.
4. Save the plan.
5. Run it into an empty target database.
6. Press Verify. Read the not checked rows as carefully as the differences.
7. Only then point anything at the new database.