Migration database constraint

Question

I'm writing a migration file and one of the fields I'd like to set a DB (Postgres) constraint for:

 def change do
   create table(:reviews) do
     add(:title, :string)
     add(:contents, :string)
     add(:stars, :integer)
     add(:user_id, references(:users), null: false)
     add(:user_id, references(:users), null: false)

     timestamps()
  end

Id like to set a constraint on the stars field to be integer values only 1-5. How can I do this with Ecto.Migration?

Answer 1

How can I do this with Ecto.Migration

Ecto.Migration.create(Ecto.Migration.constraint/3)

foo/priv/repo/migations/....create_reviews.exs:

defmodule Foo.Repo.Migrations.CreateReviews do
  use Ecto.Migration

  def change do
    create table("reviews") do
      add :title, :string
      add :contents, :string
      add :stars, :integer

      timestamps()
    end

    create constraint("reviews",
                      :stars_range, 
                      check: "stars>=1 and stars<=5")
  end
end

foo/lib/foo/review.ex:

defmodule Foo.Review do
  use Ecto.Schema
  import Ecto.Changeset
  require Logger

  schema "reviews" do
    field :title, :string
    field :contents, :string
    field :stars, :integer

    timestamps()
  end

  def changeset(%Foo.Review{}=review, attrs \\ %{}) do
    review
    |> cast(attrs, [:title, :contents, :stars])
    |> check_constraint(
        :stars,
        name: :stars_range,
        message: "stars must be between 1 and 5 (inclusive)"
      )
  end

end

foo/lib/foo.ex:

defmodule Foo do
  alias Foo.Review
  require Logger

  @repo Foo.Repo

  def list_reviews do
    @repo.all(Review)
  end

  def insert_review(attrs) do
    changeset = Review.changeset(%Review{}, attrs)
    @repo.insert(changeset)
  end

  def delete_review(%Review{}=review) do
    @repo.delete(review)
  end

end

In iex:

~/phoenix_apps/foo$ iex -S mix
Erlang/OTP 20 [erts-9.3] [source] [64-bit] [smp:4:4] [ds:4:4:10] [async-threads:10] [hipe] [kernel-poll:false]
Interactive Elixir (1.8.2) - press Ctrl+C to exit (type h() ENTER for help)

iex(1)> reviews = Foo.list_reviews()
[debug] QUERY OK source="reviews" db=1.0ms decode=4.0ms queue=1.5ms
SELECT r0."id", r0."title", r0."contents", r0."stars", r0."inserted_at", r0."updated_at" FROM "reviews" AS r0 []
[]

iex(2)> Foo.insert_review(%{title: "book", contents: "good", stars: 4})
[debug] QUERY OK db=5.0ms queue=2.4ms
INSERT INTO "reviews" ("contents","stars","title","inserted_at","updated_at") VALUES ($1,$2,$3,$4,$5) RETURNING "id" ["good", 4, "book", ~N[2019-07-09 23:26:44], ~N[2019-07-09 23:26:44]]
{:ok,
 %Foo.Review{
   __meta__: #Ecto.Schema.Metadata<:loaded, "reviews">,
   contents: "good",
   id: 5,
   inserted_at: ~N[2019-07-09 23:26:44],
   stars: 4,
   title: "book",
   updated_at: ~N[2019-07-09 23:26:44]
 }}

iex(3)> Foo.insert_review(%{title: "movie", contents: "shite", stars: 0})
[debug] QUERY ERROR db=10.3ms queue=2.0ms
INSERT INTO "reviews" ("contents","stars","title","inserted_at","updated_at") VALUES ($1,$2,$3,$4,$5) RETURNING "id" ["shite", 0, "movie", ~N[2019-07-09 23:27:13], ~N[2019-07-09 23:27:13]]
{:error,
 #Ecto.Changeset<
   action: :insert,
   changes: %{contents: "shite", stars: 0, title: "movie"},
   errors: [
     stars: {"stars must be between 1 and 5 (inclusive)",
      [constraint: :check, constraint_name: "stars_range"]}
   ],
   data: #Foo.Review<>,
   valid?: false
 >}

iex(4)> reviews = Foo.list_reviews()                                     
[
  %Foo.Review{
    __meta__: #Ecto.Schema.Metadata<:loaded, "reviews">,
    contents: "good",
    id: 5,
    inserted_at: ~N[2019-07-09 23:26:44],
    stars: 4,
    title: "book",
    updated_at: ~N[2019-07-09 23:26:44]
  }
]
[debug] QUERY OK source="reviews" db=4.7ms
SELECT r0."id", r0."title", r0."contents", r0."stars", r0."inserted_at", r0."updated_at" FROM "reviews" AS r0 []

iex(5)> Foo.insert_review(%{title: "theater", contents: "really, really good", stars: 6})
[debug] QUERY ERROR db=1.6ms queue=2.2ms
INSERT INTO "reviews" ("contents","stars","title","inserted_at","updated_at") VALUES ($1,$2,$3,$4,$5) RETURNING "id" ["really, really good", 6, "theater", ~N[2019-07-09 23:28:07], ~N[2019-07-09 23:28:07]]
{:error,
 #Ecto.Changeset<
   action: :insert,
   changes: %{contents: "really, really good", stars: 6, title: "theater"},
   errors: [
     stars: {"stars must be between 1 and 5 (inclusive)",
      [constraint: :check, constraint_name: "stars_range"]}
   ],
   data: #Foo.Review<>,
   valid?: false
 >}

iex(6)> reviews = Foo.list_reviews()                                                     
[debug] QUERY OK source="reviews" db=0.5ms
SELECT r0."id", r0."title", r0."contents", r0."stars", r0."inserted_at", r0."updated_at" FROM "reviews" AS r0 []
[
  %Foo.Review{
    __meta__: #Ecto.Schema.Metadata<:loaded, "reviews">,
    contents: "good",
    id: 5,
    inserted_at: ~N[2019-07-09 23:26:44],
    stars: 4,
    title: "book",
    updated_at: ~N[2019-07-09 23:26:44]
  }

]