Ecto SQL sigil

[benjamin...@gmail.com]

Hey there,

I've been experimenting with creating an Ecto SQL sigil, and I'm wondering if it's possible to validate interpolation in a macro sigil.

```

id = 1

~SQL"select * from users where id = #{id}"

```

FYI, getting the information to validate column types is straightforward as the SQL standard has the information schema. For any non-compliant RDBMS, the adapter structure would make it straightforward to implement specific queries.

https://www.postgresql.org/docs/current/infoschema-columns.html

[Ben Schultzer]

After looking into other sigil implementation e.g Phoenix ~p then I believe the best I would be able to do is generating if clause for the vars that are used in a interpolation, that would give us runtime validation, which I think it good enough for a POC. But I’m still interested if anyone know if we could archive compile-time validation for the sigil.

Technically we could create a lock file with all the information of the columns, but not sure if there would be any road blocks.

--
You received this message because you are subscribed to a topic in the Google Groups "elixir-ecto" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/elixir-ecto/8MOkRFAdLZc/unsubscribe.
To unsubscribe from this group and all its topics, send an email to elixir-ecto+unsubscribe@googlegroups.com.
To view this discussion visit https://groups.google.com/d/msgid/elixir-ecto/98c2f8fe-7d59-4e7d-8d77-b46793f23dban%40googlegroups.com.

[benjamin...@gmail.com]

I figured out most of the issues I had with creating a sigil and interpolation; the only thing left is guarding against SQL injection and casting to the proper type based on table information / Ecto Schema.

I've used nimle_persec for parsing, and my code is less than 200 LOC.

```

iex(192)> ~SQL[select id, (select id, COALESCE(id,null) from users) from users]
%{
  select: [
    "id",
    %{select: ["id", {:coalesce, [["id", :null]]}], from: ["users"]}
  ],
  from: ["users"]
}

```

```

iex(192)> sub = "File.read!(\"/mix.lock\")"

iex(193)> ~SQL[where id = "1" select #{1}, #{sub}, #{1 + 1} from users]
%{
  select: [1, "File.read!(\"/mix.lock\")", 2],
  where: ["id", :=, ~c"\"1\""],
  from: ["users"]
}

```

I think it would be nice to an SQL sigil in Ecto, and AFAIK they can still be composable in the same way Ecto.Query are.

Is there any interest from the core team to see this?

On Sunday, October 27, 2024 at 7:22:59 PM UTC-4 benjamin...@gmail.com wrote:

After looking into other sigil implementation e.g Phoenix ~p then I believe the best I would be able to do is generating if clause for the vars that are used in a interpolation, that would give us runtime validation, which I think it good enough for a POC. But I’m still interested if anyone know if we could archive compile-time validation for the sigil.

Technically we could create a lock file with all the information of the columns, but not sure if there would be any road blocks.

On Sunday, October 27, 2024, benjamin...@gmail.com <benjamin...@gmail.com> wrote:

Hey there,

I've been experimenting with creating an Ecto SQL sigil, and I'm wondering if it's possible to validate interpolation in a macro sigil.

```

id = 1

~SQL"select * from users where id = #{id}"

```

FYI, getting the information to validate column types is straightforward as the SQL standard has the information schema. For any non-compliant RDBMS, the adapter structure would make it straightforward to implement specific queries.

https://www.postgresql.org/docs/current/infoschema-columns.html

--
You received this message because you are subscribed to a topic in the Google Groups "elixir-ecto" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/elixir-ecto/8MOkRFAdLZc/unsubscribe.

To unsubscribe from this group and all its topics, send an email to elixir-ecto...@googlegroups.com.

[José Valim]

I recommend to continue exploring it as a separate library for now. :) Thanks for reaching out!

José Valim

https://dashbit.co/

You received this message because you are subscribed to the Google Groups "elixir-ecto" group.
To unsubscribe from this group and stop receiving emails from it, send an email to elixir-ecto...@googlegroups.com.
To view this discussion visit https://groups.google.com/d/msgid/elixir-ecto/b7b8953c-0567-4f68-89df-f9c06799cc54n%40googlegroups.com.

[benjamin...@gmail.com]

Getting ready to publish an MVP of an Ecto.SQL sigil library.

The biggest advantage I see with sigils is that we can separate querying database and data transformation in Elixir. Here I mostly think about how the select macro works.

However, each solution will also have pros and cons. One of the cons I'm running into is how to solve the mapping from database results and Ecto.Schema. Initially, I thought of leveraging Enummerable and Collectable protocols.

The enumerable implementation for Ecto.SQL would query the database and return the results which could be mapped by the Collectable implementation Enum.into(~SQL[select id, email, inserted_at, updated_at from users], %User{}).

I'm not sure if you ever thought about leveraging the two protocols when developing Ecto, if you did, is there something I'm missing for why this is not a good idea, since to me separation of concerns sounds like a big win for extensibility and maintainability. 

[José Valim]

Thank you for the update. Just one suggestion, consider calling it EctoSQL or similar, as if one day we want to add it to Ecto, it would be at Ecto.SQL :)

José Valim

https://dashbit.co/

To view this discussion visit https://groups.google.com/d/msgid/elixir-ecto/696cd009-7c86-4c61-8a81-794dd25e3148n%40googlegroups.com.

[benjamin...@gmail.com]

I have released an MVP: https://hexdocs.pm/sql https://github.com/elixir-dbvisor/sql

There is still quite a lot to do on the parser side, e.g. tokenize DDL etc. (we don't break user's queries, so tokens that are not recognized by the parser, would be passed through as :ident tokens)

It is not my best work by far, I've struggled a lot with the parser and API.

I would appreciate any feedback since this has just been me fiddling with it.

[benjamin...@gmail.com]

Against all odds I have built a generated lexer and parser from the 2023 SQL BNF https://github.com/elixir-dbvisor/sql/pull/5 trying to stuff the grammar down leex and yeec turned out to be futile. There does exist some hand written Leex and Yeec SQL parser out there.

This also gave a decent 2x performance improvement, and I’m sure there is more gains to be made with optimization string generation.

I haven’t seen anyone leveraging Mix Task for parser generators, so I’m still interested in some feedback.

When it comes to enforcing any grammer rules, only encapsulation is enforced, but should be fairly simple to add additional checks in the lexer or parser.

[benjamin...@gmail.com]

Great news, with https://github.com/elixir-dbvisor/sql/pull/6 we're now conformant with SQL 2016, with over 900 generated tests.

[benjamin...@gmail.com]

I got exciting news, sql is between 400-650x faster then Ecto to generate SQL. I had not expected these results since sql does not currently build iodata.

➜  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.18.0
Erlang 27.2
JIT enabled: true

Benchmark suite executing with the following configuration:
warmup: 2 s
time: 10 s
memory time: 2 s
reduction time: 0 ns
parallel: 1
inputs: none specified
Estimated total run time: 56 s

Benchmarking Ecto.Repo.to_sql ...
Benchmarking inspect ...
Benchmarking to_sql ...
Benchmarking to_stirng ...
Calculating statistics...
Formatting results...

Name                       ips        average  deviation         median         99th %
to_sql                  4.88 K        0.20 ms    ±21.12%       0.198 ms        0.31 ms
to_stirng               4.58 K        0.22 ms     ±2.98%        0.22 ms        0.24 ms
inspect                 0.24 K        4.18 ms     ±3.00%        4.16 ms        4.54 ms
Ecto.Repo.to_sql     0.00747 K      133.89 ms     ±4.22%      132.76 ms      148.82 ms

Comparison:
to_sql                  4.88 K
to_stirng               4.58 K - 1.06x slower +0.0133 ms
inspect                 0.24 K - 20.41x slower +3.98 ms
Ecto.Repo.to_sql     0.00747 K - 653.15x slower +133.68 ms

Memory usage statistics:

Name                Memory usage
to_sql                   0.38 MB
to_stirng               0.153 MB - 0.40x memory usage -0.22888 MB
inspect                  4.88 MB - 12.80x memory usage +4.50 MB
Ecto.Repo.to_sql       179.35 MB - 470.13x memory usage +178.97 MB

**All measurements for memory usage were the same**

➜  sql git:(main) ✗ mix sql.bench
Compiling 1 file (.ex)
Generated sql app
Operating System: macOS
CPU Information: Apple M1 Max
Number of Available Cores: 10
Available memory: 64 GB
Elixir 1.18.0
Erlang 27.2
JIT enabled: true

Benchmark suite executing with the following configuration:
warmup: 2 s
time: 10 s
memory time: 2 s
reduction time: 0 ns
parallel: 1
inputs: none specified
Estimated total run time: 56 s

Benchmarking Ecto.Repo.to_sql ...
Benchmarking inspect ...
Benchmarking to_sql ...
Benchmarking to_stirng ...
Calculating statistics...
Formatting results...

Name                       ips        average  deviation         median         99th %
to_sql                 4900.64        0.20 ms    ±17.90%       0.198 ms        0.31 ms
to_stirng                21.90       45.65 ms     ±1.91%       45.45 ms       48.89 ms
inspect                  19.60       51.01 ms     ±2.26%       50.64 ms       54.87 ms
Ecto.Repo.to_sql          7.55      132.39 ms     ±2.40%      131.83 ms      142.37 ms

Comparison:
to_sql                 4900.64
to_stirng                21.90 - 223.73x slower +45.45 ms
inspect                  19.60 - 249.97x slower +50.80 ms
Ecto.Repo.to_sql          7.55 - 648.81x slower +132.19 ms

Memory usage statistics:

Name                     average  deviation         median         99th %
to_sql                   0.38 MB     ±0.00%        0.38 MB        0.38 MB
to_stirng               22.05 MB     ±0.00%       22.05 MB       22.05 MB
inspect                 26.78 MB     ±0.00%       26.78 MB       26.78 MB
Ecto.Repo.to_sql       179.35 MB     ±0.00%      179.35 MB      179.35 MB

Comparison:
to_sql                   0.38 MB
to_stirng               22.05 MB - 57.79x memory usage +21.67 MB
inspect                 26.78 MB - 70.19x memory usage +26.40 MB
Ecto.Repo.to_sql       179.35 MB - 470.13x memory usage +178.97 MB