MySQL Bulk Updates with Arel

Ariel holding a ruby surrounded by MySQL dolphins

The Context

Consider these 2 models: Group and Event

class Group < ApplicationRecord
  include FlagShihTzu #

  has_many :events
  has_flags 5 => :reminders_enabled # other flags exist we are ignoring
  # reminder_offset: integer (in seconds)

class Event < ApplicationRecord
  belongs_to :group
  # starts_at: datetime
  # remind_at: datetime (will be nil if reminders are disabled)

  before_create do
    self.remind_at = group.reminders_enabled? ? starts_at - group.reminder_offset : nil

The Problem

When you update a group’s default reminder, we also want to update the remind_at time for all of the group’s events.

First attempt do |group|
  event.update(remind_at: group.reminders_enabled? ? starts_at - group.reminder_offset : nil)

This is the most straightforward approach. The downside is that it requires a separate update query for every event. If you have a lot of events, that is a lot of calls to the database O(n). Is there a way to do the same work with a single query? The trouble is that remind_at is calculated from 3 separate values:

  • event.starts_at
  • group.reminders_enabled
  • groups.reminder_offset

So each event will have a different value. This prevents us from doing a simple What options are we left with?

Enter MySQL case statements

MySQL supports using CASE statements within a SET command. How might this look?

UPDATE events
INNER JOIN groups ON = events.group_id
SET events.remind_at =
        WHEN (groups.flags & 16) = 16
        THEN DATE_SUB(events.starts_at, INTERVAL groups.reminder_offset SECOND)
        ELSE NULL

There’s a lot going on here, let’s break it down.

(groups.flags & 16) = 16

We are storing flags in an integer column with the flag_shih_tzu gem, where each flag is a different bit. If reminder_enabled is stored on bit 5, it means that the fifth bit of the integer column will be used to determine whether the reminder is enabled or not. In binary representation, this would mean the value 00010000, or 16 in decimal notation.

To check if the flag is enabled, you would perform a bitwise AND (&) operation between the column value and 16 (which will return either 0 or 16) and see if the result is 16.

DATE_SUB(events.starts_at, INTERVAL groups.reminder_offset SECOND)

MySQL provides really helpful date arithmetic functions. DATE_SUB lets us subtract an interval from the starts_at date. The final INTERVAL n SECOND statement is used to coerce the plain integer value into something DATE_SUB understands.

This is really cool so far, but mixing raw SQL statements with ActiveRecord doesn’t always feel amazing. Is there another option that keeps us in ActiveRecord land?

Enter Arel

Arel is a lesser-known but incredibly powerful companion to ActiveRecord. It lets us build out complex SQL in plain ruby.

Arel’s Building Blocks

  1. Arel Tables

    Using arel_table, we are able to treat our models like tables. This gives us the ability to refer to columns as if we were dealing with SQL directly:

  2. Bitwise Operations

    To check if a flag is enabled in a bitwise column:

     (Group.arel_table[:flags] & bit).eq(bit)
  3. Conditional Logic with Case

    Arel’s Nodes::Case lets us construct a SQL CASE statement:
  4. Date Arithmetic

    For date subtraction, we can utilize Arel’s capability to create custom SQL functions:
       [starts_at, interval_expression(offset)],
  5. Building Raw SQL Expressions

    Sometimes we need to craft custom SQL expressions. Arel’s SqlLiteral makes this possible:"INTERVAL ... SECOND")

The Final Class

By combining these concepts, we can create a new class using plain old ruby and ActiveRecord.

class ApplyGroupDefaultsToEvents
  def update_reminder_offsets(events)


  def remind_at

  def group_flag_enabled(flag)
    bit = Group.flag_mapping["flags"][flag]
    (Group.arel_table[:flags] & bit).eq(bit)

  def date_subtraction(starts_at, offset)
      [starts_at, interval_expression(offset)]

  def interval_expression(offset)
      "INTERVAL `#{}`.`#{}` SECOND"


Isn’t that cool! I’ve been writing Rails code for over 10 years and am just now learning about Arel. That is what I love about this job, it feels like I learn something new every day.

So the next time you find yourself writing plain SQL strings in your Rails code, give Arel a try instead.