Lesson 5: Ecto queries

Reading and writing database code in Speechwave

Primary source: Ecto.Query docs, the definitive reference for query syntax. Run mix hex.docs offline ecto to browse with search.

Two layers: Repo and Query

Ecto splits database work into two distinct parts:

Repo: executes operations against the database. It's the only module that talks to SQLite/Postgres.
Query: builds the SQL as a data structure. It doesn't touch the database until you hand it to Repo.

This separation means you can build a query, inspect it, compose it with other queries, and only execute it when ready. ActiveRecord does something similar under the hood, but Ecto makes it explicit.

Repo: the seven operations

Almost every database call in Speechwave uses one of these Repo functions:

Function	Does	Returns	ActiveRecord equivalent
`Repo.get(Schema, id)`	Find by primary key	struct or `nil`	`find_by(id:)`
`Repo.get!(Schema, id)`	Find by primary key	struct or raises	`find(id)`
`Repo.get_by(Schema, clauses)`	Find by column(s)	struct or `nil`	`find_by(col: val)`
`Repo.get_by!(Schema, clauses)`	Find by column(s)	struct or raises	`find_by!(col: val)`
`Repo.one(query)`	Run query, expect 0-1 result	struct or `nil`	`.first`
`Repo.all(query)`	Run query, return all results	list	`.to_a` / `.all`
`Repo.aggregate(query, :count)`	Run aggregate	integer	`.count`
`Repo.insert(changeset)`	Insert a record	`{:ok, struct}` or `{:error, changeset}`	`.save`
`Repo.update(changeset)`	Update a record	`{:ok, struct}` or `{:error, changeset}`	`.save`
`Repo.delete(struct)`	Delete a record	`{:ok, struct}` or `{:error, changeset}`	`.destroy`
`Repo.insert!(changeset)`	Insert or raise	struct	`.save!`
`Repo.delete_all(query)`	Delete matching records	`{count, nil}`	`.delete_all`
`Repo.preload(struct, assocs)`	Load associations	struct with assocs loaded	eager loading
`Repo.transaction(fn)`	Wrap in a transaction	`{:ok, result}` or `{:error, reason}`	`transaction { }`

Key insight: The ! convention: functions ending in ! raise on failure instead of returning {:error, ...}. Use Repo.get! when a missing record is a bug; use Repo.get when it's an expected possibility (like a user typo).

Here's how Speechwave uses the simple Repo functions:

lib/speechwave/talks.ex:46

# Find by a single column, returns nil if not found
def get_talk_by_slug(slug), do: Repo.get_by(Talk, slug: slug)

lib/speechwave/talks.ex:30-31

# Find by multiple columns, raises if not found
# The user_id check ensures you can't access another user's talk
def get_talk!(scope, id) do
  Repo.get_by!(Talk, id: id, user_id: scope.user.id)
end

Rails equivalent:

Talk.find_by(slug: slug)              # Repo.get_by
Talk.find_by!(id: id, user_id: uid)   # Repo.get_by!

The `from` query syntax

For anything beyond a simple get, you build queries with from. This is where Ecto diverges most from ActiveRecord.

Basic query

lib/speechwave/talks.ex:27

Repo.all(from t in Talk, where: t.user_id == ^user.id, order_by: [desc: t.inserted_at])

Breaking this down:

Part	Meaning
`from t in Talk`	"From the talks table, bind each row to `t`"
`where: t.user_id == ^user.id`	"Where user_id equals this value"
`order_by: [desc: t.inserted_at]`	"Order by inserted_at descending"
`Repo.all(...)`	"Execute and return all results as a list"

Rails: Talk.where(user_id: user.id).order(inserted_at: :desc)

The pin operator `^` in queries

The ^ is the pin operator, covered in Lesson 1. In pattern matching, it means "use this variable's existing value instead of rebinding it." In Ecto queries, it serves the same purpose. It tells Ecto "this is an Elixir value to inject as a parameter, not a database column name."

where: t.user_id == ^user.id
#                    ^ "inject the VALUE of user.id here"

where: t.user_id == t.some_other_column
#                   no ^ means "compare to another column"

Important: Forgetting the ^ is a common mistake. Without it, Ecto thinks you're referencing another database column. You'll get a compile error or unexpected results.

Query with join and aggregation

This is the most complex query in Speechwave: listing sessions with their reaction counts.

lib/speechwave/talks.ex:95-103

def list_sessions(talk_id) do
  from(s in TalkSession,
    where: s.talk_id == ^talk_id,
    left_join: r in assoc(s, :reactions),
    group_by: s.id,
    select: %{session: s, reaction_count: count(r.id)},
    order_by: [desc: s.started_at, desc: s.id]
  )
  |> Repo.all()
end

Step by step:

Clause	SQL equivalent	Purpose
`from s in TalkSession`	`FROM talk_sessions s`	Source table, bound to `s`
`where: s.talk_id == ^talk_id`	`WHERE s.talk_id = ?`	Filter to this talk
`left_join: r in assoc(s, :reactions)`	`LEFT JOIN reactions r ON ...`	Join reactions (left join keeps sessions with zero reactions)
`group_by: s.id`	`GROUP BY s.id`	One row per session
`select: %{session: s, reaction_count: count(r.id)}`	`SELECT s.*, COUNT(r.id)`	Return a map with the session and its count
`order_by: [desc: s.started_at, desc: s.id]`	`ORDER BY s.started_at DESC, s.id DESC`	Most recent first

Rails:

TalkSession
  .where(talk_id: talk_id)
  .left_joins(:reactions)
  .group(:id)
  .select("talk_sessions.*, COUNT(reactions.id) AS reaction_count")
  .order(started_at: :desc, id: :desc)

Very similar structure, but Ecto's select returns a map (%{session: ..., reaction_count: ...}) rather than attaching a virtual attribute to the model. This is why in Lesson 4 the template destructures with for %{session: session, reaction_count: reaction_count} <- @sessions.

Custom select shapes

The select clause controls what Ecto returns. Without it, you get schema structs. With it, you can return maps, tuples, or specific fields:

lib/speechwave/reactions.ex:28-35

from(r in Reaction,
  where: r.talk_session_id == ^session_id,
  group_by: [r.slide_number, r.emoji],
  select: %{slide_number: r.slide_number, emoji: r.emoji, count: count(r.id)},
  order_by: [asc: r.slide_number]
)
|> Repo.all()

This returns a list of plain maps like %{slide_number: 1, emoji: "👏", count: 42}, not %Reaction{} structs. The select shape determines the shape of the results.

Associations: `preload` vs `join`

In Rails, associations load eagerly or lazily depending on how you access them. In Ecto, associations are never loaded automatically. You must be explicit.

lib/speechwave/talks.ex:49-50

def get_talk_with_owner(slug) do
  Repo.one(from t in Talk, where: t.slug == ^slug, preload: [:user])
end

The preload: [:user] loads the associated user in a separate query. Without it, talk.user would be an Ecto.Association.NotLoaded struct, not nil, but a special placeholder that tells you "this wasn't loaded."

You can also preload after fetching:

lib/speechwave/accounts.ex:223

Repo.preload(identity, :user).user

Key insight: Ecto's explicit loading prevents N+1 queries by design. In Rails, you might accidentally trigger talk.user inside a loop and generate dozens of queries. In Ecto, if you didn't preload it, you get an error, not a silent performance problem.

`fragment`: raw SQL escape hatch

When Ecto's query syntax can't express something, fragment lets you drop to raw SQL:

lib/speechwave/talks.ex:131

# threshold_seconds is the "full session" cutoff; see Plans and limits for the actual value
where: fragment("(strftime('%s', ?) - strftime('%s', ?)) > ?", s.ended_at, s.started_at, threshold_seconds)

This uses SQLite's strftime to calculate session duration in seconds and compares it against a threshold (see Plans and limits for what that threshold actually is). The ? placeholders are filled with the Ecto bindings, keeping it safe from SQL injection.

Rails equivalent:

.where("(strftime('%s', ended_at) - strftime('%s', started_at)) > ?", threshold_seconds)

Transactions and `with`

When multiple database operations must succeed or fail together:

lib/speechwave/accounts.ex:220-235

defp oauth_upsert(provider, uid, email) do
  case Repo.get_by(UserIdentity, provider: provider, uid: uid) do
    %UserIdentity{} = identity ->
      Repo.preload(identity, :user).user

    nil ->
      with {:ok, user} <- register_or_get_user_by_email(email),
           {:ok, _identity} <-
             %UserIdentity{}
             |> UserIdentity.changeset(%{provider: provider, uid: uid, user_id: user.id})
             |> Repo.insert() do
        user
      else
        {:error, reason} -> Repo.rollback(reason)
      end
  end
end

The with construct chains operations that might fail. Each <- clause pattern matches the result. If it matches {:ok, value}, the value is bound and execution continues. If any clause doesn't match, it jumps to the else block.

Ruby equivalent (roughly):

user = register_or_get_user_by_email(email)
raise ActiveRecord::Rollback unless user

identity = UserIdentity.create!(provider: provider, uid: uid, user_id: user.id)
user
rescue => e
  raise ActiveRecord::Rollback

The with construct is cleaner. Each step explicitly declares what success looks like, and failure handling is centralized in the else block. It's called from within Repo.transaction (line 207) so Repo.rollback aborts the whole transaction.

Key insight: with is not specific to database code. It works with any {:ok, value} / {:error, reason} pattern. But you'll see it most often in context modules where multiple Repo operations need to succeed together.

Practice

Question 1

What does the ^ do in this query?

from t in Talk, where: t.user_id == ^user.id

Negates the comparison (NOT equal) Casts user.id to an integer Injects the Elixir variable's value into the query as a parameter References the primary key of the users table

Right. The ^ (pin) tells Ecto: "use the current value of user.id as a query parameter." Without it, Ecto would look for a column called user on the talks table. This is the same pin operator from pattern matching, applied in a query context: "don't rebind, use the existing value."

This is the pin operator from Lesson 1, applied in a query context. It tells Ecto to use an external Elixir value, not a database column. Try again.

Question 2

What's the difference between Repo.get(Talk, 42) and Repo.get!(Talk, 42)?

get returns a list; get! returns a single record get returns nil if not found; get! raises an exception get is read-only; get! locks the row for update get! bypasses validations; get does not

Correct. This is a convention across Elixir: ! functions raise on failure. Use get! when a missing record means something is wrong (a bug, a broken link). Use get when absence is expected (user searching for a slug that doesn't exist). Same pattern applies to insert/insert!, update/update!, etc.

The ! suffix in Elixir functions signals how failure is handled, not what operation is performed. Try again.

Question 3

Why does Speechwave use left_join instead of join in list_sessions?

left_join: r in assoc(s, :reactions)

left_join is faster than join for large tables left_join loads the association automatically left_join filters out sessions with too many reactions left_join keeps sessions with zero reactions; join would exclude them

Exactly. An inner join only returns rows where both sides match. Sessions with no reactions would disappear. A left_join keeps all sessions and fills in NULL for the reaction side when there are none, so count(r.id) returns 0 for those sessions. This is standard SQL behavior, the same in any language.

Think about what happens to a session that has no reactions yet. What does each type of join do with rows that have no match on the joined side? Try again.

Question 4

What happens if you access talk.user without preloading the :user association?

It returns nil It automatically runs a query to fetch the user (lazy loading) It returns an Ecto.Association.NotLoaded struct, which will cause an error if you try to use it It raises a compile-time error

Right. Ecto never lazy-loads. Instead, unloaded associations are %Ecto.Association.NotLoaded{}, a struct that will fail loudly if you try to access fields on it. This is intentional: it forces you to be explicit about what data you need, preventing accidental N+1 queries. In practice, you'll see the error "cannot access... association was not loaded" and know you need to add a preload.

Ecto does not have lazy loading. That's a deliberate design choice to prevent N+1 queries. The value isn't nil either. Think about what Ecto puts there as a placeholder. Try again.

Your next step

Open lib/speechwave/accounts.ex and read through every function. You now have the tools to understand all of them:

Pattern matching selects which clause runs
Pipes build changeset and query chains
Context modules organize the functions
Repo executes the database operations
with chains fallible operations

For a challenge, read lib/speechwave/accounts/user_token.ex. It has the most complex queries in the project (token verification with joins and time-based expiry). If you can read that file, you can read any Ecto code in Speechwave.

Previous: LiveView Lifecycle · Reference: Glossary
Next: Testing with ExUnit