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: "[email protected]"}, unique_by: :email, on_duplicate: Arel.sql("email_counter = email_counter + 1"))
which should produce the following SQL,
INSERT INTO "users" ("email") VALUES ("[email protected]") 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 ("[email protected]") 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 ("[email protected]") ON CONFLICT ("email") DO UPDATE SET email_counter = email_counter + 1 RETURNING "id"