前言

當需要大量建立資料時，可以選擇逐筆建立，但會有 N+1 insert problem

透過 activerecord-import Gem 只要幾條 SQL 便解決

如何安裝

放在 Gemfile 檔案中，可參考此 commit

推薦至 GitHub 看文件，寫得很清楚，且有提供範例

使用方法

在 rails console --sandbox 中演練示範

# rails console --sandbox 可縮寫成 rails c -s
# 不建議在 production 使用 sandbox ， 可能會造成 DB Lock，詳情自行上網查

$ rails c -s

[1] pry(main)> Shop.count
  TRANSACTION (0.2ms)  BEGIN
   (11.7ms)  SELECT COUNT(*) FROM "shops"
0
[2] pry(main)> shops = []
[]
[3] pry(main)> 10.times { |i| shops << { name: "test_#{i}", email: 'test', note: nil } }
10
[4] pry(main)> Shop.import(shops)
  Shop Create Many (1.0ms)  INSERT INTO "shops" ("name","email","note","created_at","updated_at") VALUES ('test_0','test',NULL,'2021-07-11 06:09:50.230104','2021-07-11 06:09:50.230104'),('test_1','test',NULL,'2021-07-11 06:09:50.230104','2021-07-11 06:09:50.230104'),('test_2','test',NULL,'2021-07-11 06:09:50.230104','2021-07-11 06:09:50.230104'),('test_3','test',NULL,'2021-07-11 06:09:50.230104','2021-07-11 06:09:50.230104'),('test_4','test',NULL,'2021-07-11 06:09:50.230104','2021-07-11 06:09:50.230104'),('test_5','test',NULL,'2021-07-11 06:09:50.230104','2021-07-11 06:09:50.230104'),('test_6','test',NULL,'2021-07-11 06:09:50.230104','2021-07-11 06:09:50.230104'),('test_7','test',NULL,'2021-07-11 06:09:50.230104','2021-07-11 06:09:50.230104'),('test_8','test',NULL,'2021-07-11 06:09:50.230104','2021-07-11 06:09:50.230104'),('test_9','test',NULL,'2021-07-11 06:09:50.230104','2021-07-11 06:09:50.230104') RETURNING "id"
#<Struct:ActiveRecord::Import::Result:0x00008098
    failed_instances = [],
    ids = [
        [0] 1,
        [1] 2,
        [2] 3,
        [3] 4,
        [4] 5,
        [5] 6,
        [6] 7,
        [7] 8,
        [8] 9,
        [9] 10
    ],
    num_inserts = 1,
    results = []
>
[5] pry(main)> Shop.count
   (0.4ms)  SELECT COUNT(*) FROM "shops"
10

注意

在使用 import 時，須留意不會檢查 ActiveRecord 的 validates 與觸發 callback，可參考這篇 GitHub Issue

以下為還原情境

# app/models/shop.rb
class Shop < ApplicationRecord
  strip_attributes
  validates :name, uniqueness: true
  after_commit :say_hello

  private

  def say_hello
    puts "hello"
  end
end

---

$ rails c -s

[1] pry(main)> Shop.count
  TRANSACTION (0.2ms)  BEGIN
   (1.1ms)  SELECT COUNT(*) FROM "shops"
0
[2] pry(main)> shops = []
[]
[3] pry(main)> 10.times { |i| shops << { name: "test", email: "test", note: nil } }
10
[4] pry(main)> Shop.import(shops)
  Shop Create Many (1.8ms)  INSERT INTO "shops" ("name","email","note","created_at","updated_at") VALUES ('test','test',NULL,'2021-07-11 06:41:34.694983','2021-07-11 06:41:34.694983'),('test','test',NULL,'2021-07-11 06:41:34.694983','2021-07-11 06:41:34.694983'),('test','test',NULL,'2021-07-11 06:41:34.694983','2021-07-11 06:41:34.694983'),('test','test',NULL,'2021-07-11 06:41:34.694983','2021-07-11 06:41:34.694983'),('test','test',NULL,'2021-07-11 06:41:34.694983','2021-07-11 06:41:34.694983'),('test','test',NULL,'2021-07-11 06:41:34.694983','2021-07-11 06:41:34.694983'),('test','test',NULL,'2021-07-11 06:41:34.694983','2021-07-11 06:41:34.694983'),('test','test',NULL,'2021-07-11 06:41:34.694983','2021-07-11 06:41:34.694983'),('test','test',NULL,'2021-07-11 06:41:34.694983','2021-07-11 06:41:34.694983'),('test','test',NULL,'2021-07-11 06:41:34.694983','2021-07-11 06:41:34.694983') RETURNING "id"
#<Struct:ActiveRecord::Import::Result:0x000083b8
    failed_instances = [],
    ids = [
        [0] 1,
        [1] 2,
        [2] 3,
        [3] 4,
        [4] 5,
        [5] 6,
        [6] 7,
        [7] 8,
        [8] 9,
        [9] 10
    ],
    num_inserts = 1,
    results = []
>
[5] pry(main)> Shop.count
   (0.5ms)  SELECT COUNT(*) FROM "shops"
10
[6] pry(main)> Shop.pluck(:name)
   (0.3ms)  SELECT "shops"."name" FROM "shops"
[
    [0] "test",
    [1] "test",
    [2] "test",
    [3] "test",
    [4] "test",
    [5] "test",
    [6] "test",
    [7] "test",
    [8] "test",
    [9] "test"
]

避免踩到 unique 解法

Database 增加 unique ，便可避免建立重複的資料，可參考此 commit

add_index :shops, :name, unique: true

小結

大量資料存入 Database 時，import 是很好用的工具，同時也須留意眉角，避免踩雷。

note: Callbacks 解法可參考 GitHub README

參考資料

1. activerecord-import GitHub
2. 比較各種方式在 Rails DB 中寫入大筆資料的速度

鐵人賽文章連結：https://ithelp.ithome.com.tw/articles/10264572
medium 文章連結：https://link.medium.com/GX2nvti2Mjb
本文同步發布於 小菜的 Blog https://riverye.com/

備註：之後文章修改更新，以個人部落格為主