Convert camelcase table and column names to snake case in mysql

Question

I tried googling and stackoverflowing this but couldn't find an answer, so I thought I would post one.

Currently I inherited a legacy system (PHP/MySQL) where the table and column names are defined in camelcase (i.e. tableName, columnName). I would like to migrate this system to RoR and would like to have the MySQL table to be converted to snake case as well (i.e. table_name, column_name).

How can I do this effectively and still migrate the data successfully? Has anyone done this beofre?

score 1 · Answer 1 · answered Aug 15 '19 at 08:34

You can rename the tables and columns using Rails database migration.

You can create a migration file to rename a table and another one to rename its columns.

Let's say we want to rename the emailSubscriptions table.

> bundle exec rails generate migrations rename_email_subscriptions
# this creates db/migrate/xxxxxxxx_rename_email_subscriptions.rb

Edit the migration file

# this creates db/migrate/xxxxxxxx_rename_email_subscriptions.rb

class RenameEmailSubscriptions < ActiveRecord::Migration
  def change
    rename_table :emailSubscriptions, :email_subscriptions
  end
end

And for the columns

> bundle exec rails generate migrations rename_email_subscriptions_columns
# this creates db/migrate/xxxxxxxx_rename_email_subscriptions_columns.rb

Edit the migration file

# this creates db/migrate/xxxxxxxx_rename_email_subscriptions_columns.rb

class RenameEmailSubscriptionsColumns < ActiveRecord::Migration
  def change
    change_table :email_subscriptions do |t|
      t.rename :columnName1, :column_name_1
      t.rename :columnName2, :column_name_2
      t.rename :columnName3, :column_name_3
      t.rename :columnName4, :column_name_4
    end
  end
end

Run bundle exec rake db:migrate

Do this for all the tables and their corresponding columns.

Note that I decided to split the migrations for renaming a table and its columns in order to make it possible to rollback the migrations.

score 0 · Answer 2 · edited May 23 '17 at 11:55

You can override the table name in your models:

class Project < ActiveRecord::Base
  self.table_name = "project"
end

or

class Post < ActiveRecord::Base
  def self.table_name
    "special_" + super
  end
end

Post.table_name # => "special_posts"

More info: http://guides.rubyonrails.org/3_2_release_notes.html

You can also make a script and rename you tables and use rails conventions afterwards, it's up to you!

From there you can use rails migrations to rename column names: How can I rename a database column in a Ruby on Rails migration?

Convert camelcase table and column names to snake case in mysql

2 Answers2