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,
...
>
]
I think an SQL + Ecto integration that allows control to which of multiple Repos the Query is send, would be valuable as it allows adopting the SQL + Ecto integration in brownfield projects.
Our use case is a data dashboard that pulls data from multiple, already existing, databases. In such a brownfield context, we find DBAs and peer developers that are not experienced in Elixir/Ecto already, and allowing them to contribute to a project with SQL queries, eliminating the need to translate them to Ecto.Query, would be a real boost and lower the barrier for Elixir adoption.
Your work is very interresting and relevant.
Yeah, the simplest approach for that would be to deprecate Repo.query/2 in favor of Repo.query/1.
This would allow any query executed by ecto rather that is an Ecto.Query or SQL to be prepared and optimized by the database.
What is still not all clear is how to upcast and downcast schemas, Ecto use positional references to know which column belongs to which fields or associations to map on the result set.
Today Ecto’s datamapper is entirely tied to Ecto.Query. Decoupling this would allow for better optimization and increase the maintainability of Ecto IMO.
@Schultzer I’m not sure if I’m missing something, but I’m trying to use the ~SQL sigil with Ecto repo query function and i’m getting some errors. Basically, I have this code:
sql = ~SQL"""
select distinct on (e.id) e.id as row_id
from "pacman.public".entities as e
join "pacman.public".records as r on r.grantee_entity_id = e.id or r.grantor_entity_id = e.id
join "pacman.public".properties as p on p.rebuilt_id = r.rebuilt_id
where p.id > {{id}}
and e.status = 'done'
"""
I want to use it with Repo.query, so I convert it using to_sql:
{query, params} = to_sql(sql)
Repo.query(query, params)
The issue I’m seeing is that the query replaces the {{id}} with a ? instead of $1:
iex(pacman@node1.backend.core)24> to_sql(sql)
{"select distinct on (e.id) e.id as row_id from \"pacman.public\".entities as e join \"pacman.public\".records as r on r.grantee_entity_id = e.id or r.grantor_entity_id = e.id join \"pacman.public\".properties as p on p.rebuilt_id = r.rebuilt_id where p.id > ? and e.status = 'done'",
["019721cc-7271-7364-9bde-b8431d782359"]}
And, because of that, the query call fails:
iex(pacman@node1.backend.core)26> Core.Repo.query(query, params)
15:30:48.657 [debug] QUERY ERROR db=0.0ms
select distinct on (e.id) e.id as row_id from "pacman.public".entities as e join "pacman.public".records as r on r.grantee_entity_id = e.id or r.grantor_entity_id = e.id join "pacman.public".properties as p on p.rebuilt_id = r.rebuilt_id where p.id > ? and e.status = 'done'
{:error,
%Postgrex.Error{
message: nil,
postgres: %{
code: :syntax_error,
line: "1193",
message: "syntax error at or near \"and\"",
position: "254",
file: "scan.l",
unknown: "ERROR",
severity: "ERROR",
pg_code: "42601",
routine: "scanner_yyerror"
},
connection_id: 14304,
query: "select distinct on (e.id) e.id as row_id from \"pacman.public\".entities as e join \"pacman.public\".records as r on r.grantee_entity_id = e.id or r.grantor_entity_id = e.id join \"pacman.public\".properties as p on p.rebuilt_id = r.rebuilt_id where p.id > ? and e.status = 'done'"
}}
Why does SQL returns the query fields as ? instead of $1, $2, etc which is what ecto would expect?
You need to set the proper adapter, in this case postgres: use SQL, adapter: SQL.Adapters.Postgres
There are tests for each adapter that you can use as reference sql/test/adapters/postgres_test.exs at main · elixir-dbvisor/sql · GitHub
Ah, that makes sense, I though that was only needed if I wanted to run the query directly from SQL 
Thanks!
Yeah, all sql default to ANSI where parameters are positional and uses: ?
You can still generate an Ecto queryable by injecting a fragment ast directly into the Ecto.Query struct.
The docs say not to do it but …
def to_queryable(token) do
from =
case to_sql(token) do
{sql, []} ->
%Ecto.Query.FromExpr{
source: {:fragment, [], [{:raw, sql}]}
}
{sql, params} ->
raise "this is a bit longer but still possible - check the ast of a fragment with params"
end
%Ecto.Query{from: from}
end
~SQL"select 1 as id"
|> SQL.to_queryable()
|> select([t], [:id])
|> SQL.Repo.all()
|> Enum.each(&IO.inspect/1)
what is missing currently:
to_sql or a sibling function should also return a list of fields to be selected as the library has it tokenized. Then the select can be also generated.This is cool, I did not know this was possible, I initially had a manual conversion but dropped it before I released since Ecto.Query API is limited and does not support the full SQL spec.
When I’m done refactoring the lexer and parser with a new BNF generator I’ll take a stab at this.
Although the future API of SQL will mirror Enum and Stream. In that way you’ll be able to create pipelines that transform the result set.
This will create a clear separation of concern of SQL and application logic.
We’ve happy to announce the release of 0.3.0, packed with performance improvements, formatting and compile time warnings on missing relations.
You can now generate a sql.lock file with mix sql.get the file is used to give you compile time warnings on missing relation anywhere you use the use SQL, and it will serve as the foundation for the upcoming compile time type checking and casting.
Please see the sql/CHANGELOG.md at v0.3.0 · elixir-dbvisor/sql · GitHub for all details.
Here is a little teaser of the upcoming version of SQL.
You’ll be able to ergonomically work on the result set to transform it, this is done in one pass, so no immediate list are created, so you get high performance no matter how you organize your code!
cc @holgerwiehen
We’ve happy to announce the release of 0.4.0, packed with performance and compile time improvements.
Now as we can lex, parse and generate SQL faster then Ecto can at runtime, then we have moved our attention to implementing a SOTA pool, this will make it possible for us to aim at guaranteeing 100% cache hit on prepared queries, something I have never seen before and will have big impact on general performance.
Please see the sql/CHANGELOG.md at v0.4.0 · elixir-dbvisor/sql · GitHub for all details.
The next version of SQL, is going to be a big leap for our ecosystem, we now have C speed performance with best in class expressiveness. It’s without doubt a SOTA SQL framework across languages, so it’s time for me to ask this community to help with testing it out and shape user experience, and put your name on something truly special out there!
defmodule SQL.Repo do
use Ecto.Repo, otp_app: :sql, adapter: Ecto.Adapters.Postgres
use SQL, adapter: SQL.Adapters.Postgres
import Ecto.Query
def sql() do
SQL.transaction do
SQL.transaction do
Enum.to_list(~SQL"SELECT 1")
end
end
end
def ecto() do
SQL.Repo.transaction(fn ->
SQL.Repo.transaction(fn ->
SQL.Repo.all(select(from("users"), [1]))
end)
end)
end
end
Application.put_env(:sql, :ecto_repos, [SQL.Repo])
Application.put_env(:sql, SQL.Repo, log: false, username: "postgres", password: "postgres", hostname: "localhost", database: "sql_test#{System.get_env("MIX_TEST_PARTITION")}", pool_size: :erlang.system_info(:schedulers_online), ssl: false)
SQL.Repo.__adapter__().storage_up(SQL.Repo.config())
SQL.Repo.start_link()
Benchee.run(
%{
"sql" => fn -> SQL.Repo.sql() end,
"ecto" => fn -> SQL.Repo.ecto() end,
},
parallel: 1,
memory_time: 2,
reduction_time: 2,
unit_scaling: :smallest,
measure_function_call_overhead: true)
➜ sql git:(main) ✗ mix sql.bench
Operating System: macOS
CPU Information: Apple M1 Max
Number of Available Cores: 10
Available memory: 64 GB
Elixir 1.20.0-dev
Erlang 28.1
JIT enabled: true
Benchmark suite executing with the following configuration:
warmup: 2 s
time: 5 s
memory time: 2 s
reduction time: 2 s
parallel: 1
inputs: none specified
Estimated total run time: 22 s
Measured function call overhead as: 0 ns
Benchmarking ecto ...
Benchmarking sql ...
Calculating statistics...
Formatting results...
Name ips average deviation median 99th %
sql 133.60 K 7.48 μs ±1005.19% 3.75 μs 40.08 μs
ecto 8.69 K 115.09 μs ±55.74% 111.92 μs 219.96 μs
Comparison:
sql 133.60 K
ecto 8.69 K - 15.38x slower +107.61 μs
Memory usage statistics:
Name average deviation median 99th %
sql 183.99 B ±0.36% 184 B 184 B
ecto 19192 B ±0.00% 19192 B 19192 B
Comparison:
sql 184 B
ecto 19192 B - 104.31x memory usage +19008.01 B
Reduction count statistics:
Name average deviation median 99th %
sql 0.0210 K ±0.00% 0.0210 K 0.0210 K
ecto 1.61 K ±0.02% 1.61 K 1.61 K
Comparison:
sql 0.0210 K
ecto 1.61 K - 76.62x reduction count +1.59 K
Very impressive! Love to see the focus on performance; I’ll have to check out the code as I’ve been on the lookout for Elixir performance tricks lately (for obvious reasons).
I have to say I’m surprised there’s so much of a difference. I would have thought most of the time would be spent in the adapter and on latency for the actual connection (there’s no concurrency here). But, uh, the reduction counts certainly tell a story.
As we have near zero overhead, then it all comes down to latency and db execution, and thats how you can get close to C level performance, keep you allocation to an absolute minimum and leverage recursive functions, keep your functions private and send messages to processes ideally to the same scheduler, even thought that they can move, chances are they didn’t and it will be cheap.
Is there a way to keep a set of processes pinned to a scheduler, or at least encourage it?
I have been working on a design for a next-gen simulator to replace Hobbes’s Construct (which is pretty slow), and performance is the top priority because I will need to run more strenuous tests (and far more of them) in the near future. The funny thing about the sim is that the processes within a run are not concurrent (this is how I enforce determinism), meaning they will be happiest on the same core.
I don’t really know much about the scheduler but if I had to guess I would say spawn() puts the new process on the same core. Is there more to it?
You can not control which scheduler a process is on, the VM will move them how it see fit, but by designing your application based on the schedulers then it should at least help you statistically, although I still need to verify this, which shouldn’t be to hard with my current design.
Hadn’t really looked into if the way I currently do it is good or not, short answer is you want to hash the pid to get stable affinity, and you can get around 40-75% under realistic load.
I just tested out using hash and I got even better results then before:
➜ sql git:(main) ✗ mix sql.bench
Compiling 2 files (.ex)
Generated sql app
Operating System: macOS
CPU Information: Apple M1 Max
Number of Available Cores: 10
Available memory: 64 GB
Elixir 1.20.0-dev
Erlang 28.1
JIT enabled: true
Benchmark suite executing with the following configuration:
warmup: 2 s
time: 5 s
memory time: 2 s
reduction time: 2 s
parallel: 1
inputs: none specified
Estimated total run time: 22 s
Measured function call overhead as: 0 ns
Benchmarking ecto ...
Benchmarking sql ...
Calculating statistics...
Formatting results...
Name ips average deviation median 99th %
sql 162.06 K 6.17 μs ±1992.10% 2.92 μs 34.63 μs
ecto 7.84 K 127.51 μs ±23.57% 117.29 μs 222.81 μs
Comparison:
sql 162.06 K
ecto 7.84 K - 20.66x slower +121.33 μs
Memory usage statistics:
Name average deviation median 99th %
sql 183.95 B ±0.81% 184 B 184 B
ecto 19184 B ±0.00% 19184 B 19184 B
Comparison:
sql 184 B
ecto 19184 B - 104.29x memory usage +19000.05 B
Reduction count statistics:
Name average deviation median 99th %
sql 0.0210 K ±0.13% 0.0210 K 0.0210 K
ecto 1.61 K ±0.01% 1.61 K 1.61 K
Comparison:
sql 0.0210 K
ecto 1.61 K - 76.62x reduction count +1.59 K
Profiling sql with fprof...
Reading trace data...
End of trace!
Processing data...
Creating output...
Done!
CNT ACC (ms) OWN (ms)
Total 15 0.094 0.031
:fprof.apply_start_stop/4 0 0.094 0.005
anonymous fn/0 in :elixir_compiler_2.__FILE__/1 1 0.089 0.001
SQL.Repo.sql/0 1 0.088 0.012
:suspend 2 0.063 0.000
Process.put/2 1 0.003 0.002
Process.delete/1 1 0.003 0.002
:erlang.send/2 2 0.002 0.002
Process.get/1 1 0.002 0.001
:persistent_term.get/1 1 0.001 0.001
:erlang.put/2 1 0.001 0.001
:erlang.phash2/2 1 0.001 0.001
:erlang.make_ref/0 1 0.001 0.001
:erlang.erase/1 1 0.001 0.001
Process.get/2 1 0.001 0.001
:undefined 0 0.000 0.000
Profiling ecto with fprof...
Reading trace data...
.
End of trace!
Processing data...
Creating output...
Done!
CNT ACC (ms) OWN (ms)
Total 768 1.622 1.311
:fprof.apply_start_stop/4 0 1.622 0.005
anonymous fn/0 in :elixir_compiler_2.__FILE__/1 1 1.617 0.001
SQL.Repo.ecto/0 1 1.616 0.001
SQL.Repo.transaction/1 2 1.615 0.002
SQL.Repo.transaction/2 2 1.614 0.008
Ecto.Repo.Transaction.transaction/4 2 1.587 0.002
Ecto.Adapters.Postgres.transaction/3 2 1.586 0.002
Ecto.Adapters.SQL.transaction/3 2 1.585 0.002
Ecto.Adapters.SQL.checkout_or_transaction/4 2 1.584 0.008
DBConnection.transaction/3 2 1.575 0.005
DBConnection.run_transaction/4 1 0.961 0.005
DBConnection.Holder.handle/4 3 0.722 0.003
DBConnection.Holder.handle_or_cleanup/5 3 0.719 0.009
anonymous fn/3 in Ecto.Adapters.SQL.checkout_or_transaction/ 2 0.714 0.008
DBConnection.Holder.holder_apply/4 3 0.704 0.009
anonymous fn/0 in SQL.Repo.ecto/0 1 0.698 0.001
anonymous fn/0 in SQL.Repo.ecto/0 1 0.633 0.001
SQL.Repo.all/1 1 0.632 0.001
SQL.Repo.all/2 1 0.631 0.004
DBConnection.begin/3 1 0.612 0.003
Ecto.Repo.Queryable.all/3 1 0.605 0.004
Ecto.Repo.Queryable.execute/4 1 0.594 0.009
DBConnection.checkout/4 1 0.518 0.003
Postgrex.Protocol.handle_transaction/3 2 0.501 0.006
DBConnection.run_begin/3 1 0.425 0.003
Postgrex.Protocol.handle_begin/2 1 0.408 0.002
Postgrex.Protocol.msg_recv/3 10 0.397 0.020
Postgrex.Protocol.recv_transaction/3 2 0.375 0.002
Postgrex.Protocol.recv_transaction/4 4 0.373 0.012
Postgrex.Protocol.msg_recv/4 3 0.362 0.006
Ecto.Adapters.Postgres.execute/5 1 0.345 0.003
Ecto.Adapters.SQL.execute/6 1 0.338 0.003
Ecto.Adapters.SQL.execute!/5 1 0.334 0.002
Ecto.Adapters.SQL.sql_call/5 1 0.332 0.005
:gen_tcp.recv/3 3 0.323 0.006
Ecto.Adapters.Postgres.Connection.execute/4 1 0.321 0.003
Postgrex.execute/4 1 0.317 0.001
DBConnection.execute/4 1 0.316 0.005
:suspend 8 0.311 0.000
:inet_tcp.recv/3 3 0.308 0.003
:prim_inet.recv/3 3 0.305 0.003
:prim_inet.recv0/3 3 0.302 0.009
DBConnection.log/4 3 0.245 0.003
DBConnection.log/5 3 0.242 0.012
DBConnection.commit/3 1 0.232 0.003
Enum.reduce/3 12 0.224 0.012
Enum."-reduce/3-lists^foldl/2-0-"/3 37 0.217 0.062
Ecto.Query.Planner.query/5 1 0.207 0.003
Ecto.Query.Planner.plan/3 1 0.196 0.001
Ecto.Query.Planner.plan/4 1 0.195 0.013
DBConnection.run/6 1 0.191 0.001
DBConnection.run_execute/5 1 0.190 0.003
DBConnection.log/2 3 0.185 0.003
anonymous fn/4 in Ecto.Adapters.SQL.with_log/3 3 0.182 0.003
Postgrex.Protocol.msg_send/3 3 0.179 0.006
Ecto.Adapters.SQL.log/4 3 0.179 0.033
Postgrex.Protocol.handle_execute/4 1 0.174 0.002
Postgrex.Protocol.handle_execute_result/4 1 0.169 0.004
Postgrex.Protocol.rebind_execute/4 1 0.150 0.001
Postgrex.Protocol.bind_execute/4 1 0.149 0.005
Ecto.Query.Planner.plan_cache/3 1 0.148 0.003
Ecto.Query.Planner.traverse_cache/4 1 0.144 0.003
DBConnection.checkin/4 1 0.144 0.003
Ecto.Query.Planner.traverse_exprs/4 1 0.130 0.001
DBConnection.run_commit/3 1 0.128 0.004
Postgrex.Protocol.handle_commit/2 1 0.110 0.003
Postgrex.Protocol.do_send/3 3 0.102 0.006
anonymous fn/3 in Ecto.Query.Planner.traverse_exprs/4 13 0.101 0.026
:gen_tcp.send/2 3 0.096 0.006
DBConnection.checkout/3 1 0.090 0.009
:inet_tcp.send/2 3 0.081 0.003
:prim_inet.send/3 3 0.078 0.009
Keyword.get/3 25 0.075 0.050
anonymous fn/6 in Ecto.Query.Planner.traverse_cache/4 13 0.075 0.026
Access.get/2 17 0.068 0.017
DBConnection.ConnectionPool.checkout/3 1 0.066 0.001
DBConnection.Holder.checkout/3 1 0.065 0.005
:prim_inet.send/4 3 0.063 0.010
Ecto.Repo.Supervisor.tuplet/2 3 0.057 0.012
anonymous fn/2 in Postgrex.Protocol.msg_send/3 5 0.055 0.010
Access.get/3 17 0.051 0.034
:erlang.port_command/3 3 0.050 0.006
Ecto.Query.Planner.merge_cache/6 13 0.049 0.025
Postgrex.Protocol.msg_decode/1 10 0.048 0.018
Postgrex.Protocol.maybe_encode_msg/1 5 0.045 0.005
:erts_internal.port_command/3 3 0.044 0.016
Keyword.get/2 11 0.044 0.011
:lists.keyfind/3 43 0.043 0.043
Postgrex.Messages.encode_msg/1 5 0.040 0.015
DBConnection.LogEntry.new/5 3 0.039 0.003
DBConnection.Holder.checkout/5 1 0.039 0.002
DBConnection.LogEntry.parse_times/2 3 0.036 0.009
DBConnection.Holder.checkout_call/5 1 0.036 0.007
Postgrex.Protocol.recv_execute/4 1 0.034 0.001
Postgrex.Protocol.recv_execute/5 1 0.033 0.003
Postgrex.Protocol.recv_bind/3 1 0.032 0.002
Postgrex.Messages.parse/3 7 0.030 0.010
:prim_inet.async_recv/3 3 0.027 0.009
Ecto.Repo.Registry.lookup/1 6 0.027 0.012
String.Chars.to_string/1 3 0.026 0.006
Enum.map/2 3 0.025 0.003
Postgrex.Protocol.rows_recv/4 1 0.022 0.002
Enum."-map/2-lists^map/1-1-"/2 5 0.022 0.009
:telemetry.execute/3 3 0.021 0.006
Ecto.Adapters.SQL.log_measurements/3 12 0.021 0.012
Postgrex.Messages.encode/1 5 0.020 0.009
Postgrex.Messages.decode_string/1 3 0.020 0.009
DBConnection.decode/4 1 0.020 0.003
Enum.map_reduce/3 7 0.019 0.007
:inet_db.lookup_socket/1 6 0.018 0.012
Postgrex.Protocol.notify/1 3 0.018 0.006
Ecto.Adapters.SQL.put_conn/2 3 0.018 0.006
DBConnection.meter/1 3 0.018 0.006
Ecto.Repo.Preloader.query/7 1 0.017 0.001
:prim_inet.ctl_cmd/3 3 0.015 0.006
Ecto.Query.Planner.plan_sources/3 1 0.015 0.007
DBConnection.Query.decode/3 1 0.014 0.002
Postgrex.Protocol.rows_msg/3 1 0.013 0.002
GenServer.whereis/1 4 0.013 0.004
Ecto.Adapters.SQL.reset_conn/2 2 0.013 0.003
DBConnection.maybe_encode/4 1 0.013 0.002
DBConnection.checkin/1 1 0.013 0.001
Enum."-map_reduce/3-lists^mapfoldl/2-0-"/3 8 0.012 0.010
DBConnection.Holder.checkin/1 1 0.012 0.002
DBConnection.event/2 4 0.012 0.008
anonymous fn/3 in Ecto.Repo.Queryable.postprocessor/4 1 0.011 0.002
Ecto.Query.Planner.finalize_cache/3 1 0.011 0.006
DBConnection.Query.encode/3 1 0.011 0.002
DBConnection.Holder.abs_timeout/2 1 0.010 0.003
DBConnection.conclude/2 2 0.010 0.004
:telemetry_handler_table.list_for_event/1 3 0.009 0.006
:erlang.port_control/3 3 0.009 0.006
:erlang.monotonic_time/0 9 0.009 0.009
String.Chars.impl_for!/1 3 0.009 0.006
Process.whereis/1 3 0.009 0.006
Process.put/2 3 0.009 0.006
Map.new/1 3 0.009 0.006
Ecto.Repo.Queryable.process/4 2 0.009 0.002
Ecto.Adapters.SQL.get_conn_or_pool/2 3 0.009 0.006
DBConnection.Query.Postgrex.Query.decode/3 1 0.009 0.002
DBConnection.Holder.status?/2 3 0.009 0.006
DBConnection.Holder.done/4 1 0.009 0.004
:ets.lookup/2 8 0.008 0.008
:erlang.++/2 8 0.008 0.008
:binary.match/2 3 0.008 0.008
System.monotonic_time/1 1 0.008 0.006
String.Chars.Atom.to_string/1 2 0.008 0.002
Process.get/2 4 0.008 0.008
Postgrex.Protocol.done/4 1 0.008 0.002
Postgrex.Protocol.done/2 2 0.008 0.004
Enum.reverse/1 8 0.008 0.008
Ecto.Repo.Queryable.process_args/4 1 0.008 0.001
Ecto.Repo.Assoc.query/4 1 0.008 0.001
Ecto.Query.Planner.plan_expr_subqueries/3 4 0.008 0.004
:ets.update_element/3 5 0.007 0.007
:ets.lookup_element/3 7 0.007 0.007
Postgrex.Types.decode_rows/4 1 0.007 0.001
Postgrex.Protocol.recv_ready/3 1 0.007 0.002
Postgrex.Protocol.decode_tag/1 3 0.007 0.004
Ecto.Query.Planner.query_with_cache/8 1 0.007 0.003
Ecto.Adapters.SQL.key/1 7 0.007 0.007
DBConnection.Query.Postgrex.Query.decode_map/2 1 0.007 0.002
:lists.foreach/2 3 0.006 0.003
:erlang.port_get_data/1 6 0.006 0.006
:erlang.atom_to_binary/1 2 0.006 0.004
SQL.Repo.prepare_opts/2 3 0.006 0.003
Postgrex.Protocol.mode/1 1 0.006 0.002
Postgrex.Protocol.build_commit_statement/2 1 0.006 0.002
Postgrex.DefaultTypes.decode_rows/3 1 0.006 0.001
Map.get/2 3 0.006 0.003
Ecto.Queryable.to_query/1 1 0.006 0.002
Ecto.Query.Planner.cast_and_merge_params/5 2 0.006 0.002
DBConnection.Query.Postgrex.Query.encode/3 1 0.006 0.002
DBConnection.Query.impl_for!/1 2 0.006 0.004
DBConnection.LogEntry.parse_time/2 6 0.006 0.006
:erlang.iolist_size/1 5 0.005 0.005
Process.get/1 1 0.005 0.001
Postgrex.DefaultTypes.decode_rows/4 2 0.005 0.002
Ecto.Query.Planner.attach_prefix/2 1 0.005 0.002
DBConnection.reset/1 1 0.005 0.002
:erlang.monitor/2 4 0.004 0.004
:erlang.demonitor/2 4 0.004 0.004
Postgrex.Types.encode_params/3 1 0.004 0.001
Postgrex.Protocol.decode_tags/1 2 0.004 0.002
Postgrex.DefaultTypes."Elixir.Postgrex.Extensions.Bool"/7 1 0.004 0.002
Ecto.Query.Planner.prepend_if/3 4 0.004 0.004
Ecto.Query.Planner.plan_expr_subqueries/5 4 0.004 0.004
Ecto.Query.Planner.plan_combinations/3 1 0.004 0.002
:prim_inet.enc_time/1 3 0.003 0.003
:maps.from_list/1 3 0.003 0.003
:lists.foreach_1/2 3 0.003 0.003
:erts_internal.port_control/3 3 0.003 0.003
:erlang.whereis/1 3 0.003 0.003
:erlang.term_to_binary/2 3 0.003 0.003
:erlang.split_binary/2 3 0.003 0.003
:erlang.put/2 3 0.003 0.003
:erlang.binary_to_integer/1 1 0.003 0.002
String.Chars.Postgrex.Query.to_string/1 1 0.003 0.002
String.Chars.impl_for/1 3 0.003 0.003
SQL.Repo.default_options/1 3 0.003 0.003
Process.delete/1 1 0.003 0.002
Postgrex.Protocol.query_member?/2 1 0.003 0.002
anonymous fn/2 in Postgrex.Messages.encode/1 1 0.003 0.002
Postgrex.DefaultTypes.encode_params/2 1 0.003 0.001
Map.get/3 3 0.003 0.003
Keyword.fetch/2 1 0.003 0.002
Ecto.Queryable.impl_for!/1 1 0.003 0.002
Ecto.Query.Planner.query_lookup/6 1 0.003 0.002
Ecto.Query.Planner.plan_assocs/1 1 0.003 0.002
Ecto.Adapters.SQL.with_log/3 3 0.003 0.003
DBConnection.Holder.start_deadline/5 1 0.003 0.002
DBConnection.Holder.checkout_result/3 1 0.003 0.002
DBConnection.Holder.cancel_deadline/1 1 0.003 0.002
DBConnection.log_result/1 3 0.003 0.003
DBConnection.entry_result/1 3 0.003 0.003
:erlang.list_to_bitstring/1 2 0.002 0.002
:erlang.atom_to_binary/2 2 0.002 0.002
Postgrex.DefaultTypes.encode_params/3 1 0.002 0.001
anonymous fn/4 in Ecto.Repo.Queryable.process_args/4 1 0.002 0.001
anonymous fn/3 in Ecto.Repo.Queryable.preprocessor/3 1 0.002 0.001
Ecto.Query.Planner.plan_joins/5 1 0.002 0.001
Ecto.Query.Planner.plan_from/3 1 0.002 0.001
Ecto.Query.Planner.plan_expr_subquery/3 2 0.002 0.002
Ecto.Query.Planner.merge_cache/3 2 0.002 0.002
DBConnection.Query.impl_for/1 2 0.002 0.002
DBConnection.past_event/3 2 0.002 0.002
DBConnection.handle_common_result/3 2 0.002 0.002
:maps.remove/2 1 0.001 0.001
:ets.give_away/3 1 0.001 0.001
:erts_internal.binary_to_integer/2 1 0.001 0.001
:erlang.start_timer/4 1 0.001 0.001
:erlang.send/2 1 0.001 0.001
:erlang.monotonic_time/1 1 0.001 0.001
:erlang.make_ref/0 1 0.001 0.001
:erlang.list_to_tuple/1 1 0.001 0.001
:erlang.iolist_to_binary/1 1 0.001 0.001
:erlang.erase/1 1 0.001 0.001
:erlang.cancel_timer/2 1 0.001 0.001
System.normalize_time_unit/1 1 0.001 0.001
SQL.Repo.prepare_query/3 1 0.001 0.001
Postgrex.Protocol.done/5 1 0.001 0.001
Postgrex.Messages.format/1 1 0.001 0.001
Enum.unzip/1 1 0.001 0.001
Ecto.Repo.Queryable.preprocessor/3 1 0.001 0.001
Ecto.Repo.Queryable.preprocess/4 1 0.001 0.001
Ecto.Repo.Queryable.postprocessor/4 1 0.001 0.001
Ecto.Queryable.Ecto.Query.to_query/1 1 0.001 0.001
Ecto.Queryable.impl_for/1 1 0.001 0.001
Ecto.Query.Planner.source_cache/2 1 0.001 0.001
Ecto.Query.Planner.plan_windows/2 1 0.001 0.001
Ecto.Query.Planner.plan_source/4 1 0.001 0.001
Ecto.Query.Planner.plan_joins/9 1 0.001 0.001
Ecto.Query.Planner.plan_ctes/3 1 0.001 0.001
Ecto.Query.Planner.plan_assocs/3 1 0.001 0.001
Ecto.Query.Planner.expr_to_cache/1 1 0.001 0.001
Ecto.Query.Planner.ensure_select/2 1 0.001 0.001
Ecto.Query.Planner.build_meta/2 1 0.001 0.001
Ecto.Adapters.SQL.put_source/2 1 0.001 0.001
Ecto.Adapters.Postgres.Connection.ensure_list_params!/1 1 0.001 0.001
Ecto.Adapters.Postgres.valid_prepare?/1 1 0.001 0.001
:undefined 0 0.000 0.000
A bit late to this party, but …
Calling the library SQL seems a dubious choice, to put it kindly.
SQL is a standard (more or less 
) and there are various libraries out there that provide various types of SQL features. Now there’s a library name-squatting on that technology by its name on hex.pm.
There are a sad number of libraries on hex.pm that already name-squat on the broadest possible names, too many of which are some combination of incomplete, abandoned, or not the best option anymore. Searching for those broad names still brings them up, making it often much harder than it should be to find the actually relevant choices on hex.pm.
It would be great to not replicate that with yet another library calling itself the name of the thing it provides.
Imagine if Ecto had been called SQL. What then for your library?
How does one add data types?
I work with a number of custom datatypes and have various means of working with Ecto on utilizing them across various backends. Libraries like geo_sql rely on this to work with WKT/WKB types as stored and returned by various database GIS implementations.
How would one add support for another DB, such as SQLite?
Will it eventually be possible to use existing and more complete librareis such as postgrex for backend access, or is the plan for SQL to always ship its own hand-rolled implementations?
(See how the name of the library makes it harder to have these sorts of conversations? When I write “for SQL to always” do I mean the library, the language, a given query, …)
Are there any thoughts/plans on interop with Ecto, or will these be two separate walled gardens going forward?
If one writes something like: ~SQL[from users where foo = 2 select id, email] |> ~SQL[where bar = 2]it ends up with 2 where clauses, and this causes an error. Same for any other clause for that matter. Will this library eventually support actual composibility of queries? This is a feature provided by Ecto which is absolutely invaluable.
The composability also seems somewhat fragile? e.g.:
sql = ~SQL[from users u]
|> ~SQL{where u.email = 'john@example.com'}
|> ~SQL{where u.email = 'john@example.com'}
|> ~SQL{foosball}
|> ~SQL[select id, email, inserted_at, updated_at]
Produces: “foosball where u.email = ‘john@example.com’ where u.email = ‘john@example.com’ from users u select id, email, inserted_at, updated_at” Note how the select statement is suddenly at the end.
Moving the select above the foosball bit puts the select in its correct position.
Is this resolvable in the library, or is the expectation that the user of the library always places things in a sensible order?
I appreciate that there is a focus on performance in this library, but if things don’t work it makes it hard to consider as an option.
p.s. The LetsEncrypt-issued SSL cert on dbvisor expired in September. 
Thanks for this thoughtful review, with regards to naming, there is with no doubt chutzpah behind it, but we’re talking about a library that is a foreign language integration.
We’re still at the very early stage where focus is on the foundation, so your points are well taken but at this time they are table stakes compared to the ongoing work that is actually making a bigger impact. It would even be trivial for the community to contribute to what you outlined.
I’ve talked privately about creating an sql_ecto to provide sql adapters to Ecto, should be possible to help Ecto scale a bit better.
I’m currently getting ready to tie up the Postgres adapter which would include support for custom types, canceling long running queries, improve error reporting and telemetric. I would appreciate any feedback you have to how you envision the UX should be for custom types and anything else that you care about.
© Copyright Elixir Forum | Terms | Privacy & Cookies
