Relational Migrator misses some BSON types

Aleix · 2024-04-03T21:24:26.778Z

Good afternoon,

I was using the Relational Migrator to move some data from PostgreSQL to MongoDB and I found a limitation regarding the supported types.

I want to use the same ID as I have in postgres for backward compatibility. It is a UUID

image3296×1591 338 KB

What I just found is that Relational Migrator forces me to:

Use ObjectID as _id.
If I customize the type with column["id"] it converts the ID into String.
The relationship answer_option_id is also converted into String.

I see in your docs that you don’t support other data types.

mongodb.com

Data Type Conversion Reference - Relational Migrator

Like:

mongodb.com

BSON Types - MongoDB Manual v7.0

MongoDB uses BSON (Binary JSON) field types to store and serialize documents.

Because I cannot set an explicit end data type, I cannot migrate the data using Relational Migrator as the use cases are quite limited.

I also tried to do the next using the custom JS expression for the ID.

new UUID(column["id"]) // or
new require('mongodb').UUID(column["id"])

Without luck. I just expected to have the common types available as part of the javascript expression. But this will only fix the ID, the rest of the columns still have the BSON type limitation.

So, either Relational Migrator:

Adds more supported types (_id allowing ObjectID or UUID at least)
Enables custom code on each property to select the right type manually

I think Relational Migrator has a good purpose but lacks enough customization to support common migrations in which we don’t want to lose the types that will incur into lose of performance or higher disk usage.

For now, we move to do the migration manually.

I hope next people can take advantage of more BSON types to stream-align their migrations in the future.

tomhollander · 2024-04-04T00:41:22.469Z

Hi @Aleix. sorry I wrote a response and then deleted it as I realised it wasn’t quite right. UUID is not an intrinsic BSON type, and converting to a string is a valid option. However another valid option is to represent it as a BSON BinData type with the UUID subtype. I couldn’t find a way to do this with calculated fields today; this is a good feature request which I’ll add to the backlog.

Also you are not actually restricted to using ObjectIDs for the _id value. If you go into Project Settings you can change to the Single Inherited Key strategy to use your original PK values as _id, or alternatively you can convert individual _id values to calculated fields.

Aleix · 2024-04-04T21:59:17.780Z

Thank you for the response @tomhollander .

That’s right, I’m using the UUID type exported as part of the bson package. This one in concrete.

Yes, I used the Single Inherited Key strategy but I had the problematic of using string instead of BinData. The reason is that I have already data with that schema and supporting both cases (BinData and String) won’t make sense.

I think that it will be super that Relational Migrator to support the different BSON types and then we are able to make the migration as specific as possible.

For now, I’m continuing the migration by making it manual (scripts) as the data volume isn’t that much. But I think it will be a nice product enhancement as I can imagine that any big migration (either volume size or table complexity) will require this feature.

Strange that this didn’t pop-up earlier tho