CODEWITHSUNDEEP
ruby-on-railspostgresqlrails-activerecord
Stanley_Lee
Stanley_Lee

Reputation: 25

Ruby on Rails creating migration to reverse UUID primary keys

I'm currently in the process of writing code to convert our databases default ID columns to a UUID format.

One example of the code that I have for our migration is

def up
      remove_reference :examples, :user, null: false, foreign_key: true
      add_column :users, :uuid, :uuid, default: "gen_random_uuid()", null: false
      change_table :users do |t|
        t.remove :id, type: :id
        t.rename :uuid, :id
      end
      execute "ALTER TABLE users ADD PRIMARY KEY (id);"
      add_reference :examples, :user, null: false, foreign_key: true, type: :uuid
    end

Essentially this allowed me to convert my ID column to a UUID format.

I created a down function so I would be able to rollback but it fails due to this error ERROR: column "user_id" of relation "examples" contains null values

I realize that there would be an issue because once there is data in the database it would be unable to rollback and create the correct references again. Does anyone have any ideas on how I should work on my down function?

def down
      remove_reference :examples, :user, null: false, foreign_key: true, type: :uuid

      execute 'ALTER TABLE users DROP CONSTRAINT users_pkey'
      add_column :users, :new_id, :primary_key

      change_table :users do |t|
        t.remove :id, type: :uuid
        t.rename :new_id, :id
      end

      add_reference :examples, :user, null: false, foreign_key: true
    end

Does anyone have any suggestions on how I should proceed with this? The original migration was in one change function, but it would be unable to rollback due to the execute block.

Upvotes: 1

Views: 1165

Answers (1)

manuwell
manuwell

Reputation: 412

Be careful doing this kind of database change in one-shot. I would suggest you to break into steps.

First step (new column uuid)

  • Create the new column for the uuid
def up
      add_column :users, :uuid, :uuid, default: "gen_random_uuid()", null: false
      add_column :examples, :user_uuid, :uuid
end

Adapt your code to populate the examples.user_uuid column with the recent created column; You can easily achieve this by creating a model callback, feeling the user_uuid automatically.

If your database has a GB of data, consider adding the uuid as nullable, and populate the column using queues or in batches. The new records will be already filled.

We have now two new columns, with new data comming and all synced

Second step (the renaming)

Once populated and working with new columns, is time to rename the columns and associate the new keys.

def up
      change_table :examples do |t|
        t.rename :user_id, type: :old_user_id
        t.rename :user_uuid, :user_id
      end
      
      change_table :users do |t|
        t.rename :id, type: :old_id
        t.rename :uuid, :id
      end

      execute "ALTER TABLE users ADD PRIMARY KEY (id);"
      add_reference :examples, :user, null: false, foreign_key: true, type: :uuid
    end

def down
# ...
end

Rember to remove, or review the model code changed before, to support this new columns or ignore.

Now we have changed the columns, without loosing the old reference.

Be careful here, if your database is big, you may lock your operation. Perhaps you may need a maintenance window here.

Third step (removing the old columns)

Now we can remove the old columns and everything should work fine

note: Always be careful when making this kind of change into your database. It is very risk to perform something like this. If you want to go on, simulate several times the step of renaming. Make snapshot of your database before performing and inform your clients that might have a downtime in your service.

I don't know why you want to change your primary keys to be an uuid, this costs a lot to the database to query and join data. It's more complicated to compare an UUID than an integer. Consider just create a new indexed column uuid into your tables and let the database to join and based on this field.

Upvotes: 1

Related Questions