Schultzer
SQL - Brings an extensible SQL parser and sigil to Elixir, confidently write SQL with automatic parameterized queries.
Hey there, I wrote this low-level library recently, its goal is simply to lower the barrier between Elixir and SQL, and it does that by providing a SQL sigil.
I’ll list a few highlights compared to raw SQL and Ecto.Query, although the goal with this library is not to replace either, but to provide fundamental safe building blocks to Ecto.SQL.Adapters.
- Lower the barrier for DBAs to contribute to your codebase, without having to translate SQL to Ecto.Query.
- Composable queries, no need for you to remember, when to start with select or from.
- Interpolation-al queries, don’t fiddle with fragments and
?. - prevents SQL injection, by design.
You might have noticed that I’ve listed prevent SQL injection, and if you’re thinking: wait a minute, Ecto.Query is already by design preventing SQL injection.
You would be correct, However, Ecto.SQL also provides the query/2 and query!/2 functions that are vulnerable to SQL injection since they take a String and a list of params, the intention with this API, is to give you an escape hatch for executing parameterized queries.
This library is a result of a thread on the mailing list: https://groups.google.com/g/elixir-ecto/c/8MOkRFAdLZc
As this is an MVP, I’m looking for your feedback.
First Post!
jstimps
Very interesting work! It’s on my list of things to try out with EctoFDB.
My use case: FDB has no SQL so I’m thinking I could make a cli tool to query EctoFDB (similar to psql for Postgres) that uses the Ecto adapter and your SQL library.
Most Liked
Schultzer
As I’m polishing off the next version which currently is scheduled to be released in the coming weeks, and I got two exciting sneak peaks, we’re now more or less conformant with SQL 2016 with over 900 generated tests and SQL generation is 400-650x faster than Ecto.
Schultzer
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!
Schultzer
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.
Last Post!
Schultzer
I think @krasenyp is making a good point. Being able to mix SQL.parse and ~SQL by piping the dynamic into the static might give us the best of both world:
"from #{table}"
|> SQL.parse()
|> ~SQL"select * where id > {{id}}"
There is also some example in GitHub - elixir-dbvisor/my_app: A Phoenix example app with SQL · GitHub on conditional logic
Popular in Announcing
Other popular topics
Latest Phoenix Threads
Latest on Elixir Forum
Sponsor Spotlight
Catch errors, track performance, monitor hosts and more.
Categories:
Sub Categories:
Forums
Popular Tags
- #ecto
- #liveview
- #troubleshooting
- #learning-elixir
- #deployment
- #library
- #erlang
- #testing
- #genserver
- #mix
- #absinthe
- #remote-other
- #otp
- #plug
- #how-to-question
- #macros
- #postgres
- #channels
- #elixirconf
- #exunit
- #discussion
- #code-sync
- #javascript
- #podcasts
- #onsite
- #dialyzer
- #docker
- #authentication
- #umbrella
- #full-time-contract
- #podcasts-by-brainlid
- #ecto-query
- #elixir-ls
- #phoenix_html
- #iex
- #blog-post
- #graphql
- #genstage
- #ai
- #websockets
- #supervisor
- #elixirconf-us
- #advent-of-code
- #distillery
- #processes
- #api
- #forms
- #metaprogramming
- #security
- #hex
Our Sponsors
Build Elixir applications with speed and confidence.
Supporting innovation across the BEAM ecosystem.
We build reliable cloud platforms for business-critical systems.
Catch errors, track performance, monitor hosts and more.
Error tracking, logs, uptime, and Just Enough APM. Built for developers.
Practical resources that improve the lives of professional developers.
Develop your skills with books, videos, and courses.
Producing high quality Elixir screencasts since 2017.
The team behind Membrane, Popcorn, LiveDebugger. Available for hire.
Courses that'll move you from confusion to "Aha, now I get it!"