Back

Always Generated Boolean tied to a date, smart deleted states in sql

It's a common scenario to not only know that something happened, but also when it happened. Such is the common case with something like the following example of marking a user as deleted?: true. But, rather than relying on keeping two attributes up to date (deleted and deleted_at) we can rely on the database to have an automatically generated boolean to handle that for us.

The following example implements a reversable database migration, field, and function used to mark a user as deleted (at the current timestamp) and undeleted (or reactivated). With all three implemented we'll be able to tell if a user is deleted user.deleted? == true and toggle a user's deletion delete_user(current_user) and undelete_user(deleted_user).

# mix ecto.gen.migration add_deleted_to_user
defmodule App.Repo.Migrations.AddDeletedToUser do
  use Ecto.Migration

def change do
alter table(:users) do
add(:deleted_at, :naive_datetime)
end


execute(
"alter table users add column deleted boolean generated always as (deleted_at is null) stored",
"alter table users drop column deleted"
)
end
end

# App.Accounts.User
field(:deleted?, :boolean, read_after_writes: true, source: :deleted)
field(:deleted_at, :naive_datetime)
  def delete_user(%User{} = user),
    do:
      user
      |> User.changeset(%{deleted_at: NaiveDateTime.utc_now()})
      |> Repo.update()
  
  def undelete_user(%User{} = user),
    do:
      user
      |> User.changeset(%{deleted_at: nil})
      |> Repo.update()
Back