Intermediate
Intermediate Examples (31-60)
Coverage: 40-75% of Ecto functionality
Focus: Advanced queries, complex associations, transactions, Ecto.Multi, aggregations, joins, and migrations.
These examples assume you understand beginner concepts (basic CRUD, schemas, changesets). All examples are self-contained and runnable.
Example 31: Join Query with join/5
The join macro performs SQL joins between tables, allowing you to query across associations without preloading. Essential for filtering parent records by child attributes.
%% Color Palette: Blue #0173B2, Orange #DE8F05, Teal #029E73, Purple #CC78BC, Brown #CA9161
%% Join Query Flow
graph TD
A[User Query] -->|join :posts| B[Join Posts Table]
B -->|where: post.published| C[Filter Results]
C --> D[Return Users]
style A fill:#0173B2,color:#fff
style B fill:#DE8F05,color:#fff
style C fill:#CC78BC,color:#fff
style D fill:#029E73,color:#fff
defmodule User do
use Ecto.Schema # => Import schema DSL
schema "users" do # => Map to "users" table
# => Adds :id primary key automatically
field :name, :string # => String field: name
has_many :posts, Post # => One-to-many association with Post schema
# => Enables u.posts access and assoc(u, :posts) queries
timestamps() # => Adds inserted_at, updated_at
end
end
defmodule Post do
use Ecto.Schema # => Import schema DSL
schema "posts" do # => Map to "posts" table
# => Adds :id primary key automatically
field :title, :string # => String field: title
field :published, :boolean # => Boolean field: published (true/false)
belongs_to :user, User # => Many-to-one association with User
# => Adds user_id foreign key field
timestamps() # => Adds inserted_at, updated_at
end
end
import Ecto.Query # => Import query DSL (from, join, where, select macros)
# Insert test data
{:ok, user1} = Repo.insert(%User{name: "Alice"})
# => Calls Repo.insert/1
# => Create user1 (id: 1, name: "Alice")
{:ok, user2} = Repo.insert(%User{name: "Bob"})
# => Create user2 (id: 2, name: "Bob")
Repo.insert(%Post{title: "Published", published: true, user_id: user1.id})
# => Create published post for user1
Repo.insert(%Post{title: "Draft", published: false, user_id: user2.id})
# => Create draft post for user2
# Query users with published posts
query = from u in User,
join: p in assoc(u, :posts), # => INNER JOIN posts table on p.user_id = u.id
where: p.published == true, # => Filter: only published posts
distinct: true, # => Remove duplicate users (has_many can duplicate)
select: u # => Return User structs
users = Repo.all(query) # => Execute query, fetch users
# => users is [%User{id: 1, name: "Alice"}]
# => SQL: SELECT DISTINCT u.* FROM users u INNER JOIN posts p ON p.user_id = u.id WHERE p.published = TRUE
IO.inspect(length(users)) # => Output: 1 (only Alice has published posts)
IO.inspect(hd(users).name) # => Output: "Alice" (head of list)Key Takeaway: Use join queries when you need to filter parent records by child attributes, and always include distinct: true when joining has_many associations to avoid duplicate parent records.
Why It Matters: Inner joins fetch only records with matching associations in a single query, preventing N+1 problems and enabling database-level filtering on associated data. Production APIs use joins to filter users by post content, orders by product category, or permissions by role—queries impossible with preload alone. Understanding join vs preload is critical: joins filter parent records, preload loads associations for already-fetched parents.
Example 32: Left Join with left_join/5
left_join includes all records from the left table even when no matching records exist in the right table, useful for finding records without associations.
%% Color Palette: Blue #0173B2, Orange #DE8F05, Teal #029E73, Purple #CC78BC, Brown #CA9161
%% Left Join Behavior
graph TD
A[Users Table] --> B[Left Join]
C[Posts Table] --> B
B --> D{Post Exists?}
D -->|Yes| E[User + Post Data]
D -->|No| F[User + NULL Post]
E --> G[Result Set]
F --> G
style A fill:#0173B2,color:#fff
style C fill:#0173B2,color:#fff
style B fill:#DE8F05,color:#fff
style D fill:#CC78BC,color:#fff
style E fill:#029E73,color:#fff
style F fill:#CA9161,color:#fff
style G fill:#029E73,color:#fff
defmodule User do
# => Module starts
use Ecto.Schema
# => Imports Ecto
schema "users" do
# => Schema declaration
field :name, :string
# => Field defined
has_many :posts, Post
# => Operation executes
timestamps()
# => Operation executes
end
# => Block ends
end
# => Block ends
defmodule Post do
# => Module starts
use Ecto.Schema
# => Imports Ecto
schema "posts" do
# => Schema declaration
field :title, :string
# => Field defined
belongs_to :user, User
# => Operation executes
timestamps()
# => Operation executes
end
# => Block ends
end
# => Block ends
import Ecto.Query
# => Operation executes
# Insert test data
# => Operation executes
{:ok, user_with_posts} = Repo.insert(%User{name: "Charlie"})
# => Database operation
{:ok, user_no_posts} = Repo.insert(%User{name: "Diana"})
# => Database operation
Repo.insert(%Post{title: "Post 1", user_id: user_with_posts.id})
# => Database operation
# Find users without posts using left join
# => Operation executes
query = from u in User,
# => Value assigned
left_join: p in assoc(u, :posts), # => Left join (includes users without posts)
where: is_nil(p.id), # => Filter for users with no posts
select: u
# => Operation executes
users_without_posts = Repo.all(query) # => users_without_posts is [%User{name: "Diana"}]
# => SQL: SELECT u.* FROM users u
# => LEFT JOIN posts p ON p.user_id = u.id
# => WHERE p.id IS NULL
IO.inspect(length(users_without_posts))
# => Operation executes
# => Output: 1
IO.inspect(hd(users_without_posts).name)
# => Operation executes
# => Output: "Diana"Key Takeaway: left_join is essential for finding records without associations (orphaned parents), and you must filter on is_nil(child.id) to identify rows where no child exists.
Why It Matters: Left joins preserve parent records even when associations are missing, essential for queries like “all users and their posts (including users with zero posts)”. Production dashboards use left joins to show complete datasets (all products, even unsold ones) rather than silently hiding records without associations. The nil-filled columns for missing associations require careful handling in aggregations and sorting to avoid unexpected results.
Example 33: Group By with Aggregates
The group_by clause groups records by field values, enabling aggregate functions (count, sum, avg) per group. Essential for reporting and analytics.
%% Color Palette: Blue #0173B2, Orange #DE8F05, Teal #029E73, Purple #CC78BC, Brown #CA9161
%% Group By with Aggregates
graph TD
A[All Posts] --> B[group_by :user_id]
B --> C[Group 1: User 1 Posts]
B --> D[Group 2: User 2 Posts]
C --> E[count/1]
D --> E
E --> F[Aggregated Results]
style A fill:#0173B2,color:#fff
style B fill:#DE8F05,color:#fff
style C fill:#CC78BC,color:#fff
style D fill:#CC78BC,color:#fff
style E fill:#DE8F05,color:#fff
style F fill:#029E73,color:#fff
defmodule Post do
# => Module starts
use Ecto.Schema
# => Imports Ecto
schema "posts" do
# => Schema declaration
field :title, :string
# => Field defined
field :category, :string
# => Field defined
field :views, :integer
# => Field defined
timestamps()
# => Operation executes
end
# => Block ends
end
# => Block ends
import Ecto.Query
# => Operation executes
# Insert test data
# => Operation executes
Repo.insert(%Post{title: "Tech 1", category: "tech", views: 100})
# => Database operation
Repo.insert(%Post{title: "Tech 2", category: "tech", views: 200})
# => Database operation
Repo.insert(%Post{title: "Sports 1", category: "sports", views: 150})
# => Database operation
# Count posts per category
# => Operation executes
query = from p in Post,
# => Value assigned
group_by: p.category, # => Group by category field
select: {p.category, count(p.id)} # => Select category and count
results = Repo.all(query) # => results is [{"tech", 2}, {"sports", 1}]
# => SQL: SELECT category, COUNT(id)
# => FROM posts
# => GROUP BY category
# Sum views per category
# => Operation executes
sum_query = from p in Post,
# => Value assigned
group_by: p.category,
# => Operation executes
select: {p.category, sum(p.views)} # => Sum views per category
sums = Repo.all(sum_query) # => sums is [{"tech", 300}, {"sports", 150}]
# => SQL: SELECT category, SUM(views)
# => FROM posts GROUP BY category
IO.inspect(results) # => Output: [{"tech", 2}, {"sports", 1}]
IO.inspect(sums) # => Output: [{"tech", 300}, {"sports", 150}]Key Takeaway: All non-aggregated fields in select must appear in group_by, and aggregate functions (count, sum, avg, min, max) operate within each group.
Why It Matters: Calculating aggregates in-memory (group records, then sum) is inefficient for large datasets and prevents using database indexes. Production dashboards use group_by with SUM/AVG/COUNT to generate reports in the database, leveraging query optimization and reducing memory usage. This pattern is essential for analytics (sales by category), monitoring (errors by service), and reporting (user activity by date) where aggregation scale matters.
Example 34: Having Clause for Filtered Aggregates
The having clause filters groups after aggregation, unlike where which filters before grouping. Use having to filter on aggregate results.
defmodule User do
# => Module starts
use Ecto.Schema
# => Imports Ecto
schema "users" do
# => Schema declaration
field :name, :string
# => Field defined
field :country, :string
# => Field defined
field :age, :integer
# => Field defined
timestamps()
# => Operation executes
end
# => Block ends
end
# => Block ends
import Ecto.Query
# => Operation executes
# Insert test data
# => Operation executes
Repo.insert(%User{name: "Eve", country: "USA", age: 25})
# => Database operation
Repo.insert(%User{name: "Frank", country: "USA", age: 30})
# => Database operation
Repo.insert(%User{name: "Grace", country: "UK", age: 28})
# => Database operation
# Count users per country, only countries with 2+ users
# => Operation executes
query = from u in User,
# => Value assigned
group_by: u.country,
# => Operation executes
having: count(u.id) >= 2, # => Filter groups with count >= 2
select: {u.country, count(u.id)}
# => Operation executes
results = Repo.all(query) # => results is [{"USA", 2}]
# => SQL: SELECT country, COUNT(id)
# => FROM users
# => GROUP BY country
# => HAVING COUNT(id) >= 2
# Average age per country, only countries with avg >= 27
# => Value assigned
avg_query = from u in User,
# => Value assigned
group_by: u.country,
# => Operation executes
having: avg(u.age) >= 27, # => Filter by average age
select: {u.country, avg(u.age)}
# => Operation executes
avg_results = Repo.all(avg_query) # => avg_results is [{"USA", Decimal("27.5")}, {"UK", Decimal("28.0")}]
# => SQL: SELECT country, AVG(age)
# => FROM users GROUP BY country
# => HAVING AVG(age) >= 27
IO.inspect(results) # => Output: [{"USA", 2}]
IO.inspect(avg_results) # => Output: [{"USA", #Decimal<27.5>}, {"UK", #Decimal<28.0>}]Key Takeaway: Use having for filtering aggregated results and where for filtering individual rows before aggregation; having executes after GROUP BY while where executes before.
Why It Matters: Filtering aggregated results (users with >10 posts, categories with total sales >$1000) requires HAVING clauses that execute after GROUP BY. Production analytics use having to find outliers and apply thresholds without loading all groups into memory. This pattern enables efficient queries like “top customers by order count” and “inactive categories” where filtering happens on computed values, not raw data.
Example 35: Transactions with Repo.transaction/1
Repo.transaction/1 wraps multiple database operations in a transaction, ensuring all succeed or all roll back. Essential for maintaining data consistency.
%% Color Palette: Blue #0173B2, Orange #DE8F05, Teal #029E73, Purple #CC78BC, Brown #CA9161
%% Transaction Flow
graph TD
A[Start Transaction] --> B[Operation 1]
B --> C[Operation 2]
C --> D{All Succeeded?}
D -->|Yes| E[Commit]
D -->|No| F[Rollback]
style A fill:#0173B2,color:#fff
style B fill:#DE8F05,color:#fff
style C fill:#DE8F05,color:#fff
style D fill:#CC78BC,color:#fff
style E fill:#029E73,color:#fff
style F fill:#CA9161,color:#fff
defmodule User do
# => Module starts
use Ecto.Schema
# => Imports Ecto
schema "users" do
# => Schema declaration
field :name, :string
# => Field defined
field :balance, :decimal
# => Field defined
timestamps()
# => Operation executes
end
# => Block ends
end
# => Block ends
# Insert test users
# => Operation executes
{:ok, alice} = Repo.insert(%User{name: "Alice", balance: Decimal.new("100.00")})
# => Database operation
{:ok, bob} = Repo.insert(%User{name: "Bob", balance: Decimal.new("50.00")})
# => Database operation
# Transfer money in transaction
# => Operation executes
result = Repo.transaction(fn ->
# => Database operation
# Deduct from Alice
# => Operation executes
alice_changeset = Ecto.Changeset.change(alice, balance: Decimal.sub(alice.balance, Decimal.new("30.00")))
# => Value assigned
{:ok, alice_updated} = Repo.update(alice_changeset)
# => Database operation
# => alice_updated.balance is Decimal("70.00")
# => SQL: UPDATE users SET balance = 70.00 WHERE id = 1
# Add to Bob
# => Operation executes
bob_changeset = Ecto.Changeset.change(bob, balance: Decimal.add(bob.balance, Decimal.new("30.00")))
# => Value assigned
{:ok, bob_updated} = Repo.update(bob_changeset)
# => Database operation
# => bob_updated.balance is Decimal("80.00")
# => SQL: UPDATE users SET balance = 80.00 WHERE id = 2
{alice_updated, bob_updated} # => Transaction return value
end) # => SQL: BEGIN; ... COMMIT;
{:ok, {alice_final, bob_final}} = result
# => Value assigned
# => result is {:ok, {alice_updated, bob_updated}}
IO.inspect(alice_final.balance) # => Output: #Decimal<70.00>
IO.inspect(bob_final.balance) # => Output: #Decimal<80.00>Key Takeaway: Transactions ensure atomicity—if any operation fails, all changes roll back automatically; always return the final value from the transaction function to access updated data.
Why It Matters: Multi-step operations (create order, decrement inventory, charge payment) must complete atomically or roll back entirely to prevent partial failures and data corruption. Production systems wrap dependent operations in transactions to enforce all-or-nothing semantics, preventing scenarios like charged payments with no created order. Transactions are critical for financial operations, inventory management, and any workflow where partial completion is worse than total failure.
Example 36: Rolling Back Transactions with Repo.rollback/1
Repo.rollback/1 manually aborts a transaction, causing Repo.transaction/1 to return {:error, value}. Use for business logic failures that should cancel all operations.
defmodule User do
# => Module starts
use Ecto.Schema
# => Imports Ecto
schema "users" do
# => Schema declaration
field :name, :string
# => Field defined
field :balance, :decimal
# => Field defined
timestamps()
# => Operation executes
end
# => Block ends
end
# => Block ends
# Insert test user
# => Operation executes
{:ok, user} = Repo.insert(%User{name: "Charlie", balance: Decimal.new("50.00")})
# => Database operation
# Attempt withdrawal with insufficient funds
# => Operation executes
result = Repo.transaction(fn ->
# => Database operation
withdrawal = Decimal.new("100.00") # => Attempting to withdraw 100
if Decimal.lt?(user.balance, withdrawal) do
# => Operation executes
Repo.rollback(:insufficient_funds) # => Manually rollback transaction
# => SQL: ROLLBACK
else
# => Operation executes
changeset = Ecto.Changeset.change(user, balance: Decimal.sub(user.balance, withdrawal))
# => Value assigned
{:ok, updated} = Repo.update(changeset)
# => Database operation
updated
# => Operation executes
end
# => Block ends
end)
# => Block ends
{:error, reason} = result # => result is {:error, :insufficient_funds}
# => No changes committed to database
# Verify balance unchanged
# => Operation executes
unchanged = Repo.get(User, user.id) # => unchanged.balance is still Decimal("50.00")
IO.inspect(result) # => Output: {:error, :insufficient_funds}
IO.inspect(unchanged.balance) # => Output: #Decimal<50.00>Key Takeaway: Repo.rollback/1 accepts any value which becomes the error reason in the return tuple, making it ideal for returning specific error messages when business rules fail.
Why It Matters: Business rule violations detected mid-transaction (insufficient inventory, duplicate booking) require explicit rollbacks to abort without committing partial changes. Production validation logic uses rollback to enforce constraints across multiple tables that can’t be expressed as database constraints. Understanding rollback semantics prevents data corruption when complex business rules fail partway through multi-step operations.
Example 37: Ecto.Multi for Composable Transactions
Ecto.Multi builds composable transaction pipelines, allowing you to name each operation and reference previous results. Cleaner than nested transaction functions.
%% Color Palette: Blue #0173B2, Orange #DE8F05, Teal #029E73, Purple #CC78BC, Brown #CA9161
%% Ecto.Multi Pipeline
graph TD
A[Multi.new] --> B[insert :user]
B --> C[insert :post]
C --> D[update :stats]
D --> E[Repo.transaction]
style A fill:#0173B2,color:#fff
style B fill:#DE8F05,color:#fff
style C fill:#DE8F05,color:#fff
style D fill:#DE8F05,color:#fff
style E fill:#029E73,color:#fff
defmodule User do
# => Module starts
use Ecto.Schema
# => Imports Ecto
schema "users" do
# => Schema declaration
field :name, :string
# => Field defined
has_many :posts, Post
# => Operation executes
timestamps()
# => Operation executes
end
# => Block ends
end
# => Block ends
defmodule Post do
# => Module starts
use Ecto.Schema
# => Imports Ecto
schema "posts" do
# => Schema declaration
field :title, :string
# => Field defined
belongs_to :user, User
# => Operation executes
timestamps()
# => Operation executes
end
# => Block ends
end
# => Block ends
alias Ecto.Multi
# => Operation executes
# Build multi operation
# => Operation executes
multi =
# => Value assigned
Multi.new()
# => Operation executes
|> Multi.insert(:user, %User{name: "Diana"})
# => Operation executes
# => Named operation :user
|> Multi.insert(:post, fn %{user: user} ->
# => Operation executes
%Post{title: "First Post", user_id: user.id}
# => Operation executes
# => Reference :user result
end)
# => Block ends
|> Multi.run(:count_posts, fn _repo, %{user: user} ->
# => Operation executes
count = Repo.aggregate(Post, :count, :id)
# => Database operation
{:ok, count} # => Must return {:ok, value} or {:error, value}
end)
# => Block ends
# Execute multi
# => Operation executes
{:ok, results} = Repo.transaction(multi)
# => Database operation
# => results is %{user: %User{...}, post: %Post{...}, count_posts: 1}
# => SQL: BEGIN; INSERT users ...; INSERT posts ...; SELECT COUNT(*) ...; COMMIT;
IO.inspect(results.user.name) # => Output: "Diana"
IO.inspect(results.post.title) # => Output: "First Post"
IO.inspect(results.count_posts) # => Output: 1Key Takeaway: Multi operations execute in order and can reference previous results via the changes map, making complex transaction logic more maintainable than nested callbacks.
Why It Matters: Complex transactions with dependent operations (create user → create profile → send email) become deeply nested with plain Repo.transaction callbacks, obscuring the operation sequence. Ecto.Multi flattens this into a readable pipeline where each step has a name and can reference earlier results by key. Production systems use Multi for checkout flows, user registration, and data imports where failure at any step must roll back cleanly.
Example 38: Conditional Multi Operations
Multi operations can be conditional using Multi.run/3, allowing you to skip or modify operations based on previous results.
%% Color Palette: Blue #0173B2, Orange #DE8F05, Teal #029E73, Purple #CC78BC, Brown #CA9161
%% Conditional Multi Flow
graph TD
A[Multi.new] --> B[Operation 1]
B --> C{Check Result}
C -->|Condition Met| D[Operation A]
C -->|Condition Not Met| E[Operation B]
D --> F[Repo.transaction]
E --> F
F --> G{All Success?}
G -->|Yes| H[Commit]
G -->|No| I[Rollback All]
style A fill:#0173B2,color:#fff
style B fill:#DE8F05,color:#fff
style C fill:#CC78BC,color:#fff
style D fill:#029E73,color:#fff
style E fill:#029E73,color:#fff
style F fill:#DE8F05,color:#fff
style G fill:#CC78BC,color:#fff
style H fill:#029E73,color:#fff
style I fill:#CA9161,color:#fff
defmodule User do
# => Module starts
use Ecto.Schema
# => Imports Ecto
schema "users" do
# => Schema declaration
field :name, :string
# => Field defined
field :premium, :boolean
# => Field defined
timestamps()
# => Operation executes
end
# => Block ends
end
# => Block ends
defmodule Subscription do
# => Module starts
use Ecto.Schema
# => Imports Ecto
schema "subscriptions" do
# => Schema declaration
field :plan, :string
# => Field defined
belongs_to :user, User
# => Operation executes
timestamps()
# => Operation executes
end
# => Block ends
end
# => Block ends
alias Ecto.Multi
# => Operation executes
# Create user and conditionally add subscription
# => Operation executes
multi =
# => Value assigned
Multi.new()
# => Operation executes
|> Multi.insert(:user, %User{name: "Eve", premium: true})
# => Operation executes
|> Multi.run(:subscription, fn _repo, %{user: user} ->
# => Operation executes
if user.premium do # => Conditional logic
changeset = %Subscription{plan: "premium", user_id: user.id}
# => Value assigned
case Repo.insert(changeset) do
# => Database operation
{:ok, sub} -> {:ok, sub} # => Insert subscription
{:error, cs} -> {:error, cs}
# => Operation executes
end
# => Block ends
else
# => Operation executes
{:ok, nil} # => Skip subscription for non-premium
end
# => Block ends
end)
# => Block ends
{:ok, results} = Repo.transaction(multi)
# => Database operation
# => results.user.premium is true
# => results.subscription is %Subscription{plan: "premium"}
IO.inspect(results.subscription.plan) # => Output: "premium"Key Takeaway: Multi.run/3 enables conditional logic in transactions, and returning {:error, reason} from any operation rolls back the entire transaction.
Why It Matters: Transaction logic often requires branching (upgrade user → create subscription only if premium plan) that can’t be expressed as sequential inserts. Multi.run enables business logic decisions mid-transaction while maintaining atomicity. Production checkout flows use conditional Multi operations to handle payment methods, shipping options, and discount rules that vary based on earlier transaction results.
Example 39: Migration Basics - Creating Tables
Migrations define schema changes to the database. The create table macro creates a new table with specified columns and constraints.
defmodule Repo.Migrations.CreateUsers do
# => Module starts
use Ecto.Migration
# => Imports Ecto
def change do
# => Operation executes
create table(:users) do # => Create users table
add :name, :string, null: false # => String column, not null
add :email, :string, null: false # => Email column, not null
add :age, :integer # => Integer column, nullable
add :active, :boolean, default: true
# => Operation executes
# => Boolean with default value
timestamps() # => Add inserted_at, updated_at (type: naive_datetime_usec)
end
# => Block ends
create unique_index(:users, [:email])
# => Operation executes
# => Unique constraint on email
# => Prevents duplicate emails
end
# => Block ends
end
# => Block ends
# Run migration: mix ecto.migrate
# => Operation executes
# => SQL: CREATE TABLE users (
# id BIGSERIAL PRIMARY KEY,
# => Operation executes
# name VARCHAR(255) NOT NULL,
# => Operation executes
# email VARCHAR(255) NOT NULL,
# => Operation executes
# age INTEGER,
# => Operation executes
# active BOOLEAN DEFAULT TRUE,
# => Operation executes
# inserted_at TIMESTAMP NOT NULL,
# => Operation executes
# updated_at TIMESTAMP NOT NULL
# => Operation executes
# );
# => Operation executes
# CREATE UNIQUE INDEX users_email_index ON users(email);
# => Operation executesKey Takeaway: Migrations are reversible by default (change/0 function), and timestamps/0 automatically adds inserted_at and updated_at columns with proper types and constraints.
Why It Matters: Database schema changes must be reproducible across development, staging, and production environments. Migrations provide versioned, sequential schema changes that enable team collaboration, rollback capabilities, and consistent database state across deployments. Production systems depend on migrations for safe schema evolution without manual SQL execution and the errors it introduces.
Example 40: Migration - Adding Columns
The alter table macro modifies existing tables by adding, removing, or changing columns. Use add/3 to add new columns.
%% Color Palette: Blue #0173B2, Orange #DE8F05, Teal #029E73, Purple #CC78BC, Brown #CA9161
%% Migration Evolution
graph TD
A[Current Schema] --> B[alter table]
B --> C[add :column]
C --> D[New Schema Version]
D -.->|rollback| E[remove :column]
E -.-> A
style A fill:#0173B2,color:#fff
style B fill:#DE8F05,color:#fff
style C fill:#029E73,color:#fff
style D fill:#029E73,color:#fff
style E fill:#CA9161,color:#fff
defmodule Repo.Migrations.AddBioToUsers do
# => Module starts
use Ecto.Migration
# => Imports Ecto
def change do
# => Operation executes
alter table(:users) do # => Modify existing users table
add :bio, :text # => Add bio column (type: text, nullable)
add :country, :string, size: 2 # => String column with length limit
# => size: 2 for country code (e.g., "US")
end
# => Block ends
end
# => Block ends
end
# => Block ends
# Run migration: mix ecto.migrate
# => Operation executes
# => SQL: ALTER TABLE users
# ADD COLUMN bio TEXT,
# => Operation executes
# ADD COLUMN country VARCHAR(2);
# => Operation executesKey Takeaway: alter table is non-destructive by default, and you can add multiple columns in a single migration for atomic schema changes.
Why It Matters: Evolving production schemas requires adding columns to existing tables without downtime. Nullable columns with defaults can be added online without locking tables, enabling zero-downtime deployments. Understanding which ALTER TABLE operations lock tables versus execute concurrently is critical for production database management where minutes of downtime costs significant revenue.
Example 41: Migration - Adding Indexes
Indexes improve query performance on frequently searched columns. Use create index macro to add indexes.
defmodule Repo.Migrations.AddIndexesToUsers do
# => Module starts
use Ecto.Migration
# => Imports Ecto
def change do
# => Operation executes
create index(:users, [:country]) # => Simple index on country column
# => Speeds up queries filtering by country
create index(:users, [:name, :country])
# => Operation executes
# => Composite index on name and country
# => Optimizes queries filtering by both
create index(:users, [:email], unique: true)
# => Operation executes
# => Unique index (if not already exists)
end
# => Block ends
end
# => Block ends
# Run migration: mix ecto.migrate
# => Operation executes
# => SQL: CREATE INDEX users_country_index ON users(country);
# CREATE INDEX users_name_country_index ON users(name, country);
# => Operation executes
# CREATE UNIQUE INDEX users_email_index ON users(email);
# => Operation executesKey Takeaway: Add indexes on foreign keys and frequently queried columns, but avoid over-indexing as each index adds overhead to inserts and updates.
Why It Matters: Missing indexes cause queries to scan entire tables as data grows, degrading from milliseconds to minutes. Production systems must index foreign keys, lookup columns, and sorting fields while avoiding over-indexing that slows writes. Understanding index strategies (B-tree for equality/range, GIN for arrays/JSON) enables choosing appropriate indexes for actual query patterns revealed by production workload analysis.
Example 42: Migration - Adding Foreign Keys
Foreign key constraints ensure referential integrity by validating that references point to existing records. Use references/2 in migrations.
defmodule Repo.Migrations.CreatePosts do
# => Module starts
use Ecto.Migration
# => Imports Ecto
def change do
# => Operation executes
create table(:posts) do
# => Operation executes
add :title, :string, null: false
# => Operation executes
add :content, :text
# => Operation executes
add :user_id, references(:users, on_delete: :delete_all)
# => Operation executes
# => Foreign key to users.id
# => on_delete: :delete_all cascades deletes
timestamps()
# => Operation executes
end
# => Block ends
create index(:posts, [:user_id]) # => Index foreign key for join performance
end
# => Block ends
end
# => Block ends
# Run migration: mix ecto.migrate
# => Operation executes
# => SQL: CREATE TABLE posts (
# id BIGSERIAL PRIMARY KEY,
# => Operation executes
# title VARCHAR(255) NOT NULL,
# => Operation executes
# content TEXT,
# => Operation executes
# user_id BIGINT,
# => Operation executes
# inserted_at TIMESTAMP NOT NULL,
# => Operation executes
# updated_at TIMESTAMP NOT NULL,
# => Operation executes
# FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
# => Operation executes
# );
# => Operation executes
# CREATE INDEX posts_user_id_index ON posts(user_id);
# => Operation executesKey Takeaway: Always add an index on foreign key columns for join performance, and choose appropriate on_delete strategy (nilify_all, delete_all, nothing, or restrict).
Why It Matters: Foreign keys prevent orphaned records (posts referencing deleted users) and enforce data integrity at the database level. Without foreign keys, application bugs can create referential integrity violations that corrupt data and break queries. Production databases use foreign keys with appropriate cascade rules to maintain consistency while avoiding unexpected deletions from overly aggressive cascades.
Example 43: Embedded Schemas with embeds_one
embeds_one stores a nested schema as JSON in a single column, useful for structured data that doesn’t need its own table. The embedded data is serialized and deserialized automatically.
%% Color Palette: Blue #0173B2, Orange #DE8F05, Teal #029E73, Purple #CC78BC, Brown #CA9161
%% embeds_one Structure
graph TD
A[User Schema] --> B[embeds_one :profile]
B --> C[Profile Schema<br/>name, bio, avatar]
C --> D[Stored as JSON<br/>in users.profile column]
style A fill:#0173B2,color:#fff
style B fill:#DE8F05,color:#fff
style C fill:#CC78BC,color:#fff
style D fill:#029E73,color:#fff
defmodule Address do
# => Module starts
use Ecto.Schema
# => Imports Ecto
embedded_schema do # => Not persisted to its own table
field :street, :string
# => Field defined
field :city, :string
# => Field defined
field :zip, :string
# => Field defined
end
# => Block ends
end
# => Block ends
defmodule User do
# => Module starts
use Ecto.Schema
# => Imports Ecto
schema "users" do
# => Schema declaration
field :name, :string
# => Field defined
embeds_one :address, Address # => Embedded as JSON in users table
timestamps()
# => Operation executes
end
# => Block ends
end
# => Block ends
# Create user with embedded address
# => Operation executes
address = %Address{street: "123 Main St", city: "NYC", zip: "10001"}
# => Value assigned
user = %User{name: "Frank", address: address}
# => Value assigned
{:ok, saved} = Repo.insert(user) # => saved.address is %Address{...}
# => SQL: INSERT INTO users (name, address, ...)
# => VALUES ('Frank', '{"street":"123 Main St",...}', ...)
# Fetch user with embedded address
# => Operation executes
loaded = Repo.get(User, saved.id) # => loaded.address is %Address{...} (deserialized)
IO.inspect(loaded.address.city) # => Output: "NYC"Key Takeaway: embeds_one is stored as JSON and doesn’t support querying or indexing on nested fields; use separate tables with associations when you need to query embedded data.
Why It Matters: Structured nested data (address, preferences, metadata) stored as JSON avoids join overhead for data always accessed with the parent. Production systems use embeds_one for profile data, configuration objects, and denormalized snapshots where querying nested fields isn’t needed. However, embedded data can’t be updated independently or queried efficiently, so use associations when the nested data has its own lifecycle.
Example 44: Embedded Schemas with embeds_many
embeds_many stores an array of nested schemas as JSON, useful for lists of structured data without the overhead of separate tables.
%% Color Palette: Blue #0173B2, Orange #DE8F05, Teal #029E73, Purple #CC78BC, Brown #CA9161
%% embeds_many Structure
graph TD
A[Post Schema] --> B[embeds_many :comments]
B --> C[Comment Schema Array]
C --> D[Comment 1]
C --> E[Comment 2]
C --> F[Comment N]
D --> G[Stored as JSON Array<br/>in posts.comments column]
E --> G
F --> G
style A fill:#0173B2,color:#fff
style B fill:#DE8F05,color:#fff
style C fill:#CC78BC,color:#fff
style D fill:#029E73,color:#fff
style E fill:#029E73,color:#fff
style F fill:#029E73,color:#fff
style G fill:#029E73,color:#fff
defmodule Phone do
# => Module starts
use Ecto.Schema
# => Imports Ecto
embedded_schema do
# => Schema declaration
field :type, :string # => "mobile", "home", "work"
field :number, :string
# => Field defined
end
# => Block ends
end
# => Block ends
defmodule User do
# => Module starts
use Ecto.Schema
# => Imports Ecto
import Ecto.Changeset
# => Operation executes
schema "users" do
# => Schema declaration
field :name, :string
# => Field defined
embeds_many :phones, Phone # => Array of phone numbers as JSON
timestamps()
# => Operation executes
end
# => Block ends
def changeset(user, params \\ %{}) do
# => Operation executes
user
# => Operation executes
|> cast(params, [:name])
# => Operation executes
|> cast_embed(:phones, with: &Phone.changeset/2)
# => Operation executes
# => Cast embedded phones
end
# => Block ends
end
# => Block ends
defmodule Phone do
# => Module starts
import Ecto.Changeset
# => Operation executes
def changeset(phone, params) do
# => Operation executes
phone
# => Operation executes
|> cast(params, [:type, :number])
# => Operation executes
|> validate_required([:type, :number])
# => Operation executes
end
# => Block ends
end
# => Block ends
# Create user with multiple phones
# => Operation executes
params = %{
# => Value assigned
name: "Grace",
# => Operation executes
phones: [
# => Operation executes
%{type: "mobile", number: "555-1234"},
# => Operation executes
%{type: "home", number: "555-5678"}
# => Operation executes
]
# => Operation executes
}
# => Operation executes
changeset = User.changeset(%User{}, params)
# => Value assigned
{:ok, saved} = Repo.insert(changeset) # => saved.phones is [%Phone{...}, %Phone{...}]
# => SQL: INSERT INTO users (name, phones, ...)
# => VALUES ('Grace', '[{"type":"mobile",...},...]', ...)
IO.inspect(length(saved.phones)) # => Output: 2
IO.inspect(hd(saved.phones).number) # => Output: "555-1234"Key Takeaway: embeds_many requires cast_embed/3 in changesets for validation, and changes to embedded data require updating the entire parent record.
Why It Matters: Arrays of structured data (phone numbers, tags, variant options) stored as JSON arrays avoid the overhead of many-to-many tables for data rarely queried independently. Production e-commerce systems use embeds_many for product attributes and order line items when querying individual items isn’t needed. However, embedded data sacrifices referential integrity and efficient querying, so use only when associations aren’t needed.
Example 45: Composite Primary Keys
Ecto supports composite primary keys by setting @primary_key false and manually defining multiple fields as primary keys. Useful for join tables.
defmodule UserRole do
# => Module starts
use Ecto.Schema
# => Imports Ecto
@primary_key false # => Disable auto-generated primary key
schema "user_roles" do
# => Schema declaration
field :user_id, :integer, primary_key: true
# => Field defined
# => Part of composite key
field :role_id, :integer, primary_key: true
# => Field defined
# => Part of composite key
field :assigned_at, :naive_datetime
# => Field defined
timestamps()
# => Operation executes
end
# => Block ends
end
# => Block ends
# Migration for composite key table
# => Operation executes
defmodule Repo.Migrations.CreateUserRoles do
# => Module starts
use Ecto.Migration
# => Imports Ecto
def change do
# => Operation executes
create table(:user_roles, primary_key: false) do
# => Operation executes
add :user_id, :integer, primary_key: true
# => Operation executes
add :role_id, :integer, primary_key: true
# => Operation executes
add :assigned_at, :naive_datetime
# => Operation executes
timestamps()
# => Operation executes
end
# => Block ends
create unique_index(:user_roles, [:user_id, :role_id])
# => Operation executes
# => Enforce uniqueness on composite key
end
# => Block ends
end
# => Block ends
# Insert record
# => Operation executes
{:ok, ur} = Repo.insert(%UserRole{user_id: 1, role_id: 5, assigned_at: NaiveDateTime.utc_now()})
# => Database operation
# => ur has composite key (user_id: 1, role_id: 5)
# Fetch by composite key
# => Operation executes
found = Repo.get_by(UserRole, user_id: 1, role_id: 5)
# => Database operation
# => found is %UserRole{user_id: 1, role_id: 5}
IO.inspect({found.user_id, found.role_id})
# => Operation executes
# => Output: {1, 5}Key Takeaway: Composite primary keys disable auto-generated id fields, and you must manually specify all key fields when inserting or fetching records.
Why It Matters: Join tables and time-series data often use composite keys (user_id + role_id, sensor_id + timestamp) for natural uniqueness without synthetic IDs. Production systems use composite keys to enforce uniqueness constraints and optimize storage for high-volume tables. However, composite keys complicate association configuration and are harder to reference in URLs, so use only when natural compound uniqueness is semantically meaningful.
Example 46: Custom Primary Key Types
Ecto supports custom primary key types like UUID, string, or binary_id. Set @primary_key with desired type.
defmodule Article do
# => Module starts
use Ecto.Schema
# => Imports Ecto
@primary_key {:uuid, :binary_id, autogenerate: true}
# => Operation executes
# => UUID primary key (auto-generated)
@foreign_key_type :binary_id # => Foreign keys also use binary_id
schema "articles" do
# => Schema declaration
field :title, :string
# => Field defined
belongs_to :author, User, type: :binary_id
# => Operation executes
# => Foreign key is binary_id (UUID)
timestamps()
# => Operation executes
end
# => Block ends
end
# => Block ends
# Migration for UUID primary key
# => Operation executes
defmodule Repo.Migrations.CreateArticles do
# => Module starts
use Ecto.Migration
# => Imports Ecto
def change do
# => Operation executes
create table(:articles, primary_key: false) do
# => Operation executes
add :uuid, :binary_id, primary_key: true
# => Operation executes
# => UUID as primary key
add :title, :string
# => Operation executes
add :author_id, references(:users, type: :binary_id)
# => Operation executes
# => Foreign key references UUID
timestamps()
# => Operation executes
end
# => Block ends
end
# => Block ends
end
# => Block ends
# Insert article
# => Operation executes
{:ok, article} = Repo.insert(%Article{title: "UUID Article"})
# => Database operation
# => article.uuid is auto-generated UUID
# => article.uuid is <<...>> (binary)
IO.inspect(article.uuid) # => Output: <<193, 44, 15, ...>> (binary UUID)
IO.inspect(Ecto.UUID.cast!(article.uuid))
# => Operation executes
# => Output: "c12c0f3a-..." (string format)Key Takeaway: UUIDs are stored as binary_id for efficiency and must be cast to strings for display; set @foreign_key_type to match your primary key type for consistent associations.
Why It Matters: Sequential integer IDs leak information (record count, creation rate) and create contention on inserts in distributed databases. Production systems use UUIDs for privacy-sensitive resources, distributed ID generation, and client-side ID creation before server persistence. However, UUIDs consume more storage and have slightly slower lookups, so production systems must weigh security/distribution benefits against performance costs.
Example 47: many_to_many Associations
many_to_many creates a bidirectional association through a join table, allowing multiple records to relate to multiple records. Ecto manages the join table automatically.
%% Color Palette: Blue #0173B2, Orange #DE8F05, Teal #029E73, Purple #CC78BC, Brown #CA9161
%% many_to_many Association
graph TD
A[User] <-->|user_skills| B[Skill]
A -->|user_id| C[Join Table]
B -->|skill_id| C
style A fill:#0173B2,color:#fff
style B fill:#0173B2,color:#fff
style C fill:#DE8F05,color:#fff
defmodule User do
# => Module starts
use Ecto.Schema
# => Imports Ecto
schema "users" do
# => Schema declaration
field :name, :string
# => Field defined
many_to_many :skills, Skill, join_through: "user_skills"
# => Operation executes
# => Join through user_skills table
timestamps()
# => Operation executes
end
# => Block ends
end
# => Block ends
defmodule Skill do
# => Module starts
use Ecto.Schema
# => Imports Ecto
schema "skills" do
# => Schema declaration
field :name, :string
# => Field defined
many_to_many :users, User, join_through: "user_skills"
# => Operation executes
# => Bidirectional association
timestamps()
# => Operation executes
end
# => Block ends
end
# => Block ends
# Migration for join table
# => Operation executes
defmodule Repo.Migrations.CreateUserSkills do
# => Module starts
use Ecto.Migration
# => Imports Ecto
def change do
# => Operation executes
create table(:user_skills) do
# => Operation executes
add :user_id, references(:users, on_delete: :delete_all)
# => Operation executes
add :skill_id, references(:skills, on_delete: :delete_all)
# => Operation executes
timestamps()
# => Operation executes
end
# => Block ends
create unique_index(:user_skills, [:user_id, :skill_id])
# => Operation executes
end
# => Block ends
end
# => Block ends
# Create user and skills
# => Operation executes
{:ok, user} = Repo.insert(%User{name: "Henry"})
# => Database operation
{:ok, elixir} = Repo.insert(%Skill{name: "Elixir"})
# => Database operation
{:ok, postgres} = Repo.insert(%Skill{name: "PostgreSQL"})
# => Database operation
# Associate user with skills (manual join table insert)
# => Operation executes
Repo.insert_all("user_skills", [
# => Database operation
%{user_id: user.id, skill_id: elixir.id, inserted_at: NaiveDateTime.utc_now(), updated_at: NaiveDateTime.utc_now()},
# => Operation executes
%{user_id: user.id, skill_id: postgres.id, inserted_at: NaiveDateTime.utc_now(), updated_at: NaiveDateTime.utc_now()}
# => Operation executes
])
# => Operation executes
# Preload skills
# => Operation executes
user_with_skills = Repo.preload(user, :skills)
# => Database operation
# => user_with_skills.skills is [%Skill{name: "Elixir"}, ...]
# => SQL: SELECT s.* FROM skills s
# => INNER JOIN user_skills us ON us.skill_id = s.id
# => WHERE us.user_id = 1
IO.inspect(length(user_with_skills.skills))
# => Operation executes
# => Output: 2Key Takeaway: many_to_many requires a join table with foreign keys to both tables, and Ecto doesn’t automatically insert join records—you must manually insert into the join table or use Ecto.Changeset.put_assoc/3.
Why It Matters: Many-to-many relationships (users-skills, posts-tags, students-courses) require join tables that Ecto doesn’t auto-populate. Production systems use put_assoc or explicit join table inserts to manage these relationships, understanding that each approach has trade-offs. Explicit inserts offer more control over join table metadata (timestamps, ordering), while put_assoc simplifies replacement semantics for simpler use cases.
Example 48: Putting Associations with put_assoc/3
put_assoc/3 replaces all associated records in a changeset, useful for managing many_to_many associations or replacing has_many children.
%% Color Palette: Blue #0173B2, Orange #DE8F05, Teal #029E73, Purple #CC78BC, Brown #CA9161
%% put_assoc Replacement Flow
graph TD
A[Parent Record] --> B[put_assoc/3]
B --> C[Delete Old Associated Records]
C --> D[Insert New Associated Records]
D --> E[Parent with New Associations]
style A fill:#0173B2,color:#fff
style B fill:#DE8F05,color:#fff
style C fill:#CA9161,color:#fff
style D fill:#029E73,color:#fff
style E fill:#029E73,color:#fff
defmodule User do
# => Module starts
use Ecto.Schema
# => Imports Ecto
import Ecto.Changeset
# => Operation executes
schema "users" do
# => Schema declaration
field :name, :string
# => Field defined
many_to_many :skills, Skill, join_through: "user_skills", on_replace: :delete
# => Operation executes
# => on_replace: :delete removes old associations
timestamps()
# => Operation executes
end
# => Block ends
def changeset(user, params \\ %{}) do
# => Operation executes
user
# => Operation executes
|> cast(params, [:name])
# => Operation executes
|> put_assoc(:skills, params[:skills] || [])
# => Operation executes
# => Replace all skills
end
# => Block ends
end
# => Block ends
defmodule Skill do
# => Module starts
use Ecto.Schema
# => Imports Ecto
schema "skills" do
# => Schema declaration
field :name, :string
# => Field defined
timestamps()
# => Operation executes
end
# => Block ends
end
# => Block ends
# Create user and skills
# => Operation executes
{:ok, user} = Repo.insert(%User{name: "Iris"})
# => Database operation
{:ok, elixir} = Repo.insert(%Skill{name: "Elixir"})
# => Database operation
{:ok, postgres} = Repo.insert(%Skill{name: "PostgreSQL"})
# => Database operation
# Associate skills via changeset
# => Operation executes
changeset = User.changeset(user, %{skills: [elixir, postgres]})
# => Value assigned
{:ok, updated} = Repo.update(changeset)
# => Database operation
# => Inserts into user_skills join table
# => SQL: INSERT INTO user_skills (user_id, skill_id) VALUES (1, 1), (1, 2)
# Preload and verify
# => Operation executes
user_with_skills = Repo.preload(updated, :skills)
# => Database operation
# => user_with_skills.skills is [%Skill{name: "Elixir"}, ...]
IO.inspect(length(user_with_skills.skills))
# => Operation executes
# => Output: 2Key Takeaway: put_assoc/3 with on_replace: :delete in the schema automatically removes old join table entries when updating associations, simplifying many_to_many management.
Why It Matters: Replacing all associations (setting a user’s complete tag list) differs from adding/removing individual items. Production systems use put_assoc with on_replace: :delete for full-replacement UIs (multi-select dropdowns, checkbox lists) where the new selection completely replaces the old. Understanding on_replace options (:delete, :nilify, :raise) prevents accidental orphaning or unexpected errors when association updates don’t match schema configuration.
Example 49: Casting Associations with cast_assoc/3
cast_assoc/3 casts nested association params through changesets, enabling validation and updates of associated records in a single operation.
defmodule User do
# => Module starts
use Ecto.Schema
# => Imports Ecto
import Ecto.Changeset
# => Operation executes
schema "users" do
# => Schema declaration
field :name, :string
# => Field defined
has_many :posts, Post # => One-to-many association
timestamps()
# => Operation executes
end
# => Block ends
def changeset(user, params \\ %{}) do
# => Operation executes
user
# => Operation executes
|> cast(params, [:name])
# => Operation executes
|> cast_assoc(:posts) # => Cast nested posts params
# => Calls Post.changeset for each post
end
# => Block ends
end
# => Block ends
defmodule Post do
# => Module starts
use Ecto.Schema
# => Imports Ecto
import Ecto.Changeset
# => Operation executes
schema "posts" do
# => Schema declaration
field :title, :string
# => Field defined
belongs_to :user, User
# => Operation executes
timestamps()
# => Operation executes
end
# => Block ends
def changeset(post, params \\ %{}) do
# => Operation executes
post
# => Operation executes
|> cast(params, [:title])
# => Operation executes
|> validate_required([:title])
# => Operation executes
end
# => Block ends
end
# => Block ends
# Create user with nested posts
# => Operation executes
params = %{
# => Value assigned
name: "Jack",
# => Operation executes
posts: [
# => Operation executes
%{title: "First Post"},
# => Operation executes
%{title: "Second Post"}
# => Operation executes
]
# => Operation executes
}
# => Operation executes
changeset = User.changeset(%User{}, params)
# => Value assigned
{:ok, user} = Repo.insert(changeset) # => Inserts user and both posts
# => SQL: INSERT INTO users (name) VALUES ('Jack');
# => INSERT INTO posts (title, user_id) VALUES ('First Post', 1);
# => INSERT INTO posts (title, user_id) VALUES ('Second Post', 1);
# Preload posts
# => Operation executes
user_with_posts = Repo.preload(user, :posts)
# => Database operation
# => user_with_posts.posts has 2 posts
IO.inspect(length(user_with_posts.posts))
# => Operation executes
# => Output: 2Key Takeaway: cast_assoc/3 enables nested inserts and updates by validating associated data through their changesets, making it ideal for creating parent and children in a single operation.
Why It Matters: Forms submitting parent and child data (order with line items, user with addresses) need nested validation and atomic inserts. Production systems use cast_assoc to validate all nested data through proper changesets before committing, preventing partial inserts where parent succeeds but children fail validation. This pattern is essential for complex forms and API endpoints accepting nested resources in a single request.
Example 50: Subqueries for Complex Filtering
Subqueries allow you to filter a query based on the results of another query, enabling complex filtering patterns impossible with simple WHERE clauses.
%% Color Palette: Blue #0173B2, Orange #DE8F05, Teal #029E73, Purple #CC78BC, Brown #CA9161
%% Subquery Composition
graph TD
A[Inner Query] --> B[Filter/Aggregate]
B --> C[Inner Result Set]
C --> D[Outer Query]
D --> E[Use Inner Results as Filter]
E --> F[Final Results]
style A fill:#0173B2,color:#fff
style B fill:#DE8F05,color:#fff
style C fill:#CC78BC,color:#fff
style D fill:#0173B2,color:#fff
style E fill:#DE8F05,color:#fff
style F fill:#029E73,color:#fff
defmodule User do
# => Module starts
use Ecto.Schema
# => Imports Ecto
schema "users" do
# => Schema declaration
field :name, :string
# => Field defined
field :age, :integer
# => Field defined
has_many :posts, Post
# => Operation executes
timestamps()
# => Operation executes
end
# => Block ends
end
# => Block ends
defmodule Post do
# => Module starts
use Ecto.Schema
# => Imports Ecto
schema "posts" do
# => Schema declaration
field :title, :string
# => Field defined
field :published, :boolean
# => Field defined
belongs_to :user, User
# => Operation executes
timestamps()
# => Operation executes
end
# => Block ends
end
# => Block ends
import Ecto.Query
# => Operation executes
# Insert test data
# => Operation executes
{:ok, user1} = Repo.insert(%User{name: "Kate", age: 30})
# => Database operation
{:ok, user2} = Repo.insert(%User{name: "Liam", age: 25})
# => Database operation
Repo.insert(%Post{title: "Published", published: true, user_id: user1.id})
# => Database operation
Repo.insert(%Post{title: "Draft", published: false, user_id: user2.id})
# => Database operation
# Subquery: users with published posts
# => Operation executes
published_users_subquery = from p in Post,
# => Value assigned
where: p.published == true,
# => Value assigned
select: p.user_id # => Subquery returns user IDs
# Main query using subquery
# => Operation executes
query = from u in User,
# => Value assigned
where: u.id in subquery(published_users_subquery),
# => Operation executes
# => Filter users by subquery results
select: u
# => Operation executes
users = Repo.all(query) # => users is [%User{name: "Kate"}]
# => SQL: SELECT * FROM users
# => WHERE id IN (SELECT user_id FROM posts WHERE published = TRUE)
IO.inspect(length(users)) # => Output: 1
IO.inspect(hd(users).name) # => Output: "Kate"Key Takeaway: Subqueries are composable and can be used in WHERE, FROM, or SELECT clauses; use subquery/1 to embed one query inside another for complex filtering.
Why It Matters: Complex filtering (users with posts in specific categories, orders with items above threshold) often requires correlating data across tables. Subqueries enable expressing these filters in database SQL rather than fetching excessive data and filtering in Elixir. Production systems use subqueries for performance-critical filters that would otherwise require loading thousands of records to apply application-level logic.
Example 51: Fragment for Raw SQL Expressions
fragment/1 allows you to inject raw SQL into queries when Ecto’s DSL doesn’t support specific database features. Use sparingly and be mindful of SQL injection.
defmodule User do
# => Module starts
use Ecto.Schema
# => Imports Ecto
schema "users" do
# => Schema declaration
field :name, :string
# => Field defined
field :email, :string
# => Field defined
timestamps()
# => Operation executes
end
# => Block ends
end
# => Block ends
import Ecto.Query
# => Operation executes
# Insert test data
# => Operation executes
Repo.insert(%User{name: "Mia", email: "mia@EXAMPLE.com"})
# => Database operation
Repo.insert(%User{name: "Noah", email: "noah@example.COM"})
# => Database operation
# Case-insensitive email search using fragment
# => Operation executes
email_query = from u in User,
# => Value assigned
where: fragment("LOWER(?)", u.email) == "mia@example.com",
# => Value assigned
# => Raw SQL: LOWER(email)
select: u
# => Operation executes
users = Repo.all(email_query) # => users is [%User{name: "Mia"}]
# => SQL: SELECT * FROM users WHERE LOWER(email) = 'mia@example.com'
IO.inspect(hd(users).name) # => Output: "Mia"Key Takeaway: fragment/1 is powerful but bypasses Ecto’s type safety; always use parameter placeholders (?) and bind variables to prevent SQL injection.
Why It Matters: Ecto’s query DSL doesn’t cover every SQL feature (LOWER, COALESCE, database-specific functions). Production systems use fragments for database functions while maintaining parameterization against SQL injection. This escape hatch enables using JSON operators, full-text search, and custom functions without dropping to raw SQL, preserving Ecto’s query composition benefits while accessing full database capabilities.
Example 52: Distinct Queries with distinct/2
distinct ensures the query returns unique rows, essential when joining has_many associations or aggregating data.
defmodule User do
# => Module starts
use Ecto.Schema
# => Imports Ecto
schema "users" do
# => Schema declaration
field :name, :string
# => Field defined
field :country, :string
# => Field defined
timestamps()
# => Operation executes
end
# => Block ends
end
# => Block ends
import Ecto.Query
# => Operation executes
# Insert test data with duplicates
# => Operation executes
Repo.insert(%User{name: "Olivia", country: "USA"})
# => Database operation
Repo.insert(%User{name: "Paul", country: "USA"})
# => Database operation
Repo.insert(%User{name: "Quinn", country: "UK"})
# => Database operation
# Get distinct countries
# => Operation executes
query = from u in User,
# => Value assigned
distinct: true, # => Remove duplicate rows
select: u.country,
# => Operation executes
order_by: u.country
# => Operation executes
countries = Repo.all(query) # => countries is ["UK", "USA"]
# => SQL: SELECT DISTINCT country FROM users ORDER BY country
# Distinct on specific field
# => Field defined
distinct_query = from u in User,
# => Value assigned
distinct: [desc: u.country], # => Distinct by country (keep first per country)
select: {u.name, u.country},
# => Operation executes
order_by: [desc: u.country, asc: u.name]
# => Operation executes
results = Repo.all(distinct_query) # => results is [{"Olivia", "USA"}, {"Quinn", "UK"}]
# => SQL: SELECT DISTINCT ON (country) name, country
# => FROM users ORDER BY country DESC, name ASC
IO.inspect(countries) # => Output: ["UK", "USA"]
IO.inspect(results) # => Output: [{"Olivia", "USA"}, {"Quinn", "UK"}]Key Takeaway: distinct: true removes all duplicate rows, while distinct: [field] uses PostgreSQL’s DISTINCT ON to keep the first row per distinct value of specified fields.
Why It Matters: Joins that multiply parent rows (user with multiple posts) require DISTINCT to prevent duplicate parents in results. Production systems use distinct: true for join queries and DISTINCT ON for “first per group” queries (most recent order per customer, latest comment per post). Understanding when distinct is needed prevents bugs where multiplied rows cause incorrect aggregations or display issues.
Example 53: Lock Queries with lock/2
lock/2 adds row-level locking to queries, preventing concurrent modifications during transactions. Use for critical operations requiring isolation.
defmodule User do
# => Module starts
use Ecto.Schema
# => Imports Ecto
schema "users" do
# => Schema declaration
field :name, :string
# => Field defined
field :balance, :decimal
# => Field defined
timestamps()
# => Operation executes
end
# => Block ends
end
# => Block ends
import Ecto.Query
# => Operation executes
# Insert test user
# => Operation executes
{:ok, user} = Repo.insert(%User{name: "Ruby", balance: Decimal.new("100.00")})
# => Database operation
# Lock user row during transaction
# => Operation executes
Repo.transaction(fn ->
# => Database operation
# Lock row for update
# => Operation executes
query = from u in User,
# => Value assigned
where: u.id == ^user.id,
# => Value assigned
lock: "FOR UPDATE" # => Acquire exclusive lock on row
# => Other transactions wait until this commits
locked_user = Repo.one(query) # => SQL: SELECT * FROM users WHERE id = 1 FOR UPDATE
# Perform update with guaranteed isolation
# => Operation executes
changeset = Ecto.Changeset.change(locked_user, balance: Decimal.sub(locked_user.balance, Decimal.new("50.00")))
# => Value assigned
{:ok, updated} = Repo.update(changeset)
# => Database operation
updated
# => Operation executes
end)
# => Block ends
IO.inspect("Lock acquired and balance updated")
# => Operation executes
# => Output: "Lock acquired and balance updated"Key Takeaway: FOR UPDATE locks rows for the transaction duration, preventing other transactions from modifying them; use in transactions to prevent race conditions in critical operations.
Why It Matters: Concurrent access to shared resources (inventory, wallet balances, seat reservations) causes race conditions where multiple processes read stale values and overwrite each other. Production systems use FOR UPDATE locks to serialize access to critical rows, ensuring consistent state during multi-step updates. Understanding pessimistic locking is essential for financial transactions, booking systems, and any domain where lost updates have significant consequences.
Example 54: Select Merge for Field Updates
select_merge/2 adds fields to an existing select, useful for computing derived values alongside selected fields.
defmodule User do
# => Module starts
use Ecto.Schema
# => Imports Ecto
schema "users" do
# => Schema declaration
field :first_name, :string
# => Field defined
field :last_name, :string
# => Field defined
field :age, :integer
# => Field defined
timestamps()
# => Operation executes
end
# => Block ends
end
# => Block ends
import Ecto.Query
# => Operation executes
# Insert test data
# => Operation executes
Repo.insert(%User{first_name: "Sam", last_name: "Smith", age: 30})
# => Database operation
# Select with computed field
# => Field defined
query = from u in User,
# => Value assigned
select: %{id: u.id, age: u.age} # => Initial select
|> select_merge(%{full_name: fragment("? || ' ' || ?", u.first_name, u.last_name)})
# => Operation executes
# => Add computed full_name field
results = Repo.all(query) # => results is [%{id: 1, age: 30, full_name: "Sam Smith"}]
# => SQL: SELECT id, age, first_name || ' ' || last_name AS full_name
# => FROM users
IO.inspect(hd(results)) # => Output: %{id: 1, age: 30, full_name: "Sam Smith"}Key Takeaway: select_merge/2 is composable and allows you to add computed or additional fields to an existing select, useful for building queries incrementally.
Why It Matters: Query modules often need to add computed fields (full name, age from birthdate, status derived from flags) to base queries. Production systems use select_merge to layer computed fields onto reusable query functions, keeping query composition clean. This pattern enables building domain-specific queries from generic base queries while adding context-specific derived fields.
Example 55: Windows Functions with over/2
Window functions perform calculations across rows related to the current row. Use over/2 to define window specifications.
defmodule Sale do
# => Module starts
use Ecto.Schema
# => Imports Ecto
schema "sales" do
# => Schema declaration
field :region, :string
# => Field defined
field :amount, :decimal
# => Field defined
field :date, :date
# => Field defined
timestamps()
# => Operation executes
end
# => Block ends
end
# => Block ends
import Ecto.Query
# => Operation executes
# Insert test data
# => Operation executes
Repo.insert(%Sale{region: "East", amount: Decimal.new("100"), date: ~D[2024-01-01]})
# => Database operation
Repo.insert(%Sale{region: "East", amount: Decimal.new("200"), date: ~D[2024-01-02]})
# => Database operation
Repo.insert(%Sale{region: "West", amount: Decimal.new("150"), date: ~D[2024-01-01]})
# => Database operation
# Running total per region using window function
# => Operation executes
query = from s in Sale,
# => Value assigned
select: %{
# => Operation executes
region: s.region,
# => Operation executes
amount: s.amount,
# => Operation executes
running_total: over(sum(s.amount), partition_by: s.region, order_by: s.date)
# => Operation executes
# => SUM(amount) OVER (PARTITION BY region ORDER BY date)
},
# => Operation executes
order_by: [s.region, s.date]
# => Operation executes
results = Repo.all(query) # => results with running totals per region
# => SQL: SELECT region, amount,
# => SUM(amount) OVER (PARTITION BY region ORDER BY date) AS running_total
# => FROM sales ORDER BY region, date
IO.inspect(results)
# => Operation executes
# => Output: [
# %{region: "East", amount: #Decimal<100>, running_total: #Decimal<100>},
# => Operation executes
# %{region: "East", amount: #Decimal<200>, running_total: #Decimal<300>},
# => Operation executes
# %{region: "West", amount: #Decimal<150>, running_total: #Decimal<150>}
# => Operation executes
# ]
# => Operation executesKey Takeaway: Window functions compute aggregates without collapsing rows (unlike GROUP BY), and partition_by divides rows into groups while order_by defines the calculation order within each partition.
Why It Matters: Running totals, rankings, and row numbering require aggregates without collapsing rows—impossible with GROUP BY alone. Production analytics use window functions for leaderboards (rank within category), time series (running averages), and pagination with counts (total rows alongside current page). Window functions execute in the database, enabling real-time calculations over millions of rows without loading data into Elixir.
Example 56: Common Table Expressions (CTEs) with with_cte/3
CTEs (Common Table Expressions) create named temporary result sets that can be referenced multiple times in a query, improving readability and performance.
%% Color Palette: Blue #0173B2, Orange #DE8F05, Teal #029E73, Purple #CC78BC, Brown #CA9161
%% CTE Query Flow
graph TD
A[Define CTE] --> B[Reference CTE in Main Query]
B --> C[Execute Combined Query]
style A fill:#0173B2,color:#fff
style B fill:#DE8F05,color:#fff
style C fill:#029E73,color:#fff
defmodule User do
# => Module starts
use Ecto.Schema
# => Imports Ecto
schema "users" do
# => Schema declaration
field :name, :string
# => Field defined
field :age, :integer
# => Field defined
timestamps()
# => Operation executes
end
# => Block ends
end
# => Block ends
import Ecto.Query
# => Operation executes
# Insert test data
# => Operation executes
Repo.insert(%User{name: "Tina", age: 25})
# => Database operation
Repo.insert(%User{name: "Uma", age: 35})
# => Database operation
Repo.insert(%User{name: "Victor", age: 30})
# => Database operation
# Define CTE for users over 25
# => Operation executes
young_users_cte = from u in User,
# => Value assigned
where: u.age > 25,
# => Operation executes
select: %{id: u.id, name: u.name, age: u.age}
# => Operation executes
# Main query using CTE
# => Operation executes
query = User
# => Value assigned
|> with_cte("young_users", as: ^young_users_cte)
# => Operation executes
# => Define CTE named "young_users"
|> join(:inner, [u], y in "young_users", on: u.id == y.id)
# => Value assigned
# => Join with CTE
|> select([u, y], {u.name, y.age})
# => Operation executes
results = Repo.all(query) # => results is [{"Uma", 35}, {"Victor", 30}]
# => SQL: WITH young_users AS (
# => SELECT id, name, age FROM users WHERE age > 25
# => )
# => SELECT u.name, y.age FROM users u
# => INNER JOIN young_users y ON u.id = y.id
IO.inspect(results) # => Output: [{"Uma", 35}, {"Victor", 30}]Key Takeaway: CTEs improve query readability for complex queries and can be referenced multiple times without re-executing, but they’re not cached across queries.
Why It Matters: Complex queries that reuse intermediate results (top customers referenced in multiple calculations) benefit from CTEs that name and define these result sets once. Production reporting queries use CTEs for readability and to avoid duplicating complex subqueries that appear multiple times. CTEs also enable recursive queries (org charts, bill of materials) impossible with standard SQL, making them essential for hierarchical data traversal.
Example 57: Batch Insert with insert_all/3
Repo.insert_all/3 inserts multiple records in a single SQL statement, far more efficient than individual inserts. Returns count of inserted records.
defmodule User do
# => Module starts
use Ecto.Schema
# => Imports Ecto
schema "users" do
# => Schema declaration
field :name, :string
# => Field defined
field :age, :integer
# => Field defined
timestamps()
# => Operation executes
end
# => Block ends
end
# => Block ends
# Prepare batch data
# => Operation executes
now = NaiveDateTime.utc_now() |> NaiveDateTime.truncate(:second)
# => Value assigned
users = [
# => Value assigned
%{name: "Wendy", age: 28, inserted_at: now, updated_at: now},
# => Block ends
%{name: "Xavier", age: 32, inserted_at: now, updated_at: now},
# => Operation executes
%{name: "Yara", age: 27, inserted_at: now, updated_at: now}
# => Operation executes
]
# => Operation executes
# Batch insert
# => Operation executes
{count, _} = Repo.insert_all(User, users)
# => Database operation
# => count is 3
# => SQL: INSERT INTO users (name, age, inserted_at, updated_at)
# => VALUES ('Wendy', 28, ...), ('Xavier', 32, ...), ('Yara', 27, ...)
# => Single SQL statement for all inserts
# Verify inserts
# => Operation executes
all_users = Repo.all(User) # => all_users has 3 users
IO.inspect(count) # => Output: 3
IO.inspect(length(all_users)) # => Output: 3Key Takeaway: insert_all/3 bypasses changesets and validations for performance, so use it for bulk operations where you trust the data; it returns {count, nil} or {count, records} if returning: true.
Why It Matters: Inserting thousands of records one-by-one takes minutes and generates thousands of SQL statements. Batch insert_all reduces this to seconds with a single SQL statement. Production data imports, seeding scripts, and ETL jobs use insert_all for orders-of-magnitude performance improvement, accepting the trade-off of bypassed changeset validation for pre-validated bulk data.
Example 58: Returning Inserted Data with returning Option
The returning option in insert_all/3 returns inserted records instead of nil, useful when you need database-generated values like IDs.
defmodule User do
# => Module starts
use Ecto.Schema
# => Imports Ecto
schema "users" do
# => Schema declaration
field :name, :string
# => Field defined
timestamps()
# => Operation executes
end
# => Block ends
end
# => Block ends
# Batch insert with returning
# => Operation executes
now = NaiveDateTime.utc_now() |> NaiveDateTime.truncate(:second)
# => Value assigned
users = [
# => Value assigned
%{name: "Zane", inserted_at: now, updated_at: now},
# => Operation executes
%{name: "Amy", inserted_at: now, updated_at: now}
# => Operation executes
]
# => Operation executes
{count, inserted_users} = Repo.insert_all(User, users, returning: [:id, :name])
# => Database operation
# => count is 2
# => inserted_users is [%{id: 1, name: "Zane"}, %{id: 2, name: "Amy"}]
# => SQL: INSERT INTO users (name, ...) VALUES ('Zane', ...), ('Amy', ...)
# => RETURNING id, name
IO.inspect(count) # => Output: 2
IO.inspect(inserted_users) # => Output: [%{id: 1, name: "Zane"}, %{id: 2, name: "Amy"}]Key Takeaway: returning: [:fields] specifies which fields to return, typically used for database-generated values like IDs; returning: true returns all fields.
Why It Matters: Batch inserts that need generated IDs (for subsequent operations, audit logs, or API responses) must use RETURNING to avoid a separate query per row. Production bulk creates that return created records to users use returning to get IDs in the same roundtrip, maintaining performance while providing response data. This pattern is essential for APIs that batch-create resources and return the created items with their IDs.
Example 59: Schemaless Queries for Flexibility
Ecto allows querying without schemas by using table names as strings, useful for ad-hoc queries or when schemas don’t exist.
import Ecto.Query
# => Operation executes
# Assume users table exists with columns: id, name, age
# => Operation executes
# Schemaless query (no User schema needed)
# => Schema declaration
query = from u in "users", # => Query string table name directly
where: u.age > 25,
# => Operation executes
select: %{id: u.id, name: u.name, age: u.age}
# => Operation executes
results = Repo.all(query) # => results is list of maps (not structs)
# => SQL: SELECT id, name, age FROM users WHERE age > 25
# Schemaless insert
# => Operation executes
now = NaiveDateTime.utc_now() |> NaiveDateTime.truncate(:second)
# => Value assigned
{1, _} = Repo.insert_all("users", [%{name: "Bob", age: 40, inserted_at: now, updated_at: now}])
# => Database operation
# => Insert without schema definition
IO.inspect(results) # => Output: [%{id: 1, name: "...", age: ...}, ...]Key Takeaway: Schemaless queries return plain maps instead of structs and are useful for migrations, admin tools, or when working with dynamic tables, but you lose type safety and validation.
Why It Matters: Admin tools, migrations, and reports often query tables without corresponding Elixir schemas (audit logs, analytics tables, legacy databases). Schemaless queries enable database access without defining schemas for every table. Production systems use schemaless queries for ad-hoc reporting, data migrations that predate schema changes, and integrating with databases managed by other applications.
Example 60: Query Prefixes for Multi-Tenancy
Query prefixes enable querying specific database schemas (PostgreSQL) or databases (MySQL), essential for multi-tenant applications.
defmodule User do
# => Module starts
use Ecto.Schema
# => Imports Ecto
schema "users" do
# => Schema declaration
field :name, :string
# => Field defined
timestamps()
# => Operation executes
end
# => Block ends
end
# => Block ends
import Ecto.Query
# => Operation executes
# Query with prefix (PostgreSQL schema or MySQL database)
# => Schema declaration
query = from u in User,
# => Value assigned
select: u
# => Operation executes
# Query tenant1 schema
# => Schema declaration
tenant1_users = Repo.all(query, prefix: "tenant1")
# => Database operation
# => SQL: SELECT * FROM tenant1.users
# => Query tenant1 schema
# Query tenant2 schema
# => Schema declaration
tenant2_users = Repo.all(query, prefix: "tenant2")
# => Database operation
# => SQL: SELECT * FROM tenant2.users
# => Query tenant2 schema
# Insert with prefix
# => Operation executes
{:ok, user} = Repo.insert(%User{name: "Tenant User"}, prefix: "tenant1")
# => Database operation
# => SQL: INSERT INTO tenant1.users (name, ...) VALUES ('Tenant User', ...)
IO.inspect(length(tenant1_users)) # => Output: count from tenant1 schema
IO.inspect(length(tenant2_users)) # => Output: count from tenant2 schemaKey Takeaway: Prefixes enable schema-level multi-tenancy in PostgreSQL, and you must pass the prefix option to every Repo operation; consider using Ecto.Query.put_query_prefix/2 to set prefix for entire queries.
Why It Matters: SaaS applications isolating customer data require multi-tenancy strategies where each tenant’s data lives in separate database schemas. Prefixes enable schema-per-tenant architecture with complete data isolation while sharing the same codebase. Production SaaS systems use prefix-based tenancy for regulatory compliance, data isolation, and independent backup/restore per tenant, though it requires careful middleware to ensure the correct prefix is always set.
Summary
Intermediate Examples (31-60) covered 40-75% of Ecto functionality:
- Advanced Queries: join, left_join, group_by, having, distinct, lock, select_merge
- Complex Associations: many_to_many, embeds_one, embeds_many, put_assoc, cast_assoc
- Transactions: Repo.transaction, Repo.rollback, Ecto.Multi for composable operations
- Aggregations: group_by with aggregate functions, having for filtered aggregates
- Migrations: Creating tables, adding columns, indexes, foreign keys, composite keys
- Advanced Patterns: Subqueries, fragments, window functions, CTEs, batch inserts
- Multi-Tenancy: Query prefixes for schema-level isolation
- Schemaless Queries: Dynamic table queries without schema definitions
Next Steps: Proceed to Advanced Examples (61-85) for dynamic queries, custom types, performance optimization, and expert patterns (75-95% coverage).