Migrating MongoDB to SQL (and Back) Without Losing Your Schema

SQL ↔ NoSQL is the migration everyone calls impossible and then does anyway. The two models genuinely disagree — documents nest, rows don't; collections are schemaless, tables aren't — but the translation is mechanical if you're honest about the edge cases.

Inferring a schema that documents never promised

Going MongoDB → SQL, DBShifts samples each collection and infers per-field types: ints become BIGINT (a document that stored 42 today may store 9 billion tomorrow), strings size into VARCHAR tiers by observed length, Decimal128 maps to exact DECIMAL — never float — and _id becomes the primary key.

What happens to nested documents?

Nested objects and arrays land as JSON columns (JSONB on PostgreSQL, JSON on MySQL, text elsewhere) — data preserved byte-for-byte, queryable on engines with JSON operators. Flattening into child tables is tempting but opinionated; we'd rather hand you intact documents than guess your relational model.

BSON's awkward citizens

• ObjectId → its 24-char hex string.
• Decimal128 → exact DECIMAL on SQL targets, never a float round-trip.
• Binary → BYTEA / VARBINARY / BLOB by target.
• BSON dates are UTC instants → timezone-aware timestamp columns.
• Ints beyond 8 bytes (from Oracle NUMBER sources, going the other way) → Decimal128, since BSON's int64 can't hold them.

SQL → MongoDB: the easy direction with one trap

Rows map to flat documents trivially. The trap is silent write failure: bulk inserts that swallow errors under "duplicate tolerance" settings can swallow encoding errors too — reporting success while writing nothing. Distinguish duplicate-key errors (tolerable, idempotent retries) from everything else (must surface). Then validate counts per collection, like any other pair.

Both directions are part of DBShifts's 49-pair live certification — MongoDB → each SQL engine and back, checked with the same counts-and-fidelity bar as the pure-SQL pairs.