Schema Mapping

Auto-detect fields, map BSON types to SQL, and customize every column.

Auto schema inference

When you create a migration, NoSQLSync samples up to 500 documents from your MongoDB collection and infers the schema. Each field is assigned a MongoDB type based on observed values. The resolver maps each type to the best SQL equivalent for your target dialect.

The introspection engine walks every nested field path and records: observed types, null fraction, and sample values. This powers the visual Field Mapping Table in the Schema Mapper.

Indexes on the target table

The _id field becomes the table's primary key, so it is indexed by that declaration. NoSQLSync additionally indexes the columns you are most likely to join on: fields holding an ObjectId other than _id, and string fields named like references (userId, user_id, authorID). At most 10 such indexes are created per table, since each one costs write throughput on the initial load. For a one-time migration they are built after the data lands, not before.

NoSQLSync does not create foreign key constraints. Collections migrate independently and in no particular order, so a real foreign key would reject rows whose parent row has not arrived yet and fail the load. The values are preserved exactly, so you can add the constraints yourself once every table is in place.

BSON → PostgreSQL type mapping

MongoDB TypePostgreSQL TypeNotes
ObjectIdVARCHAR(24)Stored as the 24-character hex string
StringTEXT / VARCHAR(n)Configurable max length
NumberIntINTEGER32-bit signed integer
NumberDoubleDOUBLE PRECISION64-bit floating point
ISODateTIMESTAMPTZFull timezone-aware timestamp
BooleanBOOLEANStandard true/false
ArrayJSONB / ARRAYTyped arrays or generic JSONB
Nested ObjectJSONBIndexed with dot-notation queries
BinaryBYTEAVariable-length binary data

BSON → BigQuery type mapping

MongoDB TypeBigQuery TypeNotes
ObjectIdSTRINGHex-encoded string
StringSTRINGUnlimited Unicode
NumberIntINT6464-bit integer
NumberDoubleFLOAT64IEEE 754 double
ISODateTIMESTAMPUTC microsecond precision
BooleanBOOLNative boolean
ArrayARRAY<T> / JSONTyped or flexible arrays
Nested ObjectSTRUCT / JSONStructured or flexible
BinaryBYTESBase64-encoded binary

Type mapping for all targets

NoSQLSync supports 8 SQL dialects with dialect-specific type mappings. The type mapper resolves every MongoDB type to the best native SQL type for your target:

  • PostgreSQL: VARCHAR(24), TEXT, INTEGER, TIMESTAMPTZ, JSONB, BYTEA
  • MySQL: BIGINT UNSIGNED, VARCHAR, INT, DATETIME(3), JSON, BLOB
  • BigQuery: STRING, INT64, FLOAT64, TIMESTAMP, STRUCT
  • Snowflake: VARCHAR, NUMBER, TIMESTAMP_NTZ, VARIANT, OBJECT, ARRAY
  • Redshift: VARCHAR, BIGINT, DOUBLE PRECISION, TIMESTAMP, SUPER
  • ClickHouse: String, Int32, Float64, DateTime64, JSON
  • SQL Server: VARCHAR, BIGINT, FLOAT, DATETIME2, NVARCHAR(MAX)
  • MariaDB: VARCHAR, INT, DOUBLE, DATETIME(3), LONGTEXT

Handling nested documents and arrays

MongoDB nested documents become JSONB (or equivalent) by default. The Schema Mapper offers three strategies:

  • JSONB: Keep nested structure intact — queryable with native JSON operators
  • Flattened: Extract specific fields from nested docs into top-level columns
  • Normalized: Create separate tables with foreign keys for one-to-many arrays

Example: { user: { name: "Alex", email: "[email protected]" } } becomes user_name TEXT, user_email TEXT (flattened) or user JSONB.

Column renaming and aliasing

In the Field Mapping Table, click any Target Column to rename it. MongoDB field names like created_at.$date are automatically sanitized to safe SQL names like created_at_date. You can override any auto-generated name.

Custom type overrides

Click the SQL Type column to select a different type from the dropdown. Mark fields as NOT NULL or primary key. The _id field from MongoDB is automatically set as the primary key. You can also export the schema as DDL (CREATE TABLE) for review.