Skip to content
Tutorials Center
My learning

Topics

Lesson 15 of 38 ยท Ecto Schemas and Queries

Ecto Schemas, Migrations and the Repo

Define Ecto schemas, create and run migrations, add indexes and constraints, and use the Repo for insert, get, update and delete in a Phoenix application.

Ecto is the data layer of Phoenix. It has four main parts: the Repo (talks to the database), schemas (map tables to Elixir structs), migrations (change the database structure over time) and changesets (validate and track changes). Ecto is deliberately not a classic ORM: it does not hide SQL or keep objects in sync with rows. You write explicit code that says what to read and write.

In this lesson you will create a schema and its migration, run it, and use the Repo for basic reads and writes. Changesets get their own lesson next but you will see a tiny one here.

Ecto needs a database and Elixir, so the Ecto code is read-only here. TryIts model migrations and a repo using in-memory JavaScript.

Generating a schema

Terminal
mix phx.gen.schema Blog.Post posts title:string body:text published:boolean

This creates a schema module and a migration. Read both.

ELIXIR
defmodule Blog.Post do
  use Ecto.Schema
  import Ecto.Changeset

  schema "posts" do
    field :title, :string
    field :body, :string
    field :published, :boolean, default: false

    timestamps(type: :utc_datetime)
  end

  def changeset(post, attrs) do
    post
    |> cast(attrs, [:title, :body, :published])
    |> validate_required([:title, :body])
  end
end

The schema "posts" block declares the table name and its fields. An id primary key (integer by default) is added for you, and timestamps() adds inserted_at and updated_at. Calling %Blog.Post{} creates an empty struct with the defaults; %Blog.Post{title: "Hi"} fills a field.

Field types

Ecto type Elixir value Typical column
:string binary varchar(255)
:integer, :float, :decimal numbers / Decimal integer, float, numeric
:boolean true/false boolean
:date, :time, :utc_datetime, :naive_datetime date and time structs date/time columns
:map map jsonb
{:array, :string} list array column (PostgreSQL)
Ecto.Enum atom string or integer
:binary_id UUID uuid

Use Ecto.Enum for a fixed set of values: field :status, Ecto.Enum, values: [:draft, :published, :archived] stores a string and loads an atom. Use :decimal for money, never :float.

Migrations

A migration is an Elixir file in priv/repo/migrations/, named with a timestamp so they run in order:

ELIXIR
defmodule Blog.Repo.Migrations.CreatePosts do
  use Ecto.Migration

  def change do
    create table(:posts) do
      add :title, :string, null: false
      add :body, :text, null: false
      add :published, :boolean, default: false, null: false

      timestamps(type: :utc_datetime)
    end

    create index(:posts, [:published])
    create unique_index(:posts, [:title])
  end
end
Terminal
mix ecto.migrate        # apply pending migrations
mix ecto.rollback       # undo the last one
mix ecto.migrations     # show which have run

change/0 describes a reversible step, so Ecto can roll it back automatically. When a step cannot be reversed automatically, such as running raw SQL, write up/0 and down/0 instead. Ecto records applied migrations in a schema_migrations table.

Useful migration commands:

ELIXIR
alter table(:posts) do
  add :slug, :string
  remove :legacy_field
  modify :title, :string, size: 120
end

rename table(:posts), :body, to: :content
drop index(:posts, [:published])
execute "UPDATE posts SET slug = lower(title)"

Never edit a migration that has already run on production. Write a new migration for the change. Otherwise the database and the history disagree.

Constraints and indexes

Put rules in the database as well as in changesets: null: false, unique_index, foreign keys and check constraints. The database is the last line of defense against race conditions that application code cannot prevent. A unique index also makes lookups by that column fast.

The Repo

All database access goes through Blog.Repo:

ELIXIR
alias Blog.{Repo, Post}

Repo.insert(%Post{title: "Hello", body: "World"})   # {:ok, %Post{id: 1, ...}}
Repo.get(Post, 1)                                   # %Post{} or nil
Repo.get!(Post, 1)                                  # raises if missing
Repo.get_by(Post, title: "Hello")                   # first match or nil
Repo.all(Post)                                      # list of structs
Repo.update(Post.changeset(post, %{title: "New"}))  # {:ok, post} | {:error, changeset}
Repo.delete(post)                                   # {:ok, deleted}
Repo.aggregate(Post, :count)                        # 3

Notice {:ok, struct} and {:error, changeset} return values. Functions ending in ! (insert!, get!) raise instead; use them where failure is a bug rather than a normal outcome.

Seeds

priv/repo/seeds.exs is a plain script run by mix run priv/repo/seeds.exs (and by mix ecto.reset). Use it to create development data.

A migration runner, modeled

JS Ordered, once-only migrations

Press Run (or Ctrl + Enter) to see the console output here.

No output.

The second migrate() does nothing, because applied versions are remembered, the same job schema_migrations does.

A Repo, modeled

JS insert, get and update with ok/error tuples

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What does a migration do?

    Migrations are ordered scripts that alter the schema, tracked in schema_migrations.
  2. 2. Why should you not edit a migration that already ran in production?

    Applied migrations are not re-run. A new migration carries the change forward.
  3. 3. Which type should hold money amounts?

    :decimal avoids floating point rounding errors.
  4. 4. What does Repo.get!(Post, 99) do when no row exists?

    Bang functions raise; Phoenix turns that error into a 404 response.

Summary

  • A schema maps a table to a struct; timestamps() and an id come by default.
  • Migrations change the database in order, are tracked, and must not be edited after deployment.
  • Back rules with database constraints and indexes, not only with changesets.
  • The Repo returns {:ok, struct} or {:error, changeset}; bang functions raise.

Next: Ecto queries