Query Support

Lazarus applies soft-delete behavior automatically only when it can interpret an Ecto query source safely. This guide explains which query shapes are supported, which ones are rejected, and where the rules differ across reads, bulk updates, bulk soft-deletes, and bulk hard-deletes.

Terminology

schema-aware source

A schema source like Author or from(author in Author).
A schema-attached tuple source like from(author in {"authors", Author}).

derived raw source

A raw-looking source name that is defined inside the same query, currently through a CTE.
Example:

cte =
  from(author in Author,
    select: %{id: author.id}
  )

query =
  from(row in "visible_authors",
    select: field(row, :id)
  )
  |> with_cte("visible_authors", as: ^cte)

schema-less source

A bare table string with no schema attached, such as from(row in "authors") or join: row in "authors".

Reads

Schema-aware sources

Schema-aware roots and joins are filtered by default.

Repo.all(Author)

from(author in Author, select: author.id)
|> Repo.all()

from(author in {"authors", Author}, select: author.id)
|> Repo.all()

# Soft-deleted posts AND comments are filtered out
from(post in Post,
  join: comment in Comment,
  on: comment.post_id == post.id,
  select: {post.id, comment.id}
)
|> Repo.all()

To include soft-deleted rows, pass with_deleted: true.

Repo.all(Author, with_deleted: true)

from(author in {"authors", Author}, select: author.id)
|> Repo.all(with_deleted: true)

# Soft-deleted posts AND comments are included
from(post in Post,
  join: comment in Comment,
  on: comment.post_id == post.id,
  select: {post.id, comment.id}
)
|> Repo.all(with_deleted: true)

That same with_deleted: true opt applies to schema-aware joined sources, not just query roots.

For outer joins, Lazarus keeps the soft-delete predicate on the join itself, so outer-join semantics are preserved while soft-deleted joined rows stay hidden.

with_deleted: true only controls row visibility. It does not allow query shapes Lazarus cannot inspect. Use allow_raw_sql: true for intentional raw SQL, or allow_schema_less_sources: true for intentional schema-less Ecto sources.

What gets filtered recursively

When Lazarus is active, it does not stop at the query root. It also recurses into schema-aware subqueries and derived queries used inside the rest of the query.

That includes:

joined subqueries
where clauses such as exists(...) and not exists(...)
update expressions in Repo.update_all/3
select expressions
order_by expressions
group_by expressions
having expressions
distinct expressions
window definitions
CTE definitions
combination queries such as union_all

The same with_deleted: true opt disables all of that recursive filtering for the whole operation, which means soft-deleted records will be included.

`where` / `exists(...)`

Schema-aware subqueries inside where are filtered too:

from(location in Location,
  as: :location,
  where:
    exists(
      from(author in Author,
        where: author.location_id == parent_as(:location).id,
        select: 1
      )
    ),
  select: location.id
)
|> Repo.all()

In that example, soft-deleted Author rows do not satisfy the exists(...) check unless you pass with_deleted: true.

`select`, `order_by`, `group_by`, and `having`

The same recursive filtering applies inside expression containers such as select, order_by, group_by, and having.

has_authors_query =
  from(author in Author,
    where: author.location_id == parent_as(:location).id,
    select: 1
  )

from(location in Location,
  as: :location,
  select: %{
    id: location.id,
    has_authors: exists(has_authors_query)
  },
  order_by: [desc: exists(has_authors_query)]
)
|> Repo.all()

has_authors_query =
  from(author in Author,
    where: author.location_id == parent_as(:location).id,
    select: 1
  )

from(location in Location,
  as: :location,
  group_by: exists(has_authors_query),
  having: exists(has_authors_query),
  select: {exists(has_authors_query), count(location.id)}
)
|> Repo.all()

That means soft-deleted rows do not silently leak back in just because the subquery appears in grouping or aggregate-related clauses instead of where.

`distinct` and windows

distinct expressions and window definitions are filtered the same way:

has_authors_query =
  from(author in Author,
    where: author.location_id == parent_as(:location).id,
    select: 1
  )

from(location in Location,
  as: :location,
  distinct: exists(has_authors_query),
  windows: [
    authors: [
      partition_by: exists(has_authors_query),
      order_by: [desc: location.id]
    ]
  ],
  select: {location.id, over(row_number(), :authors)}
)
|> Repo.all()

Joined subqueries

If you join a subquery, Lazarus recurses into the subquery and filters it:

author_subquery =
  from(author in Author,
    select: author
  )

from(location in Location,
  join: author in subquery(author_subquery),
  on: author.location_id == location.id,
  select: author.id
)
|> Repo.all()

CTEs

CTE-backed raw-looking sources are allowed because Lazarus filters the schema-aware query that defines the CTE:

cte =
  from(author in Author,
    select: %{id: author.id}
  )

from(row in "visible_authors",
  select: field(row, :id)
)
|> with_cte("visible_authors", as: ^cte)
|> Repo.all()

This is why derived raw sources are allowed on the read path while schema-less tables are not.

Combination queries

Combination queries are filtered recursively too:

query_a =
  from(author in Author,
    where: author.name == "A",
    select: %{id: author.id}
  )

query_b =
  from(author in Author,
    where: author.name == "B",
    select: %{id: author.id}
  )

from(result in subquery(query_a))
|> union_all(^subquery(query_b))
|> select([result], result.id)
|> Repo.all()

Both branches are filtered unless you opt out with with_deleted: true.

Schema-less Sources

Schema-less roots and joins raise by default.

from(row in "authors",
  select: field(row, :id)
)
|> Repo.all()

from(post in Post,
  join: row in "comments",
  on: field(row, :post_id) == post.id,
  select: {post.id, field(row, :id)}
)
|> Repo.all()

The same rule applies to nested schema-less sources inside subqueries. If Lazarus is recursing through a query and encounters a schema-less source anywhere in that nested structure, the read raises unless you pass allow_schema_less_sources: true or the source is configured as a repo-level bypass.

Use one of these instead:

switch to the schema: from(author in Author)
attach the schema explicitly: from(author in {"authors", Author})
acknowledge the schema-less source with: allow_schema_less_sources: true
configure a third-party source with bypass_schemas or bypass_tables

allow_schema_less_sources: true is the explicit per-query opt-in for schema-less sources. It does not allow fragments or direct SQL calls. Repo-level bypasses are for third-party sources Lazarus should always leave alone. When a bypassed source is the query root, Lazarus leaves the whole Ecto query alone, including fragments. Bypassed schema-less joins are allowed, but a bypassed join does not make unrelated fragments in the rest of the query safe.

Read more about repo-level bypasses in Bypassing schemas and tables.

from(row in "authors",
  select: field(row, :id)
)
|> Repo.all(allow_schema_less_sources: true)

from(post in Post,
  join: row in "comments",
  on: field(row, :post_id) == post.id,
  select: {post.id, field(row, :id)}
)
|> Repo.all(allow_schema_less_sources: true)

Fragments and direct SQL

Lazarus rejects Ecto fragments by default because it cannot safely inspect arbitrary SQL or attach soft-delete predicates inside SQL it does not understand.

That includes fragments in query expressions, fragment-backed roots and joins, and fragment-backed CTE definitions.

# Raise
from(author in Author,
  where: fragment("lower(?) = ?", author.name, ^name)
)
|> Repo.all()

Use schema-aware Ecto expressions when possible. If the raw SQL is intentional and you have no other options, pass allow_raw_sql: true.

# Success
from(author in Author,
  where: fragment("lower(?) = ?", author.name, ^name)
)
|> Repo.all(allow_raw_sql: true)

Direct SQL calls through the Repo also require allow_raw_sql: true.

Repo.query!("select * from authors", [], allow_raw_sql: true)
Repo.stream("select * from authors", [], allow_raw_sql: true)

Direct calls to Ecto.Adapters.SQL.query/4 or Ecto.Adapters.SQL.stream/4 bypass Lazarus entirely.

Bulk Update Queries

Repo.update_all/3 is the update API that accepts Ecto queries. It uses the same recursive filtering model as reads: schema-aware roots, joins, subqueries, CTE definitions, combinations, selected return values, and Ecto update expressions are filtered by default. Passing with_deleted: true disables that filtering for the whole bulk update.

For single-row Repo.update* and loaded Repo.insert_or_update* behavior, see Fetch, Update, and Delete APIs.

Recursive filtering

Suppose one post has an active comment and another post has only soft-deleted comments:

active_comment_exists =
  from(comment in Comment,
    where: comment.post_id == parent_as(:post).id,
    select: 1
  )

from(post in Post,
  as: :post,
  where: exists(active_comment_exists)
)
|> Repo.update_all(set: [status: "has_comments"])

Only the post with an active comment is updated. Passing with_deleted: true also lets the post with only soft-deleted comments match.

Schema-less update targets

Schema-less update targets raise by default because Lazarus cannot identify which deleted-at field it would need to check. Use one of these instead:

switch to the schema: from(post in Post)
attach the schema explicitly: from(post in {"posts", Post})
configure a third-party source with bypass_schemas or bypass_tables

Only acknowledge a schema-less update target when the query is intentional and it is acceptable for Lazarus to leave that target unfiltered:

from(row in "posts", where: field(row, :author_id) == ^author_id)
|> Repo.update_all([set: [status: "archived"]], allow_schema_less_sources: true)

allow_schema_less_sources: true preserves default Ecto behavior for the update target, allowing schema-less query shape but cannot add deleted_at IS NULL, so matching soft-deleted rows are updated too, essentially removing soft-deleted row filtering that Lazarus provides.

Raw SQL in bulk updates

Fragments in Repo.update_all/3 queries raise by default, including fragments inside Ecto update expressions. Lazarus raises because raw SQL can hide table references or predicates it cannot inspect, so it cannot guarantee that soft-deleted rows are skipped.

# Raises by default
from(post in Post,
  where: fragment("lower(?) = ?", post.title, ^title)
)
|> Repo.update_all(set: [status: "archived"])

Use schema-aware Ecto expressions when possible. If the raw SQL is intentional and you have no other option, pass allow_raw_sql: true.

from(post in Post,
  where: fragment("lower(?) = ?", post.title, ^title)
)
|> Repo.update_all([set: [status: "archived"]], allow_raw_sql: true)

The raw SQL opt-in acknowledges the fragment, but Lazarus still skips soft-deleted rows in schema-aware sources unless with_deleted: true is also passed.

Bulk Soft-Delete Queries

Repo.soft_delete_all/2 is the soft-delete API that accepts Ecto queries. Its root must be schema-aware because Lazarus needs schema metadata to know which deleted-at field and deletion-reason field to update. For that reason, allow_schema_less_sources: true does not allow a schema-less root:

# Success
Repo.soft_delete_all(Post)
Repo.soft_delete_all(from(post in Post, where: post.author_id == ^author_id))
Repo.soft_delete_all(
  from(post in {"posts", Post}, where: post.author_id == ^author_id)
)

# Raises
from(row in "posts", where: field(row, :author_id) == ^author_id)
|> Repo.soft_delete_all()

# Still raises; allow_schema_less_sources does not apply to the root
from(row in "posts", where: field(row, :author_id) == ^author_id)
|> Repo.soft_delete_all(allow_schema_less_sources: true)

Prefer schema-aware joins and nested sources for the same reason.

# Success (schema-aware root and join)
from(post in Post,
  join: flag in PostFlags,
  on: field(flag, :post_id) == post.id,
  where: field(flag, :name) == "expired"
)
|> Repo.soft_delete_all()

# Raises because the join is schema-less
from(post in Post,
  join: flag in "post_flags",
  on: field(flag, :post_id) == post.id,
  where: field(flag, :name) == "expired"
)
|> Repo.soft_delete_all()

If a schema-less join or nested source is unavoidable, allow_schema_less_sources: true acknowledges that Lazarus cannot inspect that source. It does nothing for the root query, but still applies inside a soft-delete query, so it can acknowledge schema-less joins and nested sources as long as the root remains schema-aware:

# Success (schema-less join is acknowledged explicitly)
from(post in Post,
  join: flag in "post_flags",
  on: field(flag, :post_id) == post.id,
  where: field(flag, :name) == "expired"
)
|> Repo.soft_delete_all(allow_schema_less_sources: true)

Keep in mind that allowing schema-less sources does not make them safe. Lazarus is still unable to inspect and apply soft-delete filtering to them.

Fragments inside a bulk soft-delete query raise by default, just like read and bulk-update fragments. Pass allow_raw_sql: true only when the fragment is intentional. Fragment-backed roots still raise even with allow_raw_sql: true because the root must be schema-aware.

# Success (fragment predicate is acknowledged explicitly)
from(post in Post,
  where: fragment("lower(?) = ?", post.title, ^title)
)
|> Repo.soft_delete_all(allow_raw_sql: true)

# Raises because the root is not schema-aware
from(row in fragment("select * from posts"))
|> Repo.soft_delete_all(allow_raw_sql: true)

Like Ecto bulk write targets, Repo.soft_delete_all/2 also cannot use a subquery(...) as the query root:

# Raises
inner =
  from(post in Post,
    where: post.id == ^id,
    select: post
  )

from(post in subquery(inner))
|> Repo.soft_delete_all()

That raises because the root is a derived subquery instead of the table being updated. This is only a root-target restriction. Subqueries inside where, join, exists(...), CTEs, and other nested query positions remain supported and are filtered recursively.

Bulk Hard-Delete Queries

Repo.hard_delete_all/2 delegates to Ecto's physical bulk-delete path. Lazarus does not apply soft-delete filtering and does not require schema metadata, so schema-aware roots and schema-less table roots are allowed as long as Ecto and the database support the delete query.

from(author in Author, where: author.id == ^id)
|> Repo.hard_delete_all()

from(row in "authors", where: field(row, :id) == ^id)
|> Repo.hard_delete_all()

For single-row Repo.hard_delete* behavior, see Fetch, Update, and Delete APIs.

← Previous Page Fetch, Update, and Delete APIs

Next Page → Cascade Soft-Deletes