1. Creating The Project - abarr/remote GitHub Wiki

Based on the requirements, I created a new application using Phoenix; this will give me a template to support business logic implementation via contexts, a management framework for Postgres and a web server for hosting the API.

Using mix and because there is no web UI pass in the following flags --no-live, --no-mailer, --no-gettext, --no-assets and --no-html.

$ mix phx.new remote --no-live --no-mailer --no-gettext --no-assets --no-html
# solution created
$ cd remote && mix deps.get
# dependencies downloaded

Because I am the only one working on the project, I will remain on the main branch and commit as I progress through the exercise.

Setup Requirments

mix ecto.setup will create a Users table:

Col	Type	Description	Assumptions
id	:integer	Auto-generated	None
points	:integer	A number between 0 and 100	Includes 0 and 100 has valid
inserted_at	:utc_datetime_usec		UTC timestamps to provide maximum flexibility
updated_at	:utc_datetime_usec		UTC timestamps to provide maximum flexibility

It will also seed the database with 1,000,000 rows, each with a points value of 0.

Steps to create a project

1. Add a Users context, User schema, a migration file and a Context API

$ mix phx.gen.context Users User users points:integer

2. Add migrations support for UTC timestamps to the configuration

"/config.config.exs"

config :remote,
  ...
  migration_timestamps: [type: :utc_datetime],
  ...

3. Add type to timestamps in the User schema

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

  @timestamps_opts [type: :utc_datetime] # Added

  schema "users" do
    field :points, :integer

    timestamps()
  end

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

4. Add function to seeds file to generate 1,000,000 users with points value of 0

The most efficient way to generate many rows is to use the Postgres function generate_series.

"/priv/repo/seeds.exs"


Ecto.Adapters.SQL.query!(
  Remote.Repo,
  """
  INSERT INTO users (points, inserted_at, updated_at)
  SELECT 0, now() at time zone 'utc', now() at time zone 'utc'
  FROM generate_series(1, 1000000);
  """
)

5. Ensure it works with ecto.setup

One small change to ensure that if the database exists it is dropped, I moved the ecto.drop call to ecto.setup

"/mix.exs"

defp aliases do
    [
      setup: ["deps.get", "ecto.setup"],
      "ecto.setup": ["ecto.drop","ecto.create", "ecto.migrate", "run priv/repo/seeds.exs"],
      "ecto.reset": ["ecto.setup"],
      test: ["ecto.create --quiet", "ecto.migrate --quiet", "test"]
    ]
  end

With those changes made, I test them

$ mix ecto.setup

The database for Remote.Repo has been dropped
The database for Remote.Repo has been created

11:30:52.069 [info]  == Running 20220110005341 Remote.Repo.Migrations.CreateUsers.change/0 forward

11:30:52.071 [info]  create table users

11:30:52.075 [info]  == Migrated 20220110005341 in 0.0s
[debug] QUERY OK db=5764.1ms queue=1.5ms idle=51.8ms

INSERT INTO users (points, inserted_at, updated_at)
SELECT 0, now() at time zone 'utc', now() at time zone 'utc'
FROM generate_series(1, 1000000);
 []

TODO: The seeds took 5.7 seconds to run; this might be something to research and see if there is any way to reduce the time.

A quick test (I use Postico for accessing Postgres databases):

SELECT COUNT(*) FROM users;
# 1000000