dynamically create mysql columns in Rails

Question

I'm working on a private api that unifies our many dbs/vendor data in one sql db.

We face a challenge with our ESP where our marketers are always changing/adding new columns. We want this app to scale with those column changes and not require massaging columns every time they change names or add new attributes.

I need to create a list of columns on the fly using Rails (we don't want to do this 100% in Mysql, because we're renaming some columns in mapping).

table = "customer"
attrs = ["vendor_cm_name_d", "vendor_cm_email address_d", "vendor_cm_date added_d", "vendor_cm_extid_d" ]

attrs.each_with_index do |attr, i|
  connection = ActiveRecord::Base.connection
  connection.execute( "ALTER TABLE #{table}  ADD COLUMN #{attr} VARCHAR(15);"  )
  # this didnt work :-( connection.close 
end

This approach works a single column but fails with more than one element in the array. reading the logs (pasted below) seems like it's trying to run the entire statement as one call, not transactionally. I tried adding in a connection.close before the end of each loop and that didn't work either. All pointers are welcome.

Mysql2::Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'address_d VARCHAR(15)' at line 1: ALTER TABLE customer  ADD COLUMN vendor_cm_email address_d VARCHAR(15);
ActiveRecord::StatementInvalid: Mysql2::Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'address_d VARCHAR(15)' at line 1: ALTER TABLE customer  ADD COLUMN vendor_cm_email address_d VARCHAR(15);

Answer 1

Your sql doesn't work because column names have space. Wrap you column names in generated sql with quotes.

attrs.each_with_index do |x, i|
  connection = ActiveRecord::Base.connection
  connection.execute "ALTER TABLE #{table}  ADD COLUMN \"#{x}\" VARCHAR(15);" 
# this didnt work :-( connection.close 
end

Though I would rather not go down the path of writing sql myself. Instead you can create a service like this:

class DbService < ActiveRecord::Migration
  def initialize(table_name)
    @table_name = table_name
  end
  def add_string_column(column_name)
    add_column @table_name, column_name, :string
  end
end

And then do something like

service = DbService.new("customer")
attrs.each do |column_name|
  service.add_string_column(column_name)
end