Moving from numeric ID to UUID in existing app

Why you should migrate to UUIDs? #

In terms of your project growth, it would be a good idea to migrate from numeric IDs to UUIDs (Universally Unique IDentifier) in your application, however, you should take into account some edge cases as well. If you read this article you probably already made a decision about migration, but let me briefly remind you of some pros and cons of such refactoring.

Pros #

• globally unique IDs
• IDs can be generated autonomously without checking against the central node

Cons #

• UUIDs take up more space than numeric (128 bit)
• UUIDs are slower in terms of joining tables in the database

Generally, If you don’t have strong memory restrictions or strict performance requirements - moving to UUIDs will be a good choice. It reduces the chances of future ID collisions and facilitates building a distributed system.

Let’s migrate! #

In this article, we’ll concentrate on moving to UUID on the server side, more notably we’ll use Ruby on Rails application for considering such migration. Despite that, the migration steps that I’ll talk about in this article are common for most backend systems.

Scenario #

Assume we have a simple Rails application where users can create text posts, react to them, and follow other users. Despite the small size of the app we decided to move to UUID.

There is an initial database schema of our app

Before we start #

Before starting the migration, I recommend paying attention to some preparations that can make life after refactoring painless.

Timestamps for sorting purposes #

As you can see, all tables in our example have timestamps. If you don’t have them in tables that you want to migrate to UUID, perhaps, you should add them: after migration, it possibly will be the only option to sort records to see the newest one.

Use UUID by default #

If you want all your future models to use UUID for primary keys by default you need to add and run the following initializer:

# config/initializers/generators.rb

Rails.application.config.generators do |g|
  g.orm :active_record, primary_key_type: :uuid
end

Migration process #

The migration process can be split into the following steps:

• Step 0: Enable UUID in your DB
• Step 1: Investigate your DB architecture
• Step 2: Create UUID columns for PKs
• Step 3: Update PKs for tables with “belongs to” relations only
• Step 4: Create UUID columns for FKs
• Step 5: Update PKs and FKs in all tables

Let’s consider steps further. For each of them I’ll give an example of code that might help you to migrate your app to UUIDs.

Step 0: Enable UUID in your DB #

Make sure that your database supports UUID type and that it has a tool to generate them. For instance, to generate random UUID in PostgreSQL you need to enable the pgcrypto. I did it through add hoc migration

class EnableUuid < ActiveRecord::Migration[7.0]
  def change
    enable_extension 'pgcrypto'
  end
end

Step 1: Investigate your DB architecture #

This step may sound ridiculous for such simple architecture as we have in our example, but your database can be much more complicated, so I recommend investigating tables and relations between them to have a clear picture.

Perhaps, you even will make some notes. For example, it would be useful for future steps to know which tables have “belongs to” relations only.

Step 2: Create UUID columns for PKs #

Let’s start with adding uuid column to each table, which will become the PK (primary key) in the future. Note, that these fields should be auto-generated and have to be generated for all existing records in the table. Also, we should add the rules for these columns, that they must be unique and not null.

class AddUuidToAllTables < ActiveRecord::Migration[7.0]
  TABLES = [:users, :posts, :reactions, :followings]

  def up
    TABLES.each do |table|
      add_column table, :uuid, :uuid, default: "gen_random_uuid()", null: false
      add_index table, :uuid, unique: true
    end
  end

  def down
    TABLES.each do |table|
      remove_column table, :uuid
    end
  end
end

Note, that I wrote something like a dictionary with all my tables to avoid rewriting the same lines of code for each table. Again, it can look unnecessary for only four data tables, but if you have fourteen - it’ll already be more useful.

I’ll use such an approach for the subsequent migrations too. While they will become more complicated, dictionaries will also become more complex.

Step 3: Update PKs for tables with belongs to relations only #

For tables with belongs to relations only, migration will be easier because we do not have to take into account places where the IDs of these tables were used as foreign keys. So let’s start with them to simplify more complex cases that we’ll have with other tables.

In the example database we have two such tables: followings and reactions.

Steps to migrate from numeric ID PK to UUID: #

1. Rename id column to numeric_id
2. Rename uuid column to id
3. Drop the existing primary key
4. Create a new primary key with id column

class ChangePkInTablesWithBelongsToRelationsOnly < ActiveRecord::Migration[7.0]
  TABLES = [:followings, :reactions]

  def up
    TABLES.each do |table|
      rename_column table, :id, :numeric_id
      rename_column table, :uuid, :id
      change_pk(table)
    end
  end

  def down
    TABLES.each do |table|
      rename_column table, :id, :uuid
      rename_column table, :numeric_id, :id
      change_pk(table)
    end
  end

  def change_pk(table)
    execute "ALTER TABLE #{table} DROP CONSTRAINT #{table}_pkey;"
    execute "ALTER TABLE #{table} ADD PRIMARY KEY (id);"
  end
end

Note, that we did not delete columns with numeric IDs. It is not required for tables with “belongs to” relations only, but might be helpful for rollback.

Step 4: Create UUID columns for FKs #

In this step, we are adding a new column for each FK (foreign key) that will inherit the name of this FK. For instance, we can create column “user_uuid” for “user_id” FK.

class AddColumnsForUuidFk < ActiveRecord::Migration[7.0]
  TABLES_WITH_FK = {
    posts: [:user_uuid],
    reactions: [:user_uuid, :post_uuid],
    followings: [:following_uuid, :follower_uuid]
  }

  def up
    TABLES_WITH_FK.each do |table, fk_names|
      fk_names.each do |fk_name|
        add_column table, fk_name, :uuid
      end
    end
  end

  def down
    TABLES_WITH_FK.each do |table, fk_names|
      fk_names.each do |fk_name|
        remove_column table, fk_name
      end
    end
  end
end

Step 5: Update PKs and FKs in all tables #

That’s our final and most complex step, which comprised of:

1. Remove relations between tables
2. Migrate to UUID PKs in remaining tables
3. Migrate to UUID FKs in all places
4. Fill newly created FKs by matching them with old numeric FKs

We’ll accomplish this step through two separate migrations. They have a lot of custom logic that depends on DB architecture, so you probably will need to write your own, but I hope the examples below will give you some fresh ideas.

Before we dive deeper into code, let me list the migration steps for FKs: #

1. Allow null values for the current FK column (if it wasn’t)
2. Rename current FK column to numeric_{FK name}
3. Rename the UUID FK column (created during the previous step) as you need for FK
4. Add a relation between tables using the UUID FK column

The first migration will do the first three substeps:

class MoveToUuidInAllTables < ActiveRecord::Migration[7.0]
  TABLES_WITH_RELATIONS = {
    users: {
      posts: {user_id: :user_uuid},
      reactions: {user_id: :user_uuid},
      followings: {following_id: :following_uuid, follower_id: :follower_uuid}
    },
    posts: {
      reactions: {post_id: :post_uuid}
    }
  }

  def up
    TABLES_WITH_RELATIONS.each do |table, related_tables|
      # remove relations
      remove_foreign_keys(related_tables)

      # migrate to UUID PKs
      rename_column table, :id, :numeric_id
      rename_column table, :uuid, :id
      change_pk(table)

      # migrate to UUID FKs
      related_tables.each do |related_table, fks|
        fks.each do |fk_name, uuid_fk_name|
          numeric_fk_name = "numeric_#{fk_name}".to_sym
          change_column_null related_table, fk_name, true
          rename_column related_table, fk_name, numeric_fk_name
          rename_column related_table, uuid_fk_name, fk_name
          add_foreign_key related_table, table, column: fk_name
        end
      end
    end
  end

  def down
    TABLES_WITH_RELATIONS.each do |table, related_tables|
      # remove relations
      remove_foreign_keys(related_tables)

      # rollback to numeric PKs
      rename_column table, :id, :uuid
      rename_column table, :numeric_id, :id
      change_pk(table)

      # rollback to numeric FKs
      related_tables.each do |related_table, fks|
        fks.each do |fk_name, uuid_fk_name|
          numeric_fk_name = "numeric_#{fk_name}".to_sym
          rename_column related_table, fk_name, uuid_fk_name
          rename_column related_table, numeric_fk_name, fk_name
          add_foreign_key related_table, table, column: fk_name
          change_column_null related_table, fk_name, false
        end
      end
    end
  end

  def remove_foreign_keys(tables)
    tables.each do |table, fks|
      fks.each_key do |fk_name|
        remove_foreign_key table, column: fk_name
      end
    end
  end

  def change_pk(table)
    execute "ALTER TABLE #{table} DROP CONSTRAINT #{table}_pkey;"
    execute "ALTER TABLE #{table} ADD PRIMARY KEY (id);"
  end
end

Note, that null values are still allowed for our FKs columns. That’s because they are empty now and we will add a not null constraint only after filling these columns with IDs.

The second migration will fill newly created FKs with values and will add not null constraint to each of them after this. To fill FKs we have to find records in the table of a related entity (IDs of which records have to be stored in FK) by old numeric FK and old numeric PK of this table, then get the actual ID from this table and assign to new FK. Sounds tricky, but maybe the code of migration will be a better explanation.

class FillUuidFks < ActiveRecord::Migration[7.0]
  ENTITIES_WITH_RELATIONS = {
    {model: Post, table: :posts} => {User => [:user_id]},
    {model: Reaction, table: :reactions} => {User => [:user_id], Post => [:post_id]},
    {model: Following, table: :followings} => {User => [:following_id, :follower_id]},
  }

  def up
    ENTITIES_WITH_RELATIONS.each do |entity, related_models|
      entity[:model].all.each do |record|
        related_models.each do |related_model, fks|
          fks.each do |fk_name|
            numeric_fk_name = "numeric_#{fk_name}".to_sym
            record[fk_name] = related_model.find_by(numeric_id: record[numeric_fk_name]).id
          end
        end
        record.save!
      end
      related_models.each_value do |fks|
        fks.each do |fk_name|
          change_column_null entity[:table], fk_name, false
        end
      end
    end
  end

  def down
    ENTITIES_WITH_RELATIONS.each do |entity, related_models|
      entity[:model].all.each do |record|
        related_models.each do |related_model, fks|
          fks.each do |fk_name|
            numeric_fk_name = "numeric_#{fk_name}".to_sym
            record[numeric_fk_name] = related_model.find_by(id: record[fk_name]).numeric_id
          end
        end
        record.save!
      end
      related_models.each_value do |fks|
        fks.each do |fk_name|
          change_column_null entity[:table], fk_name, true
        end
      end
    end
  end
end

Thats it, the database now uses UUID for PKs and FKs in all tables!

Summary #

As I already noticed, migration code is a very individual thing and you probably will need to write your own, but I believe at least these common steps will help you during migration.

I assume, if you have a complicated enough database - the migration process possibly will be a burden for a long time, but you can mitigate the pain using step by step approach and a good unit test base to avoid unexpected issues.

You can discover the migration process for the example database step by step once again with the source code of the example project.