### Ecto Mix Commands for Database Setup

Source: https://github.com/elixir-ecto/ecto/blob/master/guides/howtos/Multi tenancy with query prefixes.md

Commands to create the database, run migrations, and start an IEx session.

```bash
mix ecto.create
mix ecto.migrate
iex -S mix
```

--------------------------------

### Install Project Dependencies

Source: https://github.com/elixir-ecto/ecto/blob/master/guides/introduction/Getting Started.md

Run this command after updating your `mix.exs` file to fetch and install the new dependencies.

```bash
mix deps.get
```

--------------------------------

### Start IEx Session

Source: https://github.com/elixir-ecto/ecto/blob/master/examples/friends/README.md

Start an interactive Elixir shell with the application loaded.

```sh
iex -S mix
```

--------------------------------

### Install Dependencies and Run Tests

Source: https://github.com/elixir-ecto/ecto/blob/master/examples/friends/README.md

Use these commands to fetch project dependencies and execute the test suite.

```sh
mix deps.get
mix test
```

--------------------------------

### Start Multiple Ecto Repositories in Supervision Tree

Source: https://github.com/elixir-ecto/ecto/blob/master/guides/howtos/Replicas and dynamic repositories.md

Lists the Ecto repositories to be started as part of the application's supervision tree.

```elixir
children = [
  MyApp.Repo,
  MyApp.Repo.Replica1,
  MyApp.Repo.Replica2,
  MyApp.Repo.Replica3,
  MyApp.Repo.Replica4
]
```

--------------------------------

### Database Setup Commands

Source: https://github.com/elixir-ecto/ecto/blob/master/guides/introduction/Getting Started.md

Run these commands in your terminal to drop, create, and migrate the database for your Ecto project.

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

--------------------------------

### Start Ecto Repo in Supervision Tree

Source: https://github.com/elixir-ecto/ecto/blob/master/guides/introduction/Getting Started.md

Ensure your Ecto repository process is started as part of your application's supervision tree by adding it to the `children` list in `lib/friends/application.ex`.

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

  ...
```

--------------------------------

### Start a Dynamic Ecto Repository

Source: https://github.com/elixir-ecto/ecto/blob/master/guides/howtos/Replicas and dynamic repositories.md

Use `start_link` with specific credentials to initiate a new, named dynamic repository. This is useful for connecting to different databases or for temporary connections.

```elixir
MyApp.Repo.start_link(
  name: :some_client,
  hostname: "client.example.com",
  username: "...",
  password: "...",
  pool_size: 1
)
```

--------------------------------

### Ecto Keyword Query Example

Source: https://github.com/elixir-ecto/ecto/blob/master/README.md

Example of constructing and executing a query using Ecto's keyword-based query syntax. Filters results based on conditions.

```elixir
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
end
```

--------------------------------

### Ecto Pipe Query Example

Source: https://github.com/elixir-ecto/ecto/blob/master/README.md

Example of constructing and executing a query using Ecto's pipe syntax for a more readable query chain. Allows chaining of query modifiers.

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

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

--------------------------------

### Composing Ecto Queries

Source: https://context7.com/elixir-ecto/ecto/llms.txt

Demonstrates how to compose Ecto queries by starting with a base query and extending it with additional conditions and selections. This promotes reusability and modularity in query construction.

```elixir
# Composing queries
base_query = from u in MyApp.User, where: u.active == true

# Extend later
final_query = from u in base_query,
                where: u.role == :admin,
                select: %{id: u.id, name: u.name}

MyApp.Repo.all(final_query)
```

--------------------------------

### Per-From/Join Prefix Specification

Source: https://github.com/elixir-ecto/ecto/blob/master/guides/howtos/Multi tenancy with query prefixes.md

Example of specifying prefixes individually for `from` and `join` clauses within a query. These prefixes take precedence.

```elixir
from p in Post, prefix: "foo",
  join: c in Comment, prefix: "bar"
```

--------------------------------

### Clone Ecto Repository and Get Dependencies

Source: https://github.com/elixir-ecto/ecto/blob/master/README.md

Clone the Ecto repository and fetch its dependencies using Git and Mix.

```bash
git clone https://github.com/elixir-ecto/ecto.git
cd ecto
mix deps.get
```

--------------------------------

### Ecto Queries with Joins

Source: https://context7.com/elixir-ecto/ecto/llms.txt

Shows how to perform joins between tables in Ecto queries using the `join` keyword. This example includes filtering, selecting specific fields, and grouping results.

```elixir
# Joins
from p in MyApp.Post,
  join:    c in MyApp.Comment, on: c.post_id == p.id,
  where:   c.approved == true,
  select:  {p.title, count(c.id)},
  group_by: p.title
```

--------------------------------

### Ecto.Repo.get/3, get!/3, get_by/3

Source: https://context7.com/elixir-ecto/ecto/llms.txt

These functions fetch single records from the database. `get/3` returns `nil` if not found, `get!/3` raises an error, and `get_by/3` fetches by arbitrary field conditions.

```APIDOC
## Ecto.Repo.get/3, get!/3, get_by/3

### Description
Fetches a single record by primary key or field conditions. `Repo.get/3` returns `nil` if not found. `get!/3` raises `Ecto.NoResultsError` instead. `get_by/3` fetches by arbitrary field conditions.

### Usage
```elixir
alias MyApp.{Repo, User}

# Fetch by primary key
user = Repo.get(User, 1)        #=> %User{} or nil
user = Repo.get!(User, 1)       #=> %User{} or raises

# Fetch by field conditions
user = Repo.get_by(User, email: "alice@example.com")           #=> %User{} or nil
user = Repo.get_by!(User, email: "alice@example.com")          #=> %User{} or raises

# With prefix (Postgres schema path)
Repo.get(User, 1, prefix: "tenant_acme")

# Reload a struct from the database (discards preloaded assocs)
user = Repo.get!(User, 1)
fresh_user = Repo.reload(user)         #=> %User{} or nil
fresh_user = Repo.reload!(user)        #=> %User{} or raises

# Reload a list; deleted records become nil, order is preserved
[%User{}, nil, %User{}] = Repo.reload([user1, deleted_user, user2])
```
```

--------------------------------

### Define Multiple Repository Instances

Source: https://github.com/elixir-ecto/ecto/blob/master/guides/howtos/Replicas and dynamic repositories.md

When defining a repository in your supervision tree, you can start multiple instances with different process names. This allows each instance to have its own connection pool.

```elixir
children = [
  MyApp.Repo,
  {MyApp.Repo, name: :another_instance_of_repo}
]
```

--------------------------------

### Configure Replica Repositories for Testing

Source: https://github.com/elixir-ecto/ecto/blob/master/guides/howtos/Replicas and dynamic repositories.md

In a test setup, you can configure replica repositories to use the main repository's connection pool by calling `put_dynamic_repo/1` for each replica. This ensures replica repositories direct their operations to the primary connection.

```elixir
@replicas [
  MyApp.Repo.Replica1,
  MyApp.Repo.Replica2,
  MyApp.Repo.Replica3,
  MyApp.Repo.Replica4
]

setup do
  for replica <- @replicas do
    replica.put_dynamic_repo(MyApp.Repo)
  end

  :ok
end
```

--------------------------------

### Manage Unnamed Dynamic Repositories and Cleanup

Source: https://github.com/elixir-ecto/ecto/blob/master/guides/howtos/Replicas and dynamic repositories.md

Start an unnamed dynamic repository by passing `name: nil`. Retrieve its PID, use `put_dynamic_repo/1` to set it for queries, and ensure proper cleanup by reverting the dynamic repo setting and stopping the repository process in a `try/after` block.

```elixir
default_dynamic_repo = MyApp.Repo.get_dynamic_repo()

{:ok, repo} =
  MyApp.Repo.start_link(
    name: nil,
    hostname: "client.example.com",
    username: "...",
    password: "...",
    pool_size: 1
  )

try do
  MyApp.Repo.put_dynamic_repo(repo)
  MyApp.Repo.all(Post)
after
  MyApp.Repo.put_dynamic_repo(default_dynamic_repo)
  Supervisor.stop(repo)
end
```

--------------------------------

### Fetch Single Records - Ecto.Repo.get/3, get!/3, get_by/3

Source: https://context7.com/elixir-ecto/ecto/llms.txt

Fetch records using `Repo.get/3` (returns nil on not found), `get!/3` (raises `Ecto.NoResultsError`), or `get_by/3` for arbitrary conditions. Supports schema prefixes and reloading structs.

```elixir
alias MyApp.{Repo, User}

# Fetch by primary key
user = Repo.get(User, 1)        #=> %User{} or nil
user = Repo.get!(User, 1)       #=> %User{} or raises
```

```elixir
alias MyApp.{Repo, User}

# Fetch by field conditions
user = Repo.get_by(User, email: "alice@example.com")           #=> %User{} or nil
user = Repo.get_by!(User, email: "alice@example.com")          #=> %User{} or raises
```

```elixir
alias MyApp.{Repo, User}

# With prefix (Postgres schema path)
Repo.get(User, 1, prefix: "tenant_acme")
```

```elixir
alias MyApp.{Repo, User}

# Reload a struct from the database (discards preloaded assocs)
user = Repo.get!(User, 1)
fresh_user = Repo.reload(user)         #=> %User{} or nil
fresh_user = Repo.reload!(user)        #=> %User{} or raises
```

```elixir
alias MyApp.{Repo, User}

# Reload a list; deleted records become nil, order is preserved
[%User{}, nil, %User{}] = Repo.reload([user1, deleted_user, user2])
```

--------------------------------

### Attempting Aggregate with Limit (Incorrect)

Source: https://github.com/elixir-ecto/ecto/blob/master/guides/howtos/Aggregates and subqueries.md

This example demonstrates an incorrect way to find the average of the top 10 posts. Applying `limit` directly to the aggregate query does not work as expected because aggregates return a single row.

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

--------------------------------

### Dynamic Query with Joins and Author Sorting

Source: https://github.com/elixir-ecto/ecto/blob/master/guides/howtos/Dynamic queries.md

This example extends dynamic query capabilities to include joins and sorting by related data. It demonstrates how to add named join bindings and reference them within dynamic expressions for ordering and filtering.

```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
```

--------------------------------

### Example of Successful Insertion Result

Source: https://github.com/elixir-ecto/ecto/blob/master/guides/introduction/Getting Started.md

This shows the structure of the tuple returned upon a successful insertion, including the `:ok` atom and the inserted record's struct.

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

--------------------------------

### Query a Dynamic Ecto Repository

Source: https://github.com/elixir-ecto/ecto/blob/master/guides/howtos/Replicas and dynamic repositories.md

Before querying a dynamically started repository, ensure Ecto knows which process to use by calling `put_dynamic_repo/1` with the repository's name or PID. Otherwise, it may default to the main repository process.

```elixir
MyApp.Repo.put_dynamic_repo(:some_client)
MyApp.Repo.all(Post)
```

--------------------------------

### Define Ecto Schema

Source: https://github.com/elixir-ecto/ecto/blob/master/guides/howtos/Multi tenancy with query prefixes.md

Define your Ecto schema module, mapping to a database table. This example shows a simple 'samples' table with a 'name' field and timestamps.

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

  schema "samples" do
    field :name
    timestamps
  end
end
```

--------------------------------

### Configure Ecto Repository Connection

Source: https://github.com/elixir-ecto/ecto/blob/master/guides/howtos/Multi tenancy with query prefixes.md

Configure the Ecto repository connection details, including database credentials, hostname, and connection pool size. Ensure these match your database setup.

```elixir
config :my_app, MyApp.Repo,
  username: "postgres",
  password: "postgres",
  database: "demo",
  hostname: "localhost",
  pool_size: 10
```

--------------------------------

### Set Default Dynamic Repository for Test Environment

Source: https://github.com/elixir-ecto/ecto/blob/master/guides/howtos/Replicas and dynamic repositories.md

Configure a repository to use a specific dynamic repository by default, especially for the test environment. This simplifies setup by automatically directing operations to the desired connection pool without explicit calls in tests.

```elixir
for repo <- @replicas do
  default_dynamic_repo = 
    if Mix.env() == :test do
      MyApp.Repo
    else
      repo
    end

  defmodule repo do
    use Ecto.Repo,
      otp_app: :my_app,
      adapter: Ecto.Adapters.Postgres,
      read_only: true,
      default_dynamic_repo: default_dynamic_repo
  end
end
```

--------------------------------

### Encapsulate Dynamic Repository Logic

Source: https://github.com/elixir-ecto/ecto/blob/master/guides/howtos/Replicas and dynamic repositories.md

Define a helper function within your Ecto repository module to abstract the process of starting a dynamic repository, executing a callback with it, and performing cleanup. This promotes cleaner code and easier reuse.

```elixir
defmodule MyApp.Repo do
  use Ecto.Repo, ...

  def with_dynamic_repo(credentials, callback) do
    default_dynamic_repo = get_dynamic_repo()
    start_opts = [name: nil, pool_size: 1] ++ credentials
    {:ok, repo} = MyApp.Repo.start_link(start_opts)

    try do
      MyApp.Repo.put_dynamic_repo(repo)
      callback.()
    after
      MyApp.Repo.put_dynamic_repo(default_dynamic_repo)
      Supervisor.stop(repo)
    end
  end
end
```

--------------------------------

### Example Data Structure for Form Submission

Source: https://github.com/elixir-ecto/ecto/blob/master/guides/howtos/Polymorphic associations with many to many.md

Example of the expected data structure when submitting forms for a many_to_many association with an intermediate schema.

```elixir
%{
  "todo_list" => %{
    "title" => "shipping list",
    "todo_items" => %{
      0 => %{"description" => "bread"},
      1 => %{"description" => "eggs"},
    }
  }
}
```

--------------------------------

### Seed the Database

Source: https://github.com/elixir-ecto/ecto/blob/master/examples/friends/README.md

Run this command to populate the database with initial data.

```sh
mix run priv/repo/seeds.exs
```

--------------------------------

### Initial Query on Default Schema

Source: https://github.com/elixir-ecto/ecto/blob/master/guides/howtos/Multi tenancy with query prefixes.md

An IEx session demonstrating a query against the 'samples' table on the default 'public' schema.

```elixir
iex(1)> MyApp.Repo.all MyApp.Sample
[]
```

--------------------------------

### Create Ecto Database

Source: https://github.com/elixir-ecto/ecto/blob/master/guides/introduction/Getting Started.md

Run this command in your project to create the database configured in `config/config.exs`. Ensure your configuration is correct to avoid authentication errors.

```bash
mix ecto.create
```

--------------------------------

### Basic Ecto Operations with Default Prefix

Source: https://github.com/elixir-ecto/ecto/blob/master/guides/howtos/Multi tenancy with query prefixes.md

Demonstrates basic Ecto operations like `all` and `insert` which use the default connection prefix configured via `:after_connect`.

```iex
alias MyApp.Sample
MyApp.Repo.all(Sample)
MyApp.Repo.insert(%Sample{name: "mary"})
MyApp.Repo.all(Sample)
```

--------------------------------

### Migrating with Per-Prefix Command Line Option

Source: https://github.com/elixir-ecto/ecto/blob/master/guides/howtos/Multi tenancy with query prefixes.md

Shows how to run migrations on specific prefixes by invoking `mix ecto.migrate` multiple times with the `--prefix` option.

```bash
mix ecto.migrate --prefix "prefix_1"
mix ecto.migrate --prefix "prefix_2"
mix ecto.migrate --prefix "prefix_3"
...
mix ecto.migrate --prefix "prefix_128"
```

--------------------------------

### Configure Project to Compile Factory

Source: https://github.com/elixir-ecto/ecto/blob/master/guides/howtos/Test factories.md

Ensure your test support files are compiled by adding them to `elixirc_paths` in your `mix.exs` file.

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

defp elixirc_paths(:test), do: ["lib", "test/support"]
defp elixirc_paths(_), do: ["lib"]
```

--------------------------------

### Register Ecto Repositories with Application

Source: https://github.com/elixir-ecto/ecto/blob/master/guides/introduction/Getting Started.md

Add this configuration to `config/config.exs` to inform your application about the available Ecto repositories, enabling commands like `mix ecto.create`.

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

--------------------------------

### Define a Profile Schema with Virtual Fields

Source: https://github.com/elixir-ecto/ecto/blob/master/guides/howtos/Data mapping and validation.md

Example of an Ecto schema that includes virtual fields for UI-specific data, which are not persisted to the database.

```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
```

--------------------------------

### Basic Schema Migrations for Multi-tenancy

Source: https://github.com/elixir-ecto/ecto/blob/master/guides/howtos/Multi tenancy with foreign keys.md

Sets up database tables for organizations, posts, and comments with basic foreign key constraints. The organization table uses a custom primary key.

```elixir
create table(:orgs, primary_key: false) do
  add :org_id, :bigserial, primary_key: true
  add :name, :string
  timestamps()
end

create table(:posts) do
  add :title, :string

  add :org_id,
      references(:orgs, column: :org_id),
      null: false

  timestamps()
end

create table(:comments) do
  add :body, :string
  add :org_id, references(:orgs), null: false
  add :post_id, references(:posts), null: false
  timestamps()
end
```

--------------------------------

### Configure Database Connection in config.exs

Source: https://github.com/elixir-ecto/ecto/blob/master/guides/introduction/Getting Started.md

Set up the connection details for your PostgreSQL database in the `config/config.exs` file. Adjust username, password, and hostname as needed.

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

--------------------------------

### Fetching a Record by ID

Source: https://github.com/elixir-ecto/ecto/blob/master/guides/introduction/Getting Started.md

Uses the `get` function to retrieve a specific record from the 'people' table based on its primary key (ID).

```elixir
Friends.Person |> Friends.Repo.get(1)
```

--------------------------------

### Create Sample Table Migration

Source: https://github.com/elixir-ecto/ecto/blob/master/guides/howtos/Multi tenancy with query prefixes.md

Defines a basic Ecto migration to create a 'samples' table.

```elixir
defmodule MyApp.Repo.Migrations.CreateSample do
  use Ecto.Migration

  def change do
    create table(:samples) do
      add :name, :string
      timestamps()
    end
  end
end
```

--------------------------------

### Conditional Filtering with Dynamic Fragments

Source: https://github.com/elixir-ecto/ecto/blob/master/guides/howtos/Dynamic queries.md

Conditionally apply filters to an Ecto query using an if statement. This example shows a basic conditional where clause.

```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
```

--------------------------------

### Putting Associations in Ecto Changesets

Source: https://context7.com/elixir-ecto/ecto/llms.txt

Use `put_assoc` when you have already resolved structs or changesets for associations. This example shows how to handle tags provided as a comma-separated string.

```elixir
def changeset_with_tags(post, params) do
  tags = 
    (params["tags"] || "")
    |> String.split(",")
    |> Enum.map(&String.trim/1)
    |> Enum.reject(&(&1 == ""))
    |> Enum.map(fn name ->
      Repo.get_by(MyApp.Tag, name: name) || Repo.insert!(%MyApp.Tag{name: name})
    end)

  post
  |> cast(params, [:title])
  |> put_assoc(:tags, tags)
end
```

--------------------------------

### Migrating Across Multiple Prefixes in a Single Migration

Source: https://github.com/elixir-ecto/ecto/blob/master/guides/howtos/Multi tenancy with query prefixes.md

Demonstrates how to define a migration that iterates through multiple prefixes and applies schema changes to each using `create table` with a `prefix` option.

```elixir
defmodule MyApp.Repo.Migrations.CreateSample do
  use Ecto.Migration

  def change do
    for i <- 1..128 do
      prefix = "prefix_#{i}"
      create table(:samples, prefix: prefix) do
        add :name, :string
        timestamps()
      end

      flush()
    end
  end
end
```

--------------------------------

### Define Schema with Explicit Prefix

Source: https://github.com/elixir-ecto/ecto/blob/master/guides/howtos/Multi tenancy with query prefixes.md

Defines an Ecto schema 'MyApp.Mapping' that explicitly targets the 'main' schema prefix. This is useful for shared tables in a multi-tenant setup.

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

  @schema_prefix "main"
  schema "mappings" do
    field :client_id, :integer
    field :db_prefix
    timestamps
  end
end
```

--------------------------------

### Run Ecto Integration Tests with Ecto_sql

Source: https://github.com/elixir-ecto/ecto/blob/master/README.md

Run integration tests for Ecto by cloning ecto_sql in a sibling directory and setting the ECTO_PATH environment variable.

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

--------------------------------

### Define Organization Schema with Custom Primary Key

Source: https://github.com/elixir-ecto/ecto/blob/master/guides/howtos/Multi tenancy with foreign keys.md

Defines an Organization schema where the primary key is named `org_id` instead of the default `id`. This is useful for multi-tenant setups.

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

  @primary_key {:org_id, :id, autogenerate: true}
  schema "orgs" do
    field :name
    timestamps()
  end
end
```

--------------------------------

### Composable Transactions with Ecto.Multi

Source: https://context7.com/elixir-ecto/ecto/llms.txt

Defines a series of named repository operations to be run atomically. All changesets are validated before the transaction starts. On failure, returns {:error, failed_op, failed_value, changes_so_far}.

```elixir
alias Ecto.Multi
alias MyApp.{Repo, Account, Log, Session}

def reset_password(account, params) do
  Multi.new()
  |> Multi.update(:account, Account.password_reset_changeset(account, params))
  |> Multi.insert(:log,     Log.password_reset_changeset(account, params))
  |> Multi.delete_all(:sessions, Ecto.assoc(account, :sessions))
  |> Multi.run(:notify, fn _repo, %{account: acct} ->
    case send_email(acct) do
      :ok    -> {:ok, :sent}
      :error -> {:error, :email_failed}
    end
  end)
end

case Repo.transact(reset_password(account, params)) do
  {:ok, %{account: account, log: log}} ->
    IO.puts("Password reset for #{account.email}")

  {:error, :notify, :email_failed, _changes} ->
    IO.puts("Reset done but email failed")

  {:error, failed_op, failed_value, _changes} ->
    IO.inspect({failed_op, failed_value})
end
```

--------------------------------

### Querying with Preloaded Relationships

Source: https://github.com/elixir-ecto/ecto/blob/master/guides/howtos/Self-referencing many to many.md

Demonstrates how to query for `Person` records and preload both `:relationships` and `:reverse_relationships` to retrieve associated data efficiently.

```elixir
iex> preloads = [:relationships, :reverse_relationships]
iex> people = Repo.all from p in Person, preload: preloads
[
  MyApp.Accounts.Person<
    ...
    relationships: [
      MyApp.Accounts.Person<
        id: ..., 
        ...
      >
    ]
  >,
  MyApp.Accounts.Person<
    ...
    reverse_relationships: [
      MyApp.Accounts.Person<
        id: ..., 
        ...
      >
    ]
  >
]
```

--------------------------------

### Using Ecto.Query.dynamic/2 for Conditional Filtering

Source: https://github.com/elixir-ecto/ecto/blob/master/guides/howtos/Dynamic queries.md

Use the `dynamic/2` macro to conditionally build query fragments, allowing for cleaner integration of dynamic filters. This example filters by published_at if provided.

```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)
```

--------------------------------

### Define Changeset for Embedded Schema

Source: https://github.com/elixir-ecto/ecto/blob/master/guides/howtos/Embedded Schemas.md

Define a changeset function for each embedded schema module to enforce validations. This example shows a `UserProfile` changeset requiring `online` and `visibility` fields.

```elixir
defmodule UserProfile do
  # ...

  def changeset(%UserProfile{} = profile, attrs \\ %{}) do
    profile
    |> cast(attrs, [:online, :dark_mode, :visibility])
    |> validate_required([:online, :visibility])
  end
end

profile = %UserProfile{}
UserProfile.changeset(profile, %{online: true, visibility: :public})
```

--------------------------------

### Implement Ecto.Repo.prepare_query/3 for Global Filters

Source: https://context7.com/elixir-ecto/ecto/llms.txt

Implement `prepare_query/3` in your Ecto Repo to inject global filters like soft-delete or tenant scoping. This callback is invoked before every query-based operation. Ensure your Repo definition includes `use Ecto.Repo` and the `@impl true` annotation for the callback.

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

  import Ecto.Query

  @impl true
  def prepare_query(_operation, query, opts) do
    cond do
      opts[:skip_soft_delete] ->
        {query, opts}

      opts[:admin] ->
        {query, opts}

      true ->
        # Automatically exclude soft-deleted records from all queries
        query = from(x in query, where: is_nil(x.deleted_at))
        {query, opts}
    end
  end
end

# Regular query — deleted records are excluded automatically
MyApp.Repo.all(MyApp.Post)

# Admin query — all records including soft-deleted
MyApp.Repo.all(MyApp.Post, admin: true)

# Skip the filter explicitly
MyApp.Repo.all(MyApp.Post, skip_soft_delete: true)
```

--------------------------------

### Add Ecto SQL and Database Adapter Dependencies

Source: https://github.com/elixir-ecto/ecto/blob/master/README.md

When using Ecto with SQL databases, include `ecto_sql` and the specific database adapter (e.g., `postgrex`) in your `mix.exs`.

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

--------------------------------

### Inserting Test Data in IEx

Source: https://github.com/elixir-ecto/ecto/blob/master/guides/introduction/Getting Started.md

Use this Elixir code within an `iex -S mix` session to insert multiple records into the 'people' table. Note that changesets were not used for validation in this example.

```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)
```

--------------------------------

### Configure Ecto Formatter Rules

Source: https://github.com/elixir-ecto/ecto/blob/master/guides/introduction/Getting Started.md

Add Ecto's formatting rules to your `.formatter.exs` file to ensure consistent code style.

```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}"]
]
```

--------------------------------

### Configure Mix Project for Test Support Path

Source: https://github.com/elixir-ecto/ecto/blob/master/guides/testing/Testing with Ecto.md

Update the mix.exs configuration to include the 'test/support' directory for compilation in the test environment.

```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"]
```

--------------------------------

### Extract Embedded Schema into Explicit Module

Source: https://github.com/elixir-ecto/ecto/blob/master/guides/howtos/Embedded Schemas.md

This example demonstrates extracting an embedded schema into its own explicit module (`UserProfile`) using `embedded_schema/1`. This allows for reusable embedded schemas that can be embedded by multiple parent schemas.

```elixir
defmodule User do
  use Ecto.Schema

  schema "users" do
    field :full_name, :string
    field :email, :string
    field :avatar_url, :string
    field :confirmed_at, :naive_datetime

    embeds_one :profile, UserProfile

    timestamps()
  end
end

defmodule UserProfile do
  use Ecto.Schema

  embedded_schema do
    field :online, :boolean
    field :dark_mode, :boolean
    field :visibility, Ecto.Enum, values: [:public, :private, :friends_only]
  end
end
```

--------------------------------

### Define User Schema with Inline Embedded Profile

Source: https://github.com/elixir-ecto/ecto/blob/master/guides/howtos/Embedded Schemas.md

This example shows how to define a `User` schema with an inline embedded `Profile` schema using `embeds_one/3`. The embedded schema is generated as a module in the parent scope (`User.Profile`).

```elixir
defmodule User do
  use Ecto.Schema

  schema "users" do
    field :full_name, :string
    field :email, :string
    field :avatar_url, :string
    field :confirmed_at, :naive_datetime

    embeds_one :profile, Profile do
      field :online, :boolean
      field :dark_mode, :boolean
      field :visibility, Ecto.Enum, values: [:public, :private, :friends_only]
    end

    timestamps()
  end
end
```

--------------------------------

### Import Test Environment Configuration

Source: https://github.com/elixir-ecto/ecto/blob/master/guides/testing/Testing with Ecto.md

Ensure the configuration for the test environment is imported at the bottom of the main configuration file.

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

--------------------------------

### Optimized Tag Insertion with Check First

Source: https://github.com/elixir-ecto/ecto/blob/master/guides/howtos/Constraints and Upserts.md

An optimized approach for getting or inserting tags that first checks for an existing tag. This reduces queries for existing tags but may perform more queries in race condition scenarios.

```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
```

--------------------------------

### Join with Subquery Using parent_as

Source: https://github.com/elixir-ecto/ecto/blob/master/guides/howtos/Aggregates and subqueries.md

Demonstrates joining a main query with a subquery that references the parent query using `parent_as/1`. This is useful when the subquery needs to filter or relate data based on the parent query's context, such as matching comments to specific posts.

```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)
```

--------------------------------

### Run Ecto Tests

Source: https://github.com/elixir-ecto/ecto/blob/master/README.md

Execute the tests for the Ecto project using the Mix test command.

```bash
mix test
```

--------------------------------

### Configure Ecto Repo with Custom Search Path

Source: https://github.com/elixir-ecto/ecto/blob/master/guides/howtos/Multi tenancy with query prefixes.md

Sets the database configuration for MyApp.Repo to use a custom search path, prioritizing 'connection_prefix' over 'public'. This is crucial for multi-tenancy.

```elixir
config :my_app, MyApp.Repo,
  username: "postgres",
  password: "postgres",
  database: "demo_dev",
  hostname: "localhost",
  pool_size: 10,
  parameters: [search_path: "connection_prefix,public"]
```

--------------------------------

### Stream Large Result Sets with Ecto.Repo.stream/2

Source: https://context7.com/elixir-ecto/ecto/llms.txt

Use `Repo.stream/2` to get a lazy `Stream` that fetches rows in batches, ideal for processing large datasets without high memory usage. This must be executed within a transaction.

```elixir
import Ecto.Query

query = from p in MyApp.Post, select: p.title

MyApp.Repo.transact(fn ->
  MyApp.Repo.stream(query, max_rows: 100)
  |> Stream.each(&IO.puts/1)
  |> Stream.run()
end)

# Aggregate a large table without loading all rows
MyApp.Repo.transact(fn ->
  total =
    MyApp.Repo.stream(MyApp.Post)
    |> Enum.reduce(0, fn post, acc -> acc + post.visits end)

  {:ok, total}
end)
```

--------------------------------

### Configure Development/Test Database Connections

Source: https://github.com/elixir-ecto/ecto/blob/master/guides/howtos/Replicas and dynamic repositories.md

Configures multiple Ecto repositories, including primary and replicas, to use the same database credentials for development and testing environments.

```elixir
replicas = [
  MyApp.Repo,
  MyApp.Repo.Replica1,
  MyApp.Repo.Replica2,
  MyApp.Repo.Replica3,
  MyApp.Repo.Replica4
]

for repo <- replicas do
  config :my_app, repo,
    username: "postgres",
    password: "postgres",
    database: "my_app_prod",
    hostname: "localhost",
    pool_size: 10
end
```

--------------------------------

### Test Ecto Write and Read Across Connections

Source: https://github.com/elixir-ecto/ecto/blob/master/guides/howtos/Replicas and dynamic repositories.md

This test demonstrates a common issue where a write to the primary repository is not immediately visible to a read from a replica repository due to transaction isolation. This test will fail in a primary+replica setup.

```elixir
user = Repo.insert!(%User{name: "jane doe"})
assert Repo.replica().get!(User, user.id)
```

--------------------------------

### Configure Ecto SQL Sandbox for Testing

Source: https://context7.com/elixir-ecto/ecto/llms.txt

Configure `Ecto.Adapters.SQL.Sandbox` in `config/test.exs` to wrap tests in transactions that roll back after each test. Use `Ecto.Adapters.SQL.Sandbox.mode/2` in `test/test_helper.exs` and `Ecto.Adapters.SQL.Sandbox.start_owner!/2` in a `RepoCase` to manage sandbox behavior.

```elixir
# config/test.exs
import Config

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

# test/test_helper.exs
Ecto.Adapters.SQL.Sandbox.mode(MyApp.Repo, :manual)

# test/support/repo_case.ex
defmodule MyApp.RepoCase do
  use ExUnit.CaseTemplate

  using do
    quote do
      alias MyApp.Repo
      import Ecto
      import Ecto.Query
    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

# In tests
defmodule MyApp.UserTest do
  use MyApp.RepoCase, async: true

  test "creates a user" do
    changeset = MyApp.User.registration_changeset(%{
      name: "Alice", email: "alice@example.com", age: 25
    })
    assert {:ok, user} = MyApp.Repo.insert(changeset)
    assert user.id != nil
    assert Repo.get!(MyApp.User, user.id).name == "Alice"
  end
end
```

--------------------------------

### Fetching Multiple Records with Repo.all/2 and Repo.all_by/3

Source: https://context7.com/elixir-ecto/ecto/llms.txt

Repo.all/2 executes a query and returns a list of results. Repo.all_by/3 is a shorthand for filtering by key-value conditions.

```APIDOC
## Repo.all/2 and all_by/3 

### Description
Fetches multiple records from the database based on a query or key-value conditions.

### Method
`Repo.all/2`, `Repo.all_by/3`

### Usage
```elixir
# Fetch all users
users = Repo.all(User)

# With a query
query = from u in User, where: u.age >= 18, order_by: [asc: u.name], limit: 50
users = Repo.all(query)

# Shorthand with all_by
active_users = Repo.all_by(User, role: :admin)

# Pipe-based syntax
User |> where([u], u.age > 18) |> order_by(asc: :name) |> limit(20) |> Repo.all()

# Schemaless query
Repo.all(from "users", where: [role: "admin"], select: [:id, :name])
```
```

--------------------------------

### Ecto Changeset for User Registration and Update

Source: https://context7.com/elixir-ecto/ecto/llms.txt

Use `Ecto.Changeset.cast/4` to cast external parameters into a changeset, followed by validation functions. Fields not in the permitted list are ignored. This example demonstrates required fields, format validation, number validation, and unique constraint mapping.

```elixir
defmodule MyApp.User do
  use Ecto.Schema
  import Ecto.Changeset

  schema "users" do
    field :name,  :string
    field :email, :string
    field :age,   :integer
  end

  def registration_changeset(user \\ %__MODULE__{}, params) do
    user
    |> cast(params, [:name, :email, :age])
    |> validate_required([:name, :email])
    |> validate_format(:email, ~r/^[^"]+@[^"]+\.[^"]+$/, message: "must be a valid email")
    |> validate_number(:age, greater_than_or_equal_to: 18, less_than: 130)
    |> unique_constraint(:email)
  end

  def update_changeset(user, params) do
    user
    |> cast(params, [:name, :age])
    |> validate_required([:name])
  end
end
```

```elixir
changeset = MyApp.User.registration_changeset(%{
  name: "Alice",
  email: "alice@example.com",
  age: 25
})

changeset.valid?   #=> true
changeset.changes  #=> %{name: "Alice", email: "alice@example.com", age: 25}
```

```elixir
bad = MyApp.User.registration_changeset(%{name: "", email: "not-an-email", age: 10})
bad.valid?   #=> false
bad.errors
#=> [
#     age:   {"must be greater than or equal to %{number}", [...]},
#     email: {"has invalid format", [...]},
#     name:  {"can't be blank", [...]}
#   ]
```

--------------------------------

### Aggregate with Subquery (Equivalent to Repo.aggregate)

Source: https://github.com/elixir-ecto/ecto/blob/master/guides/howtos/Aggregates and subqueries.md

This illustrates the subquery structure that `Repo.aggregate/4` creates internally when dealing with queries that include `limit`, `offset`, or `distinct`. It first selects the top 10 posts and then aggregates their visits.

```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)
```

--------------------------------

### Keyword Syntax for Dynamic Queries

Source: https://github.com/elixir-ecto/ecto/blob/master/guides/howtos/Dynamic queries.md

Use keyword syntax for building Ecto queries with multiple where clauses and ordering. Ensure Ecto.Query is imported.

```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]
```

--------------------------------

### Database Migration for TodoItem

Source: https://github.com/elixir-ecto/ecto/blob/master/guides/howtos/Polymorphic associations with many to many.md

Creates the 'todo_items' table with a 'description' field and timestamps. This migration is essential for the TodoItem schema.

```elixir
create table("todo_items")  do
  add :description
  timestamps()
end
```

--------------------------------

### PostgreSQL Full Match Foreign Key Option

Source: https://github.com/elixir-ecto/ecto/blob/master/guides/howtos/Multi tenancy with foreign keys.md

Demonstrates how to use the `match: :full` option with `Ecto.Migration.references/2` for PostgreSQL. This enforces that none of the columns in the composite foreign key can be `nil`.

```elixir
references(:posts, with: [org_id: :org_id], match: :full)
```

--------------------------------

### Run Ecto Migrations

Source: https://github.com/elixir-ecto/ecto/blob/master/guides/introduction/Getting Started.md

Applies pending migrations to create or alter database tables according to the migration files. Use `mix ecto.rollback` to undo the last migration.

```bash
mix ecto.migrate
```

--------------------------------

### Generate New Elixir Application with Supervision Tree

Source: https://github.com/elixir-ecto/ecto/blob/master/guides/introduction/Getting Started.md

Use this command to create a new Elixir application that includes a supervision tree, which is necessary for Ecto.

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

--------------------------------

### Configure Ecto Test Database with Sandbox

Source: https://github.com/elixir-ecto/ecto/blob/master/guides/testing/Testing with Ecto.md

Configure the database connection for the test environment, using the Ecto SQL Sandbox pool to wrap each test in a transaction.

```elixir
import Config

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

--------------------------------

### Execute Query on a Random Replica

Source: https://github.com/elixir-ecto/ecto/blob/master/guides/howtos/Replicas and dynamic repositories.md

Demonstrates how to execute a query against a randomly selected read replica using the `replica/0` function.

```elixir
MyApp.Repo.replica().all(query)
```

--------------------------------

### Configure Production Database Connections for Replicas

Source: https://github.com/elixir-ecto/ecto/blob/master/guides/howtos/Replicas and dynamic repositories.md

Configures Ecto repositories for production, specifying distinct hostnames for the primary and each read replica database.

```elixir
repos = %{
  MyApp.Repo => "prod-primary",
  MyApp.Repo.Replica1 => "prod-replica-1",
  MyApp.Repo.Replica2 => "prod-replica-2",
  MyApp.Repo.Replica3 => "prod-replica-3",
  MyApp.Repo.Replica4 => "prod-replica-4"
}

for {repo, hostname} <- repos do
  config :my_app, repo,
    username: "postgres",
    password: "postgres",
    database: "my_app_prod",
    hostname: hostname,
    pool_size: 10
end
```

--------------------------------

### Run Containerized Integration Tests with Earthly

Source: https://github.com/elixir-ecto/ecto/blob/master/README.md

Execute integration tests within a containerized environment using Earthly. This is useful for consistent testing across different environments.

```bash
earthly -P +all
```

--------------------------------

### Use Encapsulated Dynamic Repository Function

Source: https://github.com/elixir-ecto/ecto/blob/master/guides/howtos/Replicas and dynamic repositories.md

Call the `with_dynamic_repo/2` function to execute a block of code using a dynamically configured repository. Pass the connection credentials and a callback function containing the operations to perform.

```elixir
credentials = [
  hostname: "client.example.com",
  username: "...",
  password: "..."
]

MyApp.Repo.with_dynamic_repo(credentials, fn ->
  MyApp.Repo.all(Post)
end)
```

--------------------------------

### Fetch Multiple Records with Ecto.Repo.all/2

Source: https://context7.com/elixir-ecto/ecto/llms.txt

Use `Repo.all/2` to execute a query and retrieve a list of results. `Repo.all_by/3` is a convenient shorthand for filtering by key-value pairs. Supports schemaless queries.

```elixir
alias MyApp.{Repo, User}
import Ecto.Query

# Fetch all users
users = Repo.all(User)

# With a query
query = from u in User,
          where: u.age >= 18,
          order_by: [asc: u.name],
          limit: 50

users = Repo.all(query)

# Shorthand with all_by
active_users = Repo.all_by(User, role: :admin)
#=> equivalent to: Repo.all(from u in User, where: u.role == :admin)

# Pipe-based syntax
User
|> where([u], u.age > 18)
|> order_by(asc: :name)
|> limit(20)
|> Repo.all()

# Schemaless query — no schema module required
Repo.all(from "users", where: [role: "admin"], select: [:id, :name])
#=> [%{id: 1, name: "Alice"}, ...]
```

--------------------------------

### Copying Data to a Different Prefix

Source: https://github.com/elixir-ecto/ecto/blob/master/guides/howtos/Multi tenancy with query prefixes.md

Demonstrates how to change the prefix of a struct using `Ecto.put_meta/2` and then insert it into the new prefix.

```iex
new_sample = Ecto.put_meta(sample, prefix: "public")
MyApp.Repo.insert(new_sample)
[sample] = MyApp.Repo.all(Sample, prefix: "public")
Ecto.get_meta(sample, :prefix)
```

--------------------------------

### Configure Ecto Repository

Source: https://github.com/elixir-ecto/ecto/blob/master/README.md

Configure your application to use Ecto and specify the repository to be used. This includes database connection details.

```elixir
config :my_app, ecto_repos: [Sample.Repo]

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

--------------------------------

### Generate Ecto Repository

Source: https://github.com/elixir-ecto/ecto/blob/master/guides/introduction/Getting Started.md

Use this mix task to generate the necessary configuration and module for your Ecto repository.

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

--------------------------------

### Create Ecto Schema Struct

Source: https://github.com/elixir-ecto/ecto/blob/master/guides/introduction/Getting Started.md

Demonstrates creating a new struct for the `Friends.Person` schema. You can initialize it empty or with specific values.

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

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

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

--------------------------------

### Insert Data with Ecto.Repo.insert! and Handle Errors

Source: https://github.com/elixir-ecto/ecto/blob/master/guides/introduction/Getting Started.md

Demonstrates using `Friends.Repo.insert!/2` to insert data. If the changeset is invalid, an `Ecto.InvalidChangesetError` is raised, providing details about the errors, applied changes, parameters, and the changeset itself.

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