ActiveRecord querying has gotten powerful over the years. The introduction of Arel and the ability to chain methods has made it possible to write complex queries in a readable manner. One such method is upsert. This method allows to insert or update records if found.

Let’s consider an example of a user table with a unique constraint on the email column.

ActiveRecord::Schema.define do
  create_table :users, force: true do |t|
    t.integer :email, null: false
    t.integer :email_counter, null: false, default: 1
    t.index :email, unique: true
  end
end

Assume that the table already had some data with duplicate email addresses. So adding an index would not invalidate duplicate records already present.

Now let’s say we run the following command,

User.upsert({email: "bob@example.com"}, unique_by: :email, on_duplicate: Arel.sql("email_counter = email_counter + 1"))

which should produce the following SQL,

INSERT INTO "users" ("email") VALUES ("bob@example.com") ON CONFLICT ("email") DO UPDATE SET email_counter = email_counter + 1 RETURNING "id"

Ideally, the ON CONFLICT clause should trigger when there is a conflict (i.e., a row already exists with the same value for the “email” column). In this case, it will perform an update on the conflicting row. The “SET” clause states that the “email_counter” column should be incremented by 1.

Before

However, when this query is run, an error occurs.

ActiveRecord::RecordNotUnique: SQLite3::ConstraintException: UNIQUE constraint failed: users.email

As the following SQL is produced,

INSERT INTO "users" ("email") VALUES ("bob@example.com") ON CONFLICT ("email") DO NOTHING RETURNING "id"

The incorrect ON CONFLICT clause was generated.

This is because previously, the line of code that executes when @on_duplicate = :skip would disregard the presence of a custom :on_duplicate value if @on_duplicate == :update && updatable_columns.empty?.

However this error would not occur if the unique constraint is not present.

After

After this PR the order of the clauses has been changed.

Now the expected SQL is generated,

INSERT INTO "users" ("email") VALUES ("bob@example.com") ON CONFLICT ("email") DO UPDATE SET email_counter = email_counter + 1 RETURNING "id"