How does the upsert function in Rails work?

Question

As upsert is a 'newer' function that has been implemented in Rails’ ActiveRecord, there is not a lot of documentation behind it. I do understand the concept behind it, as its purpose is to insert a new record if it is a new record and update existing records if it is found in the database.

So for instance, when I call: Model.upsert(name: 'a', number: 1), does ActiveRecord look for a preexisting record with the name: 'a' or a preexisting record with the number: 1?

For example, right now when I am trying to update a Model with the name a using Model.upsert(name: 'a', number: 1), I get a NotNullViolation, because there is a null value in an id that I am not specifying. Can I update a model with upsert without passing in the id as a parameter?

Answer 1

Implementation will be different based on the database targeted. Postgres, for example natively supports upserts via INSERT...ON CONFLICT UPDATE. You should refer to your underlying database's documentation for the gory details. In general, though, ActiveRecord is going to attempt use all unique indexes on the table to determine whether an existing record conflicts or not. At a minimum this means your primary key, though it may mean others as well. If you are using Postgres or SQLite, you can pass :unique_by to upsert to explicitly indicate which fields should be used to determine whether a matching record already exists (doc). Note that if you use this and attempt to insert a record that conflicts with another record on a unique key not listed in unique_by, your database will throw an exception.

I get a NotNullViolation, because there is a null value in an id that I am not specifying.

No, of course not - you must specify all fields that would be inserted during an insert when performing an upsert, because obviously, if the record doesn't exist and must be inserted, it must contain all required fields. If you have fields specified as not null, you can't attempt an upsert of them when the inserted value would be null!

Since you're not passing an ID, Rails can't figure out how to determine record uniqueness. It may work with a unique index on one of your other fields (presuming that unique index is appropriate to your application).

Answer 2

I think the problem with the NotNullViolation you get with the missing id is caused by the underlying DB definition: the upsert method uses all unique columns of the table definition to find a possible match to update instead of creating a new record; in this case you miss an important parameter of the method and the upsert fails.