How to migrate complex Rails database to use UUID primary keys Postgresql

Question

I have a database I would like to convert to use UUID's as the primary key in postgresql.

I have roughly 30 tables with deep multi-level associations. Is there an 'easy' way to convert all current ID's to UUID?

From this: https://coderwall.com/p/n_0awq, I can see that I could alter the table in migration. I was thinking something like this:

for client in Client.all
  # Retrieve children
  underwritings = client.underwritings
  # Change primary key
  execute 'ALTER TABLE clients ALTER COLUMN id TYPE uuid;'
  execute 'ALTER TABLE clients ALTER COLUMN id SET DEFAULT uuid_generate_v1();'
  # Get new id - is this already generated?
  client_id = client.id
  for underwriting in underwritings
    locations = underwriting.locations
    other_record = underwriting.other_records...

    execute 'ALTER TABLE underwritings ALTER COLUMN id TYPE uuid;'
    execute 'ALTER TABLE underwritings ALTER COLUMN id SET DEFAULT uuid_generate_v1();'
    underwriting.client_id = client_id
    underwriting.saved
    underwriting_id = underwriting.id

    for location in locations
      buildings = location.buildings
      execute 'ALTER TABLE locations ALTER COLUMN id TYPE uuid;'
      execute 'ALTER TABLE locations ALTER COLUMN id SET DEFAULT uuid_generate_v1();'
      location.undewriting_id = underwriting_id
      location.save
      location_id = location.id

      for building in buildings
      ...
      end
    end
    for other_record in other_records
      ...
    end
    ... 
    ...
  end
end

Questions:

Will this work?
Is there an easier way to do this?
Will child records be retrieved properly as long as they are retrieved before the primary key is changed?
Will the new primary key be already generated as soon as the alter table is called?

Thanks very much for any help or tips in doing this.

No I have not tried this on a copy and of course would not just test it on a production database. I was wondering if a) this is the way to go about this, and b) is there an easier way (some Rails magic I don't know about). Thanks — riley
– riley, Commented Jun 21, 2013 at 3:47

Deepak Kumar · Accepted Answer · 2014-01-25 15:00:35Z

8

I found these to be quite tedious. 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.

answered Jan 25, 2014 at 15:00

Deepak Kumar

9929 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

house9 Over a year ago

Thanks - this simplifies the migration to uuid from integer

Deepak Kumar Over a year ago

@riley depends on which version of rails migrations were originally used. If there are Foreign Key relations defined (which some Rails versions do), it will attempt to update related columns. If not, you will have to update the relations yourself (there is helper method for doing this).

mu is too short · Accepted Answer · 2013-06-21 21:08:02Z

I think trying to do something like this through Rails would just complicate matters. I'd ignore the Rails side of things completely and just do it in SQL.

Your first step is grab a complete backup of your database. Then restore that backup into another database to:

Make sure that your backup works.
Give you a realistic playpen where you can make mistakes without consequence.

First you'd want to clean up your data by adding real foreign keys to match all your Rails associations. There's a good chance that some of your FKs will fail, if they do you'll have to clean up your broken references.

Now that you have clean data, rename all your tables to make room for the new UUID versions. For a table t, we'll refer to the renamed table as t_tmp. For each t_tmp, create another table to hold the mapping from the old integer ids to the new UUID ids, something like this:

create table t_id_map (
    old_id integer not null,
    new_id uuid not null default uuid_generate_v1()
)

and then populate it:

insert into t_id_map (old_id)
select id from t_tmp

And you'll probably want to index t_id_map.old_id while you're here.

This gives us the old tables with integer ids and a lookup table for each t_tmp that maps the old id to the new one.

Now create the new tables with UUIDs replacing all the old integer and serial columns that held ids; I'd add real foreign keys at this point as well; you should be paranoid about your data: broken code is temporary, broken data is usually forever.

Populating the new tables is pretty easy at this point: simply use insert into ... select ... from constructs and JOIN to the appropriate t_id_map tables to map the old ids to the new ones. Once the data has been mapped and copied, you'll want to do some sanity checking to make sure everything still makes sense. Then you can drop your t_tmp and t_id_map tables and get on with your life.

Practice that process on a copy of your database, script it up, and away you go.

You would of course want to shut down any applications that access your database while you're doing this work.

riley · Accepted Answer · 2013-07-21 21:32:09Z

Didn't want to add foreign keys, and wanted to to use a rails migration. Anyways, here is what I did if others are looking to do this (example for 2 tables, I did 32 total):

  def change
    execute 'CREATE EXTENSION "uuid-ossp";'
    execute <<-SQL
      ALTER TABLE buildings ADD COLUMN guid uuid DEFAULT uuid_generate_v1() NOT NULL;
      ALTER TABLE buildings ALTER COLUMN guid SET DEFAULT uuid_generate_v1();
      ALTER TABLE buildings ADD COLUMN location_guid uuid;

      ALTER TABLE clients ADD COLUMN guid uuid DEFAULT uuid_generate_v1() NOT NULL;
      ALTER TABLE clients ALTER COLUMN guid SET DEFAULT uuid_generate_v1();
      ALTER TABLE clients ADD COLUMN agency_guid uuid;
      ALTER TABLE clients ADD COLUMN account_executive_guid uuid;
      ALTER TABLE clients ADD COLUMN account_representative_guid uuid;
    SQL

    for record in Building.all
      location = record.location
      record.location_guid = location.guid

      record.save
    end

    for record in Client.all
      agency = record.agency
      record.agency_guid = agency.guid

      account_executive = record.account_executive
      record.account_executive_guid = account_executive.guid unless account_executive.blank?

      account_representative = record.account_representative
      record.account_representative_guid = account_representative.guid unless account_representative.blank?

      record.save
    end

    execute <<-SQL
      ALTER TABLE buildings DROP CONSTRAINT buildings_pkey;
      ALTER TABLE buildings DROP COLUMN id;
      ALTER TABLE buildings RENAME COLUMN guid TO id;
      ALTER TABLE buildings ADD PRIMARY KEY (id);
      ALTER TABLE buildings DROP COLUMN location_id;
      ALTER TABLE buildings RENAME COLUMN location_guid TO location_id;

      ALTER TABLE clients DROP CONSTRAINT clients_pkey;
      ALTER TABLE clients DROP COLUMN id;
      ALTER TABLE clients RENAME COLUMN guid TO id;
      ALTER TABLE clients ADD PRIMARY KEY (id);
      ALTER TABLE clients DROP COLUMN agency_id;
      ALTER TABLE clients RENAME COLUMN agency_guid TO agency_id;
      ALTER TABLE clients DROP COLUMN account_executive_id;
      ALTER TABLE clients RENAME COLUMN account_executive_guid TO account_executive_id;
      ALTER TABLE clients DROP COLUMN account_representative_id;
      ALTER TABLE clients RENAME COLUMN account_representative_guid TO account_representative_id;
    SQL
  end

Collectives™ on Stack Overflow

How to migrate complex Rails database to use UUID primary keys Postgresql

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related