Bulk upsert (Rails/PostgreSQL)

bulk_upsert.rb

class Data < ApplicationRecord
  class << self
    def bulk_upsert(values)
      transaction do
        values.uniq { |v| v[:key] }.each_slice(1000) do |values_slice|
          connection.exec_query <<-SQL.squish
            INSERT INTO #{table_name}(#{upsert_columns.join(', ')})
              VALUES #{upsert_values_statement(values_slice)}
              ON CONFLICT(key)
                DO UPDATE SET
                  payload = EXCLUDED.payload,
                  expires_at = EXCLUDED.expires_at,
                  updated_at = CURRENT_TIMESTAMP
          SQL
        end
      end
    end

    private

    def upsert_columns
      %w[key payload expires_at]
    end

    def upsert_values_statement(values)
      values.map do |item|
        <<-SQL.squish
          (
            #{quote_value('key', item.key)},
            #{quote_value('payload', item.payload)},
            #{quote_value('expires_at', item.expires_at || nil)}
          )
        SQL
      end.join(', ')
    end

    def quote_value(name, value)
      type_cast = connection.type_cast_from_column(columns_hash[name], value)
      connection.quote(type_cast)
    end
  end
end