ObjectId to UUID Conversion Guide
What happens to MongoDB ObjectIds when you migrate to a SQL database — how NoSQLSync stores them, when a UUID conversion makes sense, and how to keep references and sort order intact.
ObjectId Is Not a SQL Type
MongoDB's ObjectId is a 12-byte BSON type, and no SQL database has an equivalent. You have two reasonable options on the way into SQL: keep the ObjectId as its 24-character hex string, or convert it to a UUID, the 16-byte identifier PostgreSQL, MySQL, and SQL Server all support natively. Each has real trade-offs, covered below.
ObjectId Structure
A MongoDB ObjectId consists of: 4 bytes — Unix timestamp (seconds since epoch), 5 bytes — random value (unique per process), and 3 bytes — incrementing counter. This structure makes ObjectIds roughly sortable by creation time, which is useful for chronological queries without an explicit createdAt field.
What NoSQLSync Does
NoSQLSync stores an ObjectId as its 24-character hex string — VARCHAR(24) on PostgreSQL, MySQL, Snowflake, Redshift and MariaDB, NVARCHAR(24) on SQL Server, STRING on BigQuery and ClickHouse. The _id field becomes the table's primary key. Keeping the original value has a practical advantage: an ObjectId stored as a reference in another collection still matches the _id it points at, with no remapping step to get wrong.
If You Do Want UUIDs
A deterministic conversion is the usual approach: zero-pad the 12 bytes to 16 and format as a UUID, which is reversible — you can recover the original ObjectId from the UUID. The catch is that every ObjectId reference in every other collection has to be converted the same way, or your references stop matching. Do this in SQL after the tables land, where you can run it in one transaction and verify it, rather than partway through a migration.
References and Sort Order
Because ObjectIds embed a 4-byte timestamp, they sort approximately by creation time — a property random UUIDv4 values do not have. If you rely on that ordering, extract the timestamp into a created_at column and sort on it explicitly. On references: NoSQLSync migrates each collection to its own table and does not create foreign key constraints for you. Since the hex values are preserved unchanged, you can add the constraints yourself in SQL once the tables exist.