Ectogram: Generating The Users Table

Now that we have our project all set up and Ecto customized we can begin generating migrations for our database. Coming from frontend land I don’t have much experience with performing migrations so this will be great practice. For those out there that don’t know a migration is a snapshot of what the data in your database looks like at that point in time. In our case this is the first migration and we will be adding a table to it called users. We define what the users table looks like by defining columns on the table. A row in that table could end up looking something like the below:

SELECT * FROM users;
 
-- id          | email          | url                             | username
-- abc-123-def | [email protected] | https://ectogram.com/darealturd | @darealturd

With Ecto we have te ability to generate migrations from the command-line using mix, but we also have the ability to apply migrations and rollback if necessary. I am sure I am not the only one out there who has forgotten to apply migrations to the local database after pulling in changes on a git tree, this is where rollback can come in handy. We will take a look at mix ecto.rollback a little later when we have more migrations to work with. If you take a look at our mix.exs you can see when we run the ecto.setup command we apply all of our migrations and then seed the database. This comes in particularly handy if you have someone new joining your team. Documentation can tell that user:

After cloning the repository and confirming you have elixir and erlang on your machine just run mix setup.

As long as migrations are checked into version control, which they should be! Your team can stay up-to-date and your database in harmony.

Generating The Users Table

With Ecto’s command-line tools we can generate a template for our migrations:

mix ecto.gen.migration add_users

defmodule Ectogram.Repo.Migrations.AddUsers do
  use Ecto.Migration
 
  def change do
    # Here is where we will create tables, indexes, constraints, etc.
  end
end

As per the documentation from Ecto.Migration there are three functions we can use here change/0, down/0, and up/0. change/0 essentially is an up/0 which applies the changes made in it’s function body to the database. We could add several tables, associations, constraints, or indexes. As long as what we have written is in congruence with our previous migrations and the database engine we selected to use (Postgres in our case), then we should have no problems. The majority of the code for this section is very similar to the code generated when using phx.gen.auth. Hats off to Aaron Renner for the great job on this generator that is now a stable part of Phoenix 1.6 and onwards. The down/0 on the other hand is for undoing changes that the up or change made. This will become clearer when we work with mix ecto.rollback. In the mean time we will add the below to the migration template:

defmodule Ectogram.Repo.Migrations.AddUsers do
  use Ecto.Migration
 
  def change do
+    execute "CREATE EXTENSION IF NOT EXISTS citext", ""
 
+   create table(:users) do
+     add :avatar, :string, null: true
+     add :bio, :string, null: true
+     add :birth_date, :date, null: false
+     add :email, :citext, null: false
+     add :hashed_password, :string, null: false
+     add :name, :string, null: false
+     add :phone, :string, null: false
+     add :url, :string, null: false
+     add :username, :string, null: false
+     add :verified, :boolean, null: false, default: false
+
+     timestamps()
+   end
 
+    create constraint(:users, :older_than_18, check: "date_part('year', age(birth_date)) >= 18")
+    create index(:users, [:email], unique: true)
+    create index(:users, [:phone], unique: true)
+    create index(:users, [:username], unique: true)
  end
end

To go over some of the code above we define our columns as the first value after the add/3 function. It follows as the :term we want to name our column, the :type our column should be, and any further options represented as a keyword list. It might be helpful to see it written as:

# ...
def change do
  create table(:users) do
    # add(:term, :type, opts \\ [])
    add(:verified, :boolean, [null: false, default: false])
  end
end
# ...

As seen in the example we can define if a column should be nullable or not and we can even define the default value a row should be created with if no value is given in the query.

We might touch on it later on in this series, but there is also the ability to define columns with enums using Ecto.Enum. I am a big fan of using enums for things like statuses, roles, or any other state that is stored in the database that can be more than two state.

You might notice we can create indexes to speed up the read times on our queries. We can also add constraints. We create a simple constraint that will check to see if the user registering to use Ectogram is 18 or older. To do that we write some SQL in the check field.

For anyone not familiar with that SQL what is happening is we are passing the given birth_date to the AGE function in Postgres. This returns an object with keys like year, month, etc where the values are numbers. The DATE_PART function then cherry picks the desired part of the object for us. An example would be my birth year would evaluate to 33 when passed through these functions and then ran against the >= 18 comparison. Postgres has some pretty slick features!

With the migration written we can go ahead and apply our changes to the database by running mix ecto.migrate:

mix ecto.migrate
15:51:30.524 [info]  == Running 20220116203624 Ectogram.Repo.Migrations.AddUsers.change/0 forward
 
15:51:30.528 [info]  execute "CREATE EXTENSION IF NOT EXISTS citext"
 
15:51:30.571 [info]  create table users
 
15:51:30.575 [info]  create check constraint eighteen_or_older_only on table users
 
15:51:30.578 [info]  create index users_email_index
 
15:51:30.579 [info]  create index users_phone_index
 
15:51:30.580 [info]  create index users_username_index
 
15:51:30.581 [info]  == Migrated 20220116203624 in 0.0s

And with that we have written and applied our first migration with Ecto! We can verify that our migration was applied in a few different manners. We can query the database via iex, psql, or an SQL client:

iex -S mix
iex(1)> Repo.query("SELECT * FROM users;")
 
14:01:06.542 [debug] QUERY OK db=0.6ms queue=0.3ms idle=1796.0ms
SELECT * FROM users; []
{:ok,
 %Postgrex.Result{
   columns: ["id", "avatar", "bio", "birth_date", "email", "hashed_password",
    "name", "phone", "url", "username", "verified", "created_at",
    "modified_at"],
   command: :select,
   connection_id: 83259,
   messages: [],
   num_rows: 0,
   rows: []
 }}

psql ectogram_dev
ectogram=# \d
               List of relations
 Schema |       Name        | Type  |  Owner
--------+-------------------+-------+----------
 public | schema_migrations | table | postgres
 public | users             | table | postgres
(2 rows)
ectogram_dev=# SELECT * FROM schema_migrations;
    version     |     inserted_at
----------------+---------------------
 20220116203624 | 2022-01-18 00:16:26
(1 row)

We can see that we actually have two tables; our users table and a table who’s sole purpose is to track our migrations. That timestamp that you see added to the name of your migration becomes a key that references when the migration was applied to the database.

Adding The User Schema

The schema is a representation of a row (singular) in our users table in a manner which is friendly in the language or framework we are working in. No one really wants to write a bunch of SQL queries daily and this is why all those ORMs (Object Relational Maps) exist across all languages. The Ecto.Schema is that tool in elixir:

touch lib/ectogram/user.ex

defmodule Ectogram.User do
  use Ectogram.Schema
  import Ecto.Changeset
 
  schema "users" do
    field :avatar, :string
    field :bio, :string
    field :birth_date, :date
    field :email, :string
    field :hashed_password, :string, redact: true
    field :name, :string
    field :password, :string, virtual: true, redact: true
    field :phone, :string
    field :url, :string
    field :username, :string
    field :verified, :boolean, default: false
 
    timestamps()
  end
end

You can see we model our schema in a very similar manner to our migration. You might notice we added a password field that was not present in our migration. You can add lot and lots of fields to your schema that will never be added to the actual database. First since our migration doesn’t have a column for password it won’t be added anyways. Perhaps we have :first_name and :last_name in our database, but our frontend wants to expose on the form the concept of :full_name. We could have :full_name on our schema and have code that splits the string and sends the respective values to the correct column.

A cool feature to point out is the redact: true option. Especially in development you will notice SQL queries are logged out to the terminal via the debug logger. If you remove the redact option you will see the plain text password in your terminal when the query is executed. With this option in place that field will be “redacted” or removed from the logs which is great if we are sending logs to a third party service to monitor our application.

Before we wrap up this session don’t forget to add the following to your .iex.exs file:

- alias Ectogram.{Repo}
+ alias Ectogram.{Repo,User}
 
import_if_available Ecto.Query
 
import_if_available Ecto.Changeset

Now the user schema will be available to us in iex at the top level.

iex -S mix
iex(1)> Repo.all(User)
14:09:58.601 [debug] QUERY OK source="users" db=1.2ms queue=1.0ms idle=1666.7ms
SELECT u0."id", u0."avatar", u0."bio", u0."birth_date", u0."email", u0."hashed_password", u0."name", u0."phone", u0."url", u0."username", u0."verified", u0."created_at", u0."modified_at" FROM "users" AS u0 []
[]

Adding Bcrypt

We will need to add one dependency before moving forward. We will be making use of the popular bcrypt password hashing library. Add the following to your mix.exs and then install the dependency:

defp deps do
    [
      {:ecto_sql, "~> 3.0"},
-     {:postgrex, ">= 0.0.0"}
+     {:postgrex, ">= 0.0.0"},
+     {:bcrypt_elixir, "~> 2.0"}
    ]
end

mix deps.get

Working With Changesets

Ecto introduced me to the concept of a changeset. A changeset is:

A changeset is a way in which Ecto takes in user data and validates that the data is as we want it before allowing it to be written to the database.

An example of a changeset at work would be if we do not give an email because it is marked as a non-nullable field Ecto will return an error message stating that you cannot leave this field empty. So we try again and submit the number 1. Ecto again throws and error and says that is an invalid format. This goes on and on until the correct format and type is met.

In the below registration_changeset/3 we cast the incoming data to the %User{} struct with essentially a whitelist of fields. If we don’t include a field in this list it will not be cast to the schema. Trust me I learned this the hard way when I forgot to add :url to the @required_fields attribute 🤦🏻‍♂️. Then we begin our validation checks. Ecto.Changeset comes with many pre-baked validation functions. If you need to run many different checks on a piece of data it’s common practice to group those checks into a function by the name of the data. For instance our validate_password/2 below checks to see that a password was given to it, the min and max length of the string, and various regexes are ran against the string to verify it fits the specific format we are requiring of the user. The key thing to remember when creating your own custom validation functions is that the function should take in a changeset and return a changeset so it can continue to be used in a pipeline.

defmodule Ectogram.User do
  # ...
  alias Ectogram.{Repo}
  import Bcrypt, only: [hash_pwd_salt: 1]
  import Keyword, only: [get: 3]
  import String, only: [replace: 3]
 
  @base_url "https://ectogram.com"
  @optional_fields ~w(avatar bio)a
  @required_fields ~w(birth_date email hashed_password name password phone url username verified)a
 
  # ...
 
  def registration_changeset(user, attrs, opts \\ []) do
    user
    |> cast(attrs, @required_fields ++ @optional_fields)
    |> validate_birth_date()
    |> validate_password(opts)
    |> build_and_validate_url()
  end
 
  defp validate_birth_date(changeset) do
    changeset
    |> validate_required([:birth_date])
    |> check_constraint(:birth_date, name: :eighteen_or_older_only, message: "Must be 18 or older!")
  end
 
  # ...
 
  defp validate_password(changeset, opts) do
    changeset
    |> validate_required([:password])
    |> validate_length(:password, min: 12, max: 72)
    |> validate_format(:password, ~r/[a-z]/, message: "at least one lower case character")
    |> validate_format(:password, ~r/[A-Z]/, message: "at least one upper case character")
    |> validate_format(:password, ~r/[!?@#$%^&*_0-9]/, message: "at least one digit or punctuation character")
    |> maybe_hash_password(opts)
  end
 
  # ...
 
  defp build_and_validate_url(changeset) do
    url = get_change(changeset, :url)
    username = get_change(changeset, :username)
 
    if !url && username do
      changeset
      |> put_change(:url, @base_url <> "/" <> replace(username, ~r/@/, ""))
      |> validate_required([:url])
      |> unique_constraint(:url)
    else
      changeset
      |> validate_required([:url])
      |> unique_constraint(:url)
    end
  end
 
  defp maybe_hash_password(changeset, opts) do
    hash_password? = get(opts, :hash_password, true)
    password = get_change(changeset, :password)
 
    if hash_password? && password && changeset.valid? do
      changeset
      # If using Bcrypt, then further validate it is at most 72 bytes long
      |> validate_length(:password, max: 72, count: :bytes)
      |> put_change(:hashed_password, hash_pwd_salt(password))
      |> delete_change(:password)
    else
      changeset
    end
  end
end

For brevity I removed a lot of the validations that are being ran to condense this section down you can view the whole file here.

The first thing that happens is that we cast the input (attrs) against our data. In the case of registration_changeset/3 our data is the %User{} struct which is empty. That call to cast/3 will ONLY cast the keys that we permit it too. That is where the concatenation of @required_fields and @optional_fields comes in. These are the keys we are telling Ecto to apply as pending changes.

Cool tidbit I learned recently was that instead of creating the attribute @required_fields as a list of atoms you can instead use the ~w sigil which is for word lists add the words you want to use in a non-comma separated list, and append the a at the end. This will generate that same list of atoms.

The very next thing we do is get into validating the data and checking the various constraints. You can see that in validate_birth_date/1 we explicitly check the constraint we defined in our migration :eighteen_or_older_only.

iex -S mix
iex(1)> attrs = %{birth_date: ~D[1992-05-20], email: "[email protected]", name: "Turd Ferguson", password: "h3ll0-W0rld123**", phone: "+13168675309", username: "@darealturd"}
%{
  birth_date: ~D[1992-05-20],
  email: "[email protected]",
  name: "Turd Ferguson",
  password: "h3ll0-W0rld123**",
  phone: "+13168675309",
  username: "@darealturd"
}
iex(2)> %User{} |> User.registration_changeset(attrs) |> Repo.insert()
16:20:30.567 [debug] QUERY OK source="users" db=1.4ms decode=0.5ms queue=1.1ms idle=788.3ms
SELECT TRUE FROM "users" AS u0 WHERE (u0."email" = $1) LIMIT 1 ["[email protected]"]
 
16:20:30.819 [debug] QUERY OK db=3.2ms queue=0.9ms idle=1040.5ms
INSERT INTO "users" ("birth_date","email","hashed_password","name","phone","url","username","verified","created_at","modified_at","id") VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11) [~D[1992-05-20], "[email protected]", "$2b$12$477Xs4N8CFWRsApsaTZ5U.DJ1CI1Vrw7TmQ48yBHrgNm1Ezv6ft5a", "Turd Ferguson", "+13168675309", "https://ectogram.com/darealturd", "@darealturd", false, ~U[2022-01-16 21:20:30.813578Z], ~U[2022-01-16 21:20:30.813578Z], <<231, 2, 239, 229, 4, 67, 76, 127, 141, 182, 59, 8, 87, 47, 116, 240>>]
{:ok,
 #Ectogram.User<
   __meta__: #Ecto.Schema.Metadata<:loaded, "users">,
   avatar: nil,
   bio: nil,
   birth_date: ~D[1992-05-20],
   created_at: ~U[2022-01-16 21:20:30.813578Z],
   email: "[email protected]",
   id: "e702efe5-0443-4c7f-8db6-3b08572f74f0",
   modified_at: ~U[2022-01-16 21:20:30.813578Z],
   name: "Turd Ferguson",
   phone: "+13168675309",
   url: "https://ectogram.com/darealturd",
   username: "@darealturd",
   verified: false,
   ...
 >}

Seeding The Database

We will make use of the popular faker library for generating a bunch of random data for our database. One thing to note is that random data needs to fit within the validation and constraints of our database. That is why you will see so many manipulations of the Faker seed data

defp deps do
    [
      {:ecto_sql, "~> 3.0"},
      {:postgrex, ">= 0.0.0"}
      {:postgrex, ">= 0.0.0"},
-     {:bcrypt_elixir, "~> 2.0"}
+     {:bcrypt_elixir, "~> 2.0"},
+     {:faker, "~> 0.17", only: [:dev, :test]}
    ]
end

mix deps.get

alias Ectogram.{Repo, User}
import Faker
 
# Seed database with users.
for _ <- 1..100 do
  %User{}
  |> User.registration_changeset(%{
    avatar: Faker.Avatar.image_url(),
    # Ensures string does not exceed 'max' validation.
    bio: String.slice(Faker.Lorem.paragraph(), 0..240),
    # Ensures user is of age constraint.
    birth_date: Faker.Date.date_of_birth(18..30),
    email: Faker.Internet.email(),
    # Ensures string does not contain any punctuation (i.e. Henry O'Leary).
    name: String.replace(Faker.Person.first_name() <> Faker.Person.last_name(), ~r/[[:punct:]]/, ""),
    # Ensures string meets 'format' validation.
    password: String.capitalize(Faker.Internet.slug(["foo", "bar", "baz"], ["_"]) <> "#{random_between(2,99)}"),
    # Ensures string meets 'format' validation.
    phone: String.replace(Faker.Phone.EnGb.number(), ~r/\s/, ""),
    # Ensures string does not exceed 'max' validation.
    username: "@#{String.slice(Faker.Internet.user_name(), 0..19)}"
  })
  |> Repo.insert!()
end

With our first migration and schema under our belt we can now move forward with building Ectogram into a the Instagram clone it is trying to become! However before we do that we will get our feet wet with testing in Elixir so we can verify we haven’t missed any edge cases in our implementations. All of the code for this section can be found in PR#2.