CODEWITHSUNDEEP
ruby-on-railsoptimizationmigrationalter-table
fguillen
fguillen

Reputation: 38772

Rails Migration, how to add multiple columns to a table in batch mode

When I want to add multiple columns to an existing table I can do this:

rails g migration AddColumnsToUser col1:integer col2:integer .. etc.

This will generate a migration with several lines:

def change
  add_column :users, :col1, :integer
  add_column :users, :col2, :integer
end

Which will be translated in several alter table commands to the backend DB:

ALTER TABLE users ADD COLUMN col1 SMALLINT(6) NOT NULL;
ALTER TABLE users ADD COLUMN col2 SMALLINT(6) NOT NULL;

The problem is that if you are dealing with a big table, each of these alter table will take a lot of time, at less in MySQL, because the backend engine will generate a duplication of the table and will do a lot of expensive processes. And all this work has to be done for each column I want to add.

So, my question, is how can I aggregate all these add_column sentences in only one so the result will be an alter table in batch mode, like this:

ALTER TABLE users
    ADD COLUMN col1 SMALLINT(6) NOT NULL,
    ADD COLUMN col2 SMALLINT(6) NOT NULL;

Upvotes: 2

Views: 4780

Answers (2)

Alessandro Caporrini
Alessandro Caporrini

Reputation: 196

You can use change_table in your migration with the option bulk: true.

https://api.rubyonrails.org/classes/ActiveRecord/ConnectionAdapters/SchemaStatements.html#method-i-change_table

Upvotes: 10

Vivek Singh
Vivek Singh

Reputation: 635

If you want multiple statments or custom sql go this way:

execute <<-SQL
   ALTER TABLE users
    ADD COLUMN col1 SMALLINT(6) NOT NULL,
    ADD COLUMN col2 SMALLINT(6) NOT NULL;
SQL

Upvotes: 0

Related Questions