Add insert_all to ActiveRecord models (#35077)

Adds a method to ActiveRecord allowing records to be inserted in bulk without instantiating ActiveRecord models. This method supports options for handling uniqueness violations by skipping duplicate records or overwriting them in an UPSERT operation.

ActiveRecord already supports bulk-update and bulk-destroy actions that execute SQL UPDATE and DELETE commands directly. It also supports bulk-read actions through `pluck`. It makes sense for it also to support bulk-creation.

4 files changed, 146 insertions, 6 deletions

diff --git a/activerecord/test/cases/helper.rb b/activerecord/test/cases/helper.rb
index 730cd663a2..a9ec667eba 100644
--- a/activerecord/test/cases/helper.rb
+++ b/activerecord/test/cases/helper.rb
@@ -57,8 +57,17 @@ def supports_default_expression?
   end
 end
 
-def supports_savepoints?
-  ActiveRecord::Base.connection.supports_savepoints?
+%w[
+  supports_savepoints?
+  supports_partial_index?
+  supports_insert_returning?
+  supports_insert_on_duplicate_skip?
+  supports_insert_on_duplicate_update?
+  supports_insert_conflict_target?
+].each do |method_name|
+  define_method method_name do
+    ActiveRecord::Base.connection.public_send(method_name)
+  end
 end
 
 def with_env_tz(new_tz = "US/Eastern")
diff --git a/activerecord/test/cases/insert_all_test.rb b/activerecord/test/cases/insert_all_test.rb
new file mode 100644
index 0000000000..e0d79d2fab
--- /dev/null
+++ b/activerecord/test/cases/insert_all_test.rb
@@ -0,0 +1,127 @@
+# frozen_string_literal: true
+
+require "cases/helper"
+require "models/book"
+
+class ReadonlyNameBook < Book
+  attr_readonly :name
+end
+
+class PersistenceTest < ActiveRecord::TestCase
+  fixtures :books
+
+  def test_insert
+    assert_difference "Book.count", +1 do
+      Book.insert! name: "Rework", author_id: 1
+    end
+  end
+
+  def test_insert_all
+    assert_difference "Book.count", +10 do
+      Book.insert_all! [
+        { name: "Rework", author_id: 1 },
+        { name: "Patterns of Enterprise Application Architecture", author_id: 1 },
+        { name: "Design of Everyday Things", author_id: 1 },
+        { name: "Practical Object-Oriented Design in Ruby", author_id: 1 },
+        { name: "Clean Code", author_id: 1 },
+        { name: "Ruby Under a Microscope", author_id: 1 },
+        { name: "The Principles of Product Development Flow", author_id: 1 },
+        { name: "Peopleware", author_id: 1 },
+        { name: "About Face", author_id: 1 },
+        { name: "Eloquent Ruby", author_id: 1 },
+      ]
+    end
+  end
+
+  def test_insert_all_should_handle_empty_arrays
+    assert_no_difference "Book.count" do
+      Book.insert_all! []
+    end
+  end
+
+  def test_insert_all_raises_on_duplicate_records
+    assert_raise ActiveRecord::RecordNotUnique do
+      Book.insert_all! [
+        { name: "Rework", author_id: 1 },
+        { name: "Patterns of Enterprise Application Architecture", author_id: 1 },
+        { name: "Agile Web Development with Rails", author_id: 1 },
+      ]
+    end
+  end
+
+  def test_insert_all_returns_ActiveRecord_Result
+    result = Book.insert_all! [{ name: "Rework", author_id: 1 }]
+    assert_kind_of ActiveRecord::Result, result
+  end
+
+  def test_insert_all_returns_primary_key_if_returning_is_supported
+    skip unless supports_insert_returning?
+    result = Book.insert_all! [{ name: "Rework", author_id: 1 }]
+    assert_equal %w[ id ], result.columns
+  end
+
+  def test_insert_all_returns_nothing_if_returning_is_empty
+    skip unless supports_insert_returning?
+    result = Book.insert_all! [{ name: "Rework", author_id: 1 }], returning: []
+    assert_equal [], result.columns
+  end
+
+  def test_insert_all_returns_nothing_if_returning_is_false
+    skip unless supports_insert_returning?
+    result = Book.insert_all! [{ name: "Rework", author_id: 1 }], returning: false
+    assert_equal [], result.columns
+  end
+
+  def test_insert_all_returns_requested_fields
+    skip unless supports_insert_returning?
+    result = Book.insert_all! [{ name: "Rework", author_id: 1 }], returning: [:id, :name]
+    assert_equal %w[ Rework ], result.pluck("name")
+  end
+
+  def test_insert_all_can_skip_duplicate_records
+    skip unless supports_insert_on_duplicate_skip?
+    assert_no_difference "Book.count" do
+      Book.insert_all [{ id: 1, name: "Agile Web Development with Rails" }]
+    end
+  end
+
+  def test_insert_all_will_raise_if_duplicates_are_skipped_only_for_a_certain_conflict_target
+    skip unless supports_insert_on_duplicate_skip? && supports_insert_conflict_target?
+    assert_raise ActiveRecord::RecordNotUnique do
+      Book.insert_all [{ id: 1, name: "Agile Web Development with Rails" }],
+        unique_by: { columns: %i{author_id name} }
+    end
+  end
+
+  def test_upsert_all_updates_existing_records
+    skip unless supports_insert_on_duplicate_update?
+    new_name = "Agile Web Development with Rails, 4th Edition"
+    Book.upsert_all [{ id: 1, name: new_name }]
+    assert_equal new_name, Book.find(1).name
+  end
+
+  def test_upsert_all_does_not_update_readonly_attributes
+    skip unless supports_insert_on_duplicate_update?
+    new_name = "Agile Web Development with Rails, 4th Edition"
+    ReadonlyNameBook.upsert_all [{ id: 1, name: new_name }]
+    assert_not_equal new_name, Book.find(1).name
+  end
+
+  def test_upsert_all_does_not_update_primary_keys
+    skip unless supports_insert_on_duplicate_update? && supports_insert_conflict_target?
+    Book.upsert_all [{ id: 101, name: "Perelandra", author_id: 7 }]
+    Book.upsert_all [{ id: 103, name: "Perelandra", author_id: 7, isbn: "1974522598" }],
+      unique_by: { columns: %i{author_id name} }
+    book = Book.find_by(name: "Perelandra")
+    assert_equal 101, book.id, "Should not have updated the ID"
+    assert_equal "1974522598", book.isbn, "Should have updated the isbn"
+  end
+
+  def test_upsert_all_does_not_perform_an_upsert_if_a_partial_index_doesnt_apply
+    skip unless supports_insert_on_duplicate_update? && supports_insert_conflict_target? && supports_partial_index?
+    Book.upsert_all [{ name: "Out of the Silent Planet", author_id: 7, isbn: "1974522598", published_on: Date.new(1938, 4, 1) }]
+    Book.upsert_all [{ name: "Perelandra", author_id: 7, isbn: "1974522598" }],
+      unique_by: { columns: %w[ isbn ], where: "published_on IS NOT NULL" }
+    assert_equal ["Out of the Silent Planet", "Perelandra"], Book.where(isbn: "1974522598").order(:name).pluck(:name)
+  end
+end
diff --git a/activerecord/test/cases/instrumentation_test.rb b/activerecord/test/cases/instrumentation_test.rb
index c09ea32991..06382b6c7c 100644
--- a/activerecord/test/cases/instrumentation_test.rb
+++ b/activerecord/test/cases/instrumentation_test.rb
@@ -41,8 +41,8 @@ module ActiveRecord
           assert_equal "Book Update", event.payload[:name]
         end
       end
-      book = Book.create(name: "test book")
-      book.update_attribute(:name, "new name")
+      book = Book.create(name: "test book", format: "paperback")
+      book.update_attribute(:format, "ebook")
     ensure
       ActiveSupport::Notifications.unsubscribe(subscriber) if subscriber
     end
@@ -54,8 +54,8 @@ module ActiveRecord
           assert_equal "Book Update All", event.payload[:name]
         end
       end
-      Book.create(name: "test book")
-      Book.update_all(name: "new name")
+      Book.create(name: "test book", format: "paperback")
+      Book.update_all(format: "ebook")
     ensure
       ActiveSupport::Notifications.unsubscribe(subscriber) if subscriber
     end
diff --git a/activerecord/test/schema/schema.rb b/activerecord/test/schema/schema.rb
index 5d46bd47d9..ead4de2a13 100644
--- a/activerecord/test/schema/schema.rb
+++ b/activerecord/test/schema/schema.rb
@@ -114,6 +114,10 @@ ActiveRecord::Schema.define do
     t.column :font_size, :integer, **default_zero
     t.column :difficulty, :integer, **default_zero
     t.column :cover, :string, default: "hard"
+    t.string :isbn
+    t.datetime :published_on
+    t.index [:author_id, :name], unique: true
+    t.index :isbn, where: "published_on IS NOT NULL", unique: true
   end
 
   create_table :booleans, force: true do |t|