Encrypting columns in-place

[rgalanakis]

We recently migrated a number of columns from plain-text to encrypted, using the column_encryption plugin. Here is how we did the migration- maybe someone has a better option, but I figured this could be useful so wanted to post it:

Sequel.migration do
  up do
    testmode = false
    run "LOCK TABLE mytable IN ACCESS EXCLUSIVE MODE"
    Sequel::Model.plugin :column_encryption do |enc|
      enc.key 0, ENV.fetch('ENCRYPTION_KEY_0')
    end
    alter_table(:mytable) do
      add_column :special_column_unenc, :text
    end
    from(:mytable).update(
      special_column_unenc: :special_column,
      special_column: nil,
    )
    shim_cls = Class.new(Sequel::Model(from(:mytable))) do
      plugin :column_encryption do |enc|
        enc.column :special_column
      end
    end
    shim_cls.each do |org|
      org.special_column = org.special_column_unenc
      org.save_changes
    end
    alter_table(:mytable) do
      drop_column :special_column_unenc
    end
    testmode && shim_cls.each do |o|
      puts o.name
      puts "value is now: #{o.special_column}"
    end
    raise "did not apply migration" if testmode
  end
end

Note this is not a 'safe' migration in terms of downtime, but it was a lot more convenient than multiple steps so worth the short downtime.

I also want to say I think this felt like an intuitive use of Sequel (and worked on the first try), creating temporary classes to shim for the plugins, rather than requiring models in the migrations, which causes all sorts of other problems.