How to do atomic increment/decrement with Elixir/SQLAlchemy

Question

I'd like to increment (or decrement) a score field in an Elixir entity:

class Posting(Entity):

  score = Field(Integer, PassiveDefault(text('0')))

  def upvote(self):
      self.score = self.score + 1

However, this doesn't work reliably with concurrent calls to upvote. The best I could come up with is this ugly mess (basically constructing an SQL UPDATE statement with SQLAlchemy):

def upvote(self):
    # sqlalchemy atomic increment; is there a cleaner way?
    update = self.table.update().where(self.table.c.id==self.id)
    update = update.values({Posting.score: Posting.score + 1})
    update.execute()

Do you see any problems with this solution? Are there cleaner ways to achieve the same?

I'd like to avoid using DB locks here. I'm using Elixir, SQLAlchemy, Postgres.

Update

Here is a variant which is derived from vonPetrushev's solution:

def upvote(self):
    Posting.query.filter_by(id=self.id).update(
        {Posting.score: Posting.score + 1}
    )

This is somewhat nicer than my first solution but still requires to filter for the current entity. Unfortunately, this does not work if the Entity is spread over multiple tables.

vonPetrushev · Accepted Answer · 2015-05-04T10:36:23.577

I'll try, but I'm not sure if this meets your needs:

session.query(Posting).\
    .filter(Posting.id==self.id)\
    .update({'score':self.score+1})

You might wanna do session.commit() right after it?

EDIT: [concerning the question's update]

If the Posting is derived from Entity which is class mapped to multiple tables, the solution above still stands, but the meaning of Posting.id attribute is changed, that is, it is no longer mapped to some table's column, but to a different composition. Here: http://docs.sqlalchemy.org/en/latest/orm/nonstandard_mappings.html#mapping-a-class-against-multiple-tables you can see how to define it. I suggest it will be like:

    j = join(entity_table_1, entity_table_2)
    mapper(Entity, j, properties={
        'id': column_property(entity_table_1.c.id, entity_table_2.c.user_id)
        <... some other properties ...>
    })

This is not an atomic operation, the score might have been updated before the query is executed. — Nicklas A., Sep 06 '12 at 17:08

How to do atomic increment/decrement with Elixir/SQLAlchemy

1 Answers1