SQL Basics & Database Migrations

Before you do anything else please complete this anonymous 1 minute survey. If you feel uncomfortable doing this in class you may also do this at home but please don't forget.

In the video you have learned a tiny bit about ecto and its sql based underlying structured postgres database.

Today we do want to complete a simple CRUD (Create, Read, Update, Delete ) API for our planets controller. We will be completely replacing the content of our App.Planet module and store planet data in our SQL database. Make sure to back up via git now in case you astronomically mess up and need to revert your git repository.

Lets start by creating a planet schema file:

defmodule App.Planets.Planet do
  use Ecto.Schema

  import Ecto.Changeset

  schema "planets" do
    field :name, :string
    field :distance, :float
    field :orbital_period, :float
  end

  @required ~w(name distance orbital_period)a
  def changeset(lecture, attrs) do
    lecture
    |> cast(attrs, @required)
    |> validate_required(@required)
    |> validate_number(:distance, greater_than: 0)
    |> validate_number(:orbital_period, greater_than: 0)
  end
end

As explained in the video, the main purpose of this file is to describe a Planet, with the attributes it has, the sql table it is contained in and how we can change it in form of a Ecto Changeset. When we load planets from the database they come in form of a struct. The short section in the documentation does a good job explaining the difference to a regular map. We then want to create a migration and update our table exactly as i did in the video. Run this command:

mix ecto.gen.migration create_planets

This will create the empty migration file in wich we still have to create our table:

def change do
  create table(:planets) do
    add :name, :string
    add :distance, :float
    add :orbital_period, :float
  end
end

If you have added additional attributes during your own exploration feel free to add them to your schema and migration file! Refer to the Ecto Schema documentation for available data types and how-to.

We can then migrate our database up to the latest version via

mix ecto.migrate

This step is automatically done during deployment on linuxremote so be careful to not migrate any incomplete table or you'll have to create additional migration files to fix it afterwards. If your database is already up-to-date mix ecto.migrate will simply do nothing.

At this time we can start working on our context. I have started an API for you, your mission is to complete the remaining functions by exploring in the documentation for the missing pieces we need.

To help you start I have already completed the functions i featured in the video:

defmodule App.Planets do
  @moduledoc """
  A context to retrieve data of our solar system.
  """
  import Ecto.Query, warn: false

  alias App.Repo
  alias App.Planets.Planet

  def create_planet(attrs) do
    %Planet{}
    |> Planet.changeset(attrs)
    |> Repo.insert()
  end

  def list_planets, do: Repo.all(Planet)

  def list_planets(:sorted_by_name) do
    from(p in Planet, order_by: p.name)
    |> Repo.all()
  end

  # gets a planet by it's id.
  def get_planet(planet_id) do
    # TODO

  end

  # gets a planet struct randomly.
  def get_random_planet do
    # TODO

  end

  def update_planet(%Planet{} = planet, attrs) do
    # TODO
  end

  # deletes a planet struct
  def delete_planet(%Planet{} = planet) do
    # TODO
  end
end

Note that this file deletes your in-memory planet list so don't be surprised if your pages temporarily break because we are switching storage location.

Once you have the create_planet/1 function ready also populate and run your seed file as I showed in the video.

Once you are confident your context should be working the way it is supposed to you can start working on completing tests in test/app/planets_test.exs:

defmodule App.PlanetsTest do
  use App.DataCase

  alias App.{Repo, Planets}
  alias App.Planets.Planet

  describe "list_planets" do
    setup do
      Repo.insert!(%Planet{id: 1, name: "B", distance: 1.0, orbital_period: 1.0})
      Repo.insert!(%Planet{id: 2, name: "A", distance: 1.0, orbital_period: 1.0})
      %{}
    end

    test "/0 returns all planets" do
      assert [%Planet{id: id1}, %Planet{id: id2}] = Planets.list_planets()
      assert id1 == 1
      assert id2 == 2
    end

    test "/1 lists planets sorted alphabetically" do
      assert [%Planet{name: "A"}, %Planet{name: "B"}] = Planets.list_planets(:sorted_by_name)
    end
  end

  test "create_planet/1 creates a planet with valid inputs" do
    assert [] = Planets.list_planets()
    Planets.create_planet(%{name: "A", distance: 1.0, orbital_period: 1.0})
    assert [%Planet{name: "A"}] = Planets.list_planets()
  end

  test "create_planet/1 does not create a planet with invalid inputs" do
    assert [] = Planets.list_planets()

    assert {:error,
            %Ecto.Changeset{
              valid?: false,
              errors: errors
            }} = Planets.create_planet(%{distance: -1.0, orbital_period: 0})

    assert [] = Planets.list_planets()

    assert {:name, {"can't be blank", _}} = List.keyfind(errors, :name, 0)
    assert {:distance, {"must be greater than %{number}", _}} = List.keyfind(errors, :distance, 0)

    assert {:orbital_period, {"must be greater than %{number}", _}} =
            List.keyfind(errors, :orbital_period, 0)
  end

  test "delete/1 deletes a planet struct" do
    planet = Repo.insert!(%Planet{id: 1, name: "B", distance: 1.0, orbital_period: 1.0})

    [planet] = Planets.list_planets()

    {:ok, planet} = Planets.delete_planet(planet)
    [] = Planets.list_planets()
  end

  # TODO uncomment and complete the following tests

  # test "get/1 gets a planet by id" do

  # end

  # test "get/1 returns nil if no planet with the given id exists" do

  # end

  # test "get_random_planet/1 gets a planet randomly" do

  # end

  # test "update_planet/2 updates a planet struct with valid attrs" do

  # end

  # test "update_planet/2 doesn't update a planet struct with invalid attrs" do

  # end
end

Please examine closely how I did test stuff in the completed test functions. You should be able to deduce on how to test the remaining API functions you created.

Note that database migrations are not automatically performed for the test environment. If you migrate your database do so before running tests:

MIX_ENV=test mix ecto.migrate
MIX_ENV=test mix coveralls.html

Finally we like to fix our pages and replace all the functions that used to load from the @planets module attribute with function calls to our Planet Context.

Don't forget to alias or import your Context into your PlanetController:

alias App.Planets

Note if you want to get your planets seeded on linuxremote3 you will need to do so manually:

linuxremote3> module load elixir erlang
linuxremote3> cd ~/workspace/csci379
linuxremote3> MIX_ENV=prod mix run priv/repo/seeds.exs

Seeding is generally only performed once - once you create (or reset) the database.

This is a milestone lecture and a big leap forward in our capacity to create an awesome web application. Next we'll explore how to build forms and speed up the process of creating schemas, contexts, and their tests through automation.