The Perfect Stack for Realtime Apps Using Elixir, Phoenix, LiveView | Unicorn Stack | Part 2

Database Schema

CodeLabsPro

4 min readAug 26, 2023

The Perfect Stack for Realtime Apps Using Elixir, Phoenix, LiveView | Part 2 | Database Schema

In Part 1 of this codelab series, we covered the setup for the Unicorn Stack using Elixir, Phoenix, LiveView.

In this part, we continue where we left off and start by creating the schema for our API.

Schema Design

The relationship between User and Account in our schema is as follows

Create Accounts module

Create Accounts module with Account model and accounts table with the email and hashed_password fields in the schema as follows

mix phx.gen.json Accounts Account accounts email:string hashed_password:string

This creates the following structure

==> mix phx.gen.json Accounts Account accounts email:string hashed_password:string
* creating lib/unicorn_stack_api_web/controllers/account_controller.ex
* creating lib/unicorn_stack_api_web/controllers/account_json.ex
* creating lib/unicorn_stack_api_web/controllers/changeset_json.ex
* creating test/unicorn_stack_api_web/controllers/account_controller_test.exs
* creating lib/unicorn_stack_api_web/controllers/fallback_controller.ex
* creating lib/unicorn_stack_api/accounts/account.ex
* creating priv/repo/migrations/20230826230744_create_accounts.exs
* creating lib/unicorn_stack_api/accounts.ex
* injecting lib/unicorn_stack_api/accounts.ex
* creating test/unicorn_stack_api/accounts_test.exs
* injecting test/unicorn_stack_api/accounts_test.exs
* creating test/support/fixtures/accounts_fixtures.ex
* injecting test/support/fixtures/accounts_fixtures.ex

Add the resource to your :api scope in lib/unicorn_stack_api_web/router.ex:

    resources "/accounts", AccountController, except: [:new, :edit]


Remember to update your repository by running migrations:

    $ mix ecto.migrate

Remove the following line

|> put_resp_header("location", ~p"/api/accounts/#{account}")

from lib/controllers/account_controller.ex

Create User module

Create the User module by running the command

mix phx.gen.json Users User users account_id:references:accounts name:string gender:string bio:text

This executes as below

==> mix phx.gen.json Users User users account_id:references:accounts name:string gender:string bio:text
* creating lib/unicorn_stack_api_web/controllers/user_controller.ex
* creating lib/unicorn_stack_api_web/controllers/user_json.ex
* creating test/unicorn_stack_api_web/controllers/user_controller_test.exs
* creating lib/unicorn_stack_api/users/user.ex
* creating priv/repo/migrations/20230826235329_create_users.exs
* creating lib/unicorn_stack_api/users.ex
* injecting lib/unicorn_stack_api/users.ex
* creating test/unicorn_stack_api/users_test.exs
* injecting test/unicorn_stack_api/users_test.exs
* creating test/support/fixtures/users_fixtures.ex
* injecting test/support/fixtures/users_fixtures.ex

Add the resource to your :api scope in lib/unicorn_stack_api_web/router.ex:

    resources "/users", UserController, except: [:new, :edit]


Remember to update your repository by running migrations:

    $ mix ecto.migrate

Add validations

Add the following validation to lib/accounts/account.ex

    |> validate_format(:email, ~r/^[^\s]+@[^\s]+$/, message: "Must have the @ sign and no spaces")
    |> validate_length(:email, max: 160)
    |> unique_constraint(:email)

lib/accounts/account.ex should look as below

defmodule UnicornStackApi.Accounts.Account do
  use Ecto.Schema
  import Ecto.Changeset

  @primary_key {:id, :binary_id, autogenerate: true}
  @foreign_key_type :binary_id
  schema "accounts" do
    field :email, :string
    field :hashed_password, :string

    timestamps()
  end

  @doc false
  def changeset(account, attrs) do
    account
    |> cast(attrs, [:email, :hashed_password])
    |> validate_required([:email, :hashed_password])
    |> validate_format(:email, ~r/^[^\s]+@[^\s]+$/, message: "Must have the @ sign and no spaces")
    |> validate_length(:email, max: 160)
    |> unique_constraint(:email)
  end
end

Add the belongs_to relationship to lib/users/user.ex as below


belongs_to :account, UnicornStackApi.Accounts.Account

which replaces the line


field :account_id, :binary_id

lib/users/user.ex should look as below

defmodule UnicornStackApi.Users.User do
  use Ecto.Schema
  import Ecto.Changeset

  @primary_key {:id, :binary_id, autogenerate: true}
  @foreign_key_type :binary_id
  schema "users" do
    field :name, :string
    field :gender, :string
    field :bio, :string
    belongs_to :account, UnicornStackApi.Accounts.Account

    timestamps()
  end

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

Add the following line to lib/accounts/account.ex

has_one :user, UnicornStackApi.Users.User

lib/accounts/account.ex should now look as below

defmodule UnicornStackApi.Accounts.Account do
  use Ecto.Schema
  import Ecto.Changeset

  @primary_key {:id, :binary_id, autogenerate: true}
  @foreign_key_type :binary_id
  schema "accounts" do
    field :email, :string
    field :hashed_password, :string
    has_one :user, UnicornStackApi.Users.User
    timestamps()
  end

  @doc false
  def changeset(account, attrs) do
    account
    |> cast(attrs, [:email, :hashed_password])
    |> validate_required([:email, :hashed_password])
    |> validate_format(:email, ~r/^[^\s]+@[^\s]+$/, message: "Must have the @ sign and no spaces")
    |> validate_length(:email, max: 160)
    |> unique_constraint(:email)
  end
end

Update the below 2 lines in lib/users/user.ex

    |> cast(attrs, [:account_id, :name, :gender, :bio])
    |> validate_required([:account_id])

lib/users/user.ex should now look as below

defmodule UnicornStackApi.Users.User do
  use Ecto.Schema
  import Ecto.Changeset

  @primary_key {:id, :binary_id, autogenerate: true}
  @foreign_key_type :binary_id
  schema "users" do
    field :name, :string
    field :gender, :string
    field :bio, :string
    belongs_to :account, UnicornStackApi.Accounts.Account

    timestamps()
  end

  @doc false
  def changeset(user, attrs) do
    user
    |> cast(attrs, [:account_id, :name, :gender, :bio])
    |> validate_required([:account_id])
  end
end

Run migrations

Run mix ecto.migrate

mix ecto.migrate

This should execute as below


==> mix ecto.migrate
Compiling 10 files (.ex)
warning: no route path for UnicornStackApiWeb.Router match "/api/users/#{user}"
  lib/unicorn_stack_api_web/controllers/user_controller.ex:18: UnicornStackApiWeb.UserController.create/2

Generated unicorn_stack_api app

20:19:56.104 [info] == Running 20230826230744 UnicornStackApi.Repo.Migrations.CreateAccounts.change/0 forward

20:19:56.105 [info] create table accounts

20:19:56.124 [info] create index accounts_email_index

20:19:56.128 [info] == Migrated 20230826230744 in 0.0s

20:19:56.147 [info] == Running 20230826235329 UnicornStackApi.Repo.Migrations.CreateUsers.change/0 forward

20:19:56.147 [info] create table users

20:19:56.157 [info] create index users_account_id_name_index

20:19:56.160 [info] == Migrated 20230826235329 in 0.0s

Add account as below

==> iex -S mix
warning: no route path for UnicornStackApiWeb.Router matches "/api/users/#{user}"
  lib/unicorn_stack_api_web/controllers/user_controller.ex:18

Erlang/OTP 26 [erts-14.0.2] [source] [64-bit] [smp:12:12] [ds:12:12:10] [async-threads:1] [jit] [dtrace]

Interactive Elixir (1.15.4) - press Ctrl+C to exit (type h() ENTER for help)
iex(1)> UnicornStackApi.Accounts.create_account(%{email: "codesdktutorials@gmail.com", hashed_password: "hashedpassword"})

Check the DB to check that the email was added to the accounts table

We continue with authentication in Part 3