SQL - Brings an extensible SQL parser and sigil to Elixir, confidently write SQL with automatic parameterized queries.

[Schultzer]

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,
    ...
  >
]

[holgerwiehen]

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.

[Schultzer]

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.

[sezaru]

@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?

[Schultzer]

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

[sezaru]

Ah, that makes sense, I though that was only needed if I wanted to run the query directly from SQL

Thanks!

[Schultzer]

Yeah, all sql default to ANSI where parameters are positional and uses: ?

[dkuku]

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.

[Schultzer]

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.

[Schultzer]

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.

[Schultzer]

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