Ecto Documentation

You can write queries without using predefined schemas by using string names for tables. This is useful for selecting specific fields without duplication or generating reports that don't map directly to application schemas.

When selecting fields using a list, Ecto automatically converts the result to a map or a struct. You can also use Ecto.Query.type/2 to specify expected types for interpolated arguments, providing type casting guarantees similar to schemas.

import Ecto.Query

# Select specific fields from a table named "posts"
query = from "posts", select: [:title, :body]

# Complex reporting query without schemas
query = 
  from u in "users",
    join: a in "activities",
    on: a.user_id == u.id,
    where: a.start_at > type(^start_at, :naive_datetime),
    group_by: a.user_id,
    select: %{
      user_id: a.user_id,
      count: count(u.id)
    }

MyApp.Repo.all(query)

Instead of using third-party libraries, you can implement test factories by creating a module that defines functions to return structs with required database fields. You can use Elixir's System.unique_integer() to generate unique values for fields like emails or usernames to avoid constraint violations during testing.

defmodule MyApp.Factory do
  alias MyApp.Repo

  # Factories

  def build(:post) do
    %MyApp.Post{title: "hello world"}
  end

  def build(:comment) do
    %MyApp.Comment{body: "good post"}
  end

  def build(:post_with_comments) do
    %MyApp.Post{
      title: "hello with comments",
      comments: [
        build(:comment, body: "first"),
        build(:comment, body: "second")
      ]
    }
  end

  def build(:user) do
    %MyApp.User{
      email: "hello#{System.unique_integer()}",
      username: "hello#{System.unique_integer()}"
    }
  end

  # Convenience API

  def build(factory_name, attributes) do
    factory_name |> build() |> struct!(attributes)
  end

  def insert!(factory_name, attributes \ []) do
    factory_name |> build(attributes) |> Repo.insert!()
  end
end

When using a shared table approach for multi-tenancy, standard foreign keys only ensure a child belongs to a parent. They do not ensure that the child and parent belong to the same tenant. To prevent data corruption where a child record has a different org_id than its parent, use composite foreign keys.

Implementation Steps:

1. Unique Index: Create a unique index on the parent table covering both the primary key and the org_id (e.g., [:id, :org_id]).
2. Composite Reference: In the child table migration, use Ecto.Migration.references/2 with the with: option to link both the parent ID and the org_id.
3. PostgreSQL Strictness: For PostgreSQL, use match: :full to ensure none of the columns in the foreign key are nil.

This ensures that if a Comment belongs to a Post, the org_id on the Comment must match the org_id on the Post.

# 1. Create unique index on parent
create unique_index(:posts, [:id, :org_id])

# 2. Define composite foreign key on child
create table(:comments) do
  add :body, :string
  add :org_id, :integer, null: false

  add :post_id,
      references(:posts, with: [org_id: :org_id]),
      null: false

  timestamps()
end

# For PostgreSQL strict enforcement:
# references(:posts, with: [org_id: :org_id], match: :full)

When dealing with associations where you cannot provide a list of maps containing primary keys (for example, when parsing a comma-separated string into many-to-many tags), use Ecto.Changeset.put_assoc/4.

Unlike cast_assoc/3, which expects external parameters and relies on primary keys to determine if an association should be inserted, updated, or deleted, put_assoc/4 allows you to pass Ecto structs or changesets directly. This gives you explicit control over the data being associated.

To handle deletions of removed associations, ensure your schema defines an :on_replace option (e.g., on_replace: :delete) in the association definition.

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!(%MyApp.Tag{name: name})
  end
end

Ecto does not support the parent_id/parent_type pattern used in some other frameworks because it breaks database referential integrity. Instead, the recommended approach for performant and safe polymorphic associations is to use separate join tables for each association pair.

For example, to associate TodoItem with both TodoList and Project, create two join tables: todo_list_items and project_items. This preserves database foreign key constraints and allows for efficient indexed queries.

create table(:todo_lists)  do
  add :title
  timestamps()
end

create table(:projects)  do
  add :name
  timestamps()
end

create table(:todo_items)  do
  add :description
  timestamps()
end

create table(:todo_list_items) do
  add :todo_item_id, references(:todo_items)
  add :todo_list_id, references(:todo_lists)
  timestamps()
end

create table(:project_items) do
  add :todo_item_id, references(:todo_items)
  add :project_id, references(:projects)
  timestamps()
end

As of Ecto 3.12.0, you can map Postgres interval columns to Elixir's Duration struct (available in Elixir 1.17+) instead of the default Postgrex.Interval struct.

To enable this mapping, follow these four steps:

1. Migration: Create a table or column using the :interval type.
2. Schema: Define the field in your Ecto schema using the :duration type.
3. Postgrex Type Definition: Use Postgrex.Types.define/2 to create a custom type module that specifies interval_decode_type: Duration.
4. Configuration: Update your Repo configuration to use the newly defined Postgrex type module.

# 1. Migration
create table("movies") do
  add :running_time, :interval
end

# 2. Schema
defmodule Movie do
  use Ecto.Schema

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

# 3. Custom Postgrex type module
# Inside lib/my_app/postgrex_types.ex
Postgrex.Types.define(MyApp.PostgrexTypes, [], interval_decode_type: Duration)

# 4. Configuration
config :my_app, MyApp.Repo, types: MyApp.PostgresTypes