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]
)
Tweet