How can I upsert a document in rethinkdb (reql) by an arbitrary property?

Question

I have an .itemId which comes from a 3rd party, not generated by me.

I need to look for it in the db and update or insert it if one doesn't exist.

I've tried using this example from the cookbook: https://www.rethinkdb.com/docs/cookbook/javascript/#manipulating-documents

  const res = await this.r.table('products').filter({itemId: item.itemId})
    .limit(1)
    .replace(doc => {
      return this.r.branch(
        doc.eq(null),
        this.r.expr(item).merge({created_at: this.r.now()}),
        doc.merge(item).merge({updated_at: this.r.now()})
      )
    }, {
      returnChanges: true
    }).run(this.conn);


  if (res.replaced) {
    return res.changes[0].new_val;
  } else {
    return item; // doc was never inserted as everything in `res` is `0`.
  }

res.changes is undefined if the doc doesn't exist and if I search for the id after it is not in the database. It never got inserted.

Is there a way to simplify upsert() command given an arbitrary property of an object?

Answer 1

In the "else" clause you should do the insert query and the branch clause in your code is useless (the query will never return "null" so the item will not be "created")

There are several ways to approach this: The best approach is to use itemId (or r.uuid(itemId)) as the primary key and do an insert with a conflict clause.

If you can't One approach is to try and replace and if it didn't replace anything insert:

this.r.table('products').filter({itemId: item.itemId})
.limit(1)
.replace(
    doc => doc.merge(item).merge({updated_at: this.r.now()}), 
    { returnChanges: true }
)
.do(res => res('replaced').eq(1).branch(
    res,
    r.table('products').insert(
        { ...item, created_at: this.r.now()}, 
        { returnChanges: true }
    )
))
.run()

Another approach is to try and see if exists and use the index to upsert:

this.r.table('products').filter({itemId: item.itemId})
.nth(0)
.default(null)
.do(res => 
    r.table('products').insert(
        { 
          ...item, 
          id: res('id').default(r.uuid()), 
          created_at: this.r.now()
        }, 
        { 
            returnChanges: true,
            conflict: (id, old, new) => 
                old.merge(item).merge({updated_at: this.r.now()})
        }
    )
))
.run()

Also if you need it to perform I recommend creating a secondary index on itemId and use "getAll" instead of "filter".

These approaches won't help if you have a high probability of getting multiple items with the same itemId at the same time, to fix this you'll need to create a different unique table:

r.table('products_itemId')
 .insert(
    {itemId: item.itemId, id: r.uuid()},
    { returnChanges: true, conflict: (id, old, new) => old }
 )
 .do(res => 
     r.table('products').insert(
        { 
          ...item, 
          id: res('new_val')('id'),
          created_at: this.r.now()
        }, 
        { 
            returnChanges: true,
            conflict: (id, old, new) => 
                old.merge(item).merge({updated_at: this.r.now()})
        }
    )
))
.run()

Notice that you'll have to maintain deletions and updates to the itemId field manually