Handle some rows differently on upsert -> exists?

Question

I'm using Rails 6 and the new upsert_all method.

This is super useful, however, there are some questions I have about how to add columns only if it's a new record. Take for instance, the created_at column.

If the record is new, you would want to add this (as well as any other null: false columns without a default: ...

But, I also don't want to change created_at if I'm updating.

How would I handle this other than removing the column?

Answer 1

TLDR: Use the unique_by parameter

Seeing as upsert_all does not load the model, in order to treat some rows (i.e. db records) differently you'll need to use the unique_by parameter along with a database column(s) that has a unique_by clause.

Full Example

For example, suppose we have a Book model that has an id, name and age. I want to both insert a few new books and update the age of one or more books. The name column has no default value and cannot be null.

# books table
# id: 1, name: 'dracula', age: 10
# id: 2, name: 'jane eyre', age: 20
# id: 3, name: 'ulysses', age: 30

I could do:

Book.upsert_all([
  {id: 2, age: 40}, # <- updates this record
  {name: 'war and peace', age: 35 }, # <- new record
  {name: '1984', age: 45} # <- new record
], unique_by: :id)

Above, note that I passed the unique_by: :id argument. This instructed the database to update these records instead of creating them.

The result would be:

# books table
# id: 1, name: 'dracula', age: 10
# id: 2, name: 'jane eyre', age: 40
# id: 3, name: 'ulysses', age: 30
# id: 4, name: 'war and peace', age: 35
# id: 5, name: '1984', age: 45

I could use any database column as long it has as unique index (source). For example, if I had a unique index on the name column, this query would give a similar result:

Book.upsert_all([
  {name: 'jane eyre', age: 40}, # <- updates this record
  {name: 'war and peace', age: 35 }, # <- new record
  {name: '1984', age: 45} # <- new record
], unique_by: :name)

Finally, Rails will handle the updated_at and created_at properties as expected.