I’m very excited for the new release of sql, which comes with best in class performance and memory usage, with a minimum of 50x compared to Ecto.
The test suite has also gotten an overhaul with over 900 test, testing the conformance of SQL 2016.
sql does now also supports prepared queries.
Checkout the sql/CHANGELOG.md at main · elixir-dbvisor/sql · GitHub for more!
The time has come to merry Ecto and SQL, so I’m asking you for your feedback what do you think about this:
iex(1)> Enum.into(~SQL[from users select *], User.new())
16:31:00.046 [debug] QUERY OK db=0.4ms decode=0.7ms queue=1.6ms
select * from users []
[
#User<
__meta__: #Ecto.Schema.Metadata<:built, "users">,
id: 1,
name: "john",
age: 18,
...
>
]
defmodule User do
use Ecto.Schema
schema "users" do
field :name, :string
field :age, :integer, default: 0
field :password, :string, redact: true
end
def new, do: %User{}
defimpl Collectable do
def into(%struct{__meta__: %Ecto.Schema.Metadata{}} = schema) do
{[], fn rows, _acc -> Enum.map(rows, &(Ecto.Changeset.apply_action!(Ecto.Changeset.cast(schema, &1, struct.__schema__(:fields)), :insert))) end}
end
end
end
defmodule SQL.Repo do
use Ecto.Repo, otp_app: :sql, adapter: Ecto.Adapters.Postgres
defimpl Enumerable, for: SQL do
def count(_enumerable) do
{:error, __MODULE__}
end
def member?(_enumerable, _element) do
{:error, __MODULE__}
end
def reduce(%SQL{} = enumerable, _acc, _fun) do
{sql, params} = SQL.to_sql(enumerable)
result = SQL.Repo.query!(sql, params)
{:done, Enum.map(result.rows, &Map.new(Enum.zip(result.columns, &1)))}
end
def slice(_enumerable) do
{:error, __MODULE__}
end
end
end
Obvious above is a minimal and unoptimized example that is leveraging Ecto.Changeset for the heavy lifting.
My 2 cents follow..
It’s neat but I’m afraid it asks too much of the reader. At first glance it appears to be a pure function but there are side effects deep in the protocol. I usually prefer to be able to point to obvious code where the DB work happens.
Yeah, the Enumerable and Collectable protocols are definitely the more advanced concepts in Elixir, and does require a lot of knowledge for new users. The same issue you point out here is true for any IO work even file streams.
Protocol implementations are of course arbitrary by design but it strikes me as odd that Enum.into(..., %User{}) returns anything other than a %User{}, the same type of thing we’re collecting into. Even the following which we tend to use only for side effects returns the collectable struct:
iex> Enum.into(~w[foo bar baz], IO.stream())
foobarbaz%IO.Stream{...}
That being said, at the moment I don’t have any alternative suggestions. 
One thing about your implementation though, instead of Ecto.Changeset.apply_action!, I think you can use Repo.load/2. It should perform the same type “casting” (actually Ecto.Type.load/2) as when receiving the data from the usual Repo.all(struct):
iex> Repo.all(from u in "users", select: %{id: u.id, email: u.email}) |> Enum.map(&Repo.load(User, &1))
[
%User{
__meta__: #Ecto.Schema.Metadata<:loaded, "users">,
id: 1,
email: "alice@example.com",
inserted_at: nil,
updated_at: nil
}
]
Enum.each(~SQL"...", fun) is really cool, I love stuff like that. By implementing Enumerable for %SQL{} ourselves we need to pick a particular repo which honestly is not that big of a deal, we tend to only have one anyway but still, it feels limiting.
WDYT about this?
sql = ~SQL"..."
- Enum.each(sql, fun)
+ Repo.all(sql) |> Enum.each(fun)
i.e. instead of users implementing Enumerable themselves, SQL implements Ecto.Queryable!
The only alternative I could thing of would be a callback for the reduce in the SQL enumerable protocol.
iex(1)> Enum.to_list(~SQL[from users select *])
16:31:00.046 [debug] QUERY OK db=0.4ms decode=0.7ms queue=1.6ms
select * from users []
[
#User<
__meta__: #Ecto.Schema.Metadata<:built, "users">,
id: 1,
name: "john",
age: 18,
...
>
]
But that could only work if Ecto.Queryable would produce the SQL struct, so we know what structure it should be casted to.
oops, implementing Ecto.Queryable won’t work because it’s, well, generating SQL off Ecto.Query but the whole point is we already have SQL. Nevermind!
Agree, the super power here is the Enum protocol. And I definitely see a path forward for this, it’s just about finding the sweat spot of where to store and retrieve the essential information to query and load the result set.
Today SQL already leveraging using to set adapters, and this could be leveraged for Repo etc.
But I’m still not sure what the best approach is for a clean API.
I even thought about using sigil modifiers, but not sure if that is to abusive
iex(1)> Enum.to_list(~SQL[from users select *]User)
16:31:00.046 [debug] QUERY OK db=0.4ms decode=0.7ms queue=1.6ms
select * from users []
[
#User<
__meta__: #Ecto.Schema.Metadata<:built, "users">,
id: 1,
name: "john",
age: 18,
...
>
]