Meet Ecto, The No-Compromise Database Wrapper For Concurrent Elixir Apps

Ecto is a domain specific language for writing queries and interacting with databases in the Elixir language. The latest version (2.0) supports PostgreSQL and MySQL. (support for MSSQL, SQLite, and MongoDB will be available in the future). In case you are new to Elixir or have little experience with it, I would recommend you read Kleber Virgilio Correia’s Getting Started with Elixir Programming Language.

Tired of all the SQL dialects? Speak to your database through Ecto.

Ecto is comprised of 4 main components:

• Ecto.Repo. Defines repositories that are wrappers around a data store. Using it, we can insert, create, delete, and query a repo. An adapter and credentials are required to communicate with the database.
• Ecto.Schema. Schemas are used to map any data source into an Elixir struct.
• Ecto.Changeset. Changesets provide a way for developers to filter and cast external parameters, as well as a mechanism to track and validate changes before they are applied to data.
• Ecto.Query. Provides a DSL-like SQL query for retrieving information from a repository. Queries in Ecto are secure, avoiding common problems like SQL Injection, while still being composable, allowing developers to build queries piece by piece instead of all at once.

For this tutorial you will need:

• Elixir installed (installation guide for 1.2 or later)
• PostgreSQL installed
• A user defined with permission to create a database (Note: We will use the user “postgres” with password “postgres” as an example throughout this tutorial.)

Installation and Configuration

For starters, let’s create a new app with a supervisor using Mix. Mix is a build tool that ships with Elixir that provides tasks for creating, compiling, testing your application, managing its dependencies and much more.

mix new cart --sup

This will create a directory cart with the initial project files:

* creating README.md
* creating .gitignore
* creating mix.exs
* creating config
* creating config/config.exs
* creating lib
* creating lib/ecto_tut.ex
* creating test
* creating test/test_helper.exs
* creating test/ecto_tut_test.exs

We are using the --sup option since we need a supervisor tree that will keep the connection to the database. Next, we go to the cart directory with cd cart and open the file mix.exs and replace its contents:

defmodule Cart.Mixfile do
  use Mix.Project

  def project do
    [app: :cart,
     version: "0.0.1",
     elixir: "~> 1.2",
     build_embedded: Mix.env == :prod,
     start_permanent: Mix.env == :prod,
     deps: deps]
  end

  def application do
    [applications: [:logger, :ecto, :postgrex],
     mod: {Cart, []}]
  end

  # Type "mix help deps" for more examples and options
  defp deps do
    [{:postgrex, ">= 0.11.1"},
     {:ecto, "~> 2.0"}]
  end
end

In def application do we have to add as applications :postgrex, :ecto so these can be used inside our application. We also have to add those as dependencies by adding in defp deps do postgrex (which is the database adapter) and ecto. Once you have edited the file, run in the console:

mix deps.get

This will install all dependencies and create a file mix.lock that stores all dependencies and sub-dependencies of the installed packages (similar to Gemfile.lock in bundler).

Ecto.Repo

We will now look at how to define a repo in our application. We can have more than one repo, meaning we can connect to more than one database. We need to configure the database in the file config/config.exs:

use Mix.Config
config :cart, ecto_repos: [Cart.Repo]

We are just setting the minimum, so we can run the next command. With the line :cart, cart_repos: [Cart.Repo] we are telling Ecto which repos we are using. This is a cool feature since it allows us to have many repos, i.e. we can connect to multiple databases.

Now run the following command:

mix ecto.gen.repo

==> connection
Compiling 1 file (.ex)
Generated connection app
==> poolboy (compile)
Compiled src/poolboy_worker.erl
Compiled src/poolboy_sup.erl
Compiled src/poolboy.erl
==> decimal
Compiling 1 file (.ex)
Generated decimal app
==> db_connection
Compiling 23 files (.ex)
Generated db_connection app
==> postgrex
Compiling 43 files (.ex)
Generated postgrex app
==> ecto
Compiling 68 files (.ex)
Generated ecto app
==> cart
* creating lib/cart
* creating lib/cart/repo.ex
* updating config/config.exs
Don't forget to add your new repo to your supervision tree
(typically in lib/cart.ex):

    supervisor(Cart.Repo, [])

And to add it to the list of ecto repositories in your configuration files (so Ecto tasks work as expected):

    config :cart,
      ecto_repos: [Cart.Repo]

This command generates the repo. If you read the output, it tells you to add a supervisor and repo in your app. Let’s start with the supervisor. We will edit lib/cart.ex:

defmodule Cart do
  use Application

  def start(_type, _args) do
    import Supervisor.Spec, warn: false

    children = [
      supervisor(Cart.Repo, [])
    ]

    opts = [strategy: :one_for_one, name: Cart.Supervisor]
    Supervisor.start_link(children, opts)
  end
end

In this file, we are defining the supervisor supervisor(Cart.Repo, []) and adding it to the children list (in Elixir, lists are similar to arrays). We define the children supervised with the strategy strategy: :one_for_one which means that, if one of the supervised processes fails, the supervisor will restart only that process into its default state. You can learn more about supervisors here. If you look at lib/cart/repo.ex you will see that this file has been already created, meaning we have a Repo for our application.

defmodule Cart.Repo do
  use Ecto.Repo, otp_app: :cart
end

Now let’s edit the configuration file config/config.exs:

use Mix.Config
config :cart, ecto_repos: [Cart.Repo]

config :cart, Cart.Repo,
  adapter: Ecto.Adapters.Postgres,
  database: "cart_dev",
  username: "postgres",
  password: "postgres",
  hostname: "localhost"

Having defined all configuration for our database we can now generate it by running:

mix ecto.create

This command creates the database and, with that, we have essentially finished the configuration. We are now ready to start coding, but let’s define the scope of our app first.

Building an Invoice with Inline Items

For our demo application, we will build a simple invoicing tool. For changesets (models) we will have Invoice, Item and InvoiceItem. InvoiceItem belongs to Invoice and Item. This diagram represents how our models will be related to each other:

The diagram is pretty simple. We have a table invoices that has many invoice_items where we store all the details and also a table items that has many invoice_items. You can see that the type for invoice_id and item_id in invoice_items table is UUID. We are using UUID because it helps obfuscate the routes, in case you want to expose the app over an API and makes it simpler to sync since you don’t depend on a sequential number. Now let’s create the tables using Mix tasks.

Ecto.Migration

Migrations are files that are used to modify the database schema. Ecto.Migration gives you a set of methods to create tables, add indexes, create constraints, and other schema-related stuff. Migrations really help keep the application in sync with the database. Let’s create a migration script for our first table:

mix ecto.gen.migration create_invoices

This will generate a file similar to priv/repo/migrations/20160614115844_create_invoices.exs where we will define our migration. Open the file generated and modify its contents to be as follows:

defmodule Cart.Repo.Migrations.CreateInvoices do
  use Ecto.Migration

  def change do
    create table(:invoices, primary_key: false) do
      add :id, :uuid, primary_key: true
      add :customer, :text
      add :date, :date

      timestamps
    end
  end
end

Inside method def change do we define the schema that will generate the SQL for the database. create table(:invoices, primary_key: false) do will create the table invoices. We have set primary_key: false but we will add an ID field of type UUID, customer field of type text, date field of type date. The timestamps method will generate the fields inserted_at and updated_at that Ecto automatically fills with the time the record was inserted and the time it was updated, respectively. Now go to the console and run the migration:

mix ecto.migrate

We have created the table invoices with all the defined fields. Let’s create the items table:

mix ecto.gen.migration create_items

Now edit the generated migration script:

defmodule Cart.Repo.Migrations.CreateItems do
  use Ecto.Migration

  def change do
    create table(:items, primary_key: false) do
      add :id, :uuid, primary_key: true
      add :name, :text
      add :price, :decimal, precision: 12, scale: 2

      timestamps
    end
  end
end

The new thing here is the decimal field that allows numbers with 12 digits, 2 of which are for the decimal part of the number. Let’s run the migration again:

mix ecto.migrate

Now we have created items table and finally let’s create the invoice_items table:

mix ecto.gen.migration create_invoice_items

Edit the migration:

defmodule Cart.Repo.Migrations.CreateInvoiceItems do
  use Ecto.Migration

  def change do
    create table(:invoice_items, primary_key: false) do
      add :id, :uuid, primary_key: true
      add :invoice_id, references(:invoices, type: :uuid, null: false)
      add :item_id, references(:items, type: :uuid, null: false)
      add :price, :decimal, precision: 12, scale: 2
      add :quantity, :decimal, precision: 12, scale: 2
      add :subtotal, :decimal, precision: 12, scale: 2

      timestamps
    end

    create index(:invoice_items, [:invoice_id])
    create index(:invoice_items, [:item_id])
  end
end

As you can see, this migration has some new parts. The first thing you will notice is add :invoice_id, references(:invoices, type: :uuid, null: false). This creates the field invoice_id with a constraint in the database that references the invoices table. We have the same pattern for item_id field. Another thing that is different is the way we create an index: create index(:invoice_items, [:invoice_id]) creates the index invoice_items_invoice_id_index.

Ecto.Schema and Ecto.Changeset

In Ecto, Ecto.Model has been deprecated in favor of using Ecto.Schema, so we will call the modules schemas instead of models. Let’s create the changesets. We will start with the most simple changeset Item and create the file lib/cart/item.ex:

defmodule Cart.Item do
  use Ecto.Schema
  import Ecto.Changeset

  alias Cart.InvoiceItem

  @primary_key {:id, :binary_id, autogenerate: true}
  schema "items" do
    field :name, :string
    field :price, :decimal, precision: 12, scale: 2
    has_many :invoice_items, InvoiceItem

    timestamps
  end

  @fields ~w(name price)

  def changeset(data, params \\ %{}) do
    data
    |> cast(params, @fields)
    |> validate_required([:name, :price])
    |> validate_number(:price, greater_than_or_equal_to: Decimal.new(0))
  end
end

At the top, we inject code into the changeset using use Ecto.Schema. We are also using import Ecto.Changeset to import functionality from Ecto.Changeset. We could have specified which specific methods to import, but let’s keep it simple. The alias Cart.InvoiceItem allows us to write directly inside the changeset InvoiceItem, as you will see in a moment.

Ecto.Schema

The @primary_key {:id, :binary_id, autogenerate: true} specifies that our primary key will be auto-generated. Since we are using a UUID type, we define the schema with schema "items" do and inside the block we define each field and relationships. We defined name as string and price as decimal, very similar to the migration. Next, the macro has_many :invoice_items, InvoiceItem indicates a relationship between Item and InvoiceItem. Since by convention we named the field item_id in the invoice_items table, we don’t need to configure the foreign key. Finally the timestamps method will set the inserted_at and updated_at fields.

Ecto.Changeset

The def changeset(data, params \\ %{}) do function receives an Elixir struct with params which we will pipe through different functions. cast(params, @fields) casts the values into the correct type. For instance, you can pass only strings in the params and those would be converted to the correct type defined in the schema. validate_required([:name, :price]) validates that the name and price fields are present, validate_number(:price, greater_than_or_equal_to: Decimal.new(0)) validates that the number is greater than or equal to 0 or in this case Decimal.new(0).

In Elixir, Decimal operations are done differently since they're implemented as a struct.

That was a lot to take in, so let’s look at this in the console with examples so you can grasp the concepts better:

iex -S mix

This will load the console. -S mix loads the current project into the iex REPL.

iex(0)> item = Cart.Item.changeset(%Cart.Item{}, %{name: "Paper", price: "2.5"})
#Ecto.Changeset<action: nil, changes: %{name: "Paper", price: #Decimal<2.5>},
 errors: [], data: #Cart.Item<>, valid?: true>

This returns an Ecto.Changeset struct that is valid without errors. Now let’s save it:

iex(1)> item = Cart.Repo.insert!(item)
%Cart.Item{__meta__: #Ecto.Schema.Metadata<:loaded, "items">,
 id: "66ab2ab7-966d-4b11-b359-019a422328d7",
 inserted_at: #Ecto.DateTime<2016-06-18 16:54:54>,
 invoice_items: #Ecto.Association.NotLoaded<association :invoice_items is not loaded>,
 name: "Paper", price: #Decimal<2.5>,
 updated_at: #Ecto.DateTime<2016-06-18 16:54:54>}

We don’t show the SQL for brevity. In this case, it returns the Cart.Item struct with all the values set, You can see that inserted_at and updated_at contain their timestamps and the id field has a UUID value. Let’s see some other cases:

iex(3)> item2 = Cart.Item.changeset(%Cart.Item{price: Decimal.new(20)}, %{name: "Scissors"})         
#Ecto.Changeset<action: nil, changes: %{name: "Scissors"}, errors: [],
 data: #Cart.Item<>, valid?: true>
iex(4)> Cart.Repo.insert(item2)

Now we have set the Scissors item in a different way, setting the price directly %Cart.Item{price: Decimal.new(20)}. We need to set its correct type, unlike the first item where we just passed a string as price. We could have passed a float and this would have been cast into a decimal type. If we pass, for example %Cart.Item{price: 12.5}, when you insert the item it would throw an exception stating that the type doesn’t match.

iex(4)>  invalid_item = Cart.Item.changeset(%Cart.Item{}, %{name: "Scissors", price: -1.5})
#Ecto.Changeset<action: nil,
 changes: %{name: "Scissors", price: #Decimal<-1.5>},
 errors: [price: {"must be greater than or equal to %{number}",
   [number: #Decimal<0>]}], data: #Cart.Item<>, valid?: false>

To terminate the console, press Ctrl+C twice. You can see that validations are working and the price must be greater than or equal to zero (0). As you can see, we have defined all the schema Ecto.Schema which is the part related to how the structure of the module is defined and the changeset Ecto.Changeset which is all validations and casting. Let’s continue and create the file lib/cart/invoice_item.ex:

defmodule Cart.InvoiceItem do
  use Ecto.Schema
  import Ecto.Changeset

  @primary_key {:id, :binary_id, autogenerate: true}
  schema "invoice_items" do
    belongs_to :invoice, Cart.Invoice, type: :binary_id
    belongs_to :item, Cart.Item, type: :binary_id
    field :quantity, :decimal, precision: 12, scale: 2
    field :price, :decimal, precision: 12, scale: 2
    field :subtotal, :decimal, precision: 12, scale: 2

    timestamps
  end

  @fields ~w(item_id price quantity)
  @zero Decimal.new(0)

  def changeset(data, params \\ %{}) do
    data
    |> cast(params, @fields)
    |> validate_required([:item_id, :price, :quantity])
    |> validate_number(:price, greater_than_or_equal_to: @zero)
    |> validate_number(:quantity, greater_than_or_equal_to: @zero)
    |> foreign_key_constraint(:invoice_id, message: "Select a valid invoice")
    |> foreign_key_constraint(:item_id, message: "Select a valid item")
    |> set_subtotal
  end

  def set_subtotal(cs) do
    case {(cs.changes[:price] || cs.data.price), (cs.changes[:quantity] || cs.data.quantity)} do
      {_price, nil} -> cs
      {nil, _quantity} -> cs
      {price, quantity} ->
        put_change(cs, :subtotal, Decimal.mult(price, quantity))
    end
  end
end

This changeset is bigger but you should already be familiar with most of it. Here belongs_to :invoice, Cart.Invoice, type: :binary_id defines the “belongs to” relationship with the Cart.Invoice changeset that we will soon create. The next belongs_to :item creates a relationship with the items table. We have defined @zero Decimal.new(0). In this case, @zero is like a constant that can be accessed inside the module. The changeset function has new parts, one of which is foreign_key_constraint(:invoice_id, message: "Select a valid invoice"). This will allow an error message to be generated instead of generating an exception when the constraint is not fulfilled. And finally, the method set_subtotal will calculate the subtotal. We pass the changeset and return a new changeset with the subtotal calculated if we have both the price and quantity.

Now, let’s create the Cart.Invoice. So create and edit the file lib/cart/invoice.ex to contain the following:

defmodule Cart.Invoice do
  use Ecto.Schema
  import Ecto.Changeset

  alias Cart.{Invoice, InvoiceItem, Repo}

  @primary_key {:id, :binary_id, autogenerate: true}
  schema "invoices" do
    field :customer, :string
    field :amount, :decimal, precision: 12, scale: 2
    field :balance, :decimal, precision: 12, scale: 2
    field :date, Ecto.Date
    has_many :invoice_items, InvoiceItem, on_delete: :delete_all

    timestamps
  end

  @fields ~w(customer amount balance date)

  def changeset(data, params \\ %{}) do
    data
    |> cast(params, @fields)
    |> validate_required([:customer, :date])
  end

  def create(params) do
    cs = changeset(%Invoice{}, params)
    |> validate_item_count(params)
    |> put_assoc(:invoice_items, get_items(params))

    if cs.valid? do
      Repo.insert(cs)
    else
      cs
    end
  end

  defp get_items(params) do
    items = params[:invoice_items] || params["invoice_items"]
    Enum.map(items, fn(item)-> InvoiceItem.changeset(%InvoiceItem{}, item) end)
  end

  defp validate_item_count(cs, params) do
    items = params[:invoice_items] || params["invoice_items"]

    if Enum.count(items) <= 0 do
      add_error(cs, :invoice_items, "Invalid number of items")
    else
      cs
    end
  end

end

Cart.Invoice changeset has some differences. The first one is inside schemas: has_many :invoice_items, InvoiceItem, on_delete: :delete_all means that when we delete an invoice, all the associated invoice_items will be deleted. Keep in mind, though, that this is not a constraint defined in the database.

Let’s try the create method in the console to understand things better. You might have created the items (“Paper”, “Scissors”) which we will be using here:

iex(0)> item_ids = Enum.map(Cart.Repo.all(Cart.Item), fn(item)-> item.id end)
iex(1)> {id1, id2} = {Enum.at(items, 0), Enum.at(items, 1) }

We fetched all items with Cart.Repo.all and with the Enum.map function we just get the item.id of each item. In the second line, we just assign id1 and id2 with the first and second item_ids, respectively:

iex(2)> inv_items = [%{item_id: id1, price: 2.5, quantity: 2},
 %{item_id: id2, price: 20, quantity: 1}]
iex(3)> {:ok, inv} = Cart.Invoice.create(%{customer: "James Brown", date: Ecto.Date.utc, invoice_items: inv_items})

The invoice has been created with its invoice_items and we can fetch all the invoices now.

iex(4)> alias Cart.{Repo, Invoice}
iex(5)> Repo.all(Invoice)

You can see it returns the Invoice but we would like to also see the invoice_items:

iex(6)> Repo.all(Invoice) |> Repo.preload(:invoice_items)

With the Repo.preload function, we can get the invoice_items. Note that this can process queries concurrently. In my case the query looked like this:

iex(7)> Repo.get(Invoice, "5d573153-b3d6-46bc-a2c0-6681102dd3ab") |> Repo.preload(:invoice_items)

Ecto.Query

So far, we’ve shown how to create new items and new invoices with relationships. But what about querying? Well, let me introduce you to Ecto.Query which will help us to make queries to the database, but first we need more data to explain better.

iex(1)> alias Cart.{Repo, Item, Invoice, InvoiceItem}
iex(2)> Repo.insert(%Item{name: "Chocolates", price: Decimal.new("5")})
iex(3)> Repo.insert(%Item{name: "Gum", price: Decimal.new("2.5")})
iex(4)> Repo.insert(%Item{name: "Milk", price: Decimal.new("1.5")})
iex(5)> Repo.insert(%Item{name: "Rice", price: Decimal.new("2")})
iex(6)> Repo.insert(%Item{name: "Chocolates", price: Decimal.new("10")})

We should now have 8 items and there is a repeated “Chocolate”. We may want to know which items are repeated. So let’s try this query:

iex(7)> import Ecto.Query
iex(8)> q = from(i in Item, select: %{name: i.name, count: (i.name)}, group_by: i.name)
iex(9)> Repo.all(q)
19:12:15.739 [debug] QUERY OK db=2.7ms
SELECT i0."name", count(i0."name") FROM "items" AS i0 GROUP BY i0."name" []
[%{count: 1, name: "Scissors"}, %{count: 1, name: "Gum"},
 %{count: 2, name: "Chocolates"}, %{count: 1, name: "Paper"},
 %{count: 1, name: "Milk"}, %{count: 1, name: "Test"},
 %{count: 1, name: "Rice"}]

You can see that in the query we wanted to return a map with the name of the item and the number of times it appears in the items table. Alternatively, though, we might more likely be interested in seeing which are the best selling products. So for that, let’s create some invoices. First, let’s make our lives easier by creating a map to access an item_id:

iex(10)> l =  Repo.all(from(i in Item, select: {i.name, i.id}))
iex(11)> items = for {k, v} <- l, into: %{}, do: {k, v}
%{"Chocolates" => "8fde33d3-6e09-4926-baff-369b6d92013c",
  "Gum" => "cb1c5a93-ecbf-4e4b-8588-cc40f7d12364",
  "Milk" => "7f9da795-4d57-4b46-9b57-a40cd09cf67f",
  "Paper" => "66ab2ab7-966d-4b11-b359-019a422328d7",
  "Rice" => "ff0b14d2-1918-495e-9817-f3b08b3fa4a4",
  "Scissors" => "397b0bb4-2b04-46df-84d6-d7b1360b6c72",
  "Test" => "9f832a81-f477-4912-be2f-eac0ec4f8e8f"}

As you can see we have created a map using a comprehension

iex(12)> line_items = [%{item_id: items["Chocolates"], quantity: 2}]

We need to add the price in the invoice_items params to create an invoice, but It would be better just to pass the id of the item and have the price filled automatically. We will do make changes to the Cart.Invoice module to accomplish this:

defmodule Cart.Invoice do
  use Ecto.Schema
  import Ecto.Changeset
  import Ecto.Query # We add to query

  # ....
  # schema, changeset and create functions don't change

  # The new function here is items_with_prices
  defp get_items(params) do
    items = items_with_prices(params[:invoice_items] || params["invoice_items"])
    Enum.map(items, fn(item)-> InvoiceItem.changeset(%InvoiceItem{}, item) end)
  end
  # new function to get item prices
  defp items_with_prices(items) do
    item_ids = Enum.map(items, fn(item) -> item[:item_id] || item["item_id"] end)
    q = from(i in Item, select: %{id: i.id, price: i.price}, where: i.id in ^item_ids)
    prices = Repo.all(q)

    Enum.map(items, fn(item) ->
      item_id = item[:item_id] || item["item_id"]
      %{
         item_id: item_id,
         quantity: item[:quantity] || item["quantity"],
         price: Enum.find(prices, fn(p) -> p[:id] == item_id end)[:price] || 0
       }
    end)
  end

The first thing you will notice is that we have added Ecto.Query, which will allow us to query the database. The new function is defp items_with_prices(items) do which searches through the items and finds and sets the price for each item.

First, defp items_with_prices(items) do receives a list as an argument. With item_ids = Enum.map(items, fn(item) -> item[:item_id] || item["item_id"] end), we iterate through all items and get only the item_id. As you can see, we access either with atom :item_id or string “item_id”, since maps can have either of these as keys. The query q = from(i in Item, select: %{id: i.id, price: i.price}, where: i.id in ^item_ids) will find all items that are in item_ids and will return a map with item.id and item.price. We can then run the query prices = Repo.all(q) which returns a list of maps. We then need to iterate through the items and create a new list that will add the price. The Enum.map(items, fn(item) -> iterates through each item, finds the price Enum.find(prices, fn(p) -> p[:id] == item_id end)[:price] || 0, and creates a new list with item_id, quantity, and price. And with that, it’s no longer necessary to add the price in each of the invoice_items.

Inserting More Invoices

As you remember, earlier we created a map items that enables us to access the id using the item name for i.e items["Gum"] “cb1c5a93-ecbf-4e4b-8588-cc40f7d12364”. This makes it simple to create invoice_items. Let’s create more invoices. Start the console again and run:

Iex -S mix

iex(1)> Repo.delete_all(InvoiceItem); Repo.delete_all(Invoice)

We delete all invoice_items and invoices to have a blank slate:

iex(2)> li = [%{item_id: items["Gum"], quantity: 2}, %{item_id: items["Milk"], quantity: 1}]
iex(3)> Invoice.create(%{customer: "Mary Jane", date: Ecto.Date.utc, invoice_items: li})
iex(4)> li2 = [%{item_id: items["Chocolates"], quantity: 2}| li]
iex(5)> Invoice.create(%{customer: "Mary Jane", date: Ecto.Date.utc, invoice_items: li2})
iex(5)> li3 = li2 ++ [%{item_id: items["Paper"], quantity: 3 }, %{item_id: items["Rice"], quantity: 1}, %{item_id: items["Scissors"], quantity: 1}]
iex(6)> Invoice.create(%{customer: "Juan Perez", date: Ecto.Date.utc, invoice_items: li3})

Now we have 3 invoices; the first one with 2 items, the second with 3 items, and the third with 6 items. We would now like to know which products are the best selling items? To answer that, we are going to create a query to find the best selling items by quantity and by subtotal (price x quantity).

defmodule Cart.Item do
  use Ecto.Schema
  import Ecto.Changeset
  import Ecto.Query

  alias Cart.{InvoiceItem, Item, Repo}

  # schema and changeset don't change 
  # ...

  def items_by_quantity, do: Repo.all items_by(:quantity)

  def items_by_subtotal, do: Repo.all items_by(:subtotal)

  defp items_by(type) do
    from i in Item,
    join: ii in InvoiceItem, on: ii.item_id == i.id,
    select: %{id: i.id, name: i.name, total: sum(field(ii, ^type))},
    group_by: i.id,
    order_by: [desc: sum(field(ii, ^type))]
  end
end

We import Ecto.Query and then we alias Cart.{InvoiceItem, Item, Repo} so we don’t need to add Cart at the beginning of each module. The first function items_by_quantity calls the items_by function, passing the :quantity parameter and calling the Repo.all to execute the query. The function items_by_subtotal is similar to the previous function but passes the :subtotal parameter. Now let’s explain items_by:

• from i in Item, this macro selects the Item module
• join: ii in InvoiceItem, on: ii.item_id == i.id, creates a join on the condition “items.id = invoice_items.item_id”
• select: %{id: i.id, name: i.name, total: sum(field(ii, ^type))}, we are generating a map with all the fields we want first we select the id and name from Item and we do an operator sum. The field(ii, ^type) uses the macro field to dynamically access a field
• group_by: i.id, We group by items.id
• order_by: [desc: sum(field(ii, ^type))] and finally order by the sum in descending order

So far we have written the query in the list style but we could rewrite it in macro style:

defp items_by(type) do
  Item
  |> join(:inner, [i], ii in InvoiceItem, ii.item_id == i.id)
  |> select([i, ii], %{id: i.id, name: i.name, total: sum(field(ii, ^type))})
  |> group_by([i, _], i.id)
  |> order_by([_, ii], [desc: sum(field(ii, ^type))])
end

I prefer to write queries in list form since I find it more readable.

Conclusion

We have covered a good part of what you can do in an app with Ecto. Of course, there is a lot more you can learn from the Ecto docs. With Ecto, you can create concurrent, fault tolerant applications with little effort that can scale easily thanks to the Erlang virtual machine. Ecto provides the basis for the storage in your Elixir applications and provides functions and macros to easily manage your data.

In this tutorial, we examined Ecto.Schema, Ecto.Changeset, Ecto.Migration, Ecto.Query, and Ecto.Repo. Each of these modules helps you in different parts of your application and makes code more explicit and easier to maintain and understand.

If you want to check out the code of the tutorial, you can find it here on GitHub.

If you liked this tutorial and are interested into more information, I would recommend Phoenix (for a list of awesome projects), Awesome Elixir, and this talk that compares ActiveRecord with Ecto.

About the author

Boris Barroso, Bolivia

member since July 19, 2013

As an entrepreneur, Boris understands the importance of close communication with clients and users to better mold applications to real needs. He has also learned how to make things simple--he creates maintainable code using best practices and elegant techniques.

[click to continue...]

Read the full article here by Toptal Engineering Blog