Kick far away N+1 from your Rails App (Part 1)

12 min readMay 1, 2023

N+1 query occurs when a database query has been executed repeatedly inside an iteration or looping and this makes the performance of your application so bad and slow.

Understanding about N+1

N+1 when retrieving data

Let me give you some examples from two connected models with one-to-many relation.

# app/models/organization.rb
class Organization < ApplicationRecord
  has_many :users
end

# == Schema Information
#
# Table name: organizations
#
#  id         :uuid             not null, primary key
#  address    :text
#  email      :string           not null
#  name       :string           not null
#  phone      :string           not null
#  settings   :jsonb
#  created_at :datetime         not null
#  updated_at :datetime         not null

# app/models/user.rb
class User < ApplicationRecord
  belongs_to :organization
end

# == Schema Information
#
# Table name: users
#
#  id                     :uuid             not null, primary key
#  email                  :string           default(""), not null
#  encrypted_password     :string           default(""), not null
#  name                   :string           not null
#  remember_created_at    :datetime
#  reset_password_sent_at :datetime
#  reset_password_token   :string
#  created_at             :datetime         not null
#  updated_at             :datetime         not null
#  organization_id        :uuid             not null

Here, I want to print the user name and organization name with filtering organization

users = User.where(organization_id: params[:organization_id]).limit(10)
users.each do |user|
  puts "#{user.name} from #{user.organization.name}"
end

Look so simple queries from active record but if you look in the console, you got a lot of repeatedly calling organizations from the database

irb(main):005:0> users = User.where(organization_id: params[:organization_id]).limit(10)
  User Load (1.7ms)  SELECT "users".* FROM "users" WHERE "users"."organization_id" = $1 LIMIT $2  [["organization_id", "77cb0fdc-d535-435a-a1b3-e93ee3abfb42"], ["LIMIT", 10]]
=> [#<User id: "43376f12-55c3-4e12-85cf-50f94a6e3ddd", organization_id: "77...
irb(main):006:1* users.each do |user|
irb(main):007:1* !puts "#{user.name} from #{user.organization.name}"
irb(main):008:0> end
  Organization Load (0.5ms)  SELECT "organizations".* FROM "organizations" WHERE "organizations"."id" = $1 LIMIT $2  [["id", "77cb0fdc-d535-435a-a1b3-e93ee3abfb42"], ["LIMIT", 1]]
User Dummy from Firdaus Foundation
  Organization Load (0.3ms)  SELECT "organizations".* FROM "organizations" WHERE "organizations"."id" = $1 LIMIT $2  [["id", "77cb0fdc-d535-435a-a1b3-e93ee3abfb42"], ["LIMIT", 1]]
user2 from Firdaus Foundation
  Organization Load (0.3ms)  SELECT "organizations".* FROM "organizations" WHERE "organizations"."id" = $1 LIMIT $2  [["id", "77cb0fdc-d535-435a-a1b3-e93ee3abfb42"], ["LIMIT", 1]]
user3 from Firdaus Foundation
  Organization Load (0.3ms)  SELECT "organizations".* FROM "organizations" WHERE "organizations"."id" = $1 LIMIT $2  [["id", "77cb0fdc-d535-435a-a1b3-e93ee3abfb42"], ["LIMIT", 1]]
user4 from Firdaus Foundation
  Organization Load (0.3ms)  SELECT "organizations".* FROM "organizations" WHERE "organizations"."id" = $1 LIMIT $2  [["id", "77cb0fdc-d535-435a-a1b3-e93ee3abfb42"], ["LIMIT", 1]]
user6 from Firdaus Foundation
  Organization Load (0.3ms)  SELECT "organizations".* FROM "organizations" WHERE "organizations"."id" = $1 LIMIT $2  [["id", "77cb0fdc-d535-435a-a1b3-e93ee3abfb42"], ["LIMIT", 1]]
user7 from Firdaus Foundation
  Organization Load (0.2ms)  SELECT "organizations".* FROM "organizations" WHERE "organizations"."id" = $1 LIMIT $2  [["id", "77cb0fdc-d535-435a-a1b3-e93ee3abfb42"], ["LIMIT", 1]]
user19 from Firdaus Foundation
  Organization Load (0.2ms)  SELECT "organizations".* FROM "organizations" WHERE "organizations"."id" = $1 LIMIT $2  [["id", "77cb0fdc-d535-435a-a1b3-e93ee3abfb42"], ["LIMIT", 1]]
user20 from Firdaus Foundation
  Organization Load (0.3ms)  SELECT "organizations".* FROM "organizations" WHERE "organizations"."id" = $1 LIMIT $2  [["id", "77cb0fdc-d535-435a-a1b3-e93ee3abfb42"], ["LIMIT", 1]]
user21 from Firdaus Foundation
  Organization Load (0.2ms)  SELECT "organizations".* FROM "organizations" WHERE "organizations"."id" = $1 LIMIT $2  [["id", "77cb0fdc-d535-435a-a1b3-e93ee3abfb42"], ["LIMIT", 1]]
user22 from Firdaus Foundation

The result is very dangerous because printing with the same organization name needs 10 executing queries. it means one query to get user data and 10 queries to get organization for each iteration this is what is called N+1. Imagine if you want to call 100 users even up to 1000 users the query has been executed repeatedly until 100 or 1000 that is horrible 😱

N+1 when inserting Data

The example above is the case from retrieve data, and what about data insertion?

(1..10).each do |i|
  User.create(organization_id: params[:organization_id], name: "User Test-#{i}", email: "email-test-user#{i}@email.com", password: "password123")
end

Here, is an example of how I’m inserting data and the result from the console

irb(main):053:1* (1..10).each do |i|
irb(main):054:1*   User.create(organization_id: params[:organization_id], name: "User Test-#{i}", email: "email-test-user#{i}@email.com", password: "password123")
irb(main):055:0> end
  TRANSACTION (0.3ms)  BEGIN
  User Exists? (0.4ms)  SELECT 1 AS one FROM "users" WHERE "users"."email" = $1 LIMIT $2  [["email", "email-test-user1@email.com"], ["LIMIT", 1]]
  Organization Load (0.2ms)  SELECT "organizations".* FROM "organizations" WHERE "organizations"."id" = $1 LIMIT $2  [["id", "77cb0fdc-d535-435a-a1b3-e93ee3abfb42"], ["LIMIT", 1]]
  User Create (16.2ms)  INSERT INTO "users" ("organization_id", "name", "email", "encrypted_password", "reset_password_token", "reset_password_sent_at", "remember_created_at", "created_at", "updated_at", "jwt_token") VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10) RETURNING "id"  [["organization_id", "77cb0fdc-d535-435a-a1b3-e93ee3abfb42"], ["name", "User Test-1"], ["email", "email-test-user1@email.com"], ["encrypted_password", "[FILTERED]"], ["reset_password_token", "[FILTERED]"], ["reset_password_sent_at", "[FILTERED]"], ["remember_created_at", nil], ["created_at", "2023-05-01 14:56:13.724175"], ["updated_at", "2023-05-01 14:56:13.724175"], ["jwt_token", "[FILTERED]"]]
  TRANSACTION (2.0ms)  COMMIT
  TRANSACTION (0.3ms)  BEGIN
  User Exists? (0.5ms)  SELECT 1 AS one FROM "users" WHERE "users"."email" = $1 LIMIT $2  [["email", "email-test-user2@email.com"], ["LIMIT", 1]]
  Organization Load (0.2ms)  SELECT "organizations".* FROM "organizations" WHERE "organizations"."id" = $1 LIMIT $2  [["id", "77cb0fdc-d535-435a-a1b3-e93ee3abfb42"], ["LIMIT", 1]]
  User Create (1.7ms)  INSERT INTO "users" ("organization_id", "name", "email", "encrypted_password", "reset_password_token", "reset_password_sent_at", "remember_created_at", "created_at", "updated_at", "jwt_token") VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10) RETURNING "id"  [["organization_id", "77cb0fdc-d535-435a-a1b3-e93ee3abfb42"], ["name", "User Test-2"], ["email", "email-test-user2@email.com"], ["encrypted_password", "[FILTERED]"], ["reset_password_token", "[FILTERED]"], ["reset_password_sent_at", "[FILTERED]"], ["remember_created_at", nil], ["created_at", "2023-05-01 14:56:13.989659"], ["updated_at", "2023-05-01 14:56:13.989659"], ["jwt_token", "[FILTERED]"]]
  TRANSACTION (0.3ms)  COMMIT
  TRANSACTION (0.4ms)  BEGIN
  User Exists? (0.7ms)  SELECT 1 AS one FROM "users" WHERE "users"."email" = $1 LIMIT $2  [["email", "email-test-user3@email.com"], ["LIMIT", 1]]
  Organization Load (0.3ms)  SELECT "organizations".* FROM "organizations" WHERE "organizations"."id" = $1 LIMIT $2  [["id", "77cb0fdc-d535-435a-a1b3-e93ee3abfb42"], ["LIMIT", 1]]
  User Create (1.4ms)  INSERT INTO "users" ("organization_id", "name", "email", "encrypted_password", "reset_password_token", "reset_password_sent_at", "remember_created_at", "created_at", "updated_at", "jwt_token") VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10) RETURNING "id"  [["organization_id", "77cb0fdc-d535-435a-a1b3-e93ee3abfb42"], ["name", "User Test-3"], ["email", "email-test-user3@email.com"], ["encrypted_password", "[FILTERED]"], ["reset_password_token", "[FILTERED]"], ["reset_password_sent_at", "[FILTERED]"], ["remember_created_at", nil], ["created_at", "2023-05-01 14:56:14.235030"], ["updated_at", "2023-05-01 14:56:14.235030"], ["jwt_token", "[FILTERED]"]]
  TRANSACTION (0.4ms)  COMMIT
  TRANSACTION (0.3ms)  BEGIN
  User Exists? (0.5ms)  SELECT 1 AS one FROM "users" WHERE "users"."email" = $1 LIMIT $2  [["email", "email-test-user4@email.com"], ["LIMIT", 1]]
  Organization Load (0.2ms)  SELECT "organizations".* FROM "organizations" WHERE "organizations"."id" = $1 LIMIT $2  [["id", "77cb0fdc-d535-435a-a1b3-e93ee3abfb42"], ["LIMIT", 1]]
  User Create (0.6ms)  INSERT INTO "users" ("organization_id", "name", "email", "encrypted_password", "reset_password_token", "reset_password_sent_at", "remember_created_at", "created_at", "updated_at", "jwt_token") VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10) RETURNING "id"  [["organization_id", "77cb0fdc-d535-435a-a1b3-e93ee3abfb42"], ["name", "User Test-4"], ["email", "email-test-user4@email.com"], ["encrypted_password", "[FILTERED]"], ["reset_password_token", "[FILTERED]"], ["reset_password_sent_at", "[FILTERED]"], ["remember_created_at", nil], ["created_at", "2023-05-01 14:56:14.480148"], ["updated_at", "2023-05-01 14:56:14.480148"], ["jwt_token", "[FILTERED]"]]
  TRANSACTION (0.4ms)  COMMIT
  TRANSACTION (0.3ms)  BEGIN
  User Exists? (0.7ms)  SELECT 1 AS one FROM "users" WHERE "users"."email" = $1 LIMIT $2  [["email", "email-test-user5@email.com"], ["LIMIT", 1]]
  Organization Load (0.3ms)  SELECT "organizations".* FROM "organizations" WHERE "organizations"."id" = $1 LIMIT $2  [["id", "77cb0fdc-d535-435a-a1b3-e93ee3abfb42"], ["LIMIT", 1]]
  User Create (2.2ms)  INSERT INTO "users" ("organization_id", "name", "email", "encrypted_password", "reset_password_token", "reset_password_sent_at", "remember_created_at", "created_at", "updated_at", "jwt_token") VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10) RETURNING "id"  [["organization_id", "77cb0fdc-d535-435a-a1b3-e93ee3abfb42"], ["name", "User Test-5"], ["email", "email-test-user5@email.com"], ["encrypted_password", "[FILTERED]"], ["reset_password_token", "[FILTERED]"], ["reset_password_sent_at", "[FILTERED]"], ["remember_created_at", nil], ["created_at", "2023-05-01 14:56:14.724150"], ["updated_at", "2023-05-01 14:56:14.724150"], ["jwt_token", "[FILTERED]"]]
  TRANSACTION (0.4ms)  COMMIT
  TRANSACTION (0.3ms)  BEGIN
  User Exists? (0.5ms)  SELECT 1 AS one FROM "users" WHERE "users"."email" = $1 LIMIT $2  [["email", "email-test-user6@email.com"], ["LIMIT", 1]]
  Organization Load (0.2ms)  SELECT "organizations".* FROM "organizations" WHERE "organizations"."id" = $1 LIMIT $2  [["id", "77cb0fdc-d535-435a-a1b3-e93ee3abfb42"], ["LIMIT", 1]]
  User Create (0.7ms)  INSERT INTO "users" ("organization_id", "name", "email", "encrypted_password", "reset_password_token", "reset_password_sent_at", "remember_created_at", "created_at", "updated_at", "jwt_token") VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10) RETURNING "id"  [["organization_id", "77cb0fdc-d535-435a-a1b3-e93ee3abfb42"], ["name", "User Test-6"], ["email", "email-test-user6@email.com"], ["encrypted_password", "[FILTERED]"], ["reset_password_token", "[FILTERED]"], ["reset_password_sent_at", "[FILTERED]"], ["remember_created_at", nil], ["created_at", "2023-05-01 14:56:14.969680"], ["updated_at", "2023-05-01 14:56:14.969680"], ["jwt_token", "[FILTERED]"]]
  TRANSACTION (0.4ms)  COMMIT
  TRANSACTION (0.3ms)  BEGIN
  User Exists? (0.7ms)  SELECT 1 AS one FROM "users" WHERE "users"."email" = $1 LIMIT $2  [["email", "email-test-user7@email.com"], ["LIMIT", 1]]
  Organization Load (0.3ms)  SELECT "organizations".* FROM "organizations" WHERE "organizations"."id" = $1 LIMIT $2  [["id", "77cb0fdc-d535-435a-a1b3-e93ee3abfb42"], ["LIMIT", 1]]
  User Create (1.9ms)  INSERT INTO "users" ("organization_id", "name", "email", "encrypted_password", "reset_password_token", "reset_password_sent_at", "remember_created_at", "created_at", "updated_at", "jwt_token") VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10) RETURNING "id"  [["organization_id", "77cb0fdc-d535-435a-a1b3-e93ee3abfb42"], ["name", "User Test-7"], ["email", "email-test-user7@email.com"], ["encrypted_password", "[FILTERED]"], ["reset_password_token", "[FILTERED]"], ["reset_password_sent_at", "[FILTERED]"], ["remember_created_at", nil], ["created_at", "2023-05-01 14:56:15.213556"], ["updated_at", "2023-05-01 14:56:15.213556"], ["jwt_token", "[FILTERED]"]]
  TRANSACTION (0.4ms)  COMMIT
  TRANSACTION (0.3ms)  BEGIN
  User Exists? (0.5ms)  SELECT 1 AS one FROM "users" WHERE "users"."email" = $1 LIMIT $2  [["email", "email-test-user8@email.com"], ["LIMIT", 1]]
  Organization Load (0.2ms)  SELECT "organizations".* FROM "organizations" WHERE "organizations"."id" = $1 LIMIT $2  [["id", "77cb0fdc-d535-435a-a1b3-e93ee3abfb42"], ["LIMIT", 1]]
  User Create (0.6ms)  INSERT INTO "users" ("organization_id", "name", "email", "encrypted_password", "reset_password_token", "reset_password_sent_at", "remember_created_at", "created_at", "updated_at", "jwt_token") VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10) RETURNING "id"  [["organization_id", "77cb0fdc-d535-435a-a1b3-e93ee3abfb42"], ["name", "User Test-8"], ["email", "email-test-user8@email.com"], ["encrypted_password", "[FILTERED]"], ["reset_password_token", "[FILTERED]"], ["reset_password_sent_at", "[FILTERED]"], ["remember_created_at", nil], ["created_at", "2023-05-01 14:56:15.459320"], ["updated_at", "2023-05-01 14:56:15.459320"], ["jwt_token", "[FILTERED]"]]
  TRANSACTION (0.7ms)  COMMIT
  TRANSACTION (0.3ms)  BEGIN
  User Exists? (0.5ms)  SELECT 1 AS one FROM "users" WHERE "users"."email" = $1 LIMIT $2  [["email", "email-test-user9@email.com"], ["LIMIT", 1]]
  Organization Load (0.2ms)  SELECT "organizations".* FROM "organizations" WHERE "organizations"."id" = $1 LIMIT $2  [["id", "77cb0fdc-d535-435a-a1b3-e93ee3abfb42"], ["LIMIT", 1]]
  User Create (0.7ms)  INSERT INTO "users" ("organization_id", "name", "email", "encrypted_password", "reset_password_token", "reset_password_sent_at", "remember_created_at", "created_at", "updated_at", "jwt_token") VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10) RETURNING "id"  [["organization_id", "77cb0fdc-d535-435a-a1b3-e93ee3abfb42"], ["name", "User Test-9"], ["email", "email-test-user9@email.com"], ["encrypted_password", "[FILTERED]"], ["reset_password_token", "[FILTERED]"], ["reset_password_sent_at", "[FILTERED]"], ["remember_created_at", nil], ["created_at", "2023-05-01 14:56:15.703197"], ["updated_at", "2023-05-01 14:56:15.703197"], ["jwt_token", "[FILTERED]"]]
  TRANSACTION (0.8ms)  COMMIT
  TRANSACTION (0.3ms)  BEGIN
  User Exists? (0.6ms)  SELECT 1 AS one FROM "users" WHERE "users"."email" = $1 LIMIT $2  [["email", "email-test-user10@email.com"], ["LIMIT", 1]]
  Organization Load (0.2ms)  SELECT "organizations".* FROM "organizations" WHERE "organizations"."id" = $1 LIMIT $2  [["id", "77cb0fdc-d535-435a-a1b3-e93ee3abfb42"], ["LIMIT", 1]]
  User Create (0.6ms)  INSERT INTO "users" ("organization_id", "name", "email", "encrypted_password", "reset_password_token", "reset_password_sent_at", "remember_created_at", "created_at", "updated_at", "jwt_token") VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10) RETURNING "id"  [["organization_id", "77cb0fdc-d535-435a-a1b3-e93ee3abfb42"], ["name", "User Test-10"], ["email", "email-test-user10@email.com"], ["encrypted_password", "[FILTERED]"], ["reset_password_token", "[FILTERED]"], ["reset_password_sent_at", "[FILTERED]"], ["remember_created_at", nil], ["created_at", "2023-05-01 14:56:15.947525"], ["updated_at", "2023-05-01 14:56:15.947525"], ["jwt_token", "[FILTERED]"]]
  TRANSACTION (0.7ms)  COMMIT
=> 1..10
irb(main):056:0>

Can you imagine this? like retrieve data above, What about if the amount like 100 or 1000? I think the query is so horrible too 🤣

How to solve and fix it

Retrieve Data using Include or Preload

So, with include or preload the code can be write like this

# preload
users = User.preload(:organization).where(organization_id: params[:organization_id]).limit(10)
users.each do |user|
  puts "#{user.name} from #{user.organization.name}"
end

# =================================================================

# include
users = User.includes(:organization).where(organization_id: params[:organization_id]).limit(10)
users.each do |user|
	puts "#{user.name} from #{user.organization.name}"
end

Result from console

# use preload
irb(main):017:0> users = User.preload(:organization).where(organization_id: params[:organization_id]).limit(10)
  User Load (1.3ms)  SELECT "users".* FROM "users" WHERE "users"."organization_id" = $1 LIMIT $2  [["organization_id", "77cb0fdc-d535-435a-a1b3-e93ee3abfb42"], ["LIMIT", 10]]
  Organization Load (0.5ms)  SELECT "organizations".* FROM "organizations" WHERE "organizations"."id" = $1  [["id", "77cb0fdc-d535-435a-a1b3-e93ee3abfb42"]]
=> [#<User id: "43376f12-55c3-4e12-85cf-50f94a6e3ddd", organization_id: "77...
irb(main):018:1* users.each do |user|
irb(main):019:1* !puts "#{user.name} from #{user.organization.name}"
irb(main):020:0> end
User Dummy from Firdaus Foundation
user2 from Firdaus Foundation
user3 from Firdaus Foundation
user4 from Firdaus Foundation
user6 from Firdaus Foundation
user7 from Firdaus Foundation
user19 from Firdaus Foundation
user20 from Firdaus Foundation
user21 from Firdaus Foundation
user22 from Firdaus Foundation

# use includes
irb(main):021:0> users = User.includes(:organization).where(organization_id: params[:organization_id]).limit(10)
  User Load (1.1ms)  SELECT "users".* FROM "users" WHERE "users"."organization_id" = $1 LIMIT $2  [["organization_id", "77cb0fdc-d535-435a-a1b3-e93ee3abfb42"], ["LIMIT", 10]]
  Organization Load (0.6ms)  SELECT "organizations".* FROM "organizations" WHERE "organizations"."id" = $1  [["id", "77cb0fdc-d535-435a-a1b3-e93ee3abfb42"]]
=> [#<User id: "43376f12-55c3-4e12-85cf-50f94a6e3ddd", organization_id: "77...
irb(main):022:1* users.each do |user|
irb(main):023:1* !puts "#{user.name} from #{user.organization.name}"
irb(main):024:0> end
User Dummy from Firdaus Foundation
user2 from Firdaus Foundation
user3 from Firdaus Foundation
user4 from Firdaus Foundation
user6 from Firdaus Foundation
user7 from Firdaus Foundation
user19 from Firdaus Foundation
user20 from Firdaus Foundation
user21 from Firdaus Foundation
user22 from Firdaus Foundation

The console shows query only executes twice when the beginning example above (N+1) executes more than twice.

Using a preload or includes is look same, isn’t? in another article I will show you and we can learn depth about include, preload, eager_load until join and how to filter the data 😉

Insert data using bulk insert

How about insert data? can you imagine the solution insert without iteration? we can use bulk insert like insert_all or upsert_all

Here the example

# build data first before insert
organizations = (1..10).map do |i|
  { name: "Organization-#{i}", email: "email-test-organization#{i}@email.com", phone: "0851234134#{i}" }
end

# insert_all example
irb(main):026:0> Organization.insert_all(organizations)
  Organization Bulk Insert (9.4ms)  INSERT INTO "organizations" ("name","email","phone","created_at","updated_at") VALUES ('Organization-1', 'email-test-organization1@email.com', '08512341341', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP), ('Organization-2', 'email-test-organization2@email.com', '08512341342', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP), ('Organization-3', 'email-test-organization3@email.com', '08512341343', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP), ('Organization-4', 'email-test-organization4@email.com', '08512341344', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP), ('Organization-5', 'email-test-organization5@email.com', '08512341345', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP), ('Organization-6', 'email-test-organization6@email.com', '08512341346', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP), ('Organization-7', 'email-test-organization7@email.com', '08512341347', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP), ('Organization-8', 'email-test-organization8@email.com', '08512341348', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP), ('Organization-9', 'email-test-organization9@email.com', '08512341349', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP), ('Organization-10', 'email-test-organization10@email.com', '085123413410', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP) ON CONFLICT  DO NOTHING RETURNING "id"
=>
#<ActiveRecord::Result:0x000000010b7100f0
 @column_types={"id"=>#<ActiveRecord::ConnectionAdapters::PostgreSQL::OID::Uuid:0x000000010a11c320 @limit=nil, @precision=nil, @scale=nil>},
 @columns=["id"],
 @hash_rows=nil,
 @rows=
  [["c442d5f6-4638-4b7c-b3e5-ae5117091b8c"],
   ["009152de-9d85-4e09-a061-f76d896b93d0"],
   ["b4be1371-bf83-4297-961f-1340ec29369b"],
   ["69aee4b3-2e63-4890-a635-8fe22c8ee383"],
   ["6b2a3fe9-c143-4a79-aad0-3ef5366ad2fb"],
   ["7db312cd-05aa-4872-9b01-5455494bd606"],
   ["6c74a59d-9fc5-4528-a48f-fc268d9da328"],
   ["e79c55e2-95c1-49db-9fa8-82daca1c1c09"],
   ["e449ef83-2c60-4ff3-8eb9-c8d261c03f4e"],
   ["036e0307-379d-468f-82dd-f58f0f6f076e"]]>
irb(main):027:0>


# upsert_all example
irb(main):031:0> Organization.upsert_all(organizations)
  Organization Bulk Upsert (5.5ms)  INSERT INTO "organizations" ("name","email","phone","created_at","updated_at") VALUES ('Organization-11', 'email-test-organization11@email.com', '085123413411', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP), ('Organization-12', 'email-test-organization12@email.com', '085123413412', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP), ('Organization-13', 'email-test-organization13@email.com', '085123413413', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP), ('Organization-14', 'email-test-organization14@email.com', '085123413414', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP), ('Organization-15', 'email-test-organization15@email.com', '085123413415', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP), ('Organization-16', 'email-test-organization16@email.com', '085123413416', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP), ('Organization-17', 'email-test-organization17@email.com', '085123413417', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP), ('Organization-18', 'email-test-organization18@email.com', '085123413418', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP), ('Organization-19', 'email-test-organization19@email.com', '085123413419', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP), ('Organization-20', 'email-test-organization20@email.com', '085123413420', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP) ON CONFLICT ("id") DO UPDATE SET updated_at=(CASE WHEN ("organizations"."name" IS NOT DISTINCT FROM excluded."name" AND "organizations"."email" IS NOT DISTINCT FROM excluded."email" AND "organizations"."phone" IS NOT DISTINCT FROM excluded."phone") THEN "organizations".updated_at ELSE CURRENT_TIMESTAMP END),"name"=excluded."name","email"=excluded."email","phone"=excluded."phone" RETURNING "id"
=>
#<ActiveRecord::Result:0x000000010c4103e0
 @column_types={"id"=>#<ActiveRecord::ConnectionAdapters::PostgreSQL::OID::Uuid:0x000000010a11c320 @limit=nil, @precision=nil, @scale=nil>},
 @columns=["id"],
 @hash_rows=nil,
 @rows=
  [["1bdac9be-4b64-4d76-b4bf-bf53fc5cd56e"],
   ["c9a841c2-fec5-4232-aa33-7dbd5496677f"],
   ["4de06979-2ecf-4ef0-9f7b-22d45b34a98c"],
   ["c1351b5b-b161-41ad-9c73-e59f3f69a435"],
   ["c00eb8a6-93c1-41f7-a4f6-8b712d3d26a4"],
   ["03204b45-edda-4cae-ba1f-1e36866f2703"],
   ["4d4a6577-6386-4ff5-9b27-aec69f2e95ce"],
   ["98646c8b-9fdc-4e61-93eb-37209bc56cdd"],
   ["1d6f67f9-05a0-4403-8c2d-ac30a315695f"],
   ["385a347b-fb5e-48cb-8329-5101d3f30459"]]>
irb(main):032:0>

How looks? familiar right?, insert_all and upsert_all is familiar (in another article I will explain what is the difference). Do you aware of why I created an example by inserting organization? yeah because when I want to insert password in devise, there was an error 🤣

users = (1..10).map do |i|
  { organization_id: params[:organization_id], name: "User Test-#{i}", email: "1-email-test-user#{i}@email.com", password: "password123" }
end

# insert_all
irb(main):071:0> User.insert_all(users)
/Users/mamxalf/.rbenv/versions/3.2.1/lib/ruby/gems/3.2.0/gems/activerecord-7.0.4.2/lib/active_record/insert_all.rb:264:in `extract_types_from_columns_on': unknown attribute 'password' for User. (ActiveModel::UnknownAttributeError)
raise UnknownAttributeError.new(model.new, unknown_column) if unknown_column
            ^^^^^

# upsert_all
irb(main):072:0> User.upsert_all(users)
/Users/mamxalf/.rbenv/versions/3.2.1/lib/ruby/gems/3.2.0/gems/activerecord-7.0.4.2/lib/active_record/insert_all.rb:264:in `extract_types_from_columns_on': unknown attribute 'password' for User. (ActiveModel::UnknownAttributeError)
raise UnknownAttributeError.new(model.new, unknown_column) if unknown_column
            ^^^^^
irb(main):073:0>

So, what is the solution? the solution is we can use Active Record Import

users = (1..10).map do |i|
  User.new({ organization_id: params[:organization_id], name: "User Test-#{i}", email: "test-user-email#{i}@email.com", password: "password123" })
end

irb(main):020:0> User.import(users)
  User Create Many (10.8ms)  INSERT INTO "users" ("organization_id","name","email","encrypted_password","reset_password_token","reset_password_sent_at","remember_created_at","created_at","updated_at","jwt_token") VALUES ('009152de-9d85-4e09-a061-f76d896b93d0','User Test-1','test-user-email1@email.com','$2a$12$DLnrn84uc53ixY4aUA/iz.hYjgGuIXkDnQBVEGwN8vCKDbja/qJ6e',NULL,NULL,NULL,'2023-05-01 23:37:36.880807','2023-05-01 23:37:36.880807',NULL),('009152de-9d85-4e09-a061-f76d896b93d0','User Test-2','test-user-email2@email.com','$2a$12$vzHB5dvFWA.ilkiMr2TsEu0Ut8p4IG.bS1nL4CIWVNmOzz0.OeTIy',NULL,NULL,NULL,'2023-05-01 23:37:36.880807','2023-05-01 23:37:36.880807',NULL),('009152de-9d85-4e09-a061-f76d896b93d0','User Test-3','test-user-email3@email.com','$2a$12$tcO1ILeD8Kwv.8rEwlOUhOKtvy28DCXmhOdL5LMjYf4Ze.GwFEsmC',NULL,NULL,NULL,'2023-05-01 23:37:36.880807','2023-05-01 23:37:36.880807',NULL),('009152de-9d85-4e09-a061-f76d896b93d0','User Test-4','test-user-email4@email.com','$2a$12$V7OfvMFjMyJdGs5CBv0./.NsWiAElXpbbgCAGHjhsHTNEe7yRzxe2',NULL,NULL,NULL,'2023-05-01 23:37:36.880807','2023-05-01 23:37:36.880807',NULL),('009152de-9d85-4e09-a061-f76d896b93d0','User Test-5','test-user-email5@email.com','$2a$12$9D090bkTPhjDVnwnZeBv8uMfbZV5y1cTGzBFDNNBegJupfUijQIA2',NULL,NULL,NULL,'2023-05-01 23:37:36.880807','2023-05-01 23:37:36.880807',NULL),('009152de-9d85-4e09-a061-f76d896b93d0','User Test-6','test-user-email6@email.com','$2a$12$33iFC2jveyvUpeNToutHMOKSNyh1nlucaPakkBdvPDztlE/9uW2au',NULL,NULL,NULL,'2023-05-01 23:37:36.880807','2023-05-01 23:37:36.880807',NULL),('009152de-9d85-4e09-a061-f76d896b93d0','User Test-7','test-user-email7@email.com','$2a$12$ntX6JVvwzn7XS2O.KTmj4OxMWbopXIt9RrQlOke6VJqwmkw6g1EYG',NULL,NULL,NULL,'2023-05-01 23:37:36.880807','2023-05-01 23:37:36.880807',NULL),('009152de-9d85-4e09-a061-f76d896b93d0','User Test-8','test-user-email8@email.com','$2a$12$Wr8lcVaZ9EyC66Y9GVfM/.U4S/0ZB0AOFraIUOJFS4IRPZv0TdZjm',NULL,NULL,NULL,'2023-05-01 23:37:36.880807','2023-05-01 23:37:36.880807',NULL),('009152de-9d85-4e09-a061-f76d896b93d0','User Test-9','test-user-email9@email.com','$2a$12$XX2NgEi2TbbRCgxcFfIf/uYg0uuzDDZzXoBzK2LBnvhhXROsJRVra',NULL,NULL,NULL,'2023-05-01 23:37:36.880807','2023-05-01 23:37:36.880807',NULL),('009152de-9d85-4e09-a061-f76d896b93d0','User Test-10','test-user-email10@email.com','$2a$12$dM4Z4o79tbT.jAwSotV//.ksCu01suB5SGRhO3tr3dDv.nDgntq9e',NULL,NULL,NULL,'2023-05-01 23:37:36.880807','2023-05-01 23:37:36.880807',NULL) RETURNING "id"
=>
#<struct ActiveRecord::Import::Result
 failed_instances=[],
 num_inserts=1,
 ids=
  ["bbd52abd-59a0-40d2-854c-e53f755364ad",
   "c86b470e-5ed9-4152-9d51-f31a0673950f",
   "c3924f48-3a86-48ca-be6a-08f39c80976b",
   "1df7bc61-15fb-4431-a07b-e93c8ee45be7",
   "a64828a3-6272-44a8-857c-ae5dcd539b3f",
   "7c016f6e-6f8d-4eb9-95b4-e1c12c1d078d",
   "c83c5db0-51e5-43b3-8c5c-f308d2c12c9b",
   "fd1bb8fb-73c7-4b2f-b22c-5d3c082ff97a",
   "d2c20eb2-9c66-4e31-93fb-af88e52e3de7",
   "ac699437-5c68-4daa-882e-71d3a3d6ae20"],
 results=[]>
irb(main):021:0>

Looks simple and easy, right? That is so beautiful way how to insert with bulk. (in another article I will explain more about Active record Import and the difference between insert_all and upsert_all too.

maybe enough for part-1, and see you in part-2 with how to prevent N+1 in Rails development.

Bye bye 👋