Mass downcase a field for all records in rails

Question

When I first implemented a User model , I allowed the user to type upper or lowercase email for their login info. The problem is that its a mobile app, and sometimes autocaps happen so the user would not get authenticated. I've changed the CREATE method to downcase the email first. However, this causes people with existing accounts to not be consistent

So how can I add a migration to mass update the email field in the users table to downcase it?

Answer 1

The most efficient way would be to avoid using a Ruby iterator, and do it directly in SQL.

Inside of a normal migration file you can use this SQL for MySQL:

execute("UPDATE users SET email = LOWER(email)")

Answer 2

You can simply go with

User.update_all('email = lower(email)')

Answer 3

The simplest solution is to just use Ruby code:

class DowncaseEmail < ActiveRecord::Migration
  def up
    User.all.each do |user|
      user.update_attributes :email => user.email.downcase
    end
  end
end

As others have noted, this is not the most efficient solution. I tend to prefer portability over performance, but that depends on the number of records we are talking about.

A more complex but still portable solution depends on some db specific functions:

class DowncaseEmail < ActiveRecord::Migration
  def up
    if %w[MySQL PostgreSQL].include? ActiveRecord::Base.connection.adapter_name
      execute "UPDATE users SET email = LOWER(email)"
    else
      User.all.each do |user|
        user.update_attributes email: user.email.downcase
      end
    end
  end
end

Both Postgres and MySQL support the LOWER function. SQLite also supports it, but only for ascii encoding which is probably fine for emails, but might cause bugs (since some emails may contain other characters). Rails also potentially supports any number of databases, so relying on these functions can make your app locked in to a specific DB.

Answer 4

Never worry about optimizing something that you're only going to do once. Unless you have massive numbers of users, just use the methodology that will be most familiar to you. (Though I would recommend just running the SQL query.)