Relational to Non-Relational: Moving SQL Data into a Document Model

Moving from a relational database to a document store like MongoDB is the migration with the biggest conceptual gap. MySQL → PostgreSQL is a dialect translation; SQL → NoSQL is a change of data model. The mechanical part — rows become documents — is the easy 20%. The other 80% is deciding what happens to everything relational databases gave you for free.

Rows map to documents. Relationships don't.

A flat table converts trivially: each row becomes a document, columns become fields, the primary key becomes _id. The real question is what to do with foreign keys. Document databases give you two options, and the right one depends on how the data is read:

• Embed — child rows become nested arrays inside the parent document. Right when children are always read with the parent (order → line items), bounded in count, and not shared between parents. One read replaces a JOIN.
• Reference — keep children as their own collection holding the parent's _id, JOIN-like behavior via a second query or $lookup. Right for many-to-many, unbounded growth (a user's events), or data shared across parents.

A safe default for a first migration: reference everything, mirroring the relational shape one-to-one, then embed selectively after you've watched real query patterns. Embedding too early bakes assumptions into your documents that are painful to undo — un-embedding requires rewriting every document.

What you lose, and what replaces it

• Enforced foreign keys — gone. Referential integrity becomes the application's job (or scheduled integrity checks). Decide who owns deletes.
• Multi-row transactions — MongoDB has them, but they're not the default idiom. Embedding is the document-native answer: data that must change atomically belongs in one document.
• Schema enforcement — optional now. Without it, every writer can invent fields. Schema validation rules (or app-level validation) keep collections from drifting into chaos.
• JOIN-heavy reporting — aggregation pipelines can do it, but if analytics is the workload, consider keeping a relational replica for reporting and migrating only the operational path.

Type translation that doesn't corrupt

Going SQL → document, the types that need explicit decisions: exact decimals must become Decimal128 — never floats, or money silently rounds; dates become UTC instants (BSON dates carry no timezone, so normalize before writing); ENUM and SET become strings or arrays; binary columns become BSON Binary. Integers wider than 8 bytes (Oracle NUMBER territory) don't fit BSON's int64 and need Decimal128 too.

And the reverse direction

Document → relational is the mirror problem: a schema has to be inferred from data that never promised one. Sample documents, take the widest type observed per field, treat null as "says nothing about type", land nested objects and arrays in JSON columns, and surface every inference in a migration plan a human can override. We wrote up the details in Migrating MongoDB to SQL (and Back).

A sane migration sequence

• Migrate the relational shape as-is (collections mirror tables, references mirror FKs).
• Validate counts and spot-check field fidelity — especially decimals and dates.
• Run the application against it; watch which queries chase references.
• Embed the hot paths deliberately, one relationship at a time.
• Add schema validation rules once the document shapes stabilize.

The migrations that fail are the ones that try to redesign the data model and move the data in the same step. Separate the two and each becomes routine.