I've used my share of data access libraries and patterns (e.g. hibernate, activerecord, ecto, ...). The only time I've been happy is when I use raw SQL for non-

I've used my share of data access libraries and patterns (e.g. hibernate, activerecord, ecto, ...). The only time I've been happy is when I use raw SQL for non-dynamic SQL and a lightweight query builder for everything else. I feel like I always run into some thing that at best isn't intuitive to express/read and at worse, cannot be expressed. If I remember correctly, when I was learning Elixir/Ecto, https://github.com/elixir-ecto/ecto/issues/1616 https://github.com/elixir-ecto/ecto/issues/1616 issue and the lack of lateral join support caused me issues. Want to create a user? "insert into users (id, name, status) values ($1, $2, $3)" Our query builder takes pretty raw SQL fragments: q = Query.new() |> Query.select("u.id, u.name") |> Query.from("users u") |> Query.from("join authentications a using (id)") q = case params[:status] do nil -> q status -> Query.where(q, "u.status", :eq, status) end rows = Query.rows!(q) Looking through the docs, in Ecto, that join would be: |> join(:inner, [u], a in Authentiction, on: u.id == a.id) Which I find needlessly abstract. (I will admit that if you really do need to support multiple databases, the abstraction is useful).