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.

12 files changed, 613 insertions, 17 deletions

diff --git a/activerecord/CHANGELOG.md b/activerecord/CHANGELOG.md
index 04449a3ebc..cff6a3147b 100644
--- a/activerecord/CHANGELOG.md
+++ b/activerecord/CHANGELOG.md
@@ -1,3 +1,13 @@
+*   Add `insert_all`/`insert_all!`/`upsert_all` methods to `ActiveRecord::Persistence`,
+    allowing bulk inserts akin to the bulk updates provided by `update_all` and
+    bulk deletes by `delete_all`.
+
+    Supports skipping or upserting duplicates through the `ON CONFLICT` syntax
+    for Postgres (9.5+) and Sqlite (3.24+) and `ON DUPLICATE KEY UPDATE` syntax
+    for MySQL.
+
+    *Bob Lail*
+
 *   Add `rails db:seed:replant` that truncates tables of each database
     for current environment and loads the seeds.
 
diff --git a/activerecord/lib/active_record/connection_adapters/abstract/database_statements.rb b/activerecord/lib/active_record/connection_adapters/abstract/database_statements.rb
index b5e6d03cf5..3c872d6c1b 100644
--- a/activerecord/lib/active_record/connection_adapters/abstract/database_statements.rb
+++ b/activerecord/lib/active_record/connection_adapters/abstract/database_statements.rb
@@ -404,6 +404,17 @@ module ActiveRecord
         end
       end
 
+      # Fixture value is quoted by Arel, however scalar values
+      # are not quotable. In this case we want to convert
+      # the column value to YAML.
+      def with_yaml_fallback(value) # :nodoc:
+        if value.is_a?(Hash) || value.is_a?(Array)
+          YAML.dump(value)
+        else
+          value
+        end
+      end
+
       private
         def default_insert_value(column)
           Arel.sql("DEFAULT")
@@ -473,17 +484,6 @@ module ActiveRecord
             relation
           end
         end
-
-        # Fixture value is quoted by Arel, however scalar values
-        # are not quotable. In this case we want to convert
-        # the column value to YAML.
-        def with_yaml_fallback(value)
-          if value.is_a?(Hash) || value.is_a?(Array)
-            YAML.dump(value)
-          else
-            value
-          end
-        end
     end
   end
 end
diff --git a/activerecord/lib/active_record/connection_adapters/abstract_adapter.rb b/activerecord/lib/active_record/connection_adapters/abstract_adapter.rb
index 823efee301..cf6a1217a0 100644
--- a/activerecord/lib/active_record/connection_adapters/abstract_adapter.rb
+++ b/activerecord/lib/active_record/connection_adapters/abstract_adapter.rb
@@ -387,6 +387,22 @@ module ActiveRecord
         false
       end
 
+      def supports_insert_returning?
+        false
+      end
+
+      def supports_insert_on_duplicate_skip?
+        false
+      end
+
+      def supports_insert_on_duplicate_update?
+        false
+      end
+
+      def supports_insert_conflict_target?
+        false
+      end
+
       # This is meant to be implemented by the adapters that support extensions
       def disable_extension(name)
       end
@@ -542,6 +558,19 @@ module ActiveRecord
         index.using.nil?
       end
 
+      # Called by ActiveRecord::InsertAll,
+      # Passed an instance of ActiveRecord::InsertAll::Builder,
+      # This method implements standard bulk inserts for all databases, but
+      # should be overridden by adapters to implement common features with
+      # non-standard syntax like handling duplicates or returning values.
+      def build_insert_sql(insert) # :nodoc:
+        if insert.skip_duplicates? || insert.update_duplicates?
+          raise NotImplementedError, "#{self.class} should define `build_insert_sql` to implement adapter-specific logic for handling duplicates during INSERT"
+        end
+
+        "INSERT #{insert.into} #{insert.values_list}"
+      end
+
       private
         def check_version
         end
diff --git a/activerecord/lib/active_record/connection_adapters/abstract_mysql_adapter.rb b/activerecord/lib/active_record/connection_adapters/abstract_mysql_adapter.rb
index 96f902792e..1aab36c865 100644
--- a/activerecord/lib/active_record/connection_adapters/abstract_mysql_adapter.rb
+++ b/activerecord/lib/active_record/connection_adapters/abstract_mysql_adapter.rb
@@ -109,6 +109,14 @@ module ActiveRecord
         true
       end
 
+      def supports_insert_on_duplicate_skip?
+        true
+      end
+
+      def supports_insert_on_duplicate_update?
+        true
+      end
+
       def get_advisory_lock(lock_name, timeout = 0) # :nodoc:
         query_value("SELECT GET_LOCK(#{quote(lock_name.to_s)}, #{timeout})") == 1
       end
@@ -511,6 +519,20 @@ module ActiveRecord
         end
       end
 
+      def build_insert_sql(insert) # :nodoc:
+        sql = +"INSERT #{insert.into} #{insert.values_list}"
+
+        if insert.skip_duplicates?
+          any_column = quote_column_name(insert.model.columns.first.name)
+          sql << " ON DUPLICATE KEY UPDATE #{any_column}=#{any_column}"
+        elsif insert.update_duplicates?
+          sql << " ON DUPLICATE KEY UPDATE "
+          sql << insert.updatable_columns.map { |column| "#{column}=VALUES(#{column})" }.join(",")
+        end
+
+        sql
+      end
+
       private
         def check_version
           if version < "5.5.8"
diff --git a/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb b/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb
index 95beeb4cae..5919801519 100644
--- a/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb
+++ b/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb
@@ -196,6 +196,17 @@ module ActiveRecord
         true
       end
 
+      def supports_insert_returning?
+        true
+      end
+
+      def supports_insert_on_conflict?
+        postgresql_version >= 90500
+      end
+      alias supports_insert_on_duplicate_skip? supports_insert_on_conflict?
+      alias supports_insert_on_duplicate_update? supports_insert_on_conflict?
+      alias supports_insert_conflict_target? supports_insert_on_conflict?
+
       def index_algorithms
         { concurrently: "CONCURRENTLY" }
       end
@@ -425,6 +436,20 @@ module ActiveRecord
         index.using == :btree || super
       end
 
+      def build_insert_sql(insert) # :nodoc:
+        sql = +"INSERT #{insert.into} #{insert.values_list}"
+
+        if insert.skip_duplicates?
+          sql << " ON CONFLICT #{insert.conflict_target} DO NOTHING"
+        elsif insert.update_duplicates?
+          sql << " ON CONFLICT #{insert.conflict_target} DO UPDATE SET "
+          sql << insert.updatable_columns.map { |column| "#{column}=excluded.#{column}" }.join(",")
+        end
+
+        sql << " RETURNING #{insert.returning}" if insert.returning
+        sql
+      end
+
       private
         def check_version
           if postgresql_version < 90300
diff --git a/activerecord/lib/active_record/connection_adapters/sqlite3_adapter.rb b/activerecord/lib/active_record/connection_adapters/sqlite3_adapter.rb
index 63e07932d9..0ed7f3988d 100644
--- a/activerecord/lib/active_record/connection_adapters/sqlite3_adapter.rb
+++ b/activerecord/lib/active_record/connection_adapters/sqlite3_adapter.rb
@@ -137,6 +137,13 @@ module ActiveRecord
         true
       end
 
+      def supports_insert_on_conflict?
+        sqlite_version >= "3.24.0"
+      end
+      alias supports_insert_on_duplicate_skip? supports_insert_on_conflict?
+      alias supports_insert_on_duplicate_update? supports_insert_on_conflict?
+      alias supports_insert_conflict_target? supports_insert_on_conflict?
+
       def active?
         @active
       end
@@ -397,6 +404,19 @@ module ActiveRecord
         end
       end
 
+      def build_insert_sql(insert) # :nodoc:
+        sql = +"INSERT #{insert.into} #{insert.values_list}"
+
+        if insert.skip_duplicates?
+          sql << " ON CONFLICT #{insert.conflict_target} DO NOTHING"
+        elsif insert.update_duplicates?
+          sql << " ON CONFLICT #{insert.conflict_target} DO UPDATE SET "
+          sql << insert.updatable_columns.map { |column| "#{column}=excluded.#{column}" }.join(",")
+        end
+
+        sql
+      end
+
       private
         # See https://www.sqlite.org/limits.html,
         # the default value is 999 when not configured.
diff --git a/activerecord/lib/active_record/insert_all.rb b/activerecord/lib/active_record/insert_all.rb
new file mode 100644
index 0000000000..ca5ce11d79
--- /dev/null
+++ b/activerecord/lib/active_record/insert_all.rb
@@ -0,0 +1,153 @@
+# frozen_string_literal: true
+
+module ActiveRecord
+  class InsertAll
+    attr_reader :model, :connection, :inserts, :on_duplicate, :returning, :unique_by
+
+    def initialize(model, inserts, on_duplicate:, returning: nil, unique_by: nil)
+      @model, @connection, @inserts, @on_duplicate, @returning, @unique_by = model, model.connection, inserts, on_duplicate, returning, unique_by
+      @returning = (connection.supports_insert_returning? ? primary_keys : false) if @returning.nil?
+      @returning = false if @returning == []
+      @on_duplicate = :skip if @on_duplicate == :update && updatable_columns.empty?
+
+      ensure_valid_options_for_connection!
+    end
+
+    def execute
+      if inserts.present?
+        connection.exec_query to_sql, "Bulk Insert"
+      else
+        ActiveRecord::Result.new([], [])
+      end
+    end
+
+    def keys
+      inserts.present? ? inserts.first.keys.map(&:to_s) : []
+    end
+
+    def updatable_columns
+      keys - readonly_columns - unique_by_columns
+    end
+
+    def skip_duplicates?
+      on_duplicate == :skip
+    end
+
+    def update_duplicates?
+      on_duplicate == :update
+    end
+
+    private
+      def ensure_valid_options_for_connection!
+        if returning && !connection.supports_insert_returning?
+          raise ArgumentError, "#{connection.class} does not support :returning"
+        end
+
+        unless %i{ raise skip update }.member?(on_duplicate)
+          raise NotImplementedError, "#{on_duplicate.inspect} is an unknown value for :on_duplicate. Valid values are :raise, :skip, and :update"
+        end
+
+        if on_duplicate == :skip && !connection.supports_insert_on_duplicate_skip?
+          raise ArgumentError, "#{connection.class} does not support skipping duplicates"
+        end
+
+        if on_duplicate == :update && !connection.supports_insert_on_duplicate_update?
+          raise ArgumentError, "#{connection.class} does not support upsert"
+        end
+
+        if unique_by && !connection.supports_insert_conflict_target?
+          raise ArgumentError, "#{connection.class} does not support :unique_by"
+        end
+      end
+
+      def to_sql
+        connection.build_insert_sql(ActiveRecord::InsertAll::Builder.new(self))
+      end
+
+      def readonly_columns
+        primary_keys + model.readonly_attributes.to_a
+      end
+
+      def unique_by_columns
+        unique_by ? unique_by.fetch(:columns).map(&:to_s) : []
+      end
+
+      def primary_keys
+        Array.wrap(model.primary_key)
+      end
+
+
+      class Builder
+        attr_reader :model
+
+        delegate :skip_duplicates?, :update_duplicates?, to: :insert_all
+
+        def initialize(insert_all)
+          @insert_all, @model, @connection = insert_all, insert_all.model, insert_all.connection
+        end
+
+        def into
+          "INTO #{model.quoted_table_name}(#{columns_list})"
+        end
+
+        def values_list
+          columns = connection.schema_cache.columns_hash(model.table_name)
+          keys = insert_all.keys.to_set
+          types = keys.map { |key| [ key, connection.lookup_cast_type_from_column(columns[key]) ] }.to_h
+
+          values_list = insert_all.inserts.map do |attributes|
+            attributes = attributes.stringify_keys
+
+            unless attributes.keys.to_set == keys
+              raise ArgumentError, "All objects being inserted must have the same keys"
+            end
+
+            keys.map do |key|
+              bind = Relation::QueryAttribute.new(key, attributes[key], types[key])
+              connection.with_yaml_fallback(bind.value_for_database)
+            end
+          end
+
+          Arel::InsertManager.new.create_values_list(values_list).to_sql
+        end
+
+        def returning
+          quote_columns(insert_all.returning).join(",") if insert_all.returning
+        end
+
+        def conflict_target
+          return unless conflict_columns
+          sql = +"(#{quote_columns(conflict_columns).join(',')})"
+          sql << " WHERE #{where}" if where
+          sql
+        end
+
+        def updatable_columns
+          quote_columns(insert_all.updatable_columns)
+        end
+
+        private
+          attr_reader :connection, :insert_all
+
+          def columns_list
+            quote_columns(insert_all.keys).join(",")
+          end
+
+          def quote_columns(columns)
+            columns.map(&connection.method(:quote_column_name))
+          end
+
+          def conflict_columns
+            @conflict_columns ||= begin
+              conflict_columns = insert_all.unique_by.fetch(:columns) if insert_all.unique_by
+              conflict_columns ||= Array.wrap(model.primary_key) if update_duplicates?
+              conflict_columns
+            end
+          end
+
+          def where
+            insert_all.unique_by && insert_all.unique_by[:where]
+          end
+      end
+  end
+end
diff --git a/activerecord/lib/active_record/persistence.rb b/activerecord/lib/active_record/persistence.rb
index 10148d0dca..069bd75a71 100644
--- a/activerecord/lib/active_record/persistence.rb
+++ b/activerecord/lib/active_record/persistence.rb
@@ -1,5 +1,7 @@
 # frozen_string_literal: true
 
+require "active_record/insert_all"
+
 module ActiveRecord
   # = Active Record \Persistence
   module Persistence
@@ -55,6 +57,201 @@ module ActiveRecord
         end
       end
 
+      # Inserts a single record into the databases. This method constructs a single SQL INSERT
+      # statement and sends it straight to the database. It does not instantiate the involved
+      # models and it does not trigger Active Record callbacks or validations. However, values
+      # passed to #insert will still go through Active Record's normal type casting and
+      # serialization.
+      #
+      # See <tt>ActiveRecord::Persistence#insert_all</tt> for documentation.
+      def insert(attributes, returning: nil, unique_by: nil)
+        insert_all([ attributes ], returning: returning, unique_by: unique_by)
+      end
+
+      # Inserts multiple records into the database. This method constructs a single SQL INSERT
+      # statement and sends it straight to the database. It does not instantiate the involved
+      # models and it does not trigger Active Record callbacks or validations. However, values
+      # passed to #insert_all will still go through Active Record's normal type casting and
+      # serialization.
+      #
+      # The +attributes+ parameter is an Array of Hashes. These Hashes describe the
+      # attributes on the objects that are to be created. All of the Hashes must have
+      # same keys.
+      #
+      # Records that would violate a unique constraint on the table are skipped.
+      #
+      # Returns an <tt>ActiveRecord::Result</tt>. The contents of the result depend on the
+      # value of <tt>:returning</tt> (see below).
+      #
+      # ==== Options
+      #
+      # [:returning]
+      #   (Postgres-only) An array of attributes that should be returned for all successfully
+      #   inserted records. For databases that support <tt>INSERT ... RETURNING</tt>, this will default
+      #   to returning the primary keys of the successfully inserted records. Pass
+      #   <tt>returning: %w[ id name ]</tt> to return the id and name of every successfully inserted
+      #   record or pass <tt>returning: false</tt> to omit the clause.
+      #
+      # [:unique_by]
+      #   (Postgres and SQLite only) In a table with more than one unique constaint or index,
+      #   new records may considered duplicates according to different criteria. By default,
+      #   new rows will be skipped if they violate _any_ unique constraint/index. By defining
+      #   <tt>:unique_by</tt>, you can skip rows that would create duplicates according to the given
+      #   constraint but raise <tt>ActiveRecord::RecordNotUnique</tt> if rows violate other constraints.
+      #
+      #   (For example, maybe you assume a client will try to import the same ISBNs more than
+      #   once and want to silently ignore the duplicate records, but you don't except any of
+      #   your code to attempt to create two rows with the same primary key and would appreciate
+      #   an exception report in that scenario.)
+      #
+      #   Indexes can be identified by an array of columns:
+      #
+      #     unique_by: { columns: %w[ isbn ] }
+      #
+      #   Partial indexes can be identified by an array of columns and a <tt>:where</tt> condition:
+      #
+      #     unique_by: { columns: %w[ isbn ], where: "published_on IS NOT NULL" }
+      #
+      # ==== Example
+      #
+      #   # Insert multiple records and skip duplicates
+      #   # ('Eloquent Ruby' will be skipped because its id is duplicate)
+      #   Book.insert_all([
+      #     { id: 1, title: 'Rework', author: 'David' },
+      #     { id: 1, title: 'Eloquent Ruby', author: 'Russ' }
+      #   ])
+      #
+      def insert_all(attributes, returning: nil, unique_by: nil)
+        InsertAll.new(self, attributes, on_duplicate: :skip, returning: returning, unique_by: unique_by).execute
+      end
+
+      # Inserts a single record into the databases. This method constructs a single SQL INSERT
+      # statement and sends it straight to the database. It does not instantiate the involved
+      # models and it does not trigger Active Record callbacks or validations. However, values
+      # passed to #insert! will still go through Active Record's normal type casting and
+      # serialization.
+      #
+      # See <tt>ActiveRecord::Persistence#insert_all!</tt> for documentation.
+      def insert!(attributes, returning: nil)
+        insert_all!([ attributes ], returning: returning)
+      end
+
+      # Inserts multiple records into the database. This method constructs a single SQL INSERT
+      # statement and sends it straight to the database. It does not instantiate the involved
+      # models and it does not trigger Active Record callbacks or validations. However, values
+      # passed to #insert_all! will still go through Active Record's normal type casting and
+      # serialization.
+      #
+      # The +attributes+ parameter is an Array of Hashes. These Hashes describe the
+      # attributes on the objects that are to be created. All of the Hashes must have
+      # same keys.
+      #
+      # #insert_all! will raise <tt>ActiveRecord::RecordNotUnique</tt> if any of the records being
+      # inserts would violate a unique constraint on the table. In that case, no records
+      # would be inserted.
+      #
+      # To skip duplicate records, see <tt>ActiveRecord::Persistence#insert_all</tt>.
+      # To replace them, see <tt>ActiveRecord::Persistence#upsert_all</tt>.
+      #
+      # Returns an <tt>ActiveRecord::Result</tt>. The contents of the result depend on the
+      # value of <tt>:returning</tt> (see below).
+      #
+      # ==== Options
+      #
+      # [:returning]
+      #   (Postgres-only) An array of attributes that should be returned for all successfully
+      #   inserted records. For databases that support <tt>INSERT ... RETURNING</tt>, this will default
+      #   to returning the primary keys of the successfully inserted records. Pass
+      #   <tt>returning: %w[ id name ]</tt> to return the id and name of every successfully inserted
+      #   record or pass <tt>returning: false</tt> to omit the clause.
+      #
+      # ==== Examples
+      #
+      #   # Insert multiple records
+      #   Book.insert_all!([
+      #     { title: 'Rework', author: 'David' },
+      #     { title: 'Eloquent Ruby', author: 'Russ' }
+      #   ])
+      #
+      #   # raises ActiveRecord::RecordNotUnique beacuse 'Eloquent Ruby'
+      #   # does not have a unique ID
+      #   Book.insert_all!([
+      #     { id: 1, title: 'Rework', author: 'David' },
+      #     { id: 1, title: 'Eloquent Ruby', author: 'Russ' }
+      #   ])
+      #
+      def insert_all!(attributes, returning: nil)
+        InsertAll.new(self, attributes, on_duplicate: :raise, returning: returning).execute
+      end
+
+      # Upserts (inserts-or-creates) a single record into the databases. This method constructs
+      # a single SQL INSERT statement and sends it straight to the database. It does not
+      # instantiate the involved models and it does not trigger Active Record callbacks or
+      # validations. However, values passed to #upsert will still go through Active Record's
+      # normal type casting and serialization.
+      #
+      # See <tt>ActiveRecord::Persistence#upsert_all</tt> for documentation.
+      def upsert(attributes, returning: nil, unique_by: nil)
+        upsert_all([ attributes ], returning: returning, unique_by: unique_by)
+      end
+
+      # Upserts (creates-or-updates) multiple records into the database. This method constructs
+      # a single SQL INSERT statement and sends it straight to the database. It does not
+      # instantiate the involved models and it does not trigger Active Record callbacks or
+      # validations. However, values passed to #upsert_all will still go through Active Record's
+      # normal type casting and serialization.
+      #
+      # The +attributes+ parameter is an Array of Hashes. These Hashes describe the
+      # attributes on the objects that are to be created. All of the Hashes must have
+      # same keys.
+      #
+      # Returns an <tt>ActiveRecord::Result</tt>. The contents of the result depend on the
+      # value of <tt>:returning</tt> (see below).
+      #
+      # ==== Options
+      #
+      # [:returning]
+      #   (Postgres-only) An array of attributes that should be returned for all successfully
+      #   inserted records. For databases that support <tt>INSERT ... RETURNING</tt>, this will default
+      #   to returning the primary keys of the successfully inserted records. Pass
+      #   <tt>returning: %w[ id name ]</tt> to return the id and name of every successfully inserted
+      #   record or pass <tt>returning: false</tt> to omit the clause.
+      #
+      # [:unique_by]
+      #   (Postgres and SQLite only) In a table with more than one unique constaint or index,
+      #   new records may considered duplicates according to different criteria. For MySQL,
+      #   an upsert will take place if a new record violates _any_ unique constraint. For
+      #   Postgres and SQLite, new rows will replace existing rows when the new row has the
+      #   same primary key as the existing row. By defining <tt>:unique_by</tt>, you can supply
+      #   a different key for matching new records to existing ones than the primary key.
+      #
+      #   (For example, if you have a unique index on the ISBN column and use that as
+      #   the <tt>:unique_by</tt>, a new record with the same ISBN as an existing record
+      #   will replace the existing record but a new record with the same primary key
+      #   as an existing record will raise <tt>ActiveRecord::RecordNotUnique</tt>.)
+      #
+      #   Indexes can be identified by an array of columns:
+      #
+      #     unique_by: { columns: %w[ isbn ] }
+      #
+      #   Partial indexes can be identified by an array of columns and a <tt>:where</tt> condition:
+      #
+      #     unique_by: { columns: %w[ isbn ], where: "published_on IS NOT NULL" }
+      #
+      # ==== Examples
+      #
+      #   # Insert multiple records, performing an upsert when records have duplicate ISBNs
+      #   # ('Eloquent Ruby' will overwrite 'Rework' because its ISBN is duplicate)
+      #   Book.upsert_all([
+      #     { title: 'Rework', author: 'David', isbn: '1' },
+      #     { title: 'Eloquent Ruby', author: 'Russ', isbn: '1' }
+      #   ],
+      #      unique_by: { columns: %w[ isbn ] })
+      #
+      def upsert_all(attributes, returning: nil, unique_by: nil)
+        InsertAll.new(self, attributes, on_duplicate: :update, returning: returning, unique_by: unique_by).execute
+      end
+
       # Given an attributes hash, +instantiate+ returns a new instance of
       # the appropriate class. Accepts only keys as strings.
       #
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|