A Quick Glance Around Ecto v3 (ALPHA)

In order to help people as much as possible, I am trying to write this book in 7th-grade plain English.

English isn't my mother language. When you find any error, it would be nice if you can email me or create an issue.

This a quote.

The shell commands is prefixed by $ which is the default prompt for ordinary UNIX users.

For example:

$ ls -al ~

When something has been explained in official documents, it won't be described again. I will guide you to read the related documents.

For example, when you see something like please read h Ecto.Schema, it means:

• read docs in a terminal by typing h Ecto.Schema in IEx.
• read docs in a web browser by visiting Ecto.Schema.

Create a project called paper:

$ mix new paper --sup

The --sup option is required. It ensures that the generated application has a supervision tree, which is required by Ecto.

Two packages are required:

• ecto_sql
• postgrex - database adapter for PostgreSQL

Edit mix.exs:

defp deps do
  [
    {:ecto_sql, "~> 3.0"},
    {:postgrex, ">= 0.0.0"}
  ]
end

Fetch dependencies:

$ mix deps.get

Ecto has additional formatting rules. In order to apply these rules, following content should be added.

Edit .formatter.exs for formatting general Elixir code:

# Used by "mix format"
[
  import_deps: [:ecto],
  inputs: ["*.{ex,exs}", "{config,lib,test}/**/*.{ex,exs}", "priv/*/seeds.exs"],
  subdirectories: ["priv/*/migrations"]
]

Edit priv/repo/migrations/.formatter.exs for formatting migrations:

[
  import_deps: [:ecto_sql],
  inputs: ["*.exs"]
]

Now, Ecto is ready to communicate with the database.

A migration is a file includes a set of instructions which changes database structure.

Ecto provides a Mix task for generating a template for migrations:

$ mix ecto.gen.migration <migration name>

After executing above command, a new migration file containing a module with empty change function will be generated in priv/repo/migrations.

In relational database, there are 3 types of relationships:

As we saw in Sample Data Model, we have four relationships, and they cover all types of relationships:

• the relationship between authors and posts is one-to-many.
• the relationship between posts and comments is one-to-many.
• the relationship between posts and tags is many-to-many.
• the relationship between posts and permalinks is one-to-one.

Next, we will take the sample data model as an example, and demonstrate how to create migrations for all of these relationships.

We have introduced how to apply migration in above sections. Read more details at:

$ mix help ecto.migrate

Read more details at:

$ mix help ecto.rollback

When creating migrations, we have specified some column types, such as :varchar or :text. These data types supported by database can be used directly in migrations. And, data types with database-specific options can be used, too. Such as:

• :"int unsigned"
• :"time without time zone"
• …

Besides, Ecto also maps primitive types to the appropriate database data types by the database adapters. Such as:

• map :string to :varchar(255)
• map :binary to :bytea
• …

Read more details at h Ecto.Migration.index.

Ecto.Migration.references have two options which should be explained here - :on_delete and :on_update.

The official docs don't talk much about them. But, we can know the details by reading related source code and corresponding docs of PostgreSQL.

• Ecto Migrations: Simple to Complex

The database is ready, it's time to insert data into it.

Ecto adopts The Repository Pattern for accessing underlying data store. In this pattern, there's a role called Repository who controls all the communications between Ecto and data store.

The general lifecyle of queries looks like:

1. All the queries are submitted to the Repository.
2. The Repository transforms the queries and send them to data store.
3. The Repository handles the responses from data store and transforms them into easy-to-use format.

The keypoint is the Repository acts as a gateway of data store. When you want to make changes to data store, talk to Repository.

Ecto.Repo is the so-called Repository in Ecto.

But, Ecto.Repo isn't used directly. It should be used in a self-created module. When creating the sample application, we have created the module called Paper.Repo.

Repo provides low level API to complete CRUD operations directly.

All *_all functions support :returning option for selecting which fields to return. Read their documents for more details.

Repo is a regular Elixir module, we can extend it as normal. It is useful when we want to:

• encapsulate tediously long options for particular functions.
• add functions that Ecto doesn't support currently.
• …

For example, adding a counting function:

defmodule Paper.Repo do
  use Ecto.Repo,
    otp_app: :paper,
    adapter: Ecto.Adapters.Postgres

  def count(table) do
    aggregate(table, :count, :id)
  end
end

Call the added function:

Paper.Repo.count("authors")

Read more details at h Ecto.Repo.

We have created all required schemas, it is time to do some experiments with them.

Before starting IEx, it is better to add configurations for it. The configurations help us type less every time we starting IEx.

Edit .iex.exs:

import Ecto.Query

alias Paper.Repo
alias Paper.CMS.{Author, Permalink, Tag, Comment, Post}

Now, let's start it:

$ iex -S mix

As you see, without Ecto.Changeset, we can't insert or update data reliably.

Next, we will introduce Ecto.Changeset.

%Ecto.Changeset{} is the struct holding all the changes which will be performed to the database.

For brevity, in the following sections, I will use %Changeset{} instead of %Ecto.Changeset{}.

General updating process is broke up into 3 stages:

1. getting changes
2. validating changes
3. sending changes to database

raw_data
|> get_changes
|> validate_changes
|> send_changes_to_database

Next, we will talk about each step in detail.

The first step is getting changes.

Depending on where the data is coming from, this step can be done in two different ways.

When working with changes provided by external data, we should also validate the changes before sending the to database.

Ecto provides two kinds of utilities for validating. They perform similar functions, but differ in implementation:

• validations
• constraints

Last step, send %Changeset{} to database with functions provided by Repo, such as Repo.insert, Repo.update, etc.

We have known all the necessary things about Ecto.Changeset. It's time to write update policies for schemas.

Read more details at Ecto.Changeset.

There are two types of syntax when using Ecto.Query:

• keyword syntax
• expression syntax

Following SQL can be wrote into above two types of syntax:

SELECT a.name, a.bio FROM authors AS a

A query is created and used in following steps:

1. creating a query
2. committing a query

Ecto.Adapters.SQL.to_sql/3 is used for translating a query to corresponding SQL statement. We can use this function to help us inspecting the details of queries.

For example:

query = from a in "authors", where: a.id == 2, select: [:name]

Ecto.Adapters.SQL.to_sql(:all, Repo, query)
#=> {"SELECT a0.\"name\" FROM \"authors\" AS a0 WHERE (a0.\"id\" = 2)", []}

# equal to
Repo.to_sql(:all, query)
#=> {"SELECT a0.\"name\" FROM \"authors\" AS a0 WHERE (a0.\"id\" = 2)", []}

A query binding is a variable referring to the table of the query. You can treat it as table alias in SQL.

There are two types of bindings:

• positional bindings
• named bindings

Read more details at Composition section in h Ecto.Query.

Read h Ecto.Query.API for a complete list of all available API.

Best abstractions offer escape hatch.

– Programming Phoenix

Ecto can't represent all possible queries with its own syntax, so it provides two backup plans:

1. Ecto.Query.API.fragment/1: generate a SQL fragment which can be inserted into a Ecto query.
2. Repo.query / Repo.query!: run a raw SQL with parameterized values.

Read more details in their docs.

Ecto.Query provides two macros for filtering:

• where
• or_where

When using unions, the two queries need to have result sets with the same column names and data type.

Supported unions:

Read more details at h Ecto.Query.order_by.

When sorting rows by multiple columns specified by order_by, the records will grouped and sorted by first column, then by the second column, and so on.

When ordering, you should also notice NULL value. Some databases put them first, others put them last. If you want to control the order explicitly, try following options:

• :asc_nulls_last
• :asc_nulls_first
• :desc_nulls_last
• :desc_nulls_first

When you want to filtering rows after grouping, you should use having and or_having, rather than where and or_where.

Read more details at h Ecto.Query.group_by.

Joins are required when query across multiple tables at once.

Read more details at h Ecto.Query.join.

Ecto queries are composable. This feature can help us to break large queries into small reusable queries. Small queries are easy to read and maintain, which is good for us.

Read more details at Composition section in h Ecto.Query.

As you see, we are using table name string in above queries. But, with a schema, we can get better experiences, because it contains:

• the type info of fields, therefore, type conversion can be done automatically.
• the list of fields, therefore, :select option isn't necessary when querying.

Give it an example. Without a schema, we have to do type conversion and specify the :select manually:

author_id = "2"
query = from "authors",
  where: [id: type(^author_id, :integer)],
  select: [:name]

Repo.all(query)

With a schema, query can be simpler:

author_id = "2"
query = from Author, where: [id: ^author_id]

Repo.all(query)

Read more at h Ecto.Query.

Ecto not only supports relational database, but also other types of data sources. Because of that, it's not suitable to describe the relationship between data sources by the terminology - relationship which has been used in relational database.

Association is the term which is used by Ecto to describe the relationship between data sources. You can think it as a superset of relationship.

When inserting or updating records, there's two questions should be answered first:

1. What is the type of records? parent or child?
2. If the type is child, how many children records do you want to insert or update?

• cast_assoc is used for casting external data, it will result in multiple INSERT / UPDATE / DELETE queries.
• put_assoc is used for defining associations between existing records.
• build_assoc is used for building a record associated with another record.

Pic from Understanding Associations in Elixir's Ecto

You may handling uniqueness problem in this way:

Repo.get_by(Tag, name: "Tech") || Repo.insert!(%Tag{name: "Tech"})

But, above operation is unsafe - not an atomic operation. In certain conditions, Ecto will try to insert two same data at the same time, which will cause a database constraint error.

Instead, we shouldn't handle this uniqueness problem by ourselves. Just insert the data, and let database manage the conflict data - this feature is known as upsert.

In Ecto, the upsert feature is implemented as :on_conflict option of Repo.insert!. A possible example would be:

# ignore conflicting data
Repo.insert!(%Tag{name: "Tech", on_conflict: :nothing})

There are lots of available values for :on_conflict option, read more details at h Repo.insert.

on_conflict: :nothing will only return the struct containing conflict fields. If you insert a tag with specified name, and want to get the tag no matter it's conflict or not, use following code:

# force update on conflict record, which returns the struct with all fields.
Repo.insert(
  %Tag{name: name},
  on_conflict: {:replace, [:name]},
  conflict_target: :name,
  returning: true
)

Data can be inserted with Schema easily, even associated data.

Repo.insert(
  %Author{
    name: "Jet",
    posts: [
      %Post{
        title: "Hello World!",
        body: "Here you go!"
      }
    ]
  }
)

import Ecto.Changeset, only: [change: 2]

Repo.transaction(fn ->
  Repo.update!(change(alice, balance: alice.balance - 10))
  Repo.update!(change(bob, balance: bob.balance + 10))
end)

When a transaction succeeds, Repo.transaction returns a tuple {:ok, return_value_of_the_function}.

When a transaction fails:

• if an error is raised from the given function:
  • the transaction will be rolled back automatically.
  • the error will bubble up.
• if no error is raised from the given function:
  • the transaction will NOT be rolled back automatically. Rollback should be specified explicitly with Repo.rollback/1.
  • Repo.transaction returns a tuple {:error, value_specified_by_Repo_rollback}.

Ecto.Multi groups database operations into a data structure. Compare to the plain function described above, it has some advantages:

• you don't have to call Repo functions in the correct way carefully.
• write less code.
• easy to read.
• …

alias Ecto.Multi

multi = Multi.new
|> Multi.update(:from, change(alice, balance: alice.balance - 10))
|> Multi.update(:to, change(bob, balance: bob.balance + 10))
Repo.transaction(multi)

When a transaction succeeds, Repo.transaction returns a tuple with :ok and a map. The keys in the map are the unique names we provided to each operation in the Multi. The values are the return values for each of those operations. This makes it easy for us to grab the return values of any or all of the operations we ran.

When a transaction fails and you are using changesets, Repo.transaction returns a tuple with 4 items:

• :error
• the failed operation name, like above :from or :to
• the value that caused failure
• a map containing changes to database (the changes have been rolled back, but Ecto provides them for you to inspect)

When a transaction fails with an raised error:

• the transaction will be rolled back automatically.
• the error will bubble up.

If you are only running a small number of operations and don't need to take different action depending on which operation succeeds or fails, using transaction with a function is enough.

For all other cases, you should consider Ecto.Multi. It has a lot more flexibility, and the code needed to respond to different types of errors will be much cleaner and easier to follow.

– Programming Ecto

Because Ecto don't know how to rollback non-database operations, you should run all of database operations first, then run non-database operations.

%Ecto.Multi{} is a data structure that can be introspected.

Ecto team discourages inspecting or manipulating %Ecto.Multi{} directly, because the exact structure is subject to change.

Ecto team provides a function to introspect operations stored in %Ecto.Multi{}, read more details at Ecto.Multi.to_list/1.

• Migration
  • tables' name - pluralized_name
• Schema
  • module name - SnakeSingularizedName
  • file name - underscore_singularized_name
  • functions' arguments - attrs
• Context
  • module - SnakePluralizedName
  • file name - underscore_pluralized_name
  • functions' arguments - attrs

Repo related:

• ecto.gen.repo: Generates a new repository.

Database related:

• ecto.create: Creates the repository storage.
• ecto.drop: Drops the repository storage.

Dump related:

• ecto.dump: Dumps the repository database structure.
• ecto.load: Loads previously dumped database structure.

Migration related:

• ecto.gen.migration: Generates a new migration for the repo.
• ecto.migrations: Displays the repository migration status.
• ecto.migrate: Runs the repository migrations.
• ecto.rollback: Rolls back the repository migrations.

Common aliases:

• ecto.setup - An alias defined in mix.exs, generally:
  • mix ecto.create
  • mix ecto.migrate
  • mix run priv/repo/seeds.exs
• ecto.reset - An alias defined in mix.exs, generally:
  • mix ecto.drop
  • mix ecto.setup

Although adding indexes makes insertions become slower, but it makes queries faster. So, in general applications(more queries than writes), it is still a good idea for adding it.

Generally, indexes are added to following columns:

• primary keys
• foreign keys
• columns need to be lookup
• columns need to be sorted
• columns which are used with GROUP BY
• …

By default, indexes of primary keys are created by Ecto automatically. But, indexes for foreign keys and other columns must be created by using Ecto.Migration.index manually.

Common indexes:

• unique indexes
• sorted indexes
• partial indexes
• covering indexes

Read more at:

• PostgreSQL indexing with Ecto
• PostgreSQL - Chapter 11. Indexes

By default, Ecto use :naive_datetime. But, I recommend using :utc_datetime or :utc_datetime_usec.

When using auto-incrementing number as primary keys, it introduces some problems:

1. SCALING PROBLEM: At any given moment only one database server can generate the primary key. That means all writes have to go through a single server. That's bad news if you want to do thousands of writes per second.
2. SECURITY PROBLEM: Bots and bad actors can guess private URLs.

How using UUIDs as primary keys solve the problems?

1. SOLVE SCALING PROBLEM: All writes no longer have to go through a single database. You can spread them out across many servers. In addition they give you the flexibility to do things like generate the id of a record before its saved to the database. This might be useful if you want to send the record to a cache or search server, but don't want to wait for the database transaction to complete.
2. SECURITY PROBLEM: UUID is random in personal view. It is hard to guess.

– Summarized from Simple tips to make scaling your database easier as you grow

Setup migrations:

config :paper, Paper.Repo, migration_primary_key: [name: :id, type: :binary_id]

Setup schemas:

# Define a module to be used as base
defmodule Paper.Schema do
  defmacro __using__(_) do
    quote do
      use Ecto.Schema
      @primary_key {:id, :binary_id, autogenerate: true}
      @foreign_key_type :binary_id
    end
  end
end

# Now use Paper.CMS.Schema to define new schemas
defmodule Paper.CMS.Comment do
  use Paper.Schema

  schema "comments" do
    belongs_to :post, Paper.CMS.Post
  end
end

Then, just write any other things like before.

References:

• Ecto and Binary IDs

By default, the UUID is generated by Elixir, if you want to generate UUID by database, read Ecto and Binary IDs Generated By PostgreSQL.

References:

• Use the new Enum type in Ecto 3.5

We have known the usage of unique_constraint/3. In this section, we will introduce other commonly used constraints.

Using changeset constraints only makes sense if the error message can be something the user can take action on.

– Programming Ecto

Constraints are useful when converting errors into human-readable messages. But you don't have to use them all the time.

Give it an example.

In our blogging system, every comment belongs to a post. If not, it can only be a bug in our application or a data-integrity issue. In such case, the user can do nothing to fix the error, so crashing is the best option. You shouldn't convert such kind of errors into human-readable messages any more. Although you convert them, and notify users, users can do nothing to fix these errors.

• Data validation
• Database interaction

In order to split the concerns, official team created two packages:

• ecto
• ecto_sql