Migrate from numeric ID to UUID

Currently I have my ecto-schemas using the default for primary keys, I do assume its just an auto incremented numeric ID. And I want to switch over to UUID.

Everythin I was able to find, talks about creating table and schema from scratch using UUID, but there is no-one explaining how to get from one to the other. I’m at a stage were I still could drop and recreate the table, but I’d rather prefer to have a migration getting the job done.

3 Likes

This probably won’t be easy.

Steps I think you’ll need to take

  1. Add uuid column to each table of type :binary_id
  2. Add <foreign_key>_uuid column to each table of type :binary_id
  3. Generate ids using Ecto.UUID.generate
  4. Write and run scripts to map <foreign_key>_id to <foreign_key>_uuid values
  5. Drop id and <foreign_key>_id columns
  6. Rename uuid and <foreign_key>_uuid to id and <foriegn_key>_id respectively
  7. Add @primary_key {:id, :binary_id, autogenerate: true} and @foreign_key_type :binary_id to your schema definitions (or to web.ex)
  8. Manually set foreign key constraints on your tables

This is a lot of work and if you can’t do all of this in one series of moves during down time, you’d need to manually handle the generation of all your uuid values while Ecto is still generating integer keys.

3 Likes

OK, since there is this nasty step which needs to remember which Integer-ID has been replaced by which UUID, I do think it is in fact easier to just drop and recreate.

Currently I do only have a few entries of seed- and test-data, not a single bit of live or grown data. So I think in fact, just dropping, recreating and reseeding the database will be the easiest thing to do. At least I realized before going live :wink:

Dropping and recreating would be far be easier yes. ^.^

However, you could always make a new UUID table pointing to the old integer ID’s, therefore the integer ID’s are used everywhere but the UUID is what becomes unique for a user, a simple work-around, not entirely ‘safe’ (though fine if done properly), but much easier, well, unless you are expecting more than a few billion ID’s (if the database does not default it to 64-bits that is…)