How to increment database table column

script

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

12 comments

#ecto #structs

0 7823 12

2023-09-21 19:42:56 UTC

Showing Posts 1 to 10

Show Best Posts
Show All (oldest first)
Show All (newest first)

minhajuddin

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"}

Post #1

aseigo

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.

Post #2

LostKobrakai

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) )

Post #3

aseigo

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

Post #4

tjdam

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.

Post #5

benwilson512

Author of Craft GraphQL APIs in Elixir with Absinthe

That’s correct.

Post #6

silverdr

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”.

Post #7

LostKobrakai

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

Post #8

silverdr

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..

Post #9