How to increment database table column

Questions / Help
,
1

I know in elixir data types are immutable. I have a column name failed_login_tries. which is integer and i want to increment it every time user put incorrect password. this is the code

 defp check_chromatic_password(plaintext, %User{} = user) do
   if Bcrypt.checkpw(plaintext, user.local_password_hash) do
      {:ok, user}
   else
     {:error, :invalid_password}
   end
 end

failed_login_tries is inside the %User struct

2

You can change the user struct by using user = %{ user | failed_login_tries: user.failed_login_tries + 1} and to persist this in the database use Repo.update by passing it an updated ecto changeset. Immutability refers to the fact that the previous struct remains the same. e.g.

defmodule User do
  defstruct [:email]
end

u1 = %User{email: "mujju"}
u2 = u1
u2 = %{u2 | email: "danny"}

IO.inspect(u1)
# => %User{email: "mujju"}
IO.inspect(u2)
# => %User{email: "danny"}
1 Like
3

Just be aware that there is an inherent race condition with that approach: if the user fails auth in quick succession twice both may have the user record with the current count prior to either auth attempt, and so both will increment the old value by one and update the db with that, causing the N attempts to be counted as just 1!

The correct way to do this is in the database itself using a fragment as in:

from(u in User, 
     update: [set: [failed_login_tries: fragment("failed_login_tries + 1")]],
     where: u.id == user.id) )
|> MyRepo.update_all([])

This way the db sorts out the concurrent updates and you will always end up with the correct count.

11 Likes
4

There’s an even simpler option to write that query:

from(u in User, update: [inc: [failed_login_tries: 1]], where: u.id == user.id) )
25 Likes
5

I always forget about inc (and friends) … thanks for the reminder!

2 Likes
6

This way of expressing a query, does that mean I can only get this update passing it to an update_all([])?

At first I was trying to pass it to a one() since I know I will get only one result, but it doesn’t seem to work.

7

That’s correct.

8

Is there a “correct” way to update the timestamps() (:updated_at) when doing this? I mean I know I can add set: [updated_at: ....] to the query and pass the current time but that doesn’t seem “correct”.

9

It is though. Updating timestamps is a “schema” feature, which is not supported by the lower level *_all api’s on the repo.

10

Does it use the “elixir time” or the “database time” for this? If it uses “database time” then I’d need the same to pass to the set: [updated_at: <here>]. I am not yet the whole codebase savvy enough to quickly find and verify it in the source tree…

11

Timestamps use the autogenerate functionality of ecto schema fields, which is runtime functionality. So those are not set on the db.

1 Like
12

What do you think of the approach shown here:

Right - locking is costly but in this particular case it might be even seen as an advantage?

13

You can use Optimistic Lock feature; This keeps you away from races invalid results and also let you have timestamps functionality in place.

1 Like