## File: README.md

---

[](https://github.com/elixir-ecto/ecto/actions) [](https://hex.pm/packages/ecto) [](https://hexdocs.pm/ecto/)

## Installation

Add `:ecto` to the list of dependencies in `mix.exs`:

```elixir
def deps do
  [
    {:ecto, "~> 3.10"}
  ]
end
```

## About

Ecto is a toolkit for data mapping and language integrated query for Elixir. Here is an example:

```elixir
# In your config/config.exs file
config :my_app, ecto_repos: [Sample.Repo]

config :my_app, Sample.Repo,
  database: "ecto_simple",
  username: "postgres",
  password: "postgres",
  hostname: "localhost",
  port: "5432"

# In your application code
defmodule Sample.Repo do
  use Ecto.Repo,
    otp_app: :my_app,
    adapter: Ecto.Adapters.Postgres
end

defmodule Sample.Weather do
  use Ecto.Schema

  schema "weather" do
    field :city     # Defaults to type :string
    field :temp_lo, :integer
    field :temp_hi, :integer
    field :prcp,    :float, default: 0.0
  end
end

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

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

    Repo.all(query)
  end

  def pipe_query do
    Weather
    |> where(city: "Kraków")
    |> order_by(:temp_lo)
    |> limit(10)
    |> Repo.all
  end
end
```

Ecto is commonly used to interact with databases, such as PostgreSQL and MySQL via [Ecto.Adapters.SQL](https://hexdocs.pm/ecto_sql) ([source code](https://github.com/elixir-ecto/ecto_sql)). Ecto is also commonly used to map data from any source into Elixir structs, whether they are backed by a database or not.

See the [getting started guide](https://hexdocs.pm/ecto/getting-started.html) and the [online documentation](https://hexdocs.pm/ecto) for more information. Other resources available are:

  * [Programming Ecto](https://pragprog.com/titles/wmecto/programming-ecto/), by Darin Wilson and Eric Meadows-Jönsson, which guides you from fundamentals up to advanced concepts

  * [The Little Ecto Cookbook](https://dashbit.co/ebooks/the-little-ecto-cookbook), a free ebook by Dashbit, which is a curation of the existing Ecto guides with some extra contents

## Usage

You need to add both Ecto and the database adapter as a dependency to your `mix.exs` file. The supported databases and their adapters are:

| Database   | Ecto Adapter             | Dependencies                                     |
| :--------- | :----------------------- | :----------------------------------------------- |
| PostgreSQL | Ecto.Adapters.Postgres   | [ecto_sql][ecto_sql] + [postgrex][postgrex]      |
| MySQL      | Ecto.Adapters.MyXQL      | [ecto_sql][ecto_sql] + [myxql][myxql]            |
| MSSQL      | Ecto.Adapters.Tds        | [ecto_sql][ecto_sql] + [tds][tds]                |
| SQLite3    | Ecto.Adapters.SQLite3    | [ecto_sqlite3][ecto_sqlite3]                     |
| ClickHouse | Ecto.Adapters.ClickHouse | [ecto_ch][ecto_ch]                               |
| ETS        | Etso                     | [etso][etso]                                     |

[ecto_sql]: https://github.com/elixir-ecto/ecto_sql
[postgrex]: https://github.com/elixir-ecto/postgrex
[myxql]: https://github.com/elixir-ecto/myxql
[tds]: https://github.com/livehelpnow/tds
[ecto_sqlite3]: https://github.com/elixir-sqlite/ecto_sqlite3
[etso]: https://github.com/evadne/etso
[ecto_ch]: https://github.com/plausible/ecto_ch

For example, if you want to use PostgreSQL, add to your `mix.exs` file:

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

Then run `mix deps.get` in your shell to fetch the dependencies. If you want to use another database, just choose the proper dependency from the table above.

Finally, in the repository definition, you will need to specify the `adapter:` respective to the chosen dependency. For PostgreSQL it is:

```elixir
defmodule MyApp.Repo do
  use Ecto.Repo,
    otp_app: :my_app,
    adapter: Ecto.Adapters.Postgres,
  ...
```

### IPv6 support

If your database's host resolves to ipv6 address you should
add `socket_options: [:inet6]` to configuration block like below:

```elixir
import Mix.Config

config :my_app, MyApp.Repo,
  hostname: "db12.dc0.comp.any",
  socket_options: [:inet6],
  ...
```

## Supported Versions

| Branch            | Support                  |
| ----------------- | ------------------------ |
| v3.12             | Bug fixes                |
| v3.11             | Security patches only    |
| v3.10             | Security patches only    |
| v3.9              | Security patches only    |
| v3.8              | Security patches only    |
| v3.7 and earlier  | Unsupported              |

With version 3.0, Ecto API has become stable. Our main focus is on providing
bug fixes and incremental changes.

## Important links

  * [Documentation](https://hexdocs.pm/ecto)
  * [Mailing list](https://groups.google.com/forum/#!forum/elixir-ecto)
  * [Examples](https://github.com/elixir-ecto/ecto/tree/master/examples)

## Running tests

Clone the repo and fetch its dependencies:

    $ git clone https://github.com/elixir-ecto/ecto.git
    $ cd ecto
    $ mix deps.get
    $ mix test

Note that `mix test` does not run the tests in the `integration_test` folder. To run integration tests, you can clone `ecto_sql` in a sibling directory and then run its integration tests with the `ECTO_PATH` environment variable pointing to your Ecto checkout:

    $ cd ..
    $ git clone https://github.com/elixir-ecto/ecto_sql.git
    $ cd ecto_sql
    $ mix deps.get
    $ ECTO_PATH=../ecto mix test.all

### Running containerized tests

It is also possible to run the integration tests under a containerized environment using [earthly](https://earthly.dev/get-earthly):

    $ earthly -P +all

You can also use this to interactively debug any failing integration tests using:

    $ earthly -P -i --build-arg ELIXIR_BASE=1.8.2-erlang-21.3.8.21-alpine-3.13.1 +integration-test

Then once you enter the containerized shell, you can inspect the underlying databases with the respective commands:

    PGPASSWORD=postgres psql -h 127.0.0.1 -U postgres -d postgres ecto_test
    MYSQL_PASSWORD=root mysql -h 127.0.0.1 -uroot -proot ecto_test
    sqlcmd -U sa -P 'some!Password'

## Logo

"Ecto" and the Ecto logo are Copyright (c) 2020 Dashbit.

The Ecto logo was designed by [Dane Wesolko](https://www.danewesolko.com).

## License

Copyright (c) 2013 Plataformatec \
Copyright (c) 2020 Dashbit

Licensed under the Apache License, Version 2.0 (the "License");
you may not use this file except in compliance with the License.
You may obtain a copy of the License at [https://www.apache.org/licenses/LICENSE-2.0](https://www.apache.org/licenses/LICENSE-2.0)

Unless required by applicable law or agreed to in writing, software
distributed under the License is distributed on an "AS IS" BASIS,
WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
See the License for the specific language governing permissions and
limitations under the License.

---

## File: examples/friends/README.md

# Friends

A sample application built for Ecto guides as described in
https://hexdocs.pm/ecto/getting-started.html.

# Setup

Update the PostgreSQL database config in `config/config.exs` if the below
commands don't work.

```sh
mix deps.get
mix test
mix run priv/repo/seeds.exs
```

Start the app through IEx session:

```sh
iex -S mix
```

Run below query to verify your setup:

```elixir
iex(1)> Friends.Person |> Ecto.Query.first |> Friends.Repo.one

03:00:00.198 [debug] QUERY OK source="people" db=0.6ms decode=1.2ms queue=1.0ms idle=1384.7ms
SELECT p0."id", p0."first_name", p0."last_name", p0."age" FROM "people" AS p0 ORDER BY p0."id" LIMIT 1 []
%Friends.Person{
  __meta__: #Ecto.Schema.Metadata<:loaded, "people">,
  id: 1,
  first_name: "Ryan",
  last_name: "Bigg",
  age: 28
}
```

---

## File: guides/testing/Testing with Ecto.md

# Testing with Ecto

After you have successfully set up your database connection with Ecto for your application,
its usage for your tests requires further changes, especially if you want to leverage the
[Ecto SQL Sandbox](https://hexdocs.pm/ecto_sql/Ecto.Adapters.SQL.Sandbox.html) that allows
you to run tests that talk to the database concurrently.

Create the `config/test.exs` file or append the following content:

```elixir
import Config

config :my_app, MyApp.Repo,
  username: "postgres",
  password: "postgres",
  database: "myapp_test",
  hostname: "localhost",
  pool: Ecto.Adapters.SQL.Sandbox

 ```

Thereby, we configure the database connection for our test setup.
In this case, we use a Postgres database and set it up to use the sandbox pool that will wrap each test in a transaction.

Make sure we import the configuration for the test environment at the very bottom of `config/config.exs`:

```elixir
import_config "#{config_env()}.exs"
```

We also need to add an explicit statement to the end of `test/test_helper.exs` about the `sandbox` mode:

```elixir
Ecto.Adapters.SQL.Sandbox.mode(MyApp.Repo, :manual)
```

Lastly, you need to establish the database connection ahead of your tests.
You can enable it either for all of your test cases by extending the `ExUnit` template or by setting it up individually for each test. Let's start with the former and place it to the `test/support/repo_case.ex`:

```elixir
defmodule MyApp.RepoCase do
  use ExUnit.CaseTemplate

  using do
    quote do
      alias MyApp.Repo

      import Ecto
      import Ecto.Query
      import MyApp.RepoCase

      # and any other stuff
    end
  end

  setup tags do
    pid = Ecto.Adapters.SQL.Sandbox.start_owner!(MyApp.Repo, shared: not tags[:async])
    on_exit(fn -> Ecto.Adapters.SQL.Sandbox.stop_owner(pid) end)
    :ok
  end
end
```

The case template above brings `Ecto` and `Ecto.Query` functions into your tests and checks-out a database connection. It also enables a shared sandbox connection mode in case the test is not running asynchronously.
See [`Ecto.Adapters.SQL.Sandbox`](https://hexdocs.pm/ecto_sql/Ecto.Adapters.SQL.Sandbox.html) for more information.

To add `test/support/` folder for compilation in test environment we need to update `mix.exs` configuration

```elixir
  def project do
    [
      # ...
      elixirc_paths: elixirc_paths(Mix.env())
    ]
  end

  # Specifies which paths to compile per environment.
  defp elixirc_paths(:test), do: ["lib", "test/support"]
  defp elixirc_paths(_), do: ["lib"]
```

And then in each test that uses the repository:

```elixir
defmodule MyApp.MyTest do
  use MyApp.RepoCase

  # Tests etc...
end
```

In case you don't want to define a "case template", you can checkout on each individual case:

```elixir
defmodule MyApp.MyTest do
  use ExUnit.Case

  setup do
    :ok = Ecto.Adapters.SQL.Sandbox.checkout(MyApp.Repo)
  end

  # Tests etc...
end
```

For convenience reasons, you can also define `aliases` to automatically set up your database at the execution of your tests.
Change the following content in your `mix.exs`.

```elixir

  def project do
    [app: :my_app,

     ...

     aliases: aliases()]
  end

  defp aliases do
    [ ...
     "test": ["ecto.create --quiet", "ecto.migrate", "test"]
    ]
  end
```

---

## File: guides/introduction/Getting Started.md

# Getting Started

This guide is an introduction to [Ecto](https://github.com/elixir-lang/ecto),
the database wrapper and query generator for Elixir. Ecto provides a
standardized API and a set of abstractions for talking to all the different
kinds of databases, so that Elixir developers can query whatever database
they're using by employing similar constructs.

In this guide, we're going to learn some basics about Ecto, such as creating,
reading, updating and destroying records from a PostgreSQL database. If you want
to see the code from this guide, you can view it [at ecto/examples/friends on GitHub](https://github.com/elixir-lang/ecto/tree/master/examples/friends).

**This guide will require you to have setup PostgreSQL beforehand.**

## Adding Ecto to an application

To start off with, we'll generate a new Elixir application by running this command:

```
mix new friends --sup
```

The `--sup` option ensures that this application has [a supervision tree](`Supervisor`), which we'll need for Ecto a little later on.

To add Ecto to this application, there are a few steps that we need to take. The first step will be adding Ecto and a driver called Postgrex to our `mix.exs` file, which we'll do by changing the `deps` definition in that file to this:

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

Ecto provides the common querying API, but we need the Postgrex driver installed too, as that is what Ecto uses to speak in terms a PostgreSQL database can understand. Ecto talks to its own `Ecto.Adapters.Postgres` module, which then in turn talks to the `postgrex` package to talk to PostgreSQL.

To install these dependencies, we will run this command:

```
mix deps.get
```

The Postgrex application will receive queries from Ecto and execute them
against our database. If we didn't do this step, we wouldn't be able to do any
querying at all.

That's the first two steps taken now. We have installed Ecto and Postgrex as
dependencies of our application. Next let's update the `.formatter.exs`
file so that Ecto's rules will be applied on `mix format`:

```elixir
[
  # Add this line to enable Ecto formatter rules
  import_deps: [:ecto, :ecto_sql],

  # Default Elixir project rules
  inputs: ["{mix,.formatter}.exs", "{config,lib,test}/**/*.{ex,exs}"]
]
```

We now need to setup some configuration for Ecto so that we can perform actions
on a database from within the application's code. We can set up this
configuration by running this command:

```
mix ecto.gen.repo -r Friends.Repo
```

This command will generate the configuration required to connect to a database. The first bit of configuration is in `config/config.exs`:

```elixir
config :friends, Friends.Repo,
  database: "friends",
  username: "user",
  password: "pass",
  hostname: "localhost"
```

**NOTE**: Your PostgreSQL database may be setup to

 - not require a username and password. If the above configuration doesn't work, try removing the username and password fields, or setting them both to "postgres".
 - be running on a non-standard port. The default port is `5432`. You can specify your specific port by adding it to the config: e.g. `port: 15432`.

This configures how Ecto will connect to our database, called "friends". Specifically, it configures a "repo". More information about [Ecto.Repo can be found in its documentation](https://hexdocs.pm/ecto/Ecto.Repo.html).

The `Friends.Repo` module is defined in `lib/friends/repo.ex` by our `mix ecto.gen.repo` command:

```elixir
defmodule Friends.Repo do
  use Ecto.Repo,
    otp_app: :friends,
    adapter: Ecto.Adapters.Postgres
end
```

This module is what we'll be using to query our database shortly. It uses the `Ecto.Repo` module, and the `otp_app` tells Ecto which Elixir application it can look for database configuration in. In this case, we've specified that it is the `:friends` application where Ecto can find that configuration and so Ecto will use the configuration that was set up in `config/config.exs`. Finally, we configure the database `:adapter` to Postgres.

Finally, the `Friends.Repo` must be started within the application's supervision tree, which we can do in `lib/friends/application.ex`, inside the `start/2` function:

```elixir
def start(_type, _args) do
  children = [
    Friends.Repo,
  ]

  ...
```

This piece of configuration will start the Ecto process which receives and executes our application's queries. Without it, we wouldn't be able to query the database at all!

There's one final bit of configuration that we'll need to add ourselves, since the generator does not add it. Underneath the configuration in `config/config.exs`, add this line:

```elixir
config :friends, ecto_repos: [Friends.Repo]
```

This tells our application about the repo, which will allow us to run commands such as `mix ecto.create` very soon.

We've now configured our application so that it's able to make queries to our database. Let's now create our database, add a table to it, and then perform some queries.

## Setting up the database

To be able to query a database, it first needs to exist. We can create the database with this command:

```
mix ecto.create
```

If the database has been created successfully, then you will see this message:

```
The database for Friends.Repo has been created.
```

**NOTE:** If you get an error, you should try changing your configuration in `config/config.exs`, as it may be an authentication error.

A database by itself isn't very queryable, so we will need to create a table within that database. To do that, we'll use what's referred to as a _migration_. If you've come from Active Record (or similar), you will have seen these before. A migration is a single step in the process of constructing your database.

Let's create a migration now with this command:

```
mix ecto.gen.migration create_people
```

This command will generate a brand new migration file in `priv/repo/migrations`, which is empty by default:

```elixir
defmodule Friends.Repo.Migrations.CreatePeople do
  use Ecto.Migration

  def change do

  end
end
```

Let's add some code to this migration to create a new table called "people", with a few columns in it:

```elixir
defmodule Friends.Repo.Migrations.CreatePeople do
  use Ecto.Migration

  def change do
    create table(:people) do
      add :first_name, :string
      add :last_name, :string
      add :age, :integer
    end
  end
end
```

This new code will tell Ecto to create a new table called `people`, and add three new fields: `first_name`, `last_name` and `age` to that table. The types of these fields are `string` and `integer`. (The different types that Ecto supports are covered in the [Ecto.Schema](https://hexdocs.pm/ecto/Ecto.Schema.html) documentation.)

**NOTE**: The naming convention for tables in Ecto databases is to use a pluralized name.

To run this migration and create the `people` table in our database, we will run this command:

```
mix ecto.migrate
```

If we found out that we made a mistake in this migration, we could run `mix ecto.rollback` to undo the changes in the migration. We could then fix the changes in the migration and run `mix ecto.migrate` again. If we ran `mix ecto.rollback` now, it would delete the table that we just created.

We now have a table created in our database. The next step that we'll need to do is to create the schema.

## Creating the schema

The schema is an Elixir representation of data from our database. Schemas are commonly associated with a database table, however they can be associated with a database view as well.

Let's create the schema within our application at `lib/friends/person.ex`:

```elixir
defmodule Friends.Person do
  use Ecto.Schema

  schema "people" do
    field :first_name, :string
    field :last_name, :string
    field :age, :integer
  end
end
```

This defines the schema from the database that this schema maps to. In this case, we're telling Ecto that the `Friends.Person` schema maps to the `people` table in the database, and the `first_name`, `last_name` and `age` fields in that table. The second argument passed to `field` tells Ecto how we want the information from the database to be represented in our schema.

We've called this schema `Person` because the naming convention in Ecto for schemas is a singularized name.

We can play around with this schema in an IEx session by starting one up with `iex -S mix` and then running this code in it:

```elixir
person = %Friends.Person{}
```

This code will give us a new `Friends.Person` struct, which will have `nil` values for all the fields. We can set values on these fields by generating a new struct:

```elixir
person = %Friends.Person{age: 28}
```

Or with syntax like this:

```elixir
person = %{person | age: 28}
```

We can retrieve values using this syntax:

```elixir
person.age # => 28
```

Let's take a look at how we can insert data into the database.

## Inserting data

We can insert a new record into our `people` table with this code:

```elixir
person = %Friends.Person{}
Friends.Repo.insert(person)
```

To insert the data into our database, we call `insert` on `Friends.Repo`, which is the module that uses Ecto to talk to our database. This function tells Ecto that we want to insert a new `Friends.Person` record into the database corresponding with `Friends.Repo`. The `person` struct here represents the data that we want to insert into the database.

A successful insertion will return a tuple, like so:

```elixir
{:ok,
 %Friends.Person{__meta__: #Ecto.Schema.Metadata<:loaded, "people">, age: nil,
  first_name: nil, id: 1, last_name: nil}}
```

The `:ok` atom can be used for pattern matching purposes to ensure that the insertion succeeds. A situation where the insertion may not succeed is if you have a constraint on the database itself. For instance, if the database had a unique constraint on a field called `email` so that an email can only be used for one person record, then the insertion would fail.

You may wish to pattern match on the tuple in order to refer to the record inserted into the database:

```elixir
{:ok, person} = Friends.Repo.insert person
```

## Validating changes

In Ecto, you may wish to validate changes before they go to the database. For instance, you may wish that a person has both a first name and a last name before a record can be entered into the database. For this, Ecto has [_changesets_](https://hexdocs.pm/ecto/Ecto.Changeset.html).

Let's add a changeset to our `Friends.Person` module inside `lib/friends/person.ex` now:

```elixir
def changeset(person, params \\ %{}) do
  person
  |> Ecto.Changeset.cast(params, [:first_name, :last_name, :age])
  |> Ecto.Changeset.validate_required([:first_name, :last_name])
end
```

This changeset takes a `person` and a set of params, which are to be the changes to apply to this person. The `changeset` function first casts the `first_name`, `last_name` and `age` keys from the parameters passed in to the changeset. Casting tells the changeset what parameters are allowed to be passed through in this changeset, and anything not in the list will be ignored.

On the next line, we call `validate_required` which says that, for this changeset, we expect `first_name` and `last_name` to have values specified. Let's use this changeset to attempt to create a new record without a `first_name` and `last_name`:

```elixir
person = %Friends.Person{}
changeset = Friends.Person.changeset(person, %{})
Friends.Repo.insert(changeset)
```

On the first line here, we get a struct from the `Friends.Person` module. We know what that does, because we saw it not too long ago. On the second line we do something brand new: we define a changeset. This changeset says that on the specified `person` object, we're looking to make some changes. In this case, we're not looking to change anything at all.

On the final line, rather than inserting the `person`, we insert the `changeset`. The `changeset` knows about the `person`, the changes and the validation rules that must be met before the data can be entered into the database. When this third line runs, we'll see this:

```elixir
{:error,
 #Ecto.Changeset<action: :insert, changes: %{},
  errors: [first_name: "can't be blank", last_name: "can't be blank"],
  data: #Friends.Person<>, valid?: false>}
```

Just like the last time we did an insertion, this returns a tuple. This time however, the first element in the tuple is `:error`, which indicates something bad happened. The specifics of what happened are included in the changeset which is returned. We can access these by doing some pattern matching:

```elixir
{:error, changeset} = Friends.Repo.insert(changeset)
```

Then we can get to the errors by doing `changeset.errors`:

```elixir
[first_name: {"can't be blank", [validation: :required]}, last_name: {"can't be blank", [validation: :required]}]
```

And we can ask the changeset itself if it is valid, even before doing an insertion:

```elixir
changeset.valid?
#=> false
```

Since this changeset has errors, no new record was inserted into the `people`
table.

Let's try now with some valid data.

```elixir
person = %Friends.Person{}
changeset = Friends.Person.changeset(person, %{first_name: "Ryan", last_name: "Bigg"})
```

We start out here with a normal `Friends.Person` struct. We then create a changeset for that `person` which has a `first_name` and a `last_name` parameter specified. At this point, we can ask the changeset if it has errors:

```elixir
changeset.errors
#=> []
```

And we can ask if it's valid or not:

```elixir
changeset.valid?
#=> true
```

The changeset does not have errors, and is valid. Therefore if we try to insert this changeset it will work:

```elixir
Friends.Repo.insert(changeset)
#=> {:ok,
     %Friends.Person{__meta__: #Ecto.Schema.Metadata<:loaded, "people">, age: nil,
      first_name: "Ryan", id: 3, last_name: "Bigg"}}
```


Due to `Friends.Repo.insert` returning a tuple, we can use a `case` to determine different code paths depending on what happens:

```elixir
case Friends.Repo.insert(changeset) do
  {:ok, person} ->
    # do something with person
  {:error, changeset} ->
    # do something with changeset
end
```

**NOTE:** `changeset.valid?` will not check constraints (such as `uniqueness_constraint`). For that, you will need to attempt to do an insertion and check for errors from the database. It's for this reason it's best practice to try inserting data and validate the returned tuple from `Friends.Repo.insert` to get the correct errors, as prior to insertion the changeset will only contain validation errors from the application itself.

If the insertion of the changeset succeeds, then you can do whatever you wish with the `person` returned in that result. If it fails, then you have access to the changeset and its errors. In the failure case, you may wish to present these errors to the end user. The errors in the changeset are a keyword list that looks like this:


```elixir
[first_name: {"can't be blank", [validation: :required]},
 last_name: {"can't be blank", [validation: :required]}]
```

The first element of the tuple is the validation message, and the second element is a keyword list of options for the validation message. Imagine that we had a field called `bio` that we were validating, and that field has to be longer than 15 characters. This is what would be returned:


```elixir
[first_name: {"can't be blank", [validation: :required]},
 last_name: {"can't be blank", [validation: :required]},
 bio: {"should be at least %{count} character(s)", [count: 15, validation: :length, kind: :min, type: :string]}]
```

To display these error messages in a human friendly way, we can use `Ecto.Changeset.traverse_errors/2`:

```elixir
traverse_errors(changeset, fn {msg, opts} ->
  Enum.reduce(opts, msg, fn {key, value}, acc ->
    String.replace(acc, "%{#{key}}", to_string(value))
  end)
end)
```

This will return the following for the errors shown above:

```elixir
%{
  first_name: ["can't be blank"],
  last_name: ["can't be blank"],
  bio: ["should be at least 15 character(s)"],
}
```

One more final thing to mention here: you can trigger an exception to be thrown by using `Friends.Repo.insert!/2`. If a changeset is invalid, you will see an `Ecto.InvalidChangesetError` exception. Here's a quick example of that:

```
Friends.Repo.insert! Friends.Person.changeset(%Friends.Person{}, %{first_name: "Ryan"})

** (Ecto.InvalidChangesetError) could not perform insert because changeset is invalid.

Errors

    %{last_name: [{"can't be blank", [validation: :required]}]}

Applied changes

    %{first_name: "Ryan"}

Params

    %{"first_name" => "Ryan"}

Changeset

    #Ecto.Changeset<
      action: :insert,
      changes: %{first_name: "Ryan"},
      errors: [last_name: {"can't be blank", [validation: :required]}],
      data: #Friends.Person<>,
      valid?: false
    >

   (ecto) lib/ecto/repo/schema.ex:257: Ecto.Repo.Schema.insert!/4
```

This exception shows us the changes from the changeset, and how the changeset is invalid. This can be useful if you want to insert a bunch of data and then have an exception raised if that data is not inserted correctly at all.

Now that we've covered inserting data into the database, let's look at how we can pull that data back out.

## Our first queries

Querying a database requires two steps in Ecto. First, we must construct the query and then we must execute that query against the database by passing the query to the repository. Before we do this, let's re-create the database for our app and setup some test data. To re-create the database, we'll run these commands:

```
mix ecto.drop
mix ecto.create
mix ecto.migrate
```

Then to create the test data, we'll run this in an `iex -S mix` session:

```elixir
people = [
  %Friends.Person{first_name: "Ryan", last_name: "Bigg", age: 28},
  %Friends.Person{first_name: "John", last_name: "Smith", age: 27},
  %Friends.Person{first_name: "Jane", last_name: "Smith", age: 26},
]

Enum.each(people, fn (person) -> Friends.Repo.insert(person) end)
```

This code will create three new people in our database, Ryan, John and Jane. Note here that we could've used a changeset to validate the data going into the database, but the choice was made not to use one.

We'll be querying for these people in this section. Let's jump in!

### Fetching a single record

Let's start off with fetching just one record from our `people` table:

```elixir
Friends.Person |> Ecto.Query.first
```

That code will generate an `Ecto.Query`, which will be this:

```
#Ecto.Query<from p0 in Friends.Person, order_by: [asc: p0.id], limit: 1>
```

The code between the angle brackets `<...>` here shows the Ecto query which has been constructed. We could construct this query ourselves with almost exactly the same syntax:

```elixir
require Ecto.Query
Ecto.Query.from p in Friends.Person, order_by: [asc: p.id], limit: 1
```

We need to `require Ecto.Query` here to enable the macros from that module. Then it's a matter of calling the `from` function from `Ecto.Query` and passing in the code from between the angle brackets. As we can see here, `Ecto.Query.first` saves us from having to specify the `order` and `limit` for the query.

To execute the query that we've just constructed, we can call `Friends.Repo.one`:

```elixir
Friends.Person |> Ecto.Query.first |> Friends.Repo.one
```

The `one` function retrieves just one record from our database and returns a new struct from the `Friends.Person` module:

```elixir
%Friends.Person{__meta__: #Ecto.Schema.Metadata<:loaded, "people">, age: 28,
 first_name: "Ryan", id: 1, last_name: "Bigg"}
```

Similar to `first`, there is also `last`:

```elixir
Friends.Person |> Ecto.Query.last |> Friends.Repo.one
#=> %Friends.Person{__meta__: #Ecto.Schema.Metadata<:loaded, "people">, age: 26,
     first_name: "Jane", id: 3, last_name: "Smith"}
 ```

The `Ecto.Repo.one` function will only return a struct if there is one record in the
result from the database. If there is more than one record returned, an
`Ecto.MultipleResultsError` exception will be thrown. Some code that would
cause that issue to happen is:

```elixir
Friends.Person |> Friends.Repo.one
```

We've left out the `Ecto.Query.first` here, and so there is no `limit` or `order` clause applied to the executed query. We'll see the executed query in the debug log:

```
[timestamp] [debug] SELECT p0."id", p0."first_name", p0."last_name", p0."age" FROM "people" AS p0 [] OK query=1.8ms
```

Then immediately after that, we will see the `Ecto.MultipleResultsError` exception:

```
** (Ecto.MultipleResultsError) expected at most one result but got 3 in query:

from p in Friends.Person

    lib/ecto/repo/queryable.ex:67: Ecto.Repo.Queryable.one/4
```

This happens because Ecto doesn't know what one record out of all the records
returned that we want. Ecto will only return a result if we are explicit in
our querying about which result we want.

If there is no record which matches the query, `one` will return `nil`.

### Fetching all records

To fetch all records from the schema, Ecto provides the `all` function:

```elixir
Friends.Person |> Friends.Repo.all
```

This will return a `Friends.Person` struct representation of all the records that currently exist within our `people` table:

```elixir
[%Friends.Person{__meta__: #Ecto.Schema.Metadata<:loaded, "people">, age: 28,
  first_name: "Ryan", id: 1, last_name: "Bigg"},
 %Friends.Person{__meta__: #Ecto.Schema.Metadata<:loaded, "people">, age: 27,
  first_name: "John", id: 2, last_name: "Smith"},
 %Friends.Person{__meta__: #Ecto.Schema.Metadata<:loaded, "people">, age: 26,
  first_name: "Jane", id: 3, last_name: "Smith"}]
```

### Fetch a single record based on ID

To fetch a record based on its ID, you use the `get` function:

```elixir
Friends.Person |> Friends.Repo.get(1)
#=> %Friends.Person{__meta__: #Ecto.Schema.Metadata<:loaded, "people">, age: 28,
     first_name: "Ryan", id: 1, last_name: "Bigg"}
```

### Fetch a single record based on a specific attribute

If we want to get a record based on something other than the `id` attribute, we can use `get_by`:

```elixir
 Friends.Person |> Friends.Repo.get_by(first_name: "Ryan")
 #=> %Friends.Person{__meta__: #Ecto.Schema.Metadata<:loaded, "people">, age: 28,
      first_name: "Ryan", id: 1, last_name: "Bigg"}
```

### Filtering results

If we want to get multiple records matching a specific attribute, we can use `where`:

```elixir
Friends.Person |> Ecto.Query.where(last_name: "Smith") |> Friends.Repo.all
```

```elixir
[%Friends.Person{__meta__: #Ecto.Schema.Metadata<:loaded, "people">, age: 27,
  first_name: "John", id: 2, last_name: "Smith"},
 %Friends.Person{__meta__: #Ecto.Schema.Metadata<:loaded, "people">, age: 26,
  first_name: "Jane", id: 3, last_name: "Smith"}]
```

If we leave off the `Friends.Repo.all` on the end of this, we will see the query Ecto generates:

```
#Ecto.Query<from p in Friends.Person, where: p.last_name == "Smith">
```

We can also use this query syntax to fetch these same records:

```elixir
Ecto.Query.from(p in Friends.Person, where: p.last_name == "Smith") |> Friends.Repo.all
```

One important thing to note with both query syntaxes is that they require variables to be pinned, using the pin operator (`^`). Otherwise, this happens:

```elixir
last_name = "Smith"
Friends.Person |> Ecto.Query.where(last_name: last_name) |> Friends.Repo.all
```

```
** (Ecto.Query.CompileError) unbound variable `last_name` in query. If you are attempting to interpolate a value, use ^var
    (ecto)   expanding macro: Ecto.Query.where/2
             iex:15: (file)
    (elixir) expanding macro: Kernel.|>/2
             iex:15: (file)
```

The same will happen in the longer query syntax too:

```elixir
Ecto.Query.from(p in Friends.Person, where: p.last_name == last_name) |> Friends.Repo.all
```

```
** (Ecto.Query.CompileError) unbound variable `last_name` in query. If you are attempting to interpolate a value, use ^var
    (ecto)   expanding macro: Ecto.Query.where/3
             iex:15: (file)
    (ecto)   expanding macro: Ecto.Query.from/2
             iex:15: (file)
    (elixir) expanding macro: Kernel.|>/2
             iex:15: (file)
```

To get around this, we use the pin operator (`^`):

```elixir
last_name = "Smith"
Friends.Person |> Ecto.Query.where(last_name: ^last_name) |> Friends.Repo.all
```

Or:

```elixir
last_name = "Smith"
Ecto.Query.from(p in Friends.Person, where: p.last_name == ^last_name) |> Friends.Repo.all
```

The pin operator instructs the query builder to use parameterized SQL queries protecting against SQL injection.

### Composing Ecto queries

Ecto queries don't have to be built in one spot. They can be built up by calling `Ecto.Query` functions on existing queries. For instance, if we want to find all people with the last name "Smith", we can do:

```elixir
query = Friends.Person |> Ecto.Query.where(last_name: "Smith")
```

If we want to scope this down further to only people with the first name of "Jane", we can do this:

```elixir
query = query |> Ecto.Query.where(first_name: "Jane")
```

Our query will now have two `where` clauses in it:

```
#Ecto.Query<from p in Friends.Person, where: p.last_name == "Smith",
 where: p.first_name == "Jane">
```

This can be useful if you want to do something with the first query, and then build off that query later on.

## Updating records

Updating records in Ecto requires us to first fetch a record from the database. We then create a changeset from that record and the changes we want to make to that record, and then call the `Ecto.Repo.update` function.

Let's fetch the first person from our database and change their age. First, we'll fetch the person:

```elixir
person = Friends.Person |> Ecto.Query.first |> Friends.Repo.one
```

Next, we'll build a changeset. We need to build a changeset because if we just create a new `Friends.Person` struct with the new age, Ecto wouldn't be able to know that the age has changed without inspecting the database. Let's build that changeset:

```elixir
changeset = Friends.Person.changeset(person, %{age: 29})
```

This changeset will inform the database that we want to update the record to have the `age` set to 29. To tell the database about the change we want to make, we run this command:

```elixir
Friends.Repo.update(changeset)
```

Just like `Friends.Repo.insert`, `Friends.Repo.update` will return a tuple:

```elixir
{:ok,
 %Friends.Person{__meta__: #Ecto.Schema.Metadata<:loaded, "people">, age: 29,
  first_name: "Ryan", id: 1, last_name: "Bigg"}}
```

If the changeset fails for any reason, the result of `Friends.Repo.update` will be `{:error, changeset}`. We can see this in action by passing through a blank `first_name` in our changeset's parameters:

```elixir
changeset = Friends.Person.changeset(person, %{first_name: ""})
Friends.Repo.update(changeset)
#=> {:error,
     #Ecto.Changeset<
       action: :update,
       changes: %{},
       errors: [first_name: {"can't be blank", [validation: :required]}],
       data: #Friends.Person<>,
       valid?: false
     >}
```

This means that you can also use a `case` statement to do different things depending on the outcome of the `update` function:

```elixir
case Friends.Repo.update(changeset) do
  {:ok, person} ->
    # do something with person
  {:error, changeset} ->
    # do something with changeset
end
```

Similar to `insert!`, there is also `update!` which will raise an exception if the changeset is invalid:

```
changeset = Friends.Person.changeset(person, %{first_name: ""})
Friends.Repo.update! changeset

** (Ecto.InvalidChangesetError) could not perform update because changeset is invalid.

Errors

  %{first_name: [{"can't be blank", [validation: :required]}]}

Applied changes

  %{}

Params

  %{"first_name" => ""}

Changeset

    #Ecto.Changeset<
      action: :update,
      changes: %{},
      errors: [first_name: {"can't be blank", [validation: :required]}],
      data: #Friends.Person<>,
      valid?: false
    >

    (ecto) lib/ecto/repo/schema.ex:270: Ecto.Repo.Schema.update!/4
```

## Deleting records

We've now covered creating (`insert`), reading (`get`, `get_by`, `where`) and updating records. The last thing that we'll cover in this guide is how to delete a record using Ecto.

Similar to updating, we must first fetch a record from the database and then call `Friends.Repo.delete` to delete that record:

```elixir
person = Friends.Repo.get(Friends.Person, 1)
Friends.Repo.delete(person)
#=> {:ok,
 %Friends.Person{__meta__: #Ecto.Schema.Metadata<:deleted, "people">, age: 29,
  first_name: "Ryan", id: 2, last_name: "Bigg"}}
```

Similar to `insert` and `update`, `delete` returns a tuple. If the deletion succeeds, then the first element in the tuple will be `:ok`, but if it fails then it will be an `:error`.

---

## File: guides/howtos/Aggregates and subqueries.md

# Aggregates and subqueries

Now it's time to discuss aggregates and subqueries. As we will learn, one builds directly on the other.

## Aggregates

Ecto includes a convenience function in repositories to calculate aggregates.

For example, if we assume every post has an integer column named visits, we can find the average number of visits across all posts with:

```elixir
MyApp.Repo.aggregate(MyApp.Post, :avg, :visits)
#=> Decimal.new(1743)
```

Behind the scenes, the query above translates to:

```elixir
MyApp.Repo.one(from p in MyApp.Post, select: avg(p.visits))
```

The `c:Ecto.Repo.aggregate/4` function supports any of the aggregate operations listed in the `Ecto.Query.API` module.

At first, it looks like the implementation of `aggregate/4` is quite straight-forward. You could even start to wonder why it was added to Ecto in the first place. However, complexities start to arise on queries that rely on `limit`, `offset` or `distinct` clauses.

Imagine that instead of calculating the average of all posts, you want the average of only the top 10. Your first try may be:

```elixir
MyApp.Repo.one(
  from p in MyApp.Post,
    order_by: [desc: :visits],
    limit: 10,
    select: avg(p.visits)
)
#=> Decimal.new(1743)
```

Oops. The query above returned the same value as the queries before. The option `limit: 10` has no effect here since it is limiting the aggregated result and queries with aggregates return only a single row anyway. In order to retrieve the correct result, we would need to first find the top 10 posts and only then aggregate. That's exactly what `aggregate/4` does:

```elixir
query =
  from MyApp.Post,
    order_by: [desc: :visits],
    limit: 10

MyApp.Repo.aggregate(query, :avg, :visits)
#=> Decimal.new(4682)
```

When `limit`, `offset` or `distinct` is specified in the query, `aggregate/4` automatically wraps the given query in a subquery. Therefore the query executed by `aggregate/4` above is rather equivalent to:

```elixir
inner_query =
  from MyApp.Post,
    order_by: [desc: :visits],
    limit: 10

query =
  from q in subquery(inner_query),
  select: avg(q.visits)

MyApp.Repo.one(query)
```

Let's take a closer look at subqueries.

## Subqueries

In the previous section we have already learned some queries that would be hard to express without support for subqueries. That's one of many examples that caused subqueries to be added to Ecto.

Subqueries in Ecto are created by calling `Ecto.Query.subquery/1`. This function receives any data structure that can be converted to a query, via the `Ecto.Queryable` protocol, and returns a subquery construct (which is also queryable).

In Ecto, it is allowed for a subquery to select a whole table (`p`) or a field (`p.field`). All fields selected in a subquery can be accessed from the parent query. Let's revisit the aggregate query we saw in the previous section:

```elixir
inner_query =
  from MyApp.Post,
    order_by: [desc: :visits],
    limit: 10

query =
  from q in subquery(inner_query),
    select: avg(q.visits)

MyApp.Repo.one(query)
```

Because the query does not specify a `:select` clause, it will return `select: p` where `p` is controlled by `MyApp.Post` schema. Since the query will return all fields in `MyApp.Post`, when we convert it to a subquery, all of the fields from `MyApp.Post` will be available on the parent query, such as `q.visits`. In fact, Ecto will keep the schema properties across queries. For example, if you write `q.field_that_does_not_exist`, your Ecto query won't compile.

If you need to reference the parent in the subquery, you can do so using named bindings in the parent query and `Ecto.Query.API.parent_as/1` in the subquery. Here is an example:

```elixir
inner_query =
  from c in Comment,
    where: parent_as(:posts).id == c.post_id

query =
  from p in Post,
    as: :posts,
    inner_lateral_join: c in subquery(inner_query)

MyApp.Repo.one(query)
```

Ecto also allows an Elixir map to be returned from a subquery, making the map keys directly available to the parent query.

Let's see one last example. Imagine you manage a library (as in an actual library in the real world) and there is a table that logs every time the library lends a book. The "lendings" table uses an auto-incrementing primary key and can be backed by the following schema:

```elixir
defmodule Library.Lending do
  use Ecto.Schema

  schema "lendings" do
    belongs_to :book, MyApp.Book       # defines book_id
    belongs_to :visitor, MyApp.Visitor # defines visitor_id
  end
end
```

Now consider we want to retrieve the name of every book alongside the name of the last person the library has lent it to. To do so, we need to find the last lending ID of every book, and then join on the book and visitor tables. With subqueries, that's straight-forward:

```elixir
last_lendings =
  from l in MyApp.Lending,
    group_by: l.book_id,
    select: %{
      book_id: l.book_id,
      last_lending_id: max(l.id)
    }

from l in Lending,
  join: last in subquery(last_lendings),
  on: last.last_lending_id == l.id,
  join: b in assoc(l, :book),
  join: v in assoc(l, :visitor),
  select: {b.name, v.name}
```

---

## File: guides/howtos/Constraints and Upserts.md

# Constraints and Upserts

In this guide we will learn how to use constraints and upserts. To showcase those features, we will work on a practical scenario: which is by studying a many to many relationship between posts and tags.

## put_assoc vs cast_assoc

Imagine we are building an application that has blog posts and such posts may have many tags. Not only that, a given tag may also belong to many posts. This is a classic scenario where we would use [`many_to_many`](`Ecto.Schema.many_to_many/3`) associations. Our migrations would look like:

```elixir
create table(:posts) do
  add :title, :string
  add :body, :text
  timestamps()
end

create table(:tags) do
  add :name, :string
  timestamps()
end

create unique_index(:tags, [:name])

create table(:posts_tags, primary_key: false) do
  add :post_id, references(:posts)
  add :tag_id, references(:tags)
end
```

Note we added a unique index to the tag name because we don't want to have duplicated tags in our database. It is important to add an index at the database level instead of using a validation since there is always a chance two tags with the same name would be validated and inserted simultaneously, passing the validation and leading to duplicated entries.

Now let's also imagine we want the user to input such tags as a list of words split by comma, such as: "elixir, erlang, ecto". Once this data is received in the server, we will break it apart into multiple tags and associate them to the post, creating any tag that does not yet exist in the database.

While the constraints above sound reasonable, that's exactly what put us in trouble with [`cast_assoc/3`](`Ecto.Changeset.cast_assoc/3`). The `cast_assoc/3` changeset function was designed to receive external parameters and compare them with the associated data in our structs. To do so correctly, Ecto requires tags to be sent as a list of maps. We can see an example of this in [Polymorphic associations with many to many](Polymorphic associations with many to many.md). However, here we expect tags to be sent in a string separated by comma.

Furthermore, `cast_assoc/3` relies on the primary key field for each tag sent in order to decide if it should be inserted, updated or deleted. Again, because the user is simply passing a string, we don't have the ID information at hand.

When we can't cope with `cast_assoc/3`, it is time to use [`put_assoc/4`](`Ecto.Changeset.put_assoc/4`). In `put_assoc/4`, we give Ecto structs or changesets instead of parameters, giving us the ability to manipulate the data as we want. Let's define the schema and the changeset function for a post which may receive tags as a string:

```elixir
defmodule MyApp.Post do
  use Ecto.Schema

  schema "posts" do
    field :title
    field :body

    many_to_many :tags, MyApp.Tag,
      join_through: "posts_tags",
      on_replace: :delete

    timestamps()
  end

  def changeset(struct, params \\ %{}) do
    struct
    |> Ecto.Changeset.cast(params, [:title, :body])
    |> Ecto.Changeset.put_assoc(:tags, parse_tags(params))
  end

  defp parse_tags(params)  do
    (params["tags"] || "")
    |> String.split(",")
    |> Enum.map(&String.trim/1)
    |> Enum.reject(& &1 == "")
    |> Enum.map(&get_or_insert_tag/1)
  end

  defp get_or_insert_tag(name) do
    Repo.get_by(MyApp.Tag, name: name) ||
      Repo.insert!(%Tag{name: name})
  end
end
```

In the changeset function above, we moved all the handling of tags to a separate function, called `parse_tags/1`, which checks for the parameter, breaks each tag apart via `String.split/2`, then removes any left over whitespace with `String.trim/1`, rejects any empty string and finally checks if the tag exists in the database or not, creating one in case none exists.

The `parse_tags/1` function is going to return a list of `MyApp.Tag` structs which are then passed to [`put_assoc/4`](`Ecto.Changeset.put_assoc/4`). By calling `put_assoc/4`, we are telling Ecto those should be the tags associated to the post from now on. In case a previous tag was associated to the post and not given in `put_assoc/4`, Ecto will invoke the behaviour defined in the `:on_replace` option, which we have set to `:delete`. The `:delete` behaviour will remove the association between the post and the removed tag from the database.

And that's all we need to use [`many_to_many`](`Ecto.Schema.many_to_many/3`) associations with `put_assoc/4`. `put_assoc/4` is very useful when we want to have more explicit control over our associations and it also works with [`has_many`](`Ecto.Schema.has_many/3`), [`belongs_to`](`Ecto.Schema.belongs_to/3`) and all others association types.

However, our code is not yet ready for production. Let's see why.

## Constraints and race conditions

Remember we added a unique index to the tag `:name` column when creating the tags table. We did so to protect us from having duplicate tags in the database.

By adding the unique index and then using [`Repo.get_by`](`c:Ecto.Repo.get_by/3`) with a [`Repo.insert!`](`c:Ecto.Repo.insert!/2`) to get or insert a tag, we introduced a potential error in our application. If two posts are submitted at the same time with a similar tag, there is a chance we will check if the tag exists at the same time, leading both submissions to believe there is no such tag in the database. When that happens, only one of the submissions will succeed while the other one will fail. That's a race condition: your code will error from time to time, only when certain conditions are met. And those conditions are time sensitive.

Luckily Ecto gives us a mechanism to handle constraint errors from the database.

## Checking for constraint errors

Since our `get_or_insert_tag(name)` function fails when a tag already exists in the database, we need to handle such scenarios accordingly. Let's rewrite it taking race conditions into account:

```elixir
defp get_or_insert_tag(name) do
  %Tag{}
  |> Ecto.Changeset.change(name: name)
  |> Ecto.Changeset.unique_constraint(:name)
  |> Repo.insert()
  |> case do
    {:ok, tag} -> tag
    {:error, _} -> Repo.get_by!(MyApp.Tag, name: name)
  end
end
```

Instead of inserting the tag directly, we now build a changeset, which allows us to use the `unique_constraint` annotation. Now if the [`Repo.insert`](`c:Ecto.Repo.insert/2`) operation fails because the unique index for `:name` is violated, Ecto won't raise, but return an `{:error, changeset}` tuple. Therefore, if `Repo.insert` succeeds, it is because the tag was saved, otherwise the tag already exists, which we then fetch with [`Repo.get_by!`](`c:Ecto.Repo.get_by!/3`).

While the mechanism above fixes the race condition, it is a quite expensive one: we need to perform two queries for every tag that already exists in the database: the (failed) insert and then the repository lookup. Given that's the most common scenario, we may want to rewrite it to the following:

```elixir
defp get_or_insert_tag(name) do
  Repo.get_by(MyApp.Tag, name: name) ||
    maybe_insert_tag(name)
end

defp maybe_insert_tag(name) do
  %Tag{}
  |> Ecto.Changeset.change(name: name)
  |> Ecto.Changeset.unique_constraint(:name)
  |> Repo.insert
  |> case do
    {:ok, tag} -> tag
    {:error, _} -> Repo.get_by!(MyApp.Tag, name: name)
  end
end
```

The above performs 1 query for every tag that already exists, 2 queries for every new tag and possibly 3 queries in the case of race conditions. While the above would perform slightly better on average, Ecto has a better option in stock.

## Upserts

Ecto supports the so-called "upsert" command which is an abbreviation for "update or insert". The idea is that we try to insert a record and in case it conflicts with an existing entry, for example due to a unique index, we can choose how we want the database to act by either raising an error (the default behaviour), ignoring the insert (no error) or by updating the conflicting database entries.

"upsert" in Ecto is done with the `:on_conflict` option. Let's rewrite `get_or_insert_tag(name)` once more but this time using the `:on_conflict` option. Remember that "upsert" is a new feature in PostgreSQL 9.5, so make sure you are up to date.

Your first try in using `:on_conflict` may be by setting it to `:nothing`, as below:

```elixir
defp get_or_insert_tag(name) do
  Repo.insert!(
    %MyApp.Tag{name: name},
    on_conflict: :nothing
  )
end
```

While the above won't raise an error in case of conflicts, it also won't update the struct given, so it will return a tag without ID. One solution is to force an update to happen in case of conflicts, even if the update is about setting the tag name to its current name. In such cases, PostgreSQL also requires the `:conflict_target` option to be given, which is the column (or a list of columns) we are expecting the conflict to happen:

```elixir
defp get_or_insert_tag(name) do
  Repo.insert!(
    %MyApp.Tag{name: name},
    on_conflict: [set: [name: name]],
    conflict_target: :name
  )
end
```

And that's it! We try to insert a tag with the given name and if such tag already exists, we tell Ecto to update its name to the current value, updating the tag and fetching its id. While the above is certainly a step up from all solutions so far, it still performs one query per tag. If 10 tags are sent, we will perform 10 queries. Can we further improve this?

## Upserts and insert_all

Ecto accepts the `:on_conflict` option not only in `c:Ecto.Repo.insert/2` but also in the `c:Ecto.Repo.insert_all/3` function. This means we can build one query that attempts to insert all missing tags and then another query that fetches all of them at once. Let's see how our `Post` schema will look like after those changes:

```elixir
defmodule MyApp.Post do
  use Ecto.Schema

  # We need to import Ecto.Query
  import Ecto.Query

  # Schema is the same
  schema "posts" do
    add :title
    add :body

    many_to_many :tags, MyApp.Tag,
      join_through: "posts_tags",
      on_replace: :delete

    timestamps()
  end

  # Changeset is the same
  def changeset(struct, params \\ %{}) do
    struct
    |> Ecto.Changeset.cast(params, [:title, :body])
    |> Ecto.Changeset.put_assoc(:tags, parse_tags(params))
  end

  # Parse tags has slightly changed
  defp parse_tags(params)  do
    (params["tags"] || "")
    |> String.split(",")
    |> Enum.map(&String.trim/1)
    |> Enum.reject(& &1 == "")
    |> insert_and_get_all()
  end

  defp insert_and_get_all([]) do
    []
  end
  defp insert_and_get_all(names) do
    timestamp =
      NaiveDateTime.utc_now()
      |> NaiveDateTime.truncate(:second)

    placeholders = %{timestamp: timestamp}

    maps =
      Enum.map(names, &%{
        name: &1,
        inserted_at: {:placeholder, :timestamp},
        updated_at: {:placeholder, :timestamp}
      })

    Repo.insert_all(
      MyApp.Tag,
      maps,
      placeholders: placeholders,
      on_conflict: :nothing
    )

    Repo.all(from t in MyApp.Tag, where: t.name in ^names)
  end
end
```

Instead of getting and inserting each tag individually, the code above works on all tags at once, first by building a list of maps which is given to [`Repo.insert_all`](`c:Ecto.Repo.insert_all/3`). Then we look up all tags with the given names. Regardless of how many tags are sent, we will perform only 2 queries - unless no tag is sent, in which we return an empty list back promptly. This solution is only possible thanks to the `:on_conflict` option, which guarantees `insert_all` won't fail in case a unique index is violated, such as from duplicate tag names. Remember, `insert_all` won't autogenerate values like timestamps. That's why we define a timestamp placeholder and reuse it across `inserted_at` and `updated_at` fields.

Finally, keep in mind that we haven't used transactions in any of the examples so far. That decision was deliberate as we relied on the fact that getting or inserting tags is an idempotent operation, i.e. we can repeat it many times for a given input and it will always give us the same result back. Therefore, even if we fail to introduce the post to the database due to a validation error, the user will be free to resubmit the form and we will just attempt to get or insert the same tags once again. The downside of this approach is that tags will be created even if creating the post fails, which means some tags may not have posts associated to them. In case that's not desired, the whole operation could be wrapped in a transaction or modeled with `Ecto.Multi`.

---

## File: guides/howtos/Data mapping and validation.md

# Data mapping and validation

We will take a look at the role schemas play when validating and casting data through changesets. As we will see, sometimes the best solution is not to completely avoid schemas, but break a large schema into smaller ones. Maybe one for reading data, another for writing. Maybe one for your database, another for your forms.

## Schemas are mappers

The `Ecto.Schema` moduledoc says:

> An Ecto schema is used to map *any* data source into an Elixir struct.

We put emphasis on *any* because it is a common misconception to think Ecto schemas map only to your database tables.

For instance, when you write a web application using Phoenix and you use Ecto to receive external changes and apply such changes to your database, we have this mapping:

    Database <-> Ecto schema <-> Forms / API

Although there is a single Ecto schema mapping to both your database and your API, in many situations it is better to break this mapping in two. Let's see some practical examples.

Imagine you are working with a client that wants the "Sign Up" form to contain the fields "First name", "Last name" along side "E-mail" and other information. You know there are a couple problems with this approach.

First of all, not everyone has a first and last name. Although your client is decided on presenting both fields, they are a UI concern, and you don't want the UI to dictate the shape of your data. Furthermore, you know it would be useful to break the "Sign Up" information across two tables, the "accounts" and "profiles" tables.

Given the requirements above, how would we implement the Sign Up feature in the backend?

One approach would be to have two schemas, Account and Profile, with virtual fields such as `first_name` and `last_name`, and [use associations along side nested forms](https://dashbit.co/blog/working-with-ecto-associations-and-embeds) to tie the schemas to your UI. One of such schemas would be:

```elixir
defmodule Profile do
  use Ecto.Schema

  schema "profiles" do
    field :name
    field :first_name, :string, virtual: true
    field :last_name, :string, virtual: true
    ...
  end
end
```

It is not hard to see how we are polluting our Profile schema with UI requirements by adding fields such `first_name` and `last_name`. If the Profile schema is used for both reading and writing data, it may end-up in an awkward place where it is not useful for any, as it contains fields that map just to one or the other operation.

One alternative solution is to break the "Database <-> Ecto schema <-> Forms / API" mapping in two parts. The first will cast and validate the external data with its own structure which you then transform and write to the database. For such, let's define a schema named `Registration` that will take care of casting and validating the form data exclusively, mapping directly to the UI fields:

```elixir
defmodule Registration do
  use Ecto.Schema

  embedded_schema do
    field :first_name
    field :last_name
    field :email
  end
end
```

We used `embedded_schema` because it is not our intent to persist it anywhere. With the schema in hand, we can use Ecto changesets and validations to process the data:

```elixir
fields = [:first_name, :last_name, :email]

changeset =
  %Registration{}
  |> Ecto.Changeset.cast(params["sign_up"], fields)
  |> validate_required(...)
  |> validate_length(...)
```

Now that the registration changes are mapped and validated, we can check if the resulting changeset is valid and act accordingly:

```elixir
if changeset.valid? do
  # Get the modified registration struct from changeset
  registration = Ecto.Changeset.apply_changes(changeset)
  account = Registration.to_account(registration)
  profile = Registration.to_profile(registration)

  MyApp.Repo.transact(fn ->
    MyApp.Repo.insert_all("accounts", [account])
    MyApp.Repo.insert_all("profiles", [profile])
  end)

  {:ok, registration}
else
  # Annotate the action so the UI shows errors
  changeset = %{changeset | action: :registration}
  {:error, changeset}
end
```

The `to_account/1` and `to_profile/1` functions in `Registration` would receive the registration struct and split the attributes apart accordingly:

```elixir
def to_account(registration) do
  Map.take(registration, [:email])
end

def to_profile(%{first_name: first, last_name: last}) do
  %{name: "#{first} #{last}"}
end
```

In the example above, by breaking apart the mapping between the database and Elixir and between Elixir and the UI, our code becomes clearer and our data structures simpler.

Note we have used `MyApp.Repo.insert_all/2` to add data to both "accounts" and "profiles" tables directly. We have chosen to bypass schemas altogether. However, there is nothing stopping you from also defining both `Account` and `Profile` schemas and changing `to_account/1` and `to_profile/1` to respectively return `%Account{}` and `%Profile{}` structs. Once structs are returned, they could be inserted through the usual `MyApp.Repo.insert/2` operation. One can also check for uniqueness or other constraints during insertion by wrapping the structs in a changeset.

## Schemaless changesets

Although we chose to define a `Registration` schema to use in the changeset, Ecto also allows developers to use changesets without schemas. We can dynamically define the data and their types. Let's rewrite the registration changeset above to bypass schemas:

```elixir
data = %{}
types = %{name: :string, email: :string}

# The data+types tuple is equivalent to %Registration{}
changeset =
  {data, types}
  |> Ecto.Changeset.cast(params["sign_up"], Map.keys(types))
  |> validate_required(...)
  |> validate_length(...)
```

You can use this technique to validate API endpoints, search forms, and other sources of data. The choice of using schemas depends mostly if you want to use the same mapping in different places or if you desire the compile-time guarantees Elixir structs gives you. Otherwise, you can bypass schemas altogether, be it when using changesets or interacting with the repository.

However, the most important lesson in this guide is not when to use or not to use schemas, but rather understand when a big problem can be broken into smaller problems that can be solved independently leading to an overall cleaner solution. The choice of using schemas or not above didn't affect the solution as much as the choice of breaking the registration problem apart.

---

## File: guides/howtos/Duration Types with Postgrex.md

# Duration Types with Postgrex

As of Ecto 3.12.0, Ecto supports a `:duration` type which maps to Elixir's `Duration` struct (available as of Elixir 1.17).

One natural use case for this is when using Postgres's `interval` type. Historically, Postgrex loads intervals from the database into a custom `Postgrex.Interval` struct. With the introduction of `Duration`, there is now the option to choose between the two. Please follow the steps below to enable mapping to `Duration`.

1. Define your migration

```elixir
create table("movies") do
  add :running_time, :interval
end
```

2. Define your schema

```elixir
defmodule Movie do
  use Ecto.Schema

  schema "movies" do
    field :running_time, :duration
  end
end
```

3. Define your custom Postgrex type module and specify intervals should decode to `Duration`

```elixir
# Inside lib/my_app/postgrex_types.ex

Postgrex.Types.define(MyApp.PostgrexTypes, [], interval_decode_type: Duration)
```

4.  Make Ecto aware of the Postgrex type module in your configuration

```elixir
config :my_app, MyApp.Repo, types: MyApp.PostgresTypes
```

---

## File: guides/howtos/Dynamic queries.md

# Dynamic queries

Ecto was designed from the ground up to have an expressive query API that leverages Elixir syntax to write queries that are pre-compiled for performance and safety. When building queries, we may use the keywords syntax

```elixir
import Ecto.Query

from p in Post,
  where: p.author == "José" and p.category == "Elixir",
  where: p.published_at > ^minimum_date,
  order_by: [desc: p.published_at]
```

or the pipe-based one

```elixir
import Ecto.Query

Post
|> where([p], p.author == "José" and p.category == "Elixir")
|> where([p], p.published_at > ^minimum_date)
|> order_by([p], desc: p.published_at)
```

Both APIs are also composable. For example, imagine you want to abstract the `published_at` filtering and sorting into a function, with the keyword syntax you could write:

```elixir
def most_recent_from(query, minimum_date) do
  from p in query,
    where: p.published_at > ^minimum_date,
    order_by: [desc: p.published_at]
end
```

and with the pipe syntax:

```elixir
def most_recent_from(query, minimum_date) do
  query
  |> where([p], p.published_at > ^minimum_date)
  |> order_by([p], desc: p.published_at)
end
```

The examples above show you can build and compose queries at a high-level: by composing each call to `where`, `order_by`, and so on. However, sometimes you want the contents of the `where` or the `order_by` themselves to be defined dynamically. For example, a web application that provides search functionality on top of existing posts. The user should be able to specify multiple criteria, such as the author name, the post category, publishing interval, etc.

Furthermore, while many developers prefer the pipe-based syntax, having to repeat the binding `p` made it quite verbose compared to the keyword one.

To solve those problems, Ecto also provides a data-structure centric API to build queries as well as a very powerful mechanism for dynamic queries. Let's take a look.

## Focusing on data structures

Ecto provides a simpler API for both keyword and pipe based queries by making data structures first-class. Let's see an example:

```elixir
from p in Post,
  where: [author: "José", category: "Elixir"],
  where: p.published_at > ^minimum_date,
  order_by: [desc: :published_at]
```

and

```elixir
Post
|> where(author: "José", category: "Elixir")
|> where([p], p.published_at > ^minimum_date)
|> order_by(desc: :published_at)
```

Notice how we were able to ditch the `p` selector in most expressions. All Ecto constructs accept data structures as input. Such data structures can also be specified dynamically, shown below:

```elixir
where = [author: "José", category: "Elixir"]
order_by = [desc: :published_at]
Post
|> where(^where)
|> where([p], p.published_at > ^minimum_date)
|> order_by(^order_by)
```

While using data-structures already brings a good amount of flexibility to Ecto queries, not all expressions can be converted to data structures. For example, `where` converts a key-value to a `key == value` comparison, and therefore order-based comparisons such as `p.published_at > ^minimum_date` need to be written as before.

## Dynamic fragments

For cases where we cannot rely on data structures but still desire to build queries dynamically, Ecto includes the `Ecto.Query.dynamic/2` macro.

The `dynamic` macro allows us to conditionally build query fragments and interpolate them in the main query. For example, imagine that in the example above you may optionally filter posts by a date of publication. You could of course write it like this:

```elixir
query =
  Post
  |> where(^where)
  |> order_by(^order_by)

query =
  if published_at = params["published_at"] do
    where(query, [p], p.published_at < ^published_at)
  else
    query
  end
```

But with dynamic fragments, you can also write it as:

```elixir
where = [author: "José", category: "Elixir"]
order_by = [desc: :published_at]

filter_published_at =
  if published_at = params["published_at"] do
    dynamic([p], p.published_at < ^published_at)
  else
    true
  end

Post
|> where(^where)
|> where(^filter_published_at)
|> order_by(^order_by)
```

The [`dynamic`](`Ecto.Query.dynamic/2`) macro allows us to build dynamic expressions that are later interpolated into the query. `dynamic` expressions can also be interpolated into dynamic expressions, allowing developers to build complex expressions dynamically without hassle.

By using dynamic fragments, we can decouple the processing of parameters from the query generation. Let's see a more complex example.

## Building dynamic queries

Let's go back to the original problem. We want to build a search functionality where the user can configure how to traverse all posts in many different ways. For example, the user may choose how to order the data, filter by author and category, as well as select posts published after a certain date.

To tackle this in Ecto, we can break our problem into a bunch of small functions, that build either data structures or dynamic fragments, and then we interpolate it into the query:

```elixir
def filter(params) do
  Post
  |> order_by(^filter_order_by(params["order_by"]))
  |> where(^filter_where(params))
end

def filter_order_by("published_at_desc"),
  do: [desc: dynamic([p], p.published_at)]

def filter_order_by("published_at"),
  do: [asc: dynamic([p], p.published_at)]

def filter_order_by(_),
  do: []

def filter_where(params) do
  Enum.reduce(params, dynamic(true), fn
    {"author", value}, dynamic ->
      dynamic([p], ^dynamic and p.author == ^value)

    {"category", value}, dynamic ->
      dynamic([p], ^dynamic and p.category == ^value)

    {"published_at", value}, dynamic ->
      dynamic([p], ^dynamic and p.published_at > ^value)

    {_, _}, dynamic ->
      # Not a where parameter
      dynamic
  end)
end
```

Because we were able to break our problem into smaller functions that receive regular data structures, we can use all the tools available in Elixir to work with data. For handling the `order_by` parameter, it may be best to simply pattern match on the `order_by` parameter. For building the `where` clause, we can use `reduce` to start with an empty dynamic (that always returns true) and refine it with new conditions as we traverse the parameters.

Testing also becomes simpler as we can test each function in isolation, even when using dynamic queries:

```elixir
test "filter published at based on the given date" do
  assert dynamic_match?(
           filter_where(%{}),
           "true"
         )

  assert dynamic_match?(
           filter_where(%{"published_at" => "2010-04-17"}),
           "true and q.published_at > ^\"2010-04-17\""
         )
end

defp dynamic_match?(dynamic, string) do
  inspect(dynamic) == "dynamic([q], #{string})"
end
```

In the example above, we created a small helper that allows us to assert on the dynamic contents by matching on the results of `inspect(dynamic)`.

## Dynamic and joins

Even query joins can be tackled dynamically. For example, let's do two modifications to the example above. Let's say we can also sort by author name ("author_name" and "author_name_desc") and at the same time let's say that authors are in a separate table, which means our authors filter in `filter_where` now need to go through the join table.

Our final solution would look like this:

```elixir
def filter(params) do
  Post
  # 1. Add named join binding
  |> join(:inner, [p], assoc(p, :authors), as: :authors)
  |> order_by(^filter_order_by(params["order_by"]))
  |> where(^filter_where(params))
end

# 2. Returned dynamic with join binding
def filter_order_by("published_at_desc"),
  do: [desc: dynamic([p], p.published_at)]

def filter_order_by("published_at"),
  do: dynamic([p], p.published_at)

def filter_order_by("author_name_desc"),
  do: [desc: dynamic([authors: a], a.name)]

def filter_order_by("author_name"),
  do: dynamic([authors: a], a.name)

def filter_order_by(_),
  do: []

# 3. Change the authors clause inside reduce
def filter_where(params) do
  Enum.reduce(params, dynamic(true), fn
    {"author", value}, dynamic ->
      dynamic([authors: a], ^dynamic and a.name == ^value)

    {"category", value}, dynamic ->
      dynamic([p], ^dynamic and p.category == ^value)

    {"published_at", value}, dynamic ->
      dynamic([p], ^dynamic and p.published_at > ^value)

    {_, _}, dynamic ->
      # Not a where parameter
      dynamic
  end)
end
```

Adding more filters in the future is simply a matter of adding more clauses to the `Enum.reduce/3` call in `filter_where`.