LAlatch·Sep 15, 2021
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 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 -> Que
Read the rest
ry.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).