Let’s say you were asked by your government to implement a national dog registry in order to help them to enforce their new one-dog-policy. Everyone who wants to buy a dog will need to register it at the municipal government.
Since your government cares a lot about the well-being of the nation’s dogs and generally invests a lot of time and resources into research endeavours, they want to collect some additional data on all the dogs and want all dog owners to check in once a year to keep the information up to date. Because a year is a long time between updates to conduct proper research and also to keep the feedback loop short regarding new dog-happiness policies, the government also plans to equip dogs and dog-related products with sensors in the future.
Setup
Versions: Elixir 1.10.0, ecto 3.3.3, ecto_sql 3.3.4, PostgreSQL 11.5
Let’s start off with this Ecto migration:
defmodule DogRegistry.Repo.Migrations.CreateTables do
use Ecto.Migration
def change do
create table(:owners, primary_key: false) do
add :id, :binary_id, primary_key: true
add :name, :string, null: false
add :city, :string, null: false
timestamps()
end
create table(:dogs, primary_key: false) do
add :id, :binary_id, primary_key: true
add :name, :string, null: false
add :status, :string, null: false
add :owner_id, references(:owners, on_delete: :nilify_all, type: :uuid)
timestamps()
end
create table(:dog_toys, primary_key: false) do
add :id, :binary_id, primary_key: true
add :product, :string
add :rating, :integer
add :dog_id, references(:dogs, on_delete: :nilify_all, type: :uuid)
timestamps()
end
create index(:dog_toys, :dog_id)
end
end
With this we create an owners table, a dogs table and a dog_toys table. A dog belongs to an owner and a dog toy belongs to a dog. The government will need a lot more information on the dogs, of course, including various psychological measures and health statistics, but let’s keep it simple for now.
We also create Ecto schemas for each of these tables, but since they are quite unremarkable, I’ll only give you the Dog schema:
defmodule DogRegistry.Dog do
use Ecto.Schema
import Ecto.Changeset
alias DogRegistry.DogToy
alias DogRegistry.Owner
alias DogRegistry.Repo
@primary_key {:id, :binary_id, autogenerate: true}
@foreign_key_type :binary_id
schema "dogs" do
field :name, :string
field :status, :string
belongs_to :owner, Owner
has_many :toys, DogToy, on_replace: :delete
timestamps()
end
def changeset(dog, attrs) do
dog
|> cast(attrs, [:name, :owner_id, :status])
|> cast_assoc(:toys)
|> validate_required([:name, :owner_id, :status])
|> validate_inclusion(:status, ["alive", "dead"])
|> foreign_key_constraint(:owner_id)
end
end
Partial unique indexes
You decided to handle both dog registrations and updates to existing dogs with a single function. Don’t ask me why, you didn’t tell me.
This means we need to implement this as an upsert. The first thing we need is a unique index on the dogs table to prevent the clerk from inserting a second dog with the same owner. However, one-dog-policy does not mean that you may only have one single dog throughout your entire lifetime. Once your dog died, you can get a new one. This means the unique index may only apply if the dog is alive.
PostgreSQL allows you to define partial indexes to do just that.
In SQL that index would be defined like this:
CREATE UNIQUE INDEX dogs_owner_id_alive_ix ON dogs (owner_id, status)
WHERE status = 'alive';
Ecto.Migration.unique_index/3 allows you to add a where option, so the statement above looks like this in your application:
defmodule DogRegistry.Repo.Migrations.DogsOwnerIdAliveIx do
use Ecto.Migration
def change do
create unique_index(:dogs, [:owner_id, :status],
name: "dogs_owner_id_alive_ix",
where: "status = 'alive'"
)
end
end
The first tests
We create a test module that uses the DataCase module Phoenix creates and sets up some fixtures. The complete code example is linked below. The first describe block only has a simple test to make sure you can insert a new dog into the database:
describe "create_or_update/1" do
test "creates a dog" do
owner = insert_owner!(@owner)
assert {:ok, %Dog{}} = Dog.create_or_update(params(@living_dog_1, owner))
assert [%Dog{}] = Repo.all(Dog)
end
test "allows to add multiple dead dogs" do
owner = insert_owner!(@owner)
assert {:ok, %Dog{}} = Dog.create_or_update(params(@dead_dog_1, owner))
assert {:ok, %Dog{}} = Dog.create_or_update(params(@dead_dog_2, owner))
assert {:ok, %Dog{}} = Dog.create_or_update(params(@living_dog_1, owner))
assert {:ok, %Dog{}} = Dog.create_or_update(params(@dead_dog_3, owner))
assert [_, _, _, _] = Repo.all(Dog)
end
end
The params/2 function does nothing but add the ID of the second argument to the map that is passed as the first argument. I know that you know that there is a pipe operator.
This is the function that passes the tests:
def create_or_update(attrs) do
%__MODULE__{}
|> changeset(attrs)
|> Repo.insert()
end
The upsert test
Now on to the more interesting test case.
test "replaces previous dog if alive" do
owner = insert_owner!(@owner)
assert {:ok, %Dog{}} = Dog.create_or_update(params(@living_dog_1, owner))
assert {:ok, %Dog{}} = Dog.create_or_update(params(@living_dog_2, owner))
assert [%Dog{name: name}] = Repo.all(Dog)
assert name == @living_dog_2.name
end
This looks similar as the previous test, but now you ensure that there is still only one dog in the database after calling the function a second time. This means if there is already a living dog for the owner in the database, the entry will be replaced silently. You slowly come to the conclusion that this behaviour is probably not what you would want in this scenario, but since you are already invested in this example, you decide to continue.
Unsurprisingly, this test fails with the current implementation. To perform an upsert, you can set the :on_conflict option when calling Repo.insert/2. When using PostgreSQL, you also need to set the :conflict_target option to tell PostgreSQL which constraints you expect. This can be a list of columns or the constraint name itself. Since we named the unique index, we can try this:
def create_or_update(attrs) do
%__MODULE__{}
|> changeset(attrs)
|> Repo.insert(
on_conflict: :replace_all,
conflict_target: {:constraint, :dogs_owner_id_alive_ix}
)
end
However, now we're getting this error in all the tests:
** (Postgrex.Error) ERROR 42704 (undefined_object) constraint "dogs_owner_id_alive_ix" for table "dogs" does not exist
What’s going on?
Ecto turns the options above into this SQL fragment:
ON CONFLICT ON CONSTRAINT "dogs_owner_id_alive_ix"`
This is syntactically correct, but PostgreSQL requires you to specify the constraint including the whole WHERE clause. So we need to find a way to tell Ecto to do that. Unfortunately, there is no shortcut for this at the moment, but instead of passing the constraint name, Ecto also allows us to pass an unsafe SQL fragment.
def create_or_update(attrs) do
%__MODULE__{}
|> changeset(attrs)
|> Repo.insert(
on_conflict: :replace_all,
conflict_target:
{:unsafe_fragment, "(owner_id, status) WHERE status = 'alive'"}
)
end
This works!
Associations
Your plan was to update all the associations of the dog (except for the owner) with that same function call. So far, the test fixtures look like this:
@dead_dog_1 %{name: "Clarence", status: "dead"}
@dead_dog_2 %{name: "Penelope", status: "dead"}
@dead_dog_3 %{name: "Christopher", status: "dead"}
@living_dog_1 %{name: "Dwayne", status: "alive"}
@living_dog_2 %{name: "Claire", status: "alive"}
Very simple, no associations. Let’s give Dwayne some toys and see what happens.
@living_dog_1 %{
name: "Dwayne",
status: "alive",
toys: [
%{product: "round chewie thing", rating: 5},
%{product: "long catchie thing", rating: 4}
]
}
Running the tests again, we get a foreign key constraint error for the test create_or_update/1 replaces previous dog if alive:
1) test create_or_update/1 replaces previous dog if alive (DogRegistry.DogTest)
test/dog_registry/dog_test.exs:50
** (Ecto.ConstraintError) constraint error when attempting to insert struct:
* dog_toys_dog_id_fkey (foreign_key_constraint)
If you would like to stop this constraint violation from raising an
exception and instead add it as an error to your changeset, please
call `foreign_key_constraint/3` on your changeset with the constraint
`:name` as an option.
The changeset defined the following constraints:
* dogs_owner_id_fkey (foreign_key_constraint)
The other tests pass, though. So inserting a dog including toys is no problem, but updating an existing dog with toys is.
Let’s remove the toys from the fixtures for a moment and update the test like this:
test "replaces previous dog if alive" do
owner = insert_owner!(@owner)
assert {:ok, %Dog{id: id_1}} =
Dog.create_or_update(params(@living_dog_1, owner))
assert {:ok, %Dog{id: id_2}} =
Dog.create_or_update(params(@living_dog_2, owner))
IO.inspect(id_1, label: "ID1")
IO.inspect(id_2, label: "ID2")
assert [_] = Repo.all(Dog)
end
You will get an output similar to this:
ID1: "6a274d9b-f0c1-4f06-b8db-1ef3c7103da4"
ID2: "7e9f7b9c-7a64-4a96-a0a3-2aaa08360b33"
As it happens, passing on_conflict: :replace_all to Repo.insert/2 will not only replace the values in the changeset, but also generate new values for all autogenerated fields including timestamps and the ID. If we want to keep some of the existing values, we can pass {:replace_all_except, fields} or {:replace, fields} as the option value. Let's change the on_conflict option of create_or_update/1 to:
on_conflict: {:replace_all_except, [:id, :inserted_at]}
We want to keep the original ID, but we also want to keep the inserted_at value, while we are happy to get an updated updated_at value.
If we run the test again, we will still see two different IDs. Why is that?
We configured our application with UUIDs as primary keys. Unlike auto-incrementing integer IDs, UUIDs are auto-generated by Ecto, not in the database (unless you set autogenerate to false in your schema and configure your database to generate them). And since Ecto already knows all values it generated or set itself, it doesn’t ask them from the database when running the query. So in our case, Ecto generates a UUID, sends an insert statement to the database, the database sees a conflict and makes a replacement instead, but keeps the old ID, and this existing ID is not returned back to Ecto.
The fix is easy, though. Repo.insert/2 accepts another option called :returning, which specifies which fields should be returned from the database. If you set returning to true, all fields will be returned.
So the final version of create_or_update/1 looks like this:
def create_or_update(attrs) do
%__MODULE__{}
|> changeset(attrs)
|> Repo.insert(
returning: [:id],
on_conflict: {:replace_all_except, [:id, :inserted_at]},
conflict_target:
{:unsafe_fragment, "(owner_id, status) WHERE status = 'alive'"}
)
end
Now the IDs in the test output will match and the tests will also pass with fixtures including associations.
However, our tests still don't have any assertions about the associations. Let's change that by adding this test:
test "replaces all associations" do
owner = insert_owner!(@owner)
assert {:ok, %Dog{}} = Dog.create_or_update(params(@living_dog_1, owner))
assert {:ok, %Dog{}} = Dog.create_or_update(params(@living_dog_2, owner))
assert [%DogToy{product: "small squeaky thing"}] = Repo.all(DogToy)
end
Similar to the previous test, except that we actually have a look at the dog toys after the upsert. @living_dog_1 had two toys, @living_dog_2 had only one, so we would expect to be only the single toy of dog 2 in the database after the update. But this test fails, the database includes both toys of dog 1 and the toy of dog 2.
The problem is that PostgreSQL can handle the replacement of the dog entry itself, but it doesn't know anything about the associations, and since we only passed an empty struct as the first argument to Ecto.Changeset.changeset/2 in our create_or_update/2 function, Ecto has no way of tracking the associations either.
Now, we could change that by 1) changing the function signature of create_or_update/2 and 2) passing a fully preloaded struct to the function, but that would defeat the purpose of the upsert function, because we could then as easily call a regular update function with that preloaded resource. Which means that if you want to do a complete replacement of the existing associations, you shouldn't use an upsert function after all.
Complete source code: github.com/woylie/article-dog-registry
Don't look directly into the sun.
originally published on Medium
Top comments (0)