CODEWITHSUNDEEP
postgresqlelixirecto
Kaquadu
Kaquadu

Reputation: 21

Migration from numeric ID to UUID

I'm looking for any tips and suggestions when it comes to migrating from the numeric ID's to the UUID's in Ecto.

I'm having a project with a high amount of relations and I can't simply drop db and recreate it with UUID's, which seems like an easiest solution, because I have to keep the data.

My idea was:

  1. Add :uuid, :uuid field to a table

  2. Fill the :uuid column with a script and Ecto.UUID.generate()

  3. Modify table to (:some_table, primary_key: false) and field to: :uuid, :uuid, primary_key: true and remove the :id field.

  4. Rename the :uuid field to :id

Although it looked perfect in my mind I of course forgot about references. Is there any way I can handle references easily with a such migration from numeric ID's to UUID's? The only idea that comes to my mind is to create additional table, which will contain old ID and new UUID between 2nd and 3rd step, then remove old references and add new ones basing on the created table. I will appreciate any suggestions :)

Upvotes: 1

Views: 1992

Answers (1)

Aleksei Matiushkin
Aleksei Matiushkin

Reputation: 121010

If you have an option to put DB into read-only state from outside for a while, it should not be too hard. I am not sure it’d work properly with new records arising during the update, though.

  1. Drop all the foreign key constraints
  2. Add :uuid, :uuid field to a table
  3. Modify table to be primary_key: false
  4. Put the trigger on UPDATE updating all the referring tables’ fk values to the inserted value
  5. Fill the :uuid column with a DB procedure like gen_uuid()
  6. Make :uuid to be a primary key (and remove :id)
  7. Rename the :uuid field to :id

∞. Put all the foreign keys back in place.

The whole thing should be better done with pure SQL (save for schema update.)

Upvotes: 0

Related Questions