EctoSpect

Analyzes PostgreSQL query plans during ExUnit tests and fails tests on bad query patterns. Inspired by Credo, squawk, and excellent_migrations.

What it detects

Runtime rules (per query, via EXPLAIN ANALYZE)

Migration rules (once per suite, via AST analysis)

Installation

With Igniter (recommended)

mix igniter.install ecto_spect

This automatically patches test/test_helper.exs and your DataCase.

Manual

Add to mix.exs:

def deps do
  [
    {:ecto_spect, "~> 0.1", only: [:test, :dev]}
  ]
end

Then run:

mix deps.get
mix ecto_spect.install

Phoenix project setup

1. test/test_helper.exs

Call EctoSpect.setup/1beforeExUnit.start/0:

EctoSpect.setup(
  repos: [MyApp.Repo],
  thresholds: [
    seq_scan_min_rows: 100,   # rows before seq scan is flagged
    n_plus_one: 5,            # repeated queries before N+1 is flagged
    max_indexes: 10,          # max indexes per table
    estimation_error_ratio: 10 # plan/actual rows ratio threshold
  ],
  ignore_rules: [EctoSpect.Rules.MissingLimit],
  filter_parameters: [:password, :token]  # redact from diagnostics
)
ExUnit.start()

2. test/support/data_case.ex

Add use EctoSpect.Case inside the quote do block:

defmodule MyApp.DataCase do
  use ExUnit.CaseTemplate

  using do
    quote do
      use EctoSpect.Case, repo: MyApp.Repo  # <-- add this

      import Ecto
      import Ecto.Changeset
      import Ecto.Query
      import MyApp.DataCase
      alias MyApp.Repo
    end
  end

  setup tags do
    MyApp.DataCase.setup_sandbox(tags)
  end

  def setup_sandbox(tags) do
    pid = Ecto.Adapters.SQL.Sandbox.start_owner!(MyApp.Repo, shared: not tags[:async])
    on_exit(fn -> Ecto.Adapters.SQL.Sandbox.stop_owner(pid) end)
  end
end

3. Optional — SQL comments in dev/test logs

Add caller info as SQL comments to identify slow queries in PG logs:

# lib/my_app/repo.ex
defmodule MyApp.Repo do
  use Ecto.Repo, otp_app: :my_app, adapter: Ecto.Adapters.Postgres

  if Mix.env() in [:dev, :test] do
    @impl true
    def default_options(_op), do: [stacktrace: true]

    @impl true
    def prepare_query(_op, query, opts) do
      comment = EctoSpect.SqlAnnotator.build_comment(opts)
      {query, [comment: comment, prepare: :unnamed] ++ opts}
    end
  end
end

Queries in logs will look like:

/* ecto_spect: lib/my_app/accounts.ex:42 MyApp.Accounts.list_users/0 */
SELECT u0."id", u0."email" FROM "users" AS u0

Output

When a violation is found the test fails with a Credo-style message:

EctoSpect found 1 violation(s):

  [E] Sequential scan on `users` touching 1,432 rows — EctoSpect.Rules.SequentialScan

  Query:
    SELECT u0."id", u0."email" FROM "users" AS u0 WHERE (u0."active" = $1)

  Advice:
    Add an index on the filtered column(s).
    Filter applied: (active = true)

    Example:
      CREATE INDEX CONCURRENTLY idx_users_active ON users(active);

    For boolean columns, use a partial index:
      CREATE INDEX CONCURRENTLY idx_users_active ON users(id) WHERE active = true;

  Caller: lib/my_app/accounts.ex:42

  ──────────────────────────────────────────────────────────

Custom rules

Implement EctoSpect.Rule:

defmodule MyApp.Rules.NoFullTableExport do
  @behaviour EctoSpect.Rule

  def name, do: "no-full-table-export"
  def description, do: "Prevents SELECT * without WHERE on large tables"

  def check(nodes, entry, _thresholds) do
    top = hd(nodes)
    if top.node_type == "Seq Scan" and not String.contains?(entry.sql, "WHERE") do
      [%EctoSpect.Violation{
        rule: __MODULE__,
        severity: :error,
        message: "Full table export on `#{top.relation_name}`",
        advice: "Add a WHERE clause or use Repo.stream/2 with pagination.",
        entry: entry,
        details: %{}
      }]
    else
      []
    end
  end
end

Register in EctoSpect.setup/1:

EctoSpect.setup(
  repos: [MyApp.Repo],
  rules: EctoSpect.Config.default_rules() ++ [MyApp.Rules.NoFullTableExport]
)

How it works

1. Telemetry hook — attaches to [:your_app, :repo, :query] events
2. Query capture — stores {sql, params, stacktrace} per test PID (async-safe via $callers)
3. Migration scan — once per suite, parses migration files with Code.string_to_quoted!/1 and runs AST rules
4. EXPLAIN runner — after each test, runs EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) via a dedicated Postgrex connection separate from the Ecto sandbox
5. Plan parser — normalizes the JSON plan tree into a flat node list
6. Rules engine — each rule inspects nodes/SQL and returns violations
7. Formatter — prints Credo-style output and raises ExUnit.AssertionError

The EXPLAIN connection is separate from the Ecto sandbox so it works correctly with async: true tests.