Today I Learned

hashrocket A Hashrocket project

Rails' .insert_all method is too naive

Rails requires a unique index in order to use the .insert_all methods. This requirement can make this method very brittle and unusable. If your conflict target is the table’s primary key, this won’t work unless you create a redundant index on the table for this method to match against. This creates an amazing amount of waste not only of storage space, but also performance. This method would allow so many more use cases if it simply let you describe the conflict you want to match against.

More advanced method:

class ApplicationRecord
  def self.bulk_insert(array_of_hashes, conflict_targets = Array(primary_key))
    columns = array_of_hashes.first.keys
    values = array_of_hashes.flat_map(&:values)
    rows = array_of_hashes.map do |f|
      "(#{columns.size.times.map { "?" }.join(", ")})"
    end.join(", ")

    sql = sanitize_sql_array([<<~SQL, *values])
      INSERT INTO "#{table_name}"
      (#{columns.map { |c| "\"#{c}\"" }.join(",")})
      VALUES #{rows}
      ON CONFLICT (#{conflict_targets.map { |c| "\"#{c}\"" }.join(", ")}) DO NOTHING
    SQL

    connection.execute(sql.squish)
  end
end

SQL it produces:

User.bulk_insert([{email: "a@example.com"}, {email: "b@example.com"}])
INSERT INTO "users" ("email") VALUES ('a@example.com'), ('b@example.com') ON CONFLICT ("id") DO NOTHING

This would then allow you to reference any conflict you like:

alter table users add unique (email);
User.bulk_insert(
  [{email: "a@example.com"}, {email: "b@example.com"}],
  %i[email]
)
See More #rails TILs
Looking for help? Hashrocket has been an industry leader in Ruby on Rails since 2008. Rails is a core skill for each developer at Hashrocket, and we'd love to take a look at your project. Contact us and find out how we can help you.