DEV Community: Peter de Croos

Database Modeling With Ecto Part 2 - has many and belongs to Relationships

Peter de Croos — Mon, 17 May 2021 20:56:31 +0000

Previously, we created the initial phoenix app and created a Teacher struct and table.
We went over how to perform the basic CRUD operations as well as how to do some basic tests to verify the functionality.
In part 2, we're going to be adding more structs and defining relationships between them.
We'll also go over how to fetch records with their associations.

If you haven't been following along, you can checkout the code from github and fetch the branch.

git clone git@github.com:cultofmetatron/database-modeling-with-ecto-example.git
git checkout part1

has many and belongs to

We now have a Teacher ecto schema as an interface to out teachers table.
Lets implement a class.
A Class at most belongs to one Teacher.
The standard way to setup a relationship is through foreign keys.
That is a column that matches the a column on another table which msut be unique on the other table.
The type of the foreign key column must match the type of the key on the foreign table that its referencing.

We will create a column teacher_id on the classes table will have a foreign key constraint on the id columnn of the teachers table.
Since the id column of teachers is a uuid, the column here will also be a uuid.
Being the primary key of the table, it alrady satisfies the aformentioned uniqueness contraint.
In ecto, we call it a :binary_id.

$ mix phx.gen.schema Class classes name:string teacher_id:references:teachers subject:string active:boolean
* creating lib/grade_tracker/class.ex
* creating priv/repo/migrations/20210515195753_create_classes.exs

Remember to update your repository by running migrations:

    $ mix ecto.migrate

Now that we've created the associated class and migrations, we need to make some small adjustments.

First lets look at the migration

defmodule GradeTracker.Repo.Migrations.CreateClasses do
  use Ecto.Migration

  def change do
    create table(:classes, primary_key: false) do
      add :id, :binary_id, primary_key: true
      add :name, :string
      add :subject, :string
      add :active, :boolean, default: false, null: false
      add :teacher_id, references(:teachers, on_delete: :nothing, type: :binary_id)

      timestamps()
    end

    create index(:classes, [:teacher_id])
  end
end

Same as teacher, we have an id set with :binary_id which coresponds to the UUID type in postgres.
note, that part for teacher id, we have an on_delete: :nothing.
Change that to


add :teacher_id, references(:teachers, on_delete: :nilify_all, type: :binary_id)

The call to references/2 creates the foreign key constraint.
What this means is any non null values for the teacher_id column of this table MUST corespond to a a record in the teachers table in the referenced column.
This leads to some edge cases that must be accounted for.
What happens if the referenced teacher record is deleted?
All classes referring to that eacher would have value for teacher_id that violates the contraint.
If you have a a few classes that belong to this teacher and you try to remove the teacher, you will get an error from the database.
This is rarely what I want.

The on_delete specifies the action to take place if the referenced record is deleted.
If we leave the value as :nothing, we will get the error I mentioned.
In this case, I am switching it to :nilify_all.
This makes the class without a teacher.
In a real world scneario, I could make a dashbaord showing the orphned classes prompting them to assign a teacher.
Alternativly, I could set delete_all which would remove the classes that reference the teacher.
What you pick for your relationships depend entirely on your use case.

By default, the key is set to id. you can overide that by passing in :column.
We can also add it to make the associated column more explicit.

If We wanted to make it so that all classes MUST have a teacher id passed in, you could pass null: false to the column creation.
If you do that, nilify_all will not work as it will violate the null column contraint.
You will have to either delete_all or have your app force the user to reasign the class before removing a teacher.


# does the same thing as above
add :teacher_id, references(:teachers, on_delete: :nilify_all, type: :binary_id, column: :id)

One more thing, we should make that default for the active column to be true.

your migration should look like this:


defmodule GradeTracker.Repo.Migrations.CreateClasses do
  use Ecto.Migration

  def change do
    create table(:classes, primary_key: false) do
      add :id, :binary_id, primary_key: true
      add :name, :string
      add :subject, :string
      add :active, :boolean, default: true, null: false
      add :teacher_id, references(:teachers, on_delete: :delete_all, type: :binary_id)

      timestamps()
    end

    create index(:classes, [:teacher_id])
  end
end

Now lets take a look at the schema.


defmodule GradeTracker.Class do
  use Ecto.Schema
  import Ecto.Changeset

  @primary_key {:id, :binary_id, autogenerate: true}
  @foreign_key_type :binary_id
  schema "classes" do
    field :active, :boolean, default: false
    field :name, :string
    field :subject, :string
    field :teacher_id, :binary_id

    timestamps()
  end

  @doc false
  def changeset(class, attrs) do
    class
    |> cast(attrs, [:name, :subject, :active])
    |> validate_required([:name, :subject, :active])
  end
end

By default, the schema specifies no relationship to Teacher.
We need to modify this schema by replaceing the teacher_id field with belongs_to/3.
it'll probably be a good idea to add a foreing constraint to the validation code in teh changeset as well.
Additionally, we'll remove active as a required field since we have a default and add the :teacher_id as a castable attribute.


defmodule GradeTracker.Class do
  use Ecto.Schema
  import Ecto.Changeset

  @primary_key {:id, :binary_id, autogenerate: true}
  @foreign_key_type :binary_id
  schema "classes" do
    field :active, :boolean, default: true
    field :name, :string
    field :subject, :string


    belongs_to :teacher, GradeTracker.Teacher

    timestamps()
  end

  @doc false
  def changeset(class, attrs) do
    class
    |> cast(attrs, [:name, :subject, :active, :teacher_id])
    |> validate_required([:name, :subject])
    |> foreign_key_constraint(:teacher_id)
  end
end

I will note that adding :teacher_id is optional and may not be appropriate for your use case.
the Belongs to adds other operations we will use to link classes to their associated teachers.
By adding the column directly, you allow the column to be set directly.
This means you should do due diligence when directly passing along user input;
For instance, you take params input from a web request and pass it directly in when creating a changeset.

Now we we add a has_many/3 to the Teacher.


defmodule GradeTracker.Teacher do
  use Ecto.Schema
  import Ecto.Changeset

  @primary_key {:id, :binary_id, autogenerate: true}
  @foreign_key_type :binary_id
  schema "teachers" do
    field :name, :string

    has_many :classes,  GradeTracker.Class

    timestamps()
  end

  @doc false
  def changeset(teacher, attrs) do
    teacher
    |> cast(attrs, [:name])
    |> validate_required([:name])
  end
end

Now that we have everythign setup in code, lets run the migrations.
Make sure you have the proper configuration setup in config/dev.exs.

$ mix ecto.migrate                                                                               [2.7.1]

20:34:53.870 [info]  == Running 20210422234100 GradeTracker.Repo.Migrations.CreateTeachers.change/0 forward
20:34:53.872 [info]  create table teachers
20:34:53.876 [info]  == Migrated 20210422234100 in 0.0s
20:34:53.893 [info]  == Running 20210515195753 GradeTracker.Repo.Migrations.CreateClasses.change/0 forward
20:34:53.893 [info]  create table classes
20:34:53.898 [info]  create index classes_teacher_id_index
20:34:53.899 [info]  == Migrated 20210515195753 in 0.0s

Building Relations

Now that we have a has_many relationship setup between `Teacher, we can start thinking about inserting classes.

First lets create a new test file /test/grade_tracker_web/schemas/class_test.exs

Since we are testing out connected associations, I've added a setup case that creates a Teacher struct that we can use for all our demonstrations

In our schema, a Class can be produced without a teacher so we can insert one in exactly the same as we could with Teacher.
`elixir
defmodule GradeTracker.ClassSchemaTest do
use GradeTracker.DataCase
alias GradeTracker.Repo
alias GradeTracker.Teacher
alias GradeTracker.Class

def create_teacher(context) do
teacher = %Teacher{}
|> Teacher.changeset(%{
name: "Jose Valim"
})
|> Repo.insert!()

{:ok, %{ teacher: teacher }}

end

setup :create_teacher

test "we can insert a class", %{ teacher: _teacher } do
assert {:ok, _} = %Class{}
|> Class.changeset(%{
name: "botony 101",
subject: "biology",
})
|> Repo.insert()

end
end

Since we added :teacher_id to the list of castable attributes, we can pass it in directly.
Once its set, we can load the associated item into the structure using Repo.preload/3

`elixir
defmodule GradeTracker.ClassSchemaTest do
#...

test "we can insert a teacher with a class by setting the column directly", %{ teacher: teacher } do
assert {:ok, class} = %Class{}
|> Class.changeset(%{
name: "botony 101",
subject: "biology",
teacher_id: teacher.id
})
|> Repo.insert()

assert class.teacher_id == teacher.id
class = class |> Repo.preload([:teacher])

assert %Teacher{ name: "Jose Valim", id: teacher_id } = class.teacher
assert teacher_id == teacher.id

end

Ecto.build_assoc

Setting up a has_many in the schema provides a lot of information to Ecto for how to link up associated database structs.
The example above only works if you are directly casting a teacher_id.
In practice, you are more likely to use Ecto's Ecto.build_assoc/3 to create a new struct based on has_many relationships. You can pass this into your changeset function to create changesets with the association set.

`elixir
defmodule GradeTracker.ClassSchemaTest do
#...

test "we can insert a teacher with a class with build_assoc", %{ teacher: teacher } do
assert {:ok, class} = teacher
|> Ecto.build_assoc(:classes) # we get it from the key you set in the schmema after has_many
|> Class.changeset(%{
name: "botony 101",
subject: "biology"
})
|> Repo.insert()

assert class.teacher_id == teacher.id
class = class |> Repo.preload([:teacher])

assert %Teacher{ name: "Jose Valim", id: teacher_id } = class.teacher
assert teacher_id == teacher.id

end

Ecto.Changeset.put_assoc

We can also build a Teacher from a Class but they wont be connnected with an association.

This is because we aren't writing anything to the class which is where the column that sets the association is set.
We would have to set that explicitly using Ecto.Changeset.put_assoc/4.

Note: you will need to preload the association before you attempt to upadate the association.

`elixir

defmodule GradeTracker.ClassSchemaTest do
#...

test "we build a teacher from a class using build assoc as well", %{ teacher: _teacher } do
assert {:ok, class} = %Class{}
|> Class.changeset(%{
name: "botony 101",
subject: "biology",
})
|> Repo.insert()

assert {:ok, teacher } = class
|> Ecto.build_assoc(:teacher)
|> Teacher.changeset(%{
  name: "Richard Feynman"
})
|> Repo.insert()

# the association has to be loaded before we can alter it, leave this oput and you'll get an error
class = class |> Repo.preload([:teacher])

assert {:ok, class} = class
  |> Class.changeset(%{})
  |> Ecto.Changeset.put_assoc(:teacher, teacher)
  |> Repo.update()

class = class |> Repo.preload([:teacher], force: true) #we force a refresh to get the new data

assert class.teacher_id == teacher.id


assert %Teacher{ name: "Richard Feynman", id: teacher_id } = class.teacher
assert teacher_id == teacher.id

end

And With that, you have the tools to construct relations based on has_many and belongs_to.
to wrap things up.

Key ideas

A refernces id is used to set a record as belonging to another record.
You can use build_assoc to create an associated record for insertion
put_assoc is used to set an association after the fact.

Next I'll go over how to create many to many relationships.

Database Modeling with Ecto: Part 1

Peter de Croos — Mon, 17 May 2021 20:52:50 +0000

Creating your first records

Relational modelling in Ecto
Ecto Schema Fundamentals

Relational modelling in Ecto

You've heard of elixir and are just getting started with your first taste of the kool-aid.

If you're new to Elixir, you're probably finding ecto to be a bit of a culture shock.
Ecto is an immensly powerful library for working with sql but Unlike other ORMs you may have used, Ecto embraces what makes sql great.
ORMs like active record or django's python objects try to hide sql behind their language specifc interfaces.
SQL Land however, does not deal with objects.
Ecto takes SQL as it is an gives you very powerful tools for modeling yoru application's database layer without building abstractions that ultimatly hinder you later on with slow queries and magic behavior that gets in your way.

Of course, this means you need to know how to think in sql and the relational model.

Thinking in Relationships

I'll spare you the theoratical gibber jabber about industry jargon like "third normal form" and "denormalization".
Frankly, a lot of books on sql modeling get high on the theoretical horse to cover every single possible edge case imaginable.
As somone who learned sql on the job, I've distilled it down to a a few rules of thumb with the occasional exception.

The secret to thinking in sql is right here in the word "relational." Almost all relations you will model fit one of three catagroies.

A has many B
B belongs to A
A has many C and B has many C
A has many B through C (if B also has_many A through C, we might call this a many to many relationship)

Any nontrivial database schema is goign to have all of these relationships modeled in one way or another.
To make this all a bit more relatable, lets plan a schema for a school grade tracker.
This could very well be the heart of a system that helps etachers quickly grade their students and send out report cards.

Cosnider the following relationships.

A Class has one Teacher: belongs to
A Student attends many Class: has many
A Class has many Student: has many
A Class has many Student and only one Teacher: many to many

Right here we have a few relationships mapped out.
Given the following the information, we can also infer some secondary effect

a Student has many Teacher through a Class
a Teacher has many Student through a Class

We still havent gotten to grades! But where to store it?
Thinking about grades, I would model it as an operation over a group of Assignment.
It makes sense to think about it in terms of the Enrollment of the students.

With that we can add a few more relationships and associated constrants

An Enrollment joins a Student and Class to establish a many to many relationship
An Enrollment has many Assignment

Now that we have a birds eye map of the schema we want to build and model, we can start to hash out the details in code!

Ecto Schema Fundamentals

First, install the phoenix generator and run the following to create your project.

mix phx.new --binary-id --database postgres grade_tracker

Every record in yoru database needs a primary key.
This is a column thatserves as a unique key for a particular record in a table.
By default, phoenix sets up autoincrementing integers as the primary keys.
Setting the --binary-id flag ensures we'll be generating uuid primary keys
You are more than welcome to use the default autoincrement integers instead.
They are faster and arguably easier to index.
However, uuids are harder to blindly guess and will be useful if later you decide to spread your data out to multiple systems.

We need a teachers table and schema so lets fire up the generator

$ mix phx.gen.schema Teacher teachers name:string
* creating lib/grade_tracker/teacher.ex
* creating priv/repo/migrations/20210414205427_create_teachers.exs

This generates a migration that creates out teachers table and a schema file.

# priv/repo/migrations/20210414205427_create_teachers.exs
defmodule GradeTracker.Repo.Migrations.CreateTeachers do
  use Ecto.Migration

  def change do
    create table(:teachers, primary_key: false) do
      add :id, :binary_id, primary_key: true
      add :name, :string

      timestamps()
    end

  end
end

By default, we get 4 colums created. id is set as the primary key, and name completes the list of ones we created.
timestamps() adds inserted_at and updated_at columns.

The schema that was generated is the interface for how we will interact with this table

defmodule GradeTracker.Teacher do
  use Ecto.Schema
  import Ecto.Changeset

  @primary_key {:id, :binary_id, autogenerate: true}
  @foreign_key_type :binary_id
  schema "teachers" do
    field :name, :string

    timestamps()
  end

  @doc false
  def changeset(teacher, attrs) do
    teacher
    |> cast(attrs, [:name]) # pulls the name attribute into the changeset
    |> validate_required([:name]) # validates that a name attribute exists either on the teacher or the attrs
  end
end

Instead of objects, Elixir gives us structs.
The distinction is that there are no instance methods or mutable state.
It represents the state of the record as it was retrieved from the backend.
A struct is a datastructure similar to Map with a difference that the keys are defined up front.

In this module, use Ecto.Schema meanst that the Teacher module inherits the behavior of the an Ecto Schema.
It does this through a macro system which is beyond the cope of this article but worth deliving into.

As you can see, there is one function defined.
The changeset/2 function takes a schema struct and a set of params.
It returns an Ecto.Changeset record that carries with it information pertaining to a database change oepration.
Ecto expects that you may have different operations with different validation rules depending on the context of the operation and its perfectly fine to have different functions for generating different types of changesets.

For a complete list of built in validations, checkout the docs for Ecto.Changeset

Inserting records

Database Operations are done via Repo aliased from GradeTracker.Repo. Its a microservice that you pass data to and get back the result of your operations.
To insert a record, we create a Teacher changeset.
Since we are creating a new one, we can pass in a new Teacher struct alogn with some paramaters.

Lets test out our new teacher schema. Create a file test/schemas/teacher_test.exs


defmodule GradeTracker.TeacherSchemaTest do
  use  GradeTracker.DataCase
  alias GradeTracker.Repo
  alias GradeTracker.Teacher

  test "we can insert a teacher", _ do
    assert {:ok, %Teacher{ id: _id, name: "Jose Valim" } = _teacher} = %Teacher{}
    |> Teacher.changeset(%{
      name: "Jose Valim"
    })
    |> Repo.insert()

  end
end

Upon a successsful insertion, the insert operation, returns {:ok, *struct* }.
The struct returned represents the state of that record in the database.
Not only does it have a name attribute, it also has an id which was generated during the insertion.

now lets run this test!

$ mix test
....

Finished in 0.1 seconds
4 tests, 0 failures

Randomized with seed 80769

For completeness sake, lets verify that an error gets returned if we leave off the :name attr.
If Repo.insert/1 errors, we get back an error tuple giving us a changeset with the errors embedded within.

defmodule GradeTracker.TeacherSchemaTest do
  #...

  test "inserting a teacher errors without a name", _ do
    assert {:error, %Ecto.Changeset{
      errors: [name: {"can't be blank", [validation: :required]}],
      valid?: false
    } = _changeset} = %Teacher{}
    |> Teacher.changeset(%{})
    |> Repo.insert()
  end

end

Updating records

Repo.update/1 accepts a changeset and operates similarly to Repo.insert/1 with the exception that a primary key must exist on the struct passed into the chanegset creation.
If you try to update a record without a primary key, you will raise an exception rather than get an error tuple.

defmodule GradeTracker.TeacherSchemaTest do
  #...

  test "updating a teacher errors without an id", _ do
    assert_raise Ecto.NoPrimaryKeyValueError, fn ->
      %Teacher{}
      |> Teacher.changeset(%{ name: "Dave Thomas" })
      |> Repo.update()
    end
  end
end

Deleting a record

Repo.delete/1 accepts a struct and deletes it. You'll get back {:ok, teacher} that returns the deleted record

  test "deleting a teacher", _ do
    assert {:ok, teacher} = %Teacher{}
    |> Teacher.changeset(%{
      name: "Jose Valim"
    })
    |> Repo.insert()

    assert {:ok, teacher} = teacher
    |> Repo.delete()


  end

And with that, you can now create, update and delete a single record. In my followup, I'll cover how to setup relations and transactions