1

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?

chovy
  • 72,281
  • 52
  • 227
  • 295

1 Answers1

3

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

RonZ
  • 367
  • 2
  • 8
  • `{ ...item, created_at: this.r.now()}, ^^^ SyntaxError: Unexpected token ...` – chovy Apr 03 '17 at 23:39
  • neither example work. I changed the spread to `Object.assign()` now it throws an error about a missing return statement for both. `ReqlDriverCompileError: Anonymous function returned undefined. Did you forget a return?` – chovy Apr 03 '17 at 23:54
  • Fixed it, I think the spread operator is supported natively on node 6+. Anyway replacing it with Object.assign() is a valid change. The update part is the one in the "conflict" clause. I forgot to return (since it's only one statement the "return" keyword is not necessary but then you must not use curly brackets...), fixed it now. – RonZ Apr 04 '17 at 06:29