DEV Community: Anh Nguyen

[Boost]

Anh Nguyen — Sat, 01 Mar 2025 10:22:24 +0000

Stop One-By-One Inserts! Bulk Insert Data In Rails With insert_all Or activerecord-import ⚡

Anh Nguyen ・ Mar 1

#rails #database #performance #beginners

Stop One-By-One Inserts! Bulk Insert Data In Rails With insert_all Or activerecord-import ⚡

Anh Nguyen — Sat, 01 Mar 2025 05:42:34 +0000

If your CSV import is slower than dial-up internet, discover how bulk inserts can rocket your Rails performance. 🐢

1. Why Bulk Inserts Matter?

When dealing with large datasets for example, you want to import a CSV containing more than a thousand of tasks from your department, inserting records one by one (.create or .save) can drastically slow down performance and cause database bottlenecks

Imagine inserting 100,000 records—doing it traditionally means 100,000 separate queries! Instead, bulk inserting reduces this to a single efficient query, saving time and resources.

2. Prerequisites

Before diving in, ensure:

You're using Rails 6+ for insert_all
You have the activerecord-import gem installed if using an older version which supports Rails 3.x

3. Step-by-Step Guide

3.1 The Problem With Each-By-Each Inserts

For example, you want to create 100k records of tasks by a naive way, this is how it look like:

  # Create 100k records each times
  100_000.times do |i|
    Task.create!({ name: "Task #{i}", description: "This is description of task #{i}", is_finished: false })
  end

I did some benchmark here with this snippet:

  execution_time = Benchmark.measure do
    100_000.times do |i|
      Task.create!({ name: "Task #{i}", description: "This is description of task #{i}", is_finished: false })
    end
  end

  puts "Execution time: #{execution_time.real} seconds"

Image shows that the execution of record creation takes 20.99 seconds

✅ Pros:

Models validation or callbacks works - the name field is not null but there is a call with null name, the insert will be raise with errors
Older Rails versions < 6.0 support - Legacy project without bulk insert support.

❌ Cons:

Performance Issue - Each insert requires a separate database transaction, increasing overhead.
Increased Network Traffic - 100k request will be made into the database which increases network costs.

3.2 Solution 1: Use `insert_all`

The insert_all method allows you to insert multiple records at once, significantly boosting performance.

Example: Using insert_all for create 100k tasks

  tasks = []

  # Initialize 100k tasks hash then add to tasks array
  100_000.times do |i|
    tasks << { name: "Task #{i}", description: "This is description of task #{i}", is_finished: false }
  end

  # Bulk insert all the initialized tasks
  Task.insert_all(tasks)

✅ Pros:

Faster than .create - Single SQL query
Rails built-in method - Bulk operations directly in Rails without extra gems.

❌ Cons:

Does not accept ActiveRecord models – Only works with raw hashes.
Bypasses model validations and callbacks – Data integrity must be handled manually.
Cannot handle associations automatically – Requires extra queries to fetch related IDs.

Example: Inserting Tasks that Belong to Department

  departments = [{ name: "Marketing" }] 
  department = Department.insert_all(departments) 
  department_id = department.id # Error raised

  tasks = [{ name: "Task 0", description: "This is the task description", is_finished: false, department_id: ??? }] # missing department_id here

Image shows that no id can be returned after using insert_all

👉 Fix: Manually retrieve department_id before inserting tasks, adding an extra query.

  department = Department.find_by(name: "Marketing") # cost a SQL query
  tasks = [{ name: "Task 0", description: "This is the task description", is_finished: false, department_id: department.id }]

3.3 Solution 2: Best of both worlds - Use `activerecord-import` gem

The import method allows you to insert models with associations

Example: Using import to create 100k tasks in 1 department

  department = Department.new(name: "Marketing")
  tasks = []

  100_000.times do |i|
    tasks << Task.new({ name: "Task #{i}", description: "This is description of task #{i}", is_finished: false })
  end
  department.tasks = tasks

  execution_time = Benchmark.measure do
    Department.import [department]
  end

  puts "Execution time: #{execution_time.real} seconds"

Able to import 1 department with 100k tasks with the use of Active Record Import
Observe the performance compared to insert_all is similar to each other ( About 1.44 seconds )

✅ Pros:

Works with raw columns and arrays of values (fastest)
Works with model objects (faster)
Performs validations (fast)
Performs on duplicate key updates (requires MySQL, SQLite 3.24.0+, or Postgres 9.5+)

❌ Cons:

Need an extra gem installed
ActiveRecord callbacks related to creating, updating, or destroying records (other than before_validation and after_validation) will NOT be called when calling the import method. ( calling separately with run_callbacks as recommended )

4. Conclusion

One rule to remember when inserting a large number of records ( Example: Import a large number of records from a CSV file ) is to avoid creating records one-by-one. Instead, we can consider using activerecord-import or insert_all for a great performance.

5. Reference

activerecord-import: https://github.com/zdennis/activerecord-import
insert_all: https://apidock.com/rails/v6.0.0/ActiveRecord/Persistence/ClassMethods/insert_all

The Pitfalls of Custom Enum Ordering in Prisma Compared to TypeORM & MikroORM

Anh Nguyen — Sun, 02 Feb 2025 05:11:12 +0000

"Who use Prisma in production?" The statement is true in my daily work. During my work on a project with Prisma as ORM, whenever I worked on a feature, I usually get lots of open issues which blocks my development and getting things done.

I came up with this article based on my curiosity on how other ORM can handle the mapping of fields and custom enum ordering equally well. We’ll walk through the steps, highlight the issue, and compare Prisma with its counterparts.

Step-by-Step Guide

1. Understanding the requirement

1.1 Custom ordering requirement

Modern ORMs offer a convenient way to sort by specific fields or enum indices. This is especially handy when you have an enum that represents a strict order (like priority levels: LOW, MEDIUM, HIGH).

Why this matters: If you want items always ordered by HIGH first, then MEDIUM, then LOW, you might rely on the enum’s internal indexing or a custom field to achieve consistent sorting.

1.2 Mapped fields requirement

Prisma maps fields in your schema (e.g., someField) to columns in your database (e.g., some_field). Under most circumstances, Prisma automatically translates the naming between your application layer (camelCase) and your database layer (snake_case).

Why this matters: SQL is case-insensitive, and many databases automatically convert unquoted identifiers to lowercase when creating tables. This means that any capitalization used in camelCase is lost, obscuring the intended separation between words. Using snake_case ensures that word boundaries are clearly maintained, making your database schema more portable and consistent across different SQL databases.

2. The Drawback: Using Raw SQL for Custom Enum Order

When you need to order by an enum’s indexed order that isn’t directly supported by Prisma’s standard query capabilities, you might resort to a custom raw SQL query:

const query = Prisma.$queryRaw`
    SELECT * 
    FROM tasks
    ORDER BY 
      CASE 
        WHEN severity = HIGH THEN 1
        WHEN severity = NORMAL THEN 2
        WHEN severity = LOW THEN 3
        ELSE 999999
      END,
      created_at DESC
  `;

The issue: The result returned from prisma.$queryRaw will not automatically map column names from snake_case to camelCase. This means you end up with fields like my_enum_column in your JavaScript objects instead of the expected myEnumColumn.

3. Why This Becomes a Problem

When your application logic expects a property named myEnumColumn (camelCase), but the database returns my_enum_column (snake_case), you have to manually handle that conversion. This extra step can introduce errors or force you to create unnecessary helper functions to remap fields.

Inconsistent Code: You’ll have to remember which parts of your code deal with raw SQL (snake_case) versus Prisma’s default query system (camelCase).
Extra Boilerplate: You might add transformations to your service layer just to rename fields properly.

4. Alternatives in TypeORM or MikroORM

Other ORMs such as TypeORM or MikroORM often provide decorators or built-in mechanisms to handle custom ordering directly in their query builder or entity configuration.

TypeORM:

createQueryBuilder(Task, "task")
  .orderBy(`
    CASE 
      WHEN severity = "HIGH" THEN 1
      WHEN severity = "MEDIUM" THEN 2
      WHEN severity = "LOW" THEN 3
    END
  `, 'ASC')
  .getMany();

Even if it involves writing some SQL logic, TypeORM’s query builder often keeps the field mapping in sync with your entity definitions.

MikroORM:

const caseExpression = `
    CASE 
      WHEN severity = "HIGH" THEN 1
      WHEN severity = "MEDIUM" THEN 2
      WHEN severity = "LOW" THEN 3
    END
  `;
const allTasks = await em.find(Task, {}, { orderBy: { [raw(caseExpression)]: 'ASC' } });

MikroORM’s entity definitions and naming strategies often ensure consistent field naming without requiring raw SQL queries.

Why They Might Work Better: In these ORMs, you often define the enum fields and the ordering logic in your entity configuration (ex: Task.entity.ts). If you write a custom expression, you’re still able to leverage the ORM’s mapping layer, thus avoiding the mismatch of camelCase vs. snake_case.

5. My workarounds in Prisma

If you’re heavily invested in Prisma and still want to achieve custom enum ordering without dealing with mismatched field names:

Write a small utility function that converts snake_case keys to camelCase after you execute a raw SQL query, ensuring consistency.

type CamelCase<S extends string> =
  S extends `${infer P1}_${infer P2}${infer P3}`
    ? `${Lowercase<P1>}${Uppercase<P2>}${CamelCase<P3>}`
    : Lowercase<S>;

type CamelizeKeys<T> = {
  [K in keyof T as CamelCase<string & K>]: T[K];
};

export function camelize<T extends Record<string, any>>(
  obj: T,
): CamelizeKeys<T> {
  const result: any = {};

  for (const key in obj) {
    if (Object.prototype.hasOwnProperty.call(obj, key)) {
      const camelKey = key.replace(/_([a-z])/g, (_, letter) =>
        letter.toUpperCase(),
      );
      result[camelKey] = obj[key];
    }
  }

  return result;
}

Conclusion

Handling custom enum ordering is a common need in many applications. With TypeORM or MikroORM, you can often maintain a consistent field mapping while still using a flexible query builder for sorting. Prisma, however, requires a raw SQL approach for more complex enum sorting logic, leading to a mismatch between camelCase properties in your application and snake_case columns in your database.

I encourage you to try out these steps and see which approach works best in your application. Try to avoid using Prisma on production, you can consider 2 options above to help your application get things done.

DEV Community: Anh Nguyen

[Boost]

Stop One-By-One Inserts! Bulk Insert Data In Rails With insert_all Or activerecord-import ⚡

Anh Nguyen ・ Mar 1

Stop One-By-One Inserts! Bulk Insert Data In Rails With insert_all Or activerecord-import ⚡

1. Why Bulk Inserts Matter?

2. Prerequisites

3. Step-by-Step Guide

3.1 The Problem With Each-By-Each Inserts

3.2 Solution 1: Use insert_all

3.3 Solution 2: Best of both worlds - Use activerecord-import gem

4. Conclusion

5. Reference

The Pitfalls of Custom Enum Ordering in Prisma Compared to TypeORM & MikroORM

Step-by-Step Guide

1. Understanding the requirement

1.1 Custom ordering requirement

1.2 Mapped fields requirement

2. The Drawback: Using Raw SQL for Custom Enum Order

3. Why This Becomes a Problem

4. Alternatives in TypeORM or MikroORM

5. My workarounds in Prisma

Conclusion

3.2 Solution 1: Use `insert_all`

3.3 Solution 2: Best of both worlds - Use `activerecord-import` gem