Proper Upsert (Atomic Update Counter Field or Insert Document) with RethinkDB

Question

After looking at some SO questions and issues on RethinkDB github, I failed to come to a clear conclusion if atomic Upsert is possible?

Essentially I would like to perform the same operation as ZINCRBY using Redis.

If member does not exist in the sorted set, it is added with increment as its score (as if its previous score was 0.0). If key does not exist, a new sorted set with the specified member as its sole member is created.

The current implementation appears to differ from almost all databases that I have used. With the data being replaced or inserted not updated. This is a simple use case, like update the last visit, update the number of clicks, update a product quantity. So I must be missing something very obvious, because I cannot see a simple way to do this.

Etienne Laurin · Accepted Answer · 2016-11-02T00:45:02.753

1

Yes, it is possible. After get on the key, perform an atomic replace. Something like this might work:

function set_or_increment_score(player, points){
  return r.table('scores').get(player).replace(
    row =>
      { id: player,
        score: r.branch(
                 row.eq(null),
                 points,
                 row('score').add(points))
      });
}

It has the following behaviour:

> set_or_increment_score("alice", 1).run(conn)
{ inserted: 1 }
> set_or_increment_score("alice", 2).run(conn)
{ replaced: 1 }

It works because get returns null when the document doesn't exist, and a replace on a non-existing document tuns into an insert. See the documentation for replace

edited Nov 02 '16 at 00:45

answered Nov 01 '16 at 20:04

Etienne Laurin

6,731
2
27
31

Hi @AtnNm. Thanks for your reply, but I don't fully follow your example. I have nothing to replace therefore the get will return a null or an error. I want to insert a row with the {name:"A", score:1, type:player}. If A is already in the table then I want to take the score and update it. I want to insert the document or in case the document exists to increment the score. If I follow your example that function requires that the player already exists, and creates the score record or increments if it already exists. – Astronaut Nov 01 '16 at 22:18
The use case is similar to Redis zincrby or PouchDB https://github.com/pouchdb/upsert – Astronaut Nov 01 '16 at 22:36
1

The case where the player doesn't exist is handled by an `r.branch` on `row.eq(null)`. I've edited the answer with an example and a link to the documentation. – Etienne Laurin Nov 02 '16 at 00:46
So its actually a replace, there is no update. Ok, good to know. I found a different way to do it using function to resolve the conflict, still seems like a really messed up way to simply update one field, at least compared to other data stores. Rethinking if I actually should use this RethinkDB :/ – Astronaut Nov 02 '16 at 14:15
1

It's different but works well in practice. And if you add a `row.merge` it will only update the fields you want to change and not replace the whole document. – Etienne Laurin Nov 02 '16 at 23:40
@AtnNm I wrote an answer with the code I am using. Tell me what you think of this approach and if there are advantages using your approach instead. Thank you very much for your input, super helpful :) – Astronaut Nov 03 '16 at 11:55

score 0 · Answer 2 · answered Nov 03 '16 at 11:54

So I end up using the following code to go around the no Update issue.

r.db("test").table("t").insert(
  {id:"A", type:"player", species:"warrior", score:0, xp:0, armor:0},
  {conflict: function(id, oldDoc, newDoc) {
       return newDoc.merge(oldDoc).merge(
         {armor: oldDoc("armor").add(1)});
   }
  }
)

Do you think this is more readable/elegant or do you see any issues with the code compared to your sample?

Proper Upsert (Atomic Update Counter Field or Insert Document) with RethinkDB

2 Answers2