Pass Ruby array to SQL as values

Question

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.

max · Accepted Answer · 2021-05-17 21:45:50Z

3

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)"

edited May 17, 2021 at 21:45

answered May 17, 2021 at 18:38

max

103k15 gold badges113 silver badges177 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

engineersmnky Over a year ago

Absolutely beautiful. Only recommendation would be arel_table[name] to arel_table[name.to_sym] just to ensure this works whether or not columns are initially symbols.

max Over a year ago

Thanks and good suggestion @engineersmnky.

Asbah Ishaq Over a year ago

Thanks but these are too many lines and I don't know how optimal it would be. I have done it using gsub, I will post my answer

max Over a year ago

@AsbahIshaq why don't you try actually benchmarking it? The advantage of Arel is that it's portable and handles quoting. I doubt that using gsub! On a huge string will actually perform much better. ruby-doc.org/stdlib-2.5.3/libdoc/benchmark/rdoc/Benchmark.html

engineersmnky Over a year ago

@max add a little Bobby tables and see how that impacts things :)

|

Joel Blum · Accepted Answer · 2021-05-17 21:32:03Z

2

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

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

answered May 17, 2021 at 21:32

Joel Blum

7,89811 gold badges44 silver badges63 bronze badges

2 Comments

max Over a year ago

This would be the preferred way if you actually have a model as it will use the model for typecasting. It does have a bit more overhead though.

Asbah Ishaq Over a year ago

I don't have the model, that's why I need to use sql

Greg · Accepted Answer · 2021-05-17 18:29:25Z

0

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.

answered May 17, 2021 at 18:29

Greg

6,7911 gold badge29 silver badges61 bronze badges

Collectives™ on Stack Overflow

Pass Ruby array to SQL as values

3 Answers 3

6 Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

6 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related