Schema Mapping Guide for NoSQL to SQL Migration
Learn what schema mapping is and how it works when migrating from MongoDB, DynamoDB, or Firestore to SQL databases.
What is Schema Mapping?
Schema mapping is the process of converting a NoSQL database's flexible, schema-less structure into a SQL database's rigid, typed schema. It involves detecting field names and types, handling nested documents and arrays, mapping NoSQL-specific types to SQL equivalents, and establishing relationships and constraints.
The Challenge of Schema-Less Data
In MongoDB, a collection can contain documents with completely different fields. One document might have a 'name' field as a string, while another stores it as an object with 'first' and 'last' subfields. DynamoDB items in the same table can have different attributes. Firestore collections can have documents with arbitrary structures. Schema mapping needs to handle all of these variations gracefully.
Schema Discovery (Inference)
The first step is schema discovery: sampling documents across the collection/table to build a union schema — the set of all fields that appear anywhere, along with their types. NoSQLSync samples configurable percentages and detects: field names, data types for each field, whether a field is always present or sometimes missing (nullable), nested sub-documents and their structures, and array element types.
Type Mapping
Each NoSQL type must be mapped to the closest SQL equivalent: ObjectId → UUID or CHAR(24), ISODate → TIMESTAMPTZ, NumberInt → INTEGER, Double → DOUBLE PRECISION, Boolean → BOOLEAN, Array → JSONB or SQL ARRAY, Nested Object → JSONB or flattened columns, Null → NULL (nullable column). NoSQLSync provides smart defaults and lets you override any mapping.
Handling Nested Structures
Nested documents and arrays have no direct SQL equivalent — SQL tables are flat. There are three strategies: Flatten — promote nested fields to top-level columns with dot-notation names (address.city, address.zip), JSONB — store the entire nested structure as a JSONB column with indexing support, or Relational — create a separate table for the nested data with a foreign key back to the parent.
Visual Schema Mapper in NoSQLSync
NoSQLSync provides a visual schema mapper where you can: see every detected field and its inferred type, change column names, select a different target type for any field, flatten nested fields into top-level columns, exclude fields from migration, add custom SQL transformations, and preview the resulting CREATE TABLE statement before migrating.