Pass Ruby array to SQL as values

user_11712949 0
Viewed 210
insert into table_name (attr1, attr2) VALUES 
  (1, 2),
  (3, 4)
;

I want to insert values into this table 'table_name'. But I want to make it dynamic. I have values in array form in ruby, like this:

[[1, 2], [3, 4]]

How do I pass a ruby array to sql query as values? I am using rails and postgres.

ruby-on-rails ruby sql
3 Answers

You can use Arel::InsertManager to generate a SQL insert statement:

def insert_list(table_name:, columns:, values:)
  Arel::InsertManager.new.tap do |manager|
    table = Arel::Table.new(table_name)
    manager.into(table)
    columns.each do |name|
      manager.columns << table[name.to_sym]
    end
    manager.values = manager.create_values_list(values)
  end
end

insert_list(
   table_name: :foos, 
   columns: [:bar, :baz],
   values: [[1, 2], [3, 4], [5, 6], [7, 8], [9, 10]]
).to_sql

# => "INSERT INTO \"foos\" (\"bar\", \"baz\") VALUES (1, 2), (3, 4), (5, 6), (7, 8), (9, 10)"
user_5448250

Assuming you have a model you can use ActiveRecord's insert_all

Model.insert_all([{attr1: 1, attr2: 2}, {attr1: 3, attr2: 4}])
user_10326630

join with map would work here:

[[1, 2], [3, 4]].map{|inner| inner.join(", ")}
# => ["1, 2", "3, 4"]
[[1, 2], [3, 4]].map{|inner| inner.join(", ")}.join("),\n(")
# => "1, 2),\n(3, 4"
"(" + [[1, 2], [3, 4]].map{|inner| inner.join(", ")}.join("),\n(") + ")"
# => "(1, 2),\n(3, 4)"
puts "(" + [[1, 2], [3, 4]].map{|inner| inner.join(", ")}.join("),\n(") + ")"
# (1, 2),
# (3, 4)

Please note that this is a simple solution. In a production app, you would need to sanitize the array values [[1, 2], [3, 4]] if they were coming from untrusted sources (Users!) to avoid SQL injection attacks. But this seems to be out of the scope of this question.

user_2997740
Related