create migration:

$ mix ecto.gen.migration add_payment_service_to_transfers

run all pending migrations:

$ mix ecto.migrate

rollback last applied migration:

$ mix ecto.rollback

NOTE: there is nothing like schema.rb file in Phoenix project - database schema is not dumped to file after running migrations.

primitive column types



Our repo (MyApp.Repo) has three main tasks:

  • to bring in all the common query functions from Ecto.Repo
  • to set the otp_app name equal to our application name
  • to initialize the options passed to the database adapter in init/2



schemas implement Queryable protocol - that is why it’s possible to pass them to Ecto.Repo functions.


def changeset(%User{} = user, attrs) do
  |> cast(attrs, [:uuid])        # params.require(:user).permit(:uuid) (strong parameters)
  |> validate_required([:uuid])  # validates :uuid, presence: true (AM validations)

cast/3 permits and casts parameters to types defined in schema.

changeset vs. Ecto.Changeset.change/2

Ecto.Changeset.change/2 wraps schema inside a changeset (and optionally adding changes) but it DOESN’T call changeset function of this schema => no validations are run and resulting changeset is always valid.

changeset vs. schema struct


“Improved associations and factories” chapter, “Less changesets” section of “What’s new in Ecto 2.1” book

You can now also pass structs to the repository and Ecto will take care of building the changesets for you behind the scenes.

The struct is converted to a Changeset using Ecto.Changeset.change/2, not your own function.

it’s possible to pass both changeset and schema, say, to Repo.insert/2.

in case of changeset, this changeset can be either custom schema changeset or default changeset created by Ecto.Changeset.change/2.

in case of struct, this struct is still converted to changeset but always with Ecto.Changeset.change/2 - all validations defined in custom schema changeset become unavailable and error will be raised only if underlying data store returns error.


associations vs. FK columns


prefer defining associations to specifying FK columns in schema definitions (or else you won’t be able to use them in queries):

schema "cards" do
  # define association:
  belongs_to :user, MyApp.User
  # instead of specifying FK column:
  #field :user_id, :id

add existing association in changeset function

preloading associations


assocations are never preloaded by default - load them explicitly!

associations can be preloaded in:

associations can be loaded in:

building associations

put_assoc vs. cast_assoc


however in both cases corresponding association must be preloaded so that Ecto knows what to do in case it already exists (of course it only makes sense when parent has id field - but it doesn’t raise error otherwise).

build_assoc vs. building association explicitly

# build association using parent struct and association name
user = Repo.get_by(User, name: "John")
comment = Ecto.build_assoc(user, :comments, body: "foo")


# build association explicitly by setting FK column value
user = Repo.get_by(User, name: "John")
comment = %Comment{user_id:, body: "foo"}

The goal of build_assoc is to allow you to work on the association names instead of the key names.

usage examples of put_assoc, build_assoc and cast_assoc

it’s not recommended to use these functions inside schemas since they might require you to make calls to Repo (build_assoc or put_assoc) or use changesets of other schemas (cast_assoc) while it’s preferable to isolate both calls to Repo and usage of different changesets in contexts.

though in What's new in Ecto 2.1 ebook (chapter Many to many and upserts) there is an example of schema in which both put_assoc and Repo are used.

models vs. changesets

Typically, you’d work with a changeset for making modifications to a model via the repo, and you’d work with the model when fetching the data for display.


models have been deprecated in Phoenix 1.3 - they are called schemas now.



use it when you would need callbacks in AR: Ecto.Multi allows to pack functions that should be called after main action (like create or update) - all these functions are named operations in Ecto.Multi parlance.

moreover using Ecto.Multi allows to stop execution if some operation fails and returns {:error, reason} - this error can be returned either automatically from Repo function or manually from functions passed to

in the end Ecto.Multi struct is usually passed to Repo.transaction/1 which rollbacks transaction if any operation fails however calling Repo.transaction/1 in the end is not obligatory (if you don’t want all operations to be run in transaction - for example, they write to filesystem which cannot be rolled back).

it all resembles using monads to handle errors in general and using dry-monads and dry-matcher gems in Ruby in particular (monads in Ruby).

but unlike dry-matcher Ecto.Multi stores additional information in case of failure - not only do we have error itself but also operation name and changes accumulated in previous succeeded operations.

in this regard Ecto.Multi acts more like dry-transaction gem which allows to handle errors arising from particular steps (= operations) with the difference that dry-transaction holds operations (service objects that respond to call) from DI container while multi packs operations (Repo or arbitrary functions).

also don’t forget that there exist several monad libraries for Elixir (e.g. MonadEx) which allow to use this error handling mechanism in any module while Ecto.Multi is used when you deal with persistence and need something to replace callbacks (that is Ecto.Multi is alternative to our custom operations in Rails projects which both persist data and run after_* callbacks manually).

if it’s necessary to get access to result of previous operation it’s necessary to use run functions - say, if you first insert a record and then need to updated it you cannot use Ecto.Multi.update in the 2nd case since you won’t be able to get previously inserted record.

it’s not possible to execute Ecto.Multi operations outside of transaction - use with statement instead.

[Programming Phoenix] prefixes

NOTE: it’s not possible to perform joins across prefixes - data in different prefixes must be completely isolated.

prefixes can be configured on different levels:

global prefix -> schema prefix -> query/repository operation/struct prefix

global prefix


config :rumbl, Rumbl.Repo,
  adapter: Ecto.Adapters.Postgres,
  username: "postgres",
  password: "postgres",
  database: "rumbl_dev",
  hostname: "localhost",
  pool_size: 10,
  after_connect: {Postgrex, :query!, ["SET search_path TO new_prefix", []]}

schema prefix

NOTE: since Ecto 2.1

defmodule Rumbl.User do
  # model/0 in web/web.ex uses Ecto.Schema:
  # use Ecto.Schema
  use Rumbl.Web, :model

  @schema_prefix "new_prefix"
  schema "users" do
    field :name, :string
    field :username, :string
    field :password, :string, virtual: true
    field :password_hash, :string
    has_many :videos, Rumbl.Video


query prefix

> query = Ecto.Queryable.to_query Rumbl.User
#Ecto.Query<from u in Rumbl.User>
> Rumbl.Repo.all %{query | prefix: "new_prefix"}

repository operation prefix

> Rumbl.Repo.all Rumbl.User, prefix: "new_prefix"

struct prefix

if it’s nil it means global prefix is used (“public” or as configured in environment config).

> [video] = Rumbl.Repo.all Rumbl.Video
[%Rumbl.Video{__meta__: #Ecto.Schema.Metadata<:loaded, "videos">, ...}
> new_prefix_video = Ecto.put_meta(video, prefix: "new_prefix")
%Rumbl.Video{__meta__: #Ecto.Schema.Metadata<:loaded, "new_prefix", "videos">, ...}




you cannot update embed unless you set on_replace: :update option:

embeds_one :data, TransferData, on_replace: :update

otherwise calling cast_embed(changeset, :data) will raise error:

** (RuntimeError) you are attempting to change relation :data of
Billing.App.Transfer but the `:on_replace` option of
this relation is set to `:raise`.

after that you are allowed to pass updated fields as a map only:

** (RuntimeError) you have set that the relation :data of Billing.App.Transfer
has `:on_replace` set to `:update` but you are giving it a struct/
changeset to put_assoc/put_change.

Since you have set `:on_replace` to `:update`, you are only allowed
to update the existing entry by giving updated fields as a map or
keyword list or set it to nil.

when using on_replace: :update you cannot update embed by passing struct or changeset - use map or keywords list of fields instead:

** (exit) an exception was raised:
    ** (RuntimeError) you have set that the relation :data of Billing.App.Transfer
has `:on_replace` set to `:update` but you are giving it a struct/
changeset to put_assoc/put_change.

Since you have set `:on_replace` to `:update`, you are only allowed
to update the existing entry by giving updated fields as a map or
keyword list or set it to nil.
- transfer = md |> App.get_own_transfer_by_md!(user)
- # don't use atom key for pares or else it can be accessed
- # using only atom key in API.complete_transfer/1 - key will
- # be stringified only when transfer is read from DB again
- data = Map.put(, "pares", attrs["pares"])
- transfer
- |> Transfer.update_changeset(%{data: data})
- |> Repo.update()
+ transfer = md |> App.get_own_transfer_by_md!(user)
+ attrs = %{data: %{pares: pares}}
+ transfer
+ |> Transfer.update_changeset(attrs)
+ |> Repo.update()

complete example:

iex> transfer = Billing.Repo.get(Billing.App.Transfer, 11)
iex> attrs = %{data: %{acs_url: "foo"}}
iex> transfer |> cast(attrs, []) |> cast_embed(:data) |> Repo.update()

nested embeds



defmodule Billing.App.Transfer do
  use Ecto.Schema
  import Ecto.Changeset

  alias Billing.App.{Card, TransferData}

  schema "transfers" do
    field :fee, :decimal
    embeds_one :data, TransferData, on_replace: :update

    timestamps type: :utc_datetime

  # > casting embeds with cast/4 is not supported,
  # > use cast_embed/3 instead
  @doc false
  def update_changeset(%__MODULE__{} = transfer, attrs) do
    |> cast(attrs, [:fee])
    |> cast_embed(:data)


defmodule Billing.App.TransferData do
  use Ecto.Schema
  import Ecto.Changeset

  @primary_key false
  embedded_schema do
    field :acs_url, :string
    embeds_one :error, Error, primary_key: false, on_replace: :update do
      field :service, :integer

  # changeset/2 is called by cast_embed/3 by default
  def changeset(%__MODULE__{} = data, attrs) do
    |> cast(attrs, [:acs_url])
    |> cast_embed(:error, with: &errors_changeset/2)

  defp errors_changeset(%__MODULE__.Error{} = error, attrs) do
    |> cast(attrs, [:service])

pipe vs. keyword syntax

pipe syntax
pipe-based syntax, macro-based syntax, expression syntax, pipe expression syntax
keyword syntax
keyword-based syntax, keyword query syntax

defmodule Sample.App do
  import Ecto.Query
  alias Sample.{Weather, Repo}

  def pipe_query do
    |> where(city: "Tula")
    |> order_by(:temp_lo)
    |> limit(10)
    |> Repo.all()

  def keyword_query do
    query = from w in Weather,
         where: w.prcp > 0 or is_nil(w.prcp),
         select: w

IMO pipe syntax is more suitable for simple queries:

User |> order_by(desc: :inserted_at)
# reads better than
from u in User, order_by: [desc: u.inserted_at]

while keyword syntax is geared towards complex queries.



use map Ecto type for JSONB fields.

…my question is about “map” vs “jsonb”

They are equivalent when using postgres. map is an abstract type and each database adapter can choose the actual representation - for postgres it’s jsonb.

default value

default value can be set in either migration or schema or both but in general it’s recommended to set default value in migration only unless it’s very application specific (not just false or 0) or you need different default values in different schemas for the same table in DB.

(how to) check if JSONB column is empty

JSONB column is empty if it contains empty object or empty array:

|> where([u], == fragment("'{}'::jsonb"))
|> MyApp.Repo.all()

|> where([u], == fragment("'[]'::jsonb"))
|> MyApp.Repo.all()



Upsert will not change ID primary key of already existing rows, just update other fields.

We should replace all except the primary key.

=> upsert replaces all fields except for ID (including inserted_at)!

UPDATE (2018-11-08)


it’s now possible to replace only certain fields in Ecto 3:

on_conflict: {:replace, [:foo, :bar, :baz]}

when using this option:

all replace options:

:replace_all replace all values on the existing row with the values in the schema/changeset, including autogenerated fields such as inserted_at and updated_at

:replace_all_except_primary_key same as above except primary keys are not replaced

replace only specific columns. This option requires conflict_target

replace_all tries to replace ALL fields including timestamps, primary and foreign keys - if you try to insert conflicting association without parent ID already set (say, via cast_assoc), you’ll get Ecto.ConstraintError error:

** (Ecto.ConstraintError) constraint error when attempting to insert struct:

  * messages_conversation_id_fkey (foreign_key_constraint)