Brochure about Ecto - an interface for working with databases on Elixir

Introduction

Ecto written in Elixir DSL for communication with databases. Ecto is not ORM. Why? Yes, because Elixir is not an object-oriented language, so Ecto cannot be Object-Relational Mapping (object-relational mapping). Ecto is an abstraction over databases consisting of several large modules that allow you to create migrations, declare models (schemas), add and update data, and send requests to them.

If you are familiar with Rails, then for you the closest analogy, of course, is its ORM ActiveRecord. But these two systems are not copies of each other, and are good at using within their basic languages. Currently, the current version of Ecto 2 is compatible with PostgreSQL and MySQL. An earlier version additionally has compatibility with MSSQL, SQLite3 and MongoDB. Regardless of which DBMS is used, the format of the Ecto functions will always be the same. Ecto also comes out of the box with Phoenix and is a good standard solution.

If you decide to expand the brochure, then you are welcome to join the development of this repository https://github.com/wunsh/ecto-book-ru

Innovation Ecto 2.X

• Updated Ecto.Changeset Module
• New Subquery / 1 function in the Ecto.Query module
• New insert_all / 3 function in the Ecto.Repo module
• Added many-to-many association
• Improved work with associations
• New preload feature of the assoc / 2 associations in the Ecto module
• Upsert
• New sample conditions or_where and or_having
• Added the ability to stepwise build a query

Updated Ecto.Changeset Module

1. changeset.model renamed to changeset.data (from now on there is no "models" in Ecto).
2. It is considered obsolete to pass the required fields and options for them in cast/4 , henceforth you should use cast/3 and validate_required/3 .
3. Atom :empty in cast(source, :empty, required, optional) become obsolete, it is advisable to use empty map or :invalid instead.

As a result, instead:

 def changeset(user, params \\ :empty) do user |> cast(params, [:name], [:age]) end 

It is recommended to do better like this:

 def changeset(user, params \\ %{}) do user |> cast(params, [:name, :age]) |> validate_required([:name]) end 

New Subquery / 1 function in the Ecto.Query module

The Ecto.Query.subquery/1 function makes it possible to convert any queries into subqueries. For example, if you want to calculate the average number of views of publications, you can write:

 query = from p in Post, select: avg(p.visits) TestRepo.all(query) #=> [#Decimal<1743>] 

However, if you want to calculate the average number of views for only the 10 most popular posts, you will need a subquery:

 query = from p in Post, select: [:visits], order_by: [desc: :visits], limit: 10 TestRepo.all(from p in subquery(query), select: avg(p.visits)) #=> [#Decimal<4682>] 

For a practical example, if you use the Repo.aggregate function to calculate aggregated data:

 #  -     TestRepo.aggregate(Post, :avg, :visits) #=> #Decimal<1743> 

 #  -   10    query = from Post, order_by: [desc: :visits], limit: 10 TestRepo.aggregate(query, :avg, :visits) #=> #Decimal<4682> 

In subquery/1 is possible to set the names of the fields in the subqueries. That will allow to handle tables with conflicting names:

 posts_with_private = from p in Post, select: %{title: p.title, public: not p.private} from p in subquery(posts_with_private), where: p.public, select: p 

New insert_all / 3 function in the Ecto.Repo module

The Ecto.Repo.insert_all/3 function is intended for multiple insertion of records within a single query:

 Ecto.Repo.insert_all Post, [%{title: "foo"}, %{title: "bar"}] 

It is worth considering that when inserting rows through insert_all/3 , auto- insert_all/3 fields, such as inserted_at or updated_at , are not processed. Also insert_all/3 allows you to insert rows into the database, bypassing Ecto.Schema , simply by specifying the name of the table:

 Ecto.Repo.insert_all "some_table", [%{hello: "foo"}, %{hello: "bar"}] 

Added many-to-many association

Ecto now supports many_to_many associations:

 defmodule Post do use Ecto.Schema schema "posts" do many_to_many :tags, Tag, join_through: "posts_tags" end end 

The value in the join_through option can be the name of a table that contains the post_id and tag_id , or it can be a schema, such as PostTag , which contains foreign keys and auto-generated columns.

Improved work with associations

From now on, Ecto makes it possible to insert and modify lines to the belongs_to and many_to_many associations via the changeset . In addition, Ecto supports defining associations directly in the data structure to be inserted. For example:

 Repo.insert! %Permalink{ url: "//root", post: %Post{ title: "A permalink belongs to a post which we are inserting", comments: [ %Comment{text: "child 1"}, %Comment{text: "child 2"}, ] } } 

This improvement makes it easier to insert tree structures into the database.

New preload feature of the assoc / 2 associations in the Ecto module

The Ecto.assoc/2 function allows you to define second-order links that must be loaded to selectable records. As an example, you can get authors and comments on selected publications:

 posts = Repo.all from p in Post, where: is_nil(p.published_at) Repo.all assoc(posts, [:comments, :author]) 

It is better to load links through associations, since it does not require the addition of fields to the sampling scheme.

Upsert

The functions Ecto.Repo.insert/2 and Ecto.Repo.insert_all/3 began to support upserts (insert and update) through the options :on_conflict and :conflict_target .

The :on_conflict determines how the database should behave if the primary key matches.
The :conflict_target option determines which fields need to be checked for conflicts when inserting new lines.

 #   {:ok, inserted} = MyRepo.insert(%Post{title: "inserted"}) #     . {:ok, upserted} = MyRepo.insert(%Post{id: inserted.id, title: "updated"}, on_conflict: :nothing) #  ,    title. on_conflict = [set: [title: "updated"]] {:ok, updated} = MyRepo.insert(%Post{id: inserted.id, title: "updated"}, on_conflict: on_conflict, conflict_target: :id) 

New sample conditions or_where and or_having

In Ecto, we added the expression Ecto.Query.or_where/3 and Ecto.Query.or_having , which add new filters to the already existing conditions through the "OR".

 from(c in City, where: [state: "Sweden"], or_where: [state: "Brazil"]) 

Added the ability to stepwise build a query

This technique allows you to create expressions bit by bit in order to interpolate them later into a general query.

For example, you have a set of conditions from which you want to build your query, but you need to select only some of them depending on the context:

 dynamic = false dynamic = if params["is_public"] do dynamic([p], p.is_public or ^dynamic) else dynamic end dynamic = if params["allow_reviewers"] do dynamic([p, a], a.reviewer == true or ^dynamic) else dynamic end from query, where: ^dynamic 

The example above shows how to build a query step by step, taking into account external conditions, and at the end interpolate everything inside one query.

A dynamic expression can always be interpolated inside another dynamic expression or inside a where , having , update or in the join condition.

Query interface

Analysis of the documentation of the modules Ecto.Query and Ecto.Repo from HexDocs is done in the manner of the ActiveRecord Query Interface from RusRailsGuide . The text below reveals various ways to get data from a database using Ecto. The code examples in the following text will apply to some of these models:

    id   ,    . defmodule Showcase.Client do use Ecto.Schema import Ecto.Query schema "clients" do field :name, :string field :age, :integer, default: 0 field :sex, :integer, default: 0 field :state, :string, default: "new" has_many :orders, Showcase.Order end # ... end defmodule Showcase.Order do use Ecto.Schema import Ecto.Query schema "orders" do field :description, :text field :total_cost, :integer field :state, :string, default: "pending" belongs_to :client, Showcase.Client has_many :products, Showcase.Product end # ... end 

• Getting single line
• Getting multiple lines
• Row fetch conditions
• Row sorting
• Select specific row fields
• String grouping
• Limiting and offsetting selectable rows
• Join tables
• Override condition

Ecto provides several search functions for retrieving objects from the database. Arguments can be passed to each search function to perform certain queries to the database without having to write in pure SQL. Ecto provides two styles for building queries: using a keyword and through an expression (function / macro).

Below are some expressions provided by Ecto, they are declared in two modules:

Ecto.Repo:

• get / 3
• get_by / 3
• one / 2
• all / 3

Ecto.Query:

• where / 3
• or_where / 3
• order_by / 3
• select / 3
• group_by / 3
• limit / 3
• offset / 3
• join / 5
• exclude / 2

1. Getting a single line

get / 3

Using the get/3 function, you can get a record corresponding to a specific primary key. For example:

 #      (id) 10. client = Ecto.Repo.get(Client, 10) => %Client{id: 10, name: "Cain Ramirez", age: 34, sex: 1, state: "good"} 

The get/3 function will return nil if no records are found. If the request does not have a primary key or there are more than one, then the function will cause an error argument error .

The get!/3 function behaves like get/3 , except that it will call Ecto.NoResultsError if no matching records are found.

The closest analogue of this function in ActiveRecord is the find method.

get_by / 3

Using the get_by/3 function, you can get an entry that matches the provided sample conditions. For example:

 #     (name) "Cain Ramirez". client = Ecto.Repo.get_by(Client, name: "Cain Ramirez") => %Client{id: 10, name: "Cain Ramirez", age: 34, sex: 1, state: "good"} 

The get_by/3 function get_by/3 return nil if no records are found.

The get_by!/3 function behaves like get_by/3 , except that it will cause an Ecto.NoResultsError error if no matching records were found.

The closest analogue of this function in ActiveRecord is the find_by_* method.

one / 2

Using the one/2 function, you can get one record that matches the provided sample conditions. For example:

 #     . query = Ecto.Query.from(c in Client, where: c.name == "Jean Rousey") client = Ecto.Repo.one(query) => %Client{id: 1, name: "Jean Rousey", age: 29, sex: -1, state: "good"} 

The one/2 function will return nil if no records are found. And the function will cause an error if there is more than one entry on request.

The one!/2 function behaves like one/2 , except that it will cause an Ecto.NoResultsError error if no matching records are found.

The closest analogue of this function in ActiveRecord is the first method from the <4 version, which accepted the sample conditions, and not the limit as now.

2. Getting multiple lines

all / 3

Using the function all/3 , you can get all the records that match the provided query conditions. For example:

 #     . query = Ecto.Query.from(c in Client) clients = Ecto.Repo.all(query) => [%Client{id: 1, name: "Jean Rousey", age: 29, sex: -1, state: "good"}, ..., %Client{id: 10, name: "Cain Ramirez", age: 34, sex: 1, state: "good"}] 

The all/3 function will return Ecto.QueryError if the request fails validation.

The closest analogue of this function in ActiveRecord is the all method from the <4 version, which accepted the sample conditions.

3. Conditions for fetching rows

where / 3

The where/3 expression allows you to define conditions for restricting the returned records that the WHERE part of the SQL expression represents. If several sampling conditions are transmitted, they are combined by the AND operator.

Calling the where: keyword where: is an integral part of the from/2 macro:

 from(c in Client, where: c.name == "Cain Ramirez") from(c in Client, where: [name: "Cain Ramirez"]) 

It is possible to interpolate lists with sampling conditions, which allows you to pre-assemble the necessary restrictions.

 filters = [name: "Cain Ramirez"] from(c in Client, where: ^filters) 

Calling the where/3 macro:

 Client |> where([c], c.name == "Cain Ramirez") Client |> where(name: "Cain Ramirez") 

or_where / 3

The or_where/3 expression allows you to define more flexible conditions for restricting the returned records, which represents the WHERE part of the SQL expression. The difference between where/3 and or_where/3 minimal, but crucial. The transferred condition joins the already existing ones through the OR operator. If several sample conditions are transferred to or_where/3 , then they are combined with the AND operator between them.

The call by keyword or_where: is an integral part of the from/2 macro:

 from(c in Client, where: [name: "Cain Ramirez"], or_where: [name: "Jean Rousey"]) 

It is possible to interpolate lists with sampling conditions, which allows you to pre-assemble the necessary restrictions. The conditions in the list are combined with each other through AND , and will join the existing conditions through OR :

 filters = [sex: 1, state: "good"] from(c in Client, where: [name: "Cain Ramirez"], or_where: ^filters) 

... this expression is equivalent to:

 from c in Client, where: (c.name == "Cain Ramirez") or (c.sex == 1 and c.state == "good") 

Calling the or_where/3 macro:

 Client |> where([c], c.name == "Jean Rousey") |> or_where([c], c.name == "Cain Ramirez") 

4. Sort Rows

The expression order_by/3 allows you to define the condition for sorting records obtained from the database. order_by/3 sets the ORDER BY part of the SQL query.

It is possible to sort by several fields at once. The default sorting direction for ascending ( :asc ) can be overridden by descending ( :desc ). For each field, you can specify your own sorting direction.

Calling order_by: is an integral part of the from/2 macro:

 from(c in Client, order_by: c.name, order_by: c.age) from(c in Client, order_by: [c.name, c.age]) from(c in Client, order_by: [asc: c.name, desc: c.age]) from(c in Client, order_by: [:name, :age]) from(c in Client, order_by: [asc: :name, desc: :age]) 

It is possible to interpolate lists with sort fields, which allows you to pre-assemble the necessary sampling conditions.

 values = [asc: :name, desc: :age] from(c in Client, order_by: ^values) 

Call order_by/3 macro:

 Client |> order_by([c], asc: c.name, desc: c.age) Client |> order_by(asc: :name) 

5. Select specific row fields

The select/3 statement allows you to define table fields that you need to return for the records you receive from the database. select/3 sets the SELECT part of the SQL query. By default, Ecto selects the entire set of result fields using select * .

A call to the select: keyword select: is an integral part of the from/2 macro:

 from(c in Client, select: c) from(c in Client, select: {c.name, c.age}) from(c in Client, select: [c.name, c.state]) from(c in Client, select: {c.name, ^to_string(40 + 2), 43}) from(c in Client, select: %{name: c.name, order_counts: 42}) 

The macro call select/3 :

 Client |> select([c], c) Client |> select([c], {c.name, c.age}) Client |> select([c], %{"name" => c.name}) Client |> select([:name]) Client |> select([c], struct(c, [:name])) Client |> select([c], map(c, [:name])) 

Important: When limiting selection fields for associations, it is important to select foreign key relationships, otherwise Ecto will not be able to find related objects.

6. Grouping rows

To determine the GROUP BY in a SQL query, the group_by/3 macro is intended. All columns mentioned in SELECT must be passed to group_by/3 . This is a general rule for aggregate functions.

Calling the keyword group_by: is an integral part of the from/2 macro:

 from(c in Client, group_by: c.age, select: {c.age, count(c.id)}) from(c in Client, group_by: :sex, select: {c.sex, count(c.id)}) 

Call macro group_by/3 :

 Client |> group_by([c], c.age) |> select([c], count(c.id)) 

7. Limiting and offsetting selectable lines

To define LIMIT in a SQL query, use the expression limit/3 , it will determine the number of required records to be received.

If limit/3 transmitted twice, then the first value will be overlapped by the second.

 from(c in Client, where: c.age == 29, limit: 1) Client |> where([c], c.age == 29) |> limit(1) 

To define OFFSET in a SQL query, use the expression offset/3 , it will determine the number of records that will be skipped before the start of the returned records.

If offset/3 transmitted twice, then the first value will be overlapped by the second.

 from(c in Client, limit: 10, offset: 30) Client |> limit(10) |> offset(30) 

8. Joining tables

Queries often refer to multiple tables, such queries are built using the JOIN . In Ecto, to define such a construct, the join/5 expression is intended. The default table join strategy is INNER JOIN, which can be overridden to :inner ,: left,: right,: :cross or :full . In the case of building a query by key, :join can be replaced by:: :inner_join :left_join :right_join :cross_join or :full_join .

The call by the join: keyword join: is an integral part of the from/2 macro:

 from c in Comment, join: p in Post, on: p.id == c.post_id, select: {p.title, c.text} from p in Post, left_join: c in assoc(p, :comments), select: {p, c} from c in Comment, join: p in Post, on: [id: c.post_id], select: {p.title, c.text} 

All keys transferred to on will be considered as connection conditions.

It is possible to interpolate the right side relative to in . For example:

 posts = Post from c in Comment, join: p in ^posts, on: [id: c.post_id], select: {p.title, c.text} 

Call join/5 macro:

 Comment |> join(:inner, [c], p in Post, c.post_id == p.id) |> select([c, p], {p.title, c.text}) Post |> join(:left, [p], c in assoc(p, :comments)) |> select([p, c], {p, c}) Post |> join(:left, [p], c in Comment, c.post_id == p.id and c.is_visible == true) |> select([p, c], {p, c}) 

9. Overriding condition

Ecto allows you to remove already defined conditions in the query or return default values. To do this, use the exclude/2 expression.

 query |> Ecto.Query.exclude(:select) Ecto.Query.exclude(query, :select) 

Commands for bulk operations with strings

Bulk insert

The Ecto.Repo.insert_all/3 function will insert all transferred records.

 Repo.insert_all(Client, [[name: "Cain Ramirez", age: 34], [name: "Jean Rousey", age: 29]]) Repo.insert_all(Client, [%{name: "Cain Ramirez", age: 34}, %{name: "Jean Rousey", age: 29}]) 

The insert_all/3 function does not handle auto- insert_all/3 fields, such as inserted_at or updated_at .

Bulk update

The Ecto.Repo.update_all/3 function Ecto.Repo.update_all/3 update all rows subject to the query condition for the field values ​​passed.

 Repo.update_all(Client, set: [state: "new"]) Repo.update_all(Client, inc: [age: 1]) from(c in Client, where: p.sex < 0) |> Repo.update_all(set: [state: "new"]) from(c in Client, where: p.sex > 0, update: [set: [state: "new"]]) |> Repo.update_all([]) from(c in Client, where: c.id < 10, update: [set: [state: fragment("?", new)]]) |> Repo.update_all([]) 

Bulk removal

The Ecto.Repo.delete_all/2 function deletes all rows that fall under the query condition.

 Repo.delete_all(Client) from(p in Client, where: p.age == 0) |> Repo.delete_all 

Practical examples

Query composition

 query = from p in App.Product, select: p query2 = from p in query, where: p.state == "published" App.Repo.all(query2) 

Paginated function

 defmodule Finders.Common.Paging do import Ecto.Query def page(query), do: page(query, 1) def page(query, page), do: page(query, page, 10) def page(query, page, per_page) do offset = per_page * (page-1) query |> offset([_], ^offset) |> limit([_], ^per_page) end end 

 # With Posts: second page, five per page posts = Post |> Finders.Common.Paging.page(2, 5) |> Repo.all # With Tags: third page, 10 per page tags = Tag |> Finders.Common.Paging.page(3) |> Repo.all 

Query.API

Comparison Operators: == != , <= , >= , < , >
Boolean operators: and , or , not
Inclusion operator: in/2
Search functions: like/2 and ilike/2
Check for null : is_nil/1
Aggregators: count/1 , avg/1 , sum/1 , min/1 , max/1
Function for arbitrary SQL subqueries: fragment/1

 from p in Post, where: p.published_at > ago(3, "month") from p in Post, where: p.id in [1, 2, 3] from p in Payment, select: avg(p.value) from p in Post, where: p.published_at > datetime_add(^Ecto.DateTime.utc, -1, "month") from p in Post, where: is_nil(p.published_at) from p in Post, where: ilike(p.body, "Chapter%") from p in Post, where: is_nil(p.published_at) and fragment("lower(?)", p.title) == "title" 

Supplement from Ecto.Adapters.SQL

Ecto.Adapters.SQL.query/4

Runs an arbitrary SQL query within the transferred repository.

 Ecto.Adapters.SQL.query(Showcase, "SELECT $1::integer + $2", [40, 2]) => {:ok, %{rows: [{42}], num_rows: 1}} 

The closest analogue of this function in ActiveRecord is the find_by_sql method.

Ecto.Adapters.SQL.to_sql/3

Converts a query built from expressions to SQL.

 Ecto.Adapters.SQL.to_sql(:all, repo, Showcase.Client) => {"SELECT c.id, c.name, c.age, c.sex, c.state, c.inserted_at, c.created_at FROM clients as c", []} Ecto.Adapters.SQL.to_sql(:update_all, repo, from(c in Showcase.Client, update: [set: [state: ^"new"]])) => {"UPDATE clients AS c SET state = $1", ["new"]} 

This function is a similar method from ActiveRecord :: Relation.

Literature

http://guides.rubyonrails.org/active_record_querying.html

https://hexdocs.pm/ecto/Ecto.html

https://github.com/elixir-ecto/ecto

https://blog.drewolson.org/composable-queries-ecto/

Learning with JB

http://blog.plataformatec.com.br/2016/05/ectos-insert_all-and-schemaless-queries/

Afterword

If you are interested in the functional programming language Elixir or you are just sympathetic, then I advise you to join the Wunsh && Elixir and ProElixir Telegram chat rooms.

In the domestic Elixir community, a single platform begins to appear in the face of the Wunsh.ru project. Now the project has a thematic newsletter, in which there is nothing illegal, once a week will receive a letter with a collection of articles about Elixir in Russian.

UPD:

Update from pure_evil - c MongoDB second version of Ecto works, though it’s still in the form of a fork: https://github.com/michalmuskala/mongodb_ecto/pull/91