activerecord-import、Rails 標準のバルクインサート
activerecord-importの使用例と吐き出すSQL
code:zsh
❯ rails c
Loading development environment (Rails 7.0.3)
# 普通にやるとSQLがたくさん発行される
10.times do |i|
Book.create! name: "book #{i}"
end
(1.0ms) SELECT sqlite_version(*)
TRANSACTION (0.0ms) begin transaction
Book Create (0.5ms) INSERT INTO "books" ("name", "created_at", "updated_at") VALUES (?, ?, ?) "name", "book 0"], "created_at", "2022-06-29 06:50:41.104692", ["updated_at", "2022-06-29 06:50:41.104692"
TRANSACTION (0.5ms) commit transaction
TRANSACTION (0.0ms) begin transaction
Book Create (0.2ms) INSERT INTO "books" ("name", "created_at", "updated_at") VALUES (?, ?, ?) "name", "book 1"], "created_at", "2022-06-29 06:50:41.107137", ["updated_at", "2022-06-29 06:50:41.107137"
TRANSACTION (0.3ms) commit transaction
TRANSACTION (0.0ms) begin transaction
Book Create (0.2ms) INSERT INTO "books" ("name", "created_at", "updated_at") VALUES (?, ?, ?) "name", "book 2"], "created_at", "2022-06-29 06:50:41.108093", ["updated_at", "2022-06-29 06:50:41.108093"
TRANSACTION (0.3ms) commit transaction
TRANSACTION (0.0ms) begin transaction
Book Create (0.2ms) INSERT INTO "books" ("name", "created_at", "updated_at") VALUES (?, ?, ?) "name", "book 3"], "created_at", "2022-06-29 06:50:41.108956", ["updated_at", "2022-06-29 06:50:41.108956"
TRANSACTION (0.3ms) commit transaction
TRANSACTION (0.0ms) begin transaction
Book Create (0.2ms) INSERT INTO "books" ("name", "created_at", "updated_at") VALUES (?, ?, ?) "name", "book 4"], "created_at", "2022-06-29 06:50:41.109783", ["updated_at", "2022-06-29 06:50:41.109783"
TRANSACTION (0.5ms) commit transaction
TRANSACTION (0.0ms) begin transaction
Book Create (0.2ms) INSERT INTO "books" ("name", "created_at", "updated_at") VALUES (?, ?, ?) "name", "book 5"], "created_at", "2022-06-29 06:50:41.110838", ["updated_at", "2022-06-29 06:50:41.110838"
TRANSACTION (0.3ms) commit transaction
TRANSACTION (0.0ms) begin transaction
Book Create (0.2ms) INSERT INTO "books" ("name", "created_at", "updated_at") VALUES (?, ?, ?) "name", "book 6"], "created_at", "2022-06-29 06:50:41.111722", ["updated_at", "2022-06-29 06:50:41.111722"
TRANSACTION (0.3ms) commit transaction
TRANSACTION (0.0ms) begin transaction
Book Create (0.2ms) INSERT INTO "books" ("name", "created_at", "updated_at") VALUES (?, ?, ?) "name", "book 7"], "created_at", "2022-06-29 06:50:41.112677", ["updated_at", "2022-06-29 06:50:41.112677"
TRANSACTION (0.3ms) commit transaction
TRANSACTION (0.0ms) begin transaction
Book Create (0.2ms) INSERT INTO "books" ("name", "created_at", "updated_at") VALUES (?, ?, ?) "name", "book 8"], "created_at", "2022-06-29 06:50:41.113454", ["updated_at", "2022-06-29 06:50:41.113454"
TRANSACTION (0.2ms) commit transaction
TRANSACTION (0.0ms) begin transaction
Book Create (0.1ms) INSERT INTO "books" ("name", "created_at", "updated_at") VALUES (?, ?, ?) "name", "book 9"], "created_at", "2022-06-29 06:50:41.114172", ["updated_at", "2022-06-29 06:50:41.114172"
TRANSACTION (0.3ms) commit transaction
=> 10
# activerecord-importを使う例
books = []
10.times do |i|
books << Book.new(name: "book #{i}")
end
Book.import books
(0.1ms) SELECT sqlite_version(*)
TRANSACTION (0.1ms) begin transaction
Book Create Many (0.5ms) INSERT INTO "books" ("id","name","created_at","updated_at") VALUES (NULL,'book 0','2022-06-29 06:50:50.946518','2022-06-29 06:50:50.946518'),(NULL,'book 1','2022-06-29 06:50:50.946518','2022-06-29 06:50:50.946518'),(NULL,'book 2','2022-06-29 06:50:50.946518','2022-06-29 06:50:50.946518'),(NULL,'book 3','2022-06-29 06:50:50.946518','2022-06-29 06:50:50.946518'),(NULL,'book 4','2022-06-29 06:50:50.946518','2022-06-29 06:50:50.946518'),(NULL,'book 5','2022-06-29 06:50:50.946518','2022-06-29 06:50:50.946518'),(NULL,'book 6','2022-06-29 06:50:50.946518','2022-06-29 06:50:50.946518'),(NULL,'book 7','2022-06-29 06:50:50.946518','2022-06-29 06:50:50.946518'),(NULL,'book 8','2022-06-29 06:50:50.946518','2022-06-29 06:50:50.946518'),(NULL,'book 9','2022-06-29 06:50:50.946518','2022-06-29 06:50:50.946518')
TRANSACTION (0.9ms) commit transaction
=> #<struct ActiveRecord::Import::Result failed_instances=[], num_inserts=1, ids=[], results=[]>
irb(main):009:0>
同じことをRails標準のバルクインサートでやるとどう書けるのか
code:rb
books = []
10.times do |n|
time = Time.current
books << { name: 'name', created_at: time, updated_at: time }
end
Book.insert_all books
irb(main):016:0> Book.insert_all books
(0.8ms) SELECT sqlite_version(*)
(0.1ms) SELECT sqlite_version(*)
Book Bulk Insert (2.8ms) INSERT INTO "books" ("name","created_at","updated_at") VALUES ('name', '2022-06-29 07:07:57.969759', '2022-06-29 07:07:57.969780'), ('name', '2022-06-29 07:07:57.969789', '2022-06-29 07:07:57.969794'), ('name', '2022-06-29 07:07:57.969798', '2022-06-29 07:07:57.969803'), ('name', '2022-06-29 07:07:57.969807', '2022-06-29 07:07:57.969811'), ('name', '2022-06-29 07:07:57.969816', '2022-06-29 07:07:57.969821'), ('name', '2022-06-29 07:07:57.969825', '2022-06-29 07:07:57.969829'), ('name', '2022-06-29 07:07:57.969834', '2022-06-29 07:07:57.969838'), ('name', '2022-06-29 07:07:57.969843', '2022-06-29 07:07:57.969847'), ('name', '2022-06-29 07:07:57.969851', '2022-06-29 07:07:57.969856'), ('name', '2022-06-29 07:07:57.969860', '2022-06-29 07:07:57.969864') ON CONFLICT DO NOTHING
=> #<ActiveRecord::Result:0x0000000108685a48 @column_types={}, @columns=[], @hash_rows=nil, @rows=[]>
activerecord-importでは配列を渡すだけでよかったが、Rails標準のメソッドだと1レコードとしての表現はハッシュになる。
insert_all, upsert_all expect an array of hashes, not ActiveRecord objects
https://stackoverflow.com/questions/69757932/undefined-method-keys-when-using-upsert
そしてactiverecord-importだとタイムスタンプが自動でつくが、Rails標準だと自分でつけたりいろいろ手間がある（Rails7からはタイムスタンプをつけられるようになった）https://techracho.bpsinc.jp/hachi8833/2021_10_25/112862#1-1
簡単さでいえばactiverecord-importが上回るかな