6

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?

Andrew Marshall
  • 95,083
  • 20
  • 220
  • 214
Alex
  • 115
  • 1
  • 6

2 Answers2

10

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).

Chris Heald
  • 61,439
  • 10
  • 123
  • 137
  • Thank you for the detailed response! So if I am only being given the name attribute and a number that I want to update of a model and I am not told the id, how would I be able to utilize upsert to update a record with the same name in the database? Ex: We ask user to input name of a model they want to update and its respective number we want to update. – Alex Nov 08 '19 at 03:33
  • Is there a unique index on one of the non-ID fields? – Chris Heald Nov 08 '19 at 03:34
  • All names saved in the database would be unique, if that's what you mean. – Alex Nov 08 '19 at 03:36
  • 2
    "Would be unique" and "are enforced unique via a unique index" are two different things. You will want a unique index on your name column, and then you should be able to upsert with `upsert(..., unique_by: :name)`, presuming you're targeting a supported database. – Chris Heald Nov 08 '19 at 03:37
1

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.

Rorrim
  • 190
  • 2
  • 7