Database Schema: What and Why

In the previous video we created a migration file which allowed us to create a contacts table in our database, in a reliable and maintainable way.

What we don't have, immediately, is a way to interact with a 'thing' representing this table inside our Elixir code.

This 'thing' we need is a Schema.

We will need to create a new schema for every table we have in our database. In our Elixir code, when working with any table in any way, we will make use of that table's schema.

There's a command line command we can make use of to generate a schema definition for us:

mix phx.gen.schema ...

Personally, I find this command's syntax to be a bit unwieldy, and prefer to do the bare minimum here, then continue working from the IDE. You're opinion may, of course, differ and that's fine.

mix phx.gen.schema {SomeModule} {pluralised_table_name} {field_name}:{field_type}

You're going to thank yourself if you open this page.

The command we will need is:

mix phx.gen.schema Contact contacts first:string last:string birth_date:utc_datetime

* creating lib/hello_phoenix/contact.ex
* creating priv/repo/migrations/20171201130535_create_contacts.exs

Remember to update your repository by running migrations:

    $ mix ecto.migrate

What's this?

It created us a migration?!

Chris, why are you making me do extra work?!

:)

It's worthwhile for two reasons:

1. You're going to work with this stuff on the regular, so it pays to know how to do things manually.

2. The generated content is slightly different to the one we created by hand.

Here's our hand cranked migration:

defmodule HelloPhoenix.Repo.Migrations.AddContactTable do
  use Ecto.Migration

  def change do
    create table("contact") do
      add :first,      :string
      add :last,       :string
      add :birth_date, :utc_datetime

      timestamps()
    end
  end
end

And the generated one:

defmodule HelloPhoenix.Repo.Migrations.CreateContacts do
  use Ecto.Migration

  def change do
    create table(:contacts) do
      add :first, :string
      add :last, :string
      add :birth_date, :utc_datetime

      timestamps()
    end

  end
end

The generated name of the migration is Create{Whatever}. We went with Add...Table.

More interestingly, the table name we gave was a UTF-8 encoded binary (or a string, to you and me), but the generated version went with an atom instead.

In our case this is going to cause a conflict. We need to choose one to keep, and delete the other.

I'm keeping my own because down with robots.

For future reference, we can tell mix not to generate the migration with:

mix phx.gen.schema HelloPhoenix.Contact contacts first:string --no-migration

You can find out more about this, or any other command with: mix help phx.gen.schema

The Contact Schema

The joy of using a generator is that we need to type almost nothing, and the resulting file is guaranteed to follow all the right conventions:

# lib/hello_phoenix/contact.ex

defmodule HelloPhoenix.Contact do
  use Ecto.Schema
  import Ecto.Changeset
  alias HelloPhoenix.Contact

  schema "contacts" do
    field :birth_date, :utc_datetime
    field :first, :string
    field :last, :string

    timestamps()
  end

  @doc false
  def changeset(%Contact{} = contact, attrs) do
    contact
    |> cast(attrs, [:first, :last, :birth_date])
    |> validate_required([:first, :last, :birth_date])
  end
end

The downside to using a generator: we may not understand much of what is generated for us.

Ok, so the good news: you don't need to understand all of this to make use of it.

We could simplify this somewhat to begin with:

# lib/hello_phoenix/contact.ex

defmodule HelloPhoenix.Contact do
  use Ecto.Schema

  schema "contacts" do
    field :birth_date, :utc_datetime
    field :first, :string
    field :last, :string

    timestamps()
  end
end

use is a macro.

Macros are an advanced topic, and being completely truthful, not something I have needed to use so far in my journeys with Elixir.

use Ecto.Schema is what allows us to then make use of the schema in schema "contacts" do.

If we didn't have the use statement then Elixir wouldn't have a clue what schema meant. This would be severe enough of a problem for Elixir to result in a compilation error:

iex -S mix

Erlang/OTP 20 [erts-9.0.2] [source] [64-bit] [smp:4:4] [ds:4:4:10] [async-threads:10] [hipe] [kernel-poll:false]

Compiling 1 file (.ex)

== Compilation error in file lib/hello_phoenix/contact.ex ==
** (CompileError) lib/hello_phoenix/contact.ex:7: undefined function schema/2
    (stdlib) erl_eval.erl:670: :erl_eval.do_apply/6
    (elixir) lib/kernel/parallel_compiler.ex:121: anonymous fn/4 in Kernel.ParallelCompiler.spawn_compilers/1

Going Raw With The REPL

It would be helpful at this stage to test that our new HelloPhoenix.Contact module works as expected.

We can use the REPL (Read Eval Print Loop) for this:

iex -S mix

Interactive Elixir (1.5.1) - press Ctrl+C to exit (type h() ENTER for help)
iex(1)>

We access a schema just like we'd access a Struct. This is with the %Whatever{} syntax.

In our case this will be:

%HelloPhoenix.Contact{}

And we can see that Elixir is fairly happy with this right off the bat:

iex(1)> %HelloPhoenix.Contact{}

%HelloPhoenix.Contact{__meta__: #Ecto.Schema.Metadata<:built, "contacts">,
 birth_date: nil, first: nil, id: nil, inserted_at: nil, last: nil,
 updated_at: nil}

We can populate some or all of the fields, and assign this into a variable:

iex(2)> my_var = %HelloPhoenix.Contact{first: "Christopher", last: "Moss", birth_date: DateTime.utc_now()}

%HelloPhoenix.Contact{__meta__: #Ecto.Schema.Metadata<:built, "contacts">,
 birth_date: #DateTime<2017-12-01 14:56:34.905739Z>, first: "Christopher",
 id: nil, inserted_at: nil, last: "Moss", updated_at: nil}

Yes, I am incredibly young.

And we can see that my_var contains our Struct.

iex(3)> my_var
%HelloPhoenix.Contact{__meta__: #Ecto.Schema.Metadata<:built, "contacts">,
 birth_date: #DateTime<2017-12-01 14:56:34.905739Z>, first: "Christopher",
 id: nil, inserted_at: nil, last: "Moss", updated_at: nil}

Being lazy and / or not wanting to type the HelloPhoenix. part every time we create a new Contact, we can make use of alias:

iex(4)> alias HelloPhoenix.Contact
HelloPhoenix.Contact

iex(5)> {:ok, birth_date, _utc_offset} = DateTime.from_iso8601("2017-12-01T14:10:34Z")

iex(6)> someone_else = %Contact{first: "Someone", last: "Else", birth_date: birth_date}

%HelloPhoenix.Contact{__meta__: #Ecto.Schema.Metadata<:built, "contacts">,
 birth_date: #DateTime<2017-12-01 14:10:34Z>, first: "Someone", id: nil,
 inserted_at: nil, last: "Else", updated_at: nil}

Saving Data Into The Database

At this point we've created data that our database would accept, but we haven't actually saved anything to the database.

We can do this from the REPL, too.

Let's start by inserting in a verbose fashion:

iex(6)> %HelloPhoenix.Contact{first: "Christopher", last: "Moss", birth_date: DateTime.utc_now()} |> HelloPhoenix.Repo.insert

[debug] QUERY OK db=4.9ms

INSERT INTO "contacts" ("birth_date","first","last","inserted_at","updated_at") VALUES ($1,$2,$3,$4,$5) RETURNING "id" [{{2017, 12, 1}, {15, 5, 12, 209760}}, "Christopher", "Moss", {{2017, 12, 1}, {15, 5, 12, 218206}}, {{2017, 12, 1}, {15, 5, 12, 218217}}]

{:ok,
 %HelloPhoenix.Contact{__meta__: #Ecto.Schema.Metadata<:loaded, "contacts">,
  birth_date: #DateTime<2017-12-01 15:05:12.209760Z>, first: "Christopher",
  id: 1, inserted_at: ~N[2017-12-01 15:05:12.218206], last: "Moss",
  updated_at: ~N[2017-12-01 15:05:12.218217]}}

The terminal output you will see for yourself is more colourful and easier to read than what you see here. Watch the video to see the difference.

There's quite a lot going on here, and this will be covered in greater depth in a separate video.

Simply put we are creating the Contact as already seen, and then piping this (|>) output into the HelloPhoenix.Repo.insert function.

We could re-write this to remove the pipe, which might make this easier to understand:

HelloPhoenix.Repo.insert(
  %HelloPhoenix.Contact{first: "Christopher", last: "Moss", birth_date: DateTime.utc_now()}
)

We could also shorten this process making use of what we learned above about the use of alias, and variables:

iex(7)> alias HelloPhoenix.Repo
HelloPhoenix.Repo

iex(8)> someone_else |> Repo.insert

[debug] QUERY OK db=3.0ms

INSERT INTO "contacts" ("birth_date","first","last","inserted_at","updated_at") VALUES ($1,$2,$3,$4,$5) RETURNING "id" [{{2017, 12, 1}, {14, 10, 34, 0}}, "Someone", "Else", {{2017, 12, 1}, {15, 26, 8, 532727}}, {{2017, 12, 1}, {15, 26, 8, 532735}}]

{:ok,
 %HelloPhoenix.Contact{__meta__: #Ecto.Schema.Metadata<:loaded, "contacts">,
  birth_date: #DateTime<2017-12-01 14:10:34Z>, first: "Someone", id: 2,
  inserted_at: ~N[2017-12-01 15:26:08.532727], last: "Else",
  updated_at: ~N[2017-12-01 15:26:08.532735]}}

Ok, at this point we have two new records (or 'contacts') in our contacts table.

We took out the changeset piece of the equation to make things simpler.

This does cause a potential problem, so let's add this back in and see what problems this prevents in the very next video.