Rails 4. Migrate table id to UUID

Question

I have a table: db/migrate/20140731201801_create_voc_brands.rb:

class CreateVocBrands < ActiveRecord::Migration
  def change
    create_table :voc_brands do |t|
      t.string :name

      t.timestamps
    end
  end
end

But I need to change table to this(if I would create it from zero):

class CreateVocBrands < ActiveRecord::Migration
  def change
    create_table :voc_brands, :id => false do |t|
      t.uuid :id, :primary_key => true
      t.string :name

      t.timestamps
    end
    add_index :voc_brands, :id
  end
end

How can I change this using migration?

Answer 1

I had the same problem as yours. To migrate from default id to use uuid, I think you could something similar to what I had:

class ChangeVocBrandsPrimaryKey < ActiveRecord::Migration
  def change
    add_column :voc_brands, :uuid, :uuid, default: "uuid_generate_v4()", null: false

    change_table :voc_brands do |t|
      t.remove :id
      t.rename :uuid, :id
    end
    execute "ALTER TABLE voc_brands ADD PRIMARY KEY (id);"
  end
end

Answer 2

I know migrations are preferred way to made any db change but below approach is awesome. It is possible to use direct queries to PostgreSQL to convert table with existing data.

For primary key:

    ALTER TABLE students
        ALTER COLUMN id DROP DEFAULT,
        ALTER COLUMN id SET DATA TYPE UUID USING (uuid(lpad(replace(text(id),'-',''), 32, '0'))),
        ALTER COLUMN id SET DEFAULT uuid_generate_v4()

For other references:

    ALTER TABLE students
        ALTER COLUMN city_id SET DATA TYPE UUID USING (uuid(lpad(replace(text(city_id),'-',''), 32, '0')))

The above left pads the integer value with zeros and converts to a UUID. This approach does not require id mapping and if needed old id could be retrieved.

As there is no data copying, this approach works quite fast.

To handle these and more complicated case of polymorphic associations please use https://github.com/kreatio-sw/webdack-uuid_migration. This gem adds additional helpers to ActiveRecord::Migration to ease these migrations.

Answer 3

I know this doesn't directly answer the question, but I created a rake task that can help convert any project from id to uuid https://gist.github.com/kuczmama/152d762177968f7192df1dea184e3370

task id_to_uuid: :environment do
  puts "[START] Convert id to uuid"
  ActiveRecord::Base.connection.enable_extension 'uuid-ossp' unless ActiveRecord::Base.connection.extensions.include? 'uuid-ossp'
  ActiveRecord::Base.connection.enable_extension 'pgcrypto' unless ActiveRecord::Base.connection.extensions.include? 'pgcrypto'

  table_names = ActiveRecord::Base.connection.tables - ["schema_migrations", "ar_internal_metadata", "migration_validators"]
  table_names.each do |table_name|
    puts "[CREATE] uuid column for #{table_name}"

    #Make sure the column is a uuid if not delete it and then create it
    if ActiveRecord::Migration.column_exists? table_name, :uuid
      column_type = ActiveRecord::Migration.columns(table_name).select{|c| c.name == "uuid"}.try(:first).try(:sql_type_metadata).try(:type)
      if column_type && column_type != :uuid
        ActiveRecord::Migration.remove_column(table_name, :uuid)
      end
    end

    # Create it if it doesn't exist
    if !ActiveRecord::Migration.column_exists? table_name, :uuid
      ActiveRecord::Migration.add_column table_name, :uuid, :uuid, default: "uuid_generate_v4()", null: false
    end

  end

  # The strategy here has three steps.
  # For each association:
  # 1) write the association's uuid to a temporary foreign key _uuid column,
  # 2) For each association set the value of the _uuid column
  # 3) remove the _id column and
  # 4) rename the _uuid column to _id, effectively migrating our foreign keys to UUIDs while sticking with the _id convention.
  table_names.each do |table_name|
    puts "[UPDATE] change id to uuid #{table_name}"
    model = table_name.singularize.camelize.constantize
    id_columns = model.column_names.select{|c| c.end_with?("_id")}


    # write the association's uuid to a temporary foreign key _uuid column
    # eg. Message.room_id => Message.room_uuid
    model.reflections.each do|k, v|
      begin
        association_id_col = v.foreign_key
        # Error checking
        # Make sure the relationship actually currently exists
        next unless id_columns.include?(association_id_col)
        # Check that there is at

        # 1) Create temporary _uuid column set to nulll,
        tmp_uuid_column_name = column_name_to_uuid(association_id_col)
        unless ActiveRecord::Migration.column_exists?(table_name, tmp_uuid_column_name)
          puts "[CREATE] #{table_name}.#{tmp_uuid_column_name}"
          ActiveRecord::Migration.add_column(table_name, tmp_uuid_column_name, :uuid)
        end

        # 2) For each association set the value of the _uuid column
        #
        # For example.  Assume the following example
        #
        # message.room_id = 1
        # room = Room.find(1)
        # room.uuid = 0x123
        # message.room_uuid = 0x123
        #
        association_klass = v.klass

        model.unscoped.all.each do |inst|
          next unless inst.present?
          association = association_klass.find_by(id: inst.try(association_id_col.try(:to_sym)))
          next unless association.present?
          inst.update_column(tmp_uuid_column_name, association.try(:uuid))
        end

        # 3) Remove id column
        ActiveRecord::Migration.remove_column table_name, association_id_col if ActiveRecord::Migration.column_exists?(table_name, association_id_col)

        # 4) Rename uuid_col_name to id
        ActiveRecord::Migration.rename_column table_name, tmp_uuid_column_name, association_id_col
      rescue => e
        puts "Error: #{e} continuing"
        next
      end
    end

    # Make each temp _uuid column linked up
    # eg. Message.find(1).room_uuid = Message.find(1).room.uuid
    puts "[UPDATE] #{model}.uuid to association uuid"
  end

  ## Migrate primary keys to uuids
  table_names.each do |table_name|
    if ActiveRecord::Migration.column_exists?(table_name, :id) && ActiveRecord::Migration.column_exists?(table_name, :uuid)
      ActiveRecord::Base.connection.execute %Q{ALTER TABLE #{table_name} DROP CONSTRAINT #{table_name}_pkey CASCADE} rescue nil
      ActiveRecord::Migration.remove_column(table_name, :id)
      ActiveRecord::Migration.rename_column( table_name, :uuid, :id) if ActiveRecord::Migration.column_exists?(table_name, :uuid)
      ActiveRecord::Base.connection.execute "ALTER TABLE #{table_name} ADD PRIMARY KEY (id)"
      ActiveRecord::Base.connection.execute %Q{DROP SEQUENCE IF EXISTS #{table_name}_id_seq CASCADE} rescue nil
    end
  end
end

# Add uuid to the id
# EG. column_name_to_uuid("room_id") => "room_uuid"
# EG. column_name_to_uuid("room_ids") => "room_uuids"
def column_name_to_uuid(column_name)
  *a, b = column_name.split("_id", -1)
  a.join("_id") + "_uuid" + b
end

Answer 4

If anyone is coming here looking for how to convert from UUID to Integer ID's you can use the following migration:

class ChangeUuidToInteger < ActiveRecord::Migration::Current
  def change
    ### LOAD ALL MODELS for `.subclasses` method
    Dir.glob(Rails.root.join("app/models/*.rb")).each{|f| require(f) }

    id_map = {}

    ApplicationRecord.subclasses.each do |outer_klass|
      outer_klass.reset_column_information

      if outer_klass.column_for_attribute(outer_klass.primary_key).type == :uuid
        case outer_klass.connection.adapter_name
        when "Mysql2"
          execute "ALTER TABLE #{outer_klass.table_name} DROP PRIMARY KEY;"
        else
          result = outer_klass.connection.execute("
            SELECT ('ALTER TABLE ' || table_schema || '.' || table_name || ' DROP CONSTRAINT ' || constraint_name) as my_query
            FROM information_schema.table_constraints
            WHERE table_name = '#{outer_klass.table_name}' AND constraint_type = 'PRIMARY KEY';")
              
          sql_drop_constraint_command = result.values[0].first

          execute(sql_drop_constraint_command)
        end

        rename_column outer_klass.table_name, outer_klass.primary_key, "tmp_old_#{outer_klass.primary_key}"

        add_column outer_klass.table_name, outer_klass.primary_key, outer_klass.connection.native_database_types[:primary_key]

        outer_klass.reset_column_information

        records = outer_klass.all

        if outer_klass.column_names.include?("created_at")
          records = records.reorder(created_at: :asc)
        end

        id_map[outer_klass] = {}

        records.each_with_index do |record, i|
          old_id = record.send("tmp_old_#{outer_klass.primary_key}")

          if record.send(outer_klass.primary_key).nil?
            new_id = i+1
            record.update_columns(outer_klass.primary_key => new_id)
          else
            new_id = record.send(outer_klass.primary_key)
          end

          id_map[outer_klass][old_id] = new_id
        end

        remove_column outer_klass.table_name, "tmp_old_#{outer_klass.primary_key}"

        outer_klass.reset_column_information
      end
    end

    ApplicationRecord.subclasses.each do |inner_klass|
      inner_klass.reflect_on_all_associations(:belongs_to).each do |reflection|
        if inner_klass.column_for_attribute(reflection.foreign_key).type == :uuid
          if reflection.polymorphic?
            ### POLYMORPHIC BELONGS TO
            
            #null_constraint = inner_klass.columns.find{|x| x.name == reflection.foreign_key }.null
            if inner_klass.connection.index_exists?(inner_klass.table_name, reflection.foreign_key)
              remove_index inner_klass.table_name, reflection.foreign_key
            end
            rename_column inner_klass.table_name, reflection.foreign_key, "tmp_old_#{reflection.foreign_key}"
            add_column inner_klass.table_name, reflection.foreign_key, :bigint#, null: null_constraint
            add_index inner_klass.table_name, reflection.foreign_key

            inner_klass.reset_column_information
            
            id_map.each do |outer_klass, inner_id_map|
              records = inner_klass
                .where("#{inner_klass.table_name}.tmp_old_#{reflection.foreign_key} IS NOT NULL")
                .where("#{reflection.foreign_type}" => outer_klass.name)

              records.each do |record|
                old_id = record.send("tmp_old_#{reflection.foreign_key}")

                if old_id
                  new_id = inner_id_map[old_id]

                  if new_id
                    ### First Update Column ID Value
                    record.update_columns(reflection.foreign_key => new_id)
                  else
                    # Orphan record, we just clear the value
                    record.update_columns(reflection.foreign_key => nil)
                  end
                end
              end
            end

            ### Then Change Column Type
            remove_column inner_klass.table_name, "tmp_old_#{reflection.foreign_key}"

            inner_klass.reset_column_information

          elsif id_map[reflection.klass]
            ### DIRECT BELONGS TO
            
            inner_id_map = id_map[reflection.klass]

            #null_constraint = inner_klass.columns.find{|x| x.name == reflection.foreign_key }.null
            if inner_klass.connection.index_exists?(inner_klass.table_name, reflection.foreign_key)
              remove_index inner_klass.table_name, reflection.foreign_key
            end
            rename_column inner_klass.table_name, reflection.foreign_key, "tmp_old_#{reflection.foreign_key}"
            add_column inner_klass.table_name, reflection.foreign_key, :bigint#, null: null_constraint
            add_index inner_klass.table_name, reflection.foreign_key

            inner_klass.reset_column_information

            records = inner_klass.where("#{inner_klass.table_name}.tmp_old_#{reflection.foreign_key} IS NOT NULL")

            records.each do |record|
              old_id = record.send("tmp_old_#{reflection.foreign_key}")

              if old_id
                new_id = inner_id_map[old_id]

                if new_id
                  ### First Update Column ID Value
                  record.update_columns(reflection.foreign_key => new_id)
                else
                  # Orphan record, we just clear the value
                  record.update_columns(reflection.foreign_key => nil)
                end
              end
            end

            ### Then Change Column Type
            remove_column inner_klass.table_name, "tmp_old_#{reflection.foreign_key}"

            inner_klass.reset_column_information
          end
        end
      end

      inner_klass.reflect_on_all_associations(:has_and_belongs_to_many).each do |reflection|
        if id_map[reflection.klass]
          inner_id_map = id_map[reflection.klass]

          #null_constraint = join_klass.columns.find{|x| x.name == reflection.foreign_key }.null
          if inner_klass.connection.index_exists?(reflection.join_table, reflection.association_foreign_key)
            remove_index reflect.join_table, reflection.association_foreign_key
          end
          rename_column reflect.join_table, reflection.association_foreign_key, "tmp_old_#{reflection.association_foreign_key}"
          add_column reflect.join_table, reflection.association_foreign_key, :bigint
          add_index reflect.join_table, reflection.association_foreign_key

          inner_id_map.each do |old_id, new_id|
            if new_id
              ### First Update Column ID Value
              execute "UPDATE #{reflection.join_table} SET #{reflection.association_foreign_key} = '#{new_id}' WHERE tmp_old_#{reflection.association_foreign_key} = '#{old_id}'"
            end
          end

          execute "DELETE FROM #{reflection.join_table} WHERE tmp_old_#{reflection.association_foreign_key} NOT IN ('#{inner_id_map.values.join("','")}')"

          remove_column reflection.join_table, "tmp_old_#{reflection.association_foreign_key}"

          #join_klass.reset_column_information
        end
      end
    end
  end

end