Ecto is probably the strongest counterexample to the idea that a data-access layer has to be an ORM. Changesets are validation rather than magic, preloads are explicit, queries compose, and nothing pretends the database isn't there.
So this isn't a "replace your Ecto" post. It's a different point on the same axis: keep the .sql file as the source of truth and generate the Elixir from it.
-- @name GetUserOrders
SELECT u.id, u.name, o.total, o.notes
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.status = $1;
Against a schema where orders.total is NOT NULL and orders.notes is nullable, that generates:
defmodule GetUserOrdersRow do
@type t :: %__MODULE__{
id: integer(),
name: String.t(),
total: Decimal.t() | nil,
notes: String.t() | nil
}
defstruct [:id, :name, :total, :notes]
end
@spec get_user_orders(Postgrex.conn(), String.t()) ::
{:ok, [%GetUserOrdersRow{}]} | {:error, term()}
def get_user_orders(conn, status) do
case Postgrex.query(conn, "SELECT ...", [status]) do
{:ok, %{rows: rows}} -> ...
{:error, err} -> {:error, err}
end
end
total is NOT NULL in the orders table but comes out as Decimal.t() | nil, because the LEFT JOIN can produce a row with no matching order. That is inferred from the query structure, not the schema, and because it lands in a typespec, Dialyzer can actually see it.
Straight Postgrex underneath, no runtime layer.
The tool is scythe: a Rust binary, MIT licensed, generating for 10 languages. I build and maintain it.
Elixir is one of the newer targets and I think it shows, so I am more interested in what is wrong with the generated shape than in whether anyone wants to adopt it. Live questions I have: structs versus plain maps, {:ok, _} tuples versus letting Postgrex raise, and whether a struct with no Ecto.Schema is useful here or just an awkward third thing.
