Bylaw Ecto Query - validate ecto queries before running them

News Announcing
, , ,
1

Hi all,

I’ve released bylaw_ecto_query, a small OSS library for validating prepared Ecto.Query structs before they run.

Why I wrote it

Imagine you ship an API endpoint that does not order the results determinisitcally. A customer sees inconsistent results, and need to be bothered enough to write support ticket. Now the issue has to be prioritised, investigated, fixed, reviewed, deployed, and communicated back, involving multiple stakeholders in the process.

You can test for this, but then you need to remember that deterministic ordering is the convention, remember to write a test for each relevant endpoint or query, and keep doing that consistently. With bylaw_ecto_query, you define that rule once as an invariant and have it checked wherever the relevant query shape appears.

The package makes application-specific query rules explicit and easy to enforce from c:Ecto.Repo.prepare_query/3. It is intended for teams that want checks around query safety, tenancy, visibility, ordering, joins, and similar conventions.

This is different from source-level linting tools like Credo or security-focused tools like Sobelow. Those inspect source code. bylaw_ecto_query validates the prepared %Ecto.Query{} struct at the repo boundary, which means it catches queries built dynamically at runtime that no source-level tool can see.

This is part of a small family of Bylaw packages I’m writing to help encode project conventions around application code, database schemas, and queries. The general idea is to make those conventions executable so they are easier for both humans and AI coding agents to follow consistently.

I’m still very much experimenting with the shape of these packages and how far I can push the boundaries of what can be validated. The checks aren’t meant to catch all cases, but help us steer the code in some desired direction.

Would especially appreciate feedback around:

  • how easy it is to set up
  • how useful the checks are
  • how many false positives or false negatives you encounter

Example setup

# config/dev.exs and config/test.exs
config :my_app, :bylaw, validate_ecto_queries?: true

# config/prod.exs
config :my_app, :bylaw, validate_ecto_queries?: false

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

  @query_checks [
    Bylaw.Ecto.Query.Checks.RequiredOrder,
    {Bylaw.Ecto.Query.Checks.MandatoryWhereKeys, keys: [:organization_id]}
  ]

  @impl Ecto.Repo
  def prepare_query(operation, query, opts) do
    if bylaw_ecto_query_enabled?() do
      validate_query!(operation, query)
    end

    {query, opts}
  end

  defp validate_query!(operation, query) do
    case Bylaw.Ecto.Query.validate(operation, query, @query_checks) do
      :ok -> :ok
      {:error, issues} -> raise Bylaw.Ecto.Query.Issue.format_many(issues)
    end
  end

  defp bylaw_ecto_query_enabled? do
    :my_app
    |> Application.get_env(:bylaw, [])
    |> Keyword.get(:validate_ecto_queries?, false)
  end
end

Included checks

Check Description
CartesianJoins Prevents explicit cartesian joins.
ConflictingWherePredicates Detects root where predicates that cannot all be satisfied.
DateDatetimeMixedComparisons Catches date fields compared to datetime fields without explicit truncation.
DeterministicOrder Requires ordered queries to include the root schema primary key.
DuplicateJoins Detects repeated equivalent joins.
EmptyInPredicates Catches root where predicates that rely on empty in lists.
ExplicitVisibilityPredicates Requires configured visibility-sensitive fields to be constrained explicitly.
HalfOpenTemporalIntervals Requires temporal interval predicates to use half-open ranges.
HardDeleteOnSoftDeleteSchema Prevents delete_all against schemas with soft-delete fields.
LeftJoinWherePredicates Catches root where predicates that null-reject left_join bindings.
MandatoryJoinKeys Requires explicit schema joins to preserve configured mandatory keys.
MandatoryWhereKeys Requires root where predicates to reference configured keys.
ManualJoinInsteadOfAssoc Encourages assoc/2 when a root association exists.
NamedBindings Requires query expressions to use named binding aliases.
OffsetWithoutLimit Prevents offset without limit.
RequiredOrder Requires order_by for query shapes that need stable row order.
UnboundedDeletes Requires delete_all queries to be bounded.
UnboundedUpdates Requires update_all queries to be bounded.
UtcDatetimeNaiveComparisons Catches UTC datetime fields compared with NaiveDateTime values.

Caveat

This package inspects prepared %Ecto.Query{} structs. Ecto exposes Ecto.Query.t(), but the internal shape of query expressions is not a stable extension API, so enabled checks may need to be reviewed when upgrading Ecto.

That tradeoff is acceptable for me for now. Partly because of Hyrum’s Law, and partly because I think the practical benefit can outweigh the maintenance cost.

Next steps

One thing I’m considering next is adding more configuration so checks can run only for certain schemas and/or tables.

Links

Install with:

def deps do
  [
    {:bylaw_ecto_query, "~> 0.1.0"}
  ]
end

HexDocs:

Hex:

GitHub:

12 Likes
2

Love it l, really interesting approach

1 Like