Packages

sql

0.2.0
0.5.0 0.4.0 0.3.0 0.2.0 0.1.0

SQL provides state-of-the-art, high-performance SQL integration for Elixir, built to handle extreme concurrency with unmatched expressiveness and ergonomic query composition. Write safe, composable, parameterized queries directly, without translating to Ecto or any ORM.

HexDocs

Current section

5 Versions

Jump to
Readme
5 Versions
0 Dependencies
Dependants
Files
Activity
Hide whitespace

Compare versions

18 files changed
+3442 additions
-486 deletions
  @@ -3,6 +3,6 @@
3 3
4 4 # Used by "mix format"
5 5 [
6 - plugins: [Ecto.MixFormatter],
6 + plugins: [SQL.MixFormatter],
7 7 inputs: ["{mix,.formatter}.exs", "{config,lib,test}/**/*.{ex,exs}"]
8 8 ]
  @@ -5,6 +5,23 @@
5 5
6 6 # Changelog
7 7
8 + ## v0.2.0 (2025-05-04)
9 +
10 + ### Enhancement
11 + - SQL 2016 conformance [#6](https://github.com/elixir-dbvisor/sql/pull/6).
12 + - Lexer and Parser generated from the [SQL 2023 BNF](https://standards.iso.org/iso-iec/9075/-2/ed-6/en/) [#5](https://github.com/elixir-dbvisor/sql/pull/5).
13 + - Added SQL.Token behaviour used to implement adapters [#5](https://github.com/elixir-dbvisor/sql/pull/5).
14 + - ANSI adapter [#5](https://github.com/elixir-dbvisor/sql/pull/5).
15 + - MySQL adapter [#5](https://github.com/elixir-dbvisor/sql/pull/5).
16 + - PostgreSQL adapter [#5](https://github.com/elixir-dbvisor/sql/pull/5).
17 + - TDS adapter [#5](https://github.com/elixir-dbvisor/sql/pull/5).
18 + - Improve SQL generation with 57-344x compared to Ecto [#7](https://github.com/elixir-dbvisor/sql/pull/7) [#4](https://github.com/elixir-dbvisor/sql/pull/4).
19 + - Ensure inspect follows the standard [representation](https://hexdocs.pm/elixir/Inspect.html#module-inspect-representation) [#4](https://github.com/elixir-dbvisor/sql/pull/4).
20 + - Ensure storage is setup when running benchmarks [#5](https://github.com/elixir-dbvisor/sql/pull/5).
21 +
22 + ### Deprecation
23 + - token_to_sql/2 is deprecated in favor of SQL.Token behaviour token_to_string/2 [#11](https://github.com/elixir-dbvisor/sql/pull/11).
24 +
8 25 ## v0.1.0 (2025-03-01)
9 26
10 27 Initial release.
  @@ -7,87 +7,31 @@
7 7
8 8 <!-- MDOC !-->
9 9
10 - Bring's an extensiable SQL parser and sigil to Elixir, confidently write SQL with automatic parameterized queries.
10 + Brings an extensible SQL parser and sigil to Elixir, confidently write SQL with automatic parameterized queries.
11 11
12 - - Lower the barreir for DBA's to contribute in your codebase, without having to translate SQL to Ecto.Query.
13 - - Composilbe queries, no need for you to remember, when to start with select or from.
14 - - Interpolational queries, don't fiddle with fragments and `?`.
12 + - Lower the barrier for DBAs to contribute in your codebase, without having to translate SQL to Ecto.Query.
13 + - Composable queries, no need for you to remember, when to start with select or from.
14 + - Interpolation-al queries, don't fiddle with fragments and `?`.
15 15
16 16 ## Examples
17 17
18 18 ```elixir
19 19 iex(1)> email = "john@example.com"
20 20 "john@example.com"
21 - iex(2)> select = ~SQL"select id, email"
22 - "select id, email"
23 - iex(3)> ~SQL[from users] |> ~SQL[where email = #{email}] |> select
24 - "select id, email from users where email = \"john@example.com\""
25 - iex(4)> sql = ~SQL[from users where email = #{email} select id, email]
26 - "select id, email from users where email = \"john@example.com\""
21 + iex(2)> ~SQL[from users] |> ~SQL[where email = {{email}}] |> ~SQL"select id, email"
22 + ~SQL"""
23 + where email = {{email}} from users select id, email
24 + """
25 + iex(4)> sql = ~SQL[from users where email = {{email}} select id, email]
26 + ~SQL"""
27 + from users where email = {{email}} select id, email
28 + """
27 29 iex(5)> to_sql(sql)
28 - {"select id, email from users where email = $0", ["john@example.com"]}
30 + {"select id, email from users where email = ?", ["john@example.com"]}
29 31 iex(6)> to_string(sql)
30 - "select id, email from users where email = $0"
32 + "select id, email from users where email = ?"
31 33 iex(7)> inspect(sql)
32 - "select id, email from users where email = \"john@example.com\""
33 - ```
34 -
35 - ### Extend the SQL AST or raise on unimplemented features with ease
36 - ```elixir
37 - defmodule SQL.Adapters.TDS do
38 - use SQL
39 -
40 - @impl true
41 - def token_to_sql({:binding, _, [value]}), do: "@#{value}"
42 -
43 - # keywords not yet tokenized by the parser will end up as ident and passed through
44 - # as illustrated below, but in our specific example we use it to raise an error.
45 - # iex(1)> ~SQL[select count(*)]
46 - # "select count (*)"
47 - def token_to_sql({:ident, _, [~c"count"] = value}) do
48 - raise "#{value} is not implemented for #{__MODULE__}"
49 - end
50 -
51 - # handle conversion from postgresql "users"."id" and users.id to [users].[id]
52 - def token_to_sql({:. = tag, _, [{:"", _, [{:ident, _, _} = left]} , {:"", _, [{:ident, _, _} = right]}]}) do
53 - "[#{token_to_sql(left)}]#{token_to_sql(tag)}[#{token_to_sql(right)}]"
54 - end
55 - def token_to_sql({:. = tag, _, [{:ident, _, _} = left, {:ident, _, _} = right]}) do
56 - "[#{token_to_sql(left)}]#{token_to_sql(tag)}[#{token_to_sql(right)}]"
57 - end
58 - def token_to_sql({:. = tag, _, [{:"", _, [{:ident, _, _} = left]}, right]}) do
59 - "[#{token_to_sql(left)}]#{token_to_sql(tag)}#{token_to_sql(right)}"
60 - end
61 - def token_to_sql({:. = tag, _, [{:ident, _, _} = left, right]}) do
62 - "[#{token_to_sql(left)}]#{token_to_sql(tag)}#{token_to_sql(right)}"
63 - end
64 - def token_to_sql({:. = tag, _, [left, {:"", _, [{:ident, _, _} = right]}]}) do
65 - "#{token_to_sql(left)}#{token_to_sql(tag)}[#{token_to_sql(right)}]"
66 - end
67 - def token_to_sql({:. = tag, _, [left, {:ident, _, _} = right]}) do
68 - "#{token_to_sql(left)}#{token_to_sql(tag)}[#{token_to_sql(right)}]"
69 - end
70 -
71 - # fallback
72 - def token_to_sql(token) do
73 - IO.inspect token, label: :token_to_sql
74 - SQL.String.token_to_sql(token, __MODULE__)
75 - end
76 - end
77 -
78 - iex(8)> to_sql(~SQL[from db.users where "db"."users"."email" = #{email} select db.users.id, db.users.email])
79 - {"select [db].[users].[id], [db].[users].[email] from [db].[users] where [db].[users].[email] = @0", ["john@example.com"]}
80 -
81 - iex(9)> to_sql(~SQL[from db.users where "db"."users"."email" = #{email} select count(*)])
82 - ** (RuntimeError) count is not implemented for Elixir.SQL.Adapters.TDS
83 - iex:11: SQL.Adapters.TDS.token_to_sql/1
84 - (elixir 1.18.0) lib/enum.ex:1815: anonymous fn/2 in Enum.map_join/3
85 - (elixir 1.18.0) lib/enum.ex:4496: Enum.map_intersperse_list/3
86 - (elixir 1.18.0) lib/enum.ex:1815: Enum.map_join/3
87 - (sql 0.1.0) lib/string.ex:54: SQL.String.token_to_sql/2
88 - (elixir 1.18.0) lib/enum.ex:1815: anonymous fn/2 in Enum.map_join/3
89 - (elixir 1.18.0) lib/enum.ex:4496: Enum.map_intersperse_list/3
90 - iex:9: (file)
34 + "~SQL\"\"\"\nfrom users where email = {{email}} select id, email\n\"\"\""
91 35 ```
92 36
93 37 ### Leverage the Enumerable protocol in your repository
  @@ -95,7 +39,7 @@ iex(9)> to_sql(~SQL[from db.users where "db"."users"."email" = #{email} select c
95 39 ```elixir
96 40 defmodule MyApp.Repo do
97 41 use Ecto.Repo, otp_app: :myapp, adapter: Ecto.Adapters.Postgres
98 - use SQL
42 + use SQL, adapter: SQL.Adapters.Postgres
99 43
100 44 defimpl Enumerable, for: SQL do
101 45 def count(_enumerable) do
  @@ -121,6 +65,8 @@ iex(9)> to_sql(~SQL[from db.users where "db"."users"."email" = #{email} select c
121 65 [%{"id" => 1, "email" => "john@example.com"}, %{"id" => 2, "email" => "jane@example.com"}]
122 66 ```
123 67
68 + ## Benchmark
69 + You can find benchmark results [here](https://github.com/elixir-dbvisor/sql/benchmarks) or run `mix sql.bench`
124 70
125 71 ## Installation
126 72
  @@ -130,7 +76,7 @@ by adding `sql` to your list of dependencies in `mix.exs`:
130 76 ```elixir
131 77 def deps do
132 78 [
133 - {:sql, "~> 0.1.0"}
79 + {:sql, "~> 0.2.0"}
134 80 ]
135 81 end
136 82 ```
  @@ -1,14 +1,18 @@
1 1 {<<"links">>,[{<<"GitHub">>,<<"https://github.com/elixir-dbvisor/sql">>}]}.
2 2 {<<"name">>,<<"sql">>}.
3 - {<<"version">>,<<"0.1.0">>}.
3 + {<<"version">>,<<"0.2.0">>}.
4 4 {<<"description">>,
5 - <<"Bring's an extensiable SQL parser and sigil to Elixir, confidently write SQL with automatic parameterized queries.">>}.
5 + <<"Brings an extensible SQL parser and sigil to Elixir, confidently write SQL with automatic parameterized queries.">>}.
6 6 {<<"elixir">>,<<"~> 1.18">>}.
7 7 {<<"files">>,
8 - [<<"lib">>,<<"lib/formatter.ex">>,<<"lib/compiler.ex">>,<<"lib/string.ex">>,
9 - <<"lib/sql.ex">>,<<"lib/parser.ex">>,<<".formatter.exs">>,<<"mix.exs">>,
10 - <<"README.md">>,<<"LICENSE">>,<<"LICENSES">>,<<"LICENSES/Apache-2.0.txt">>,
11 - <<"CHANGELOG.md">>]}.
8 + [<<"lib">>,<<"lib/formatter.ex">>,<<"lib/token.ex">>,<<"lib/mix">>,
9 + <<"lib/mix/tasks">>,<<"lib/mix/tasks/sql.gen.test.ex">>,
10 + <<"lib/mix/tasks/sql.gen.parser.ex">>,<<"lib/string.ex">>,
11 + <<"lib/adapters">>,<<"lib/adapters/tds.ex">>,<<"lib/adapters/ansi.ex">>,
12 + <<"lib/adapters/mysql.ex">>,<<"lib/adapters/postgres.ex">>,<<"lib/sql.ex">>,
13 + <<"lib/bnf.ex">>,<<"lib/parser.ex">>,<<"lib/lexer.ex">>,
14 + <<".formatter.exs">>,<<"mix.exs">>,<<"README.md">>,<<"LICENSE">>,
15 + <<"LICENSES">>,<<"LICENSES/Apache-2.0.txt">>,<<"CHANGELOG.md">>]}.
12 16 {<<"app">>,<<"sql">>}.
13 17 {<<"licenses">>,[<<"Apache-2.0">>]}.
14 18 {<<"requirements">>,[]}.
  @@ -0,0 +1,112 @@
1 + # SPDX-License-Identifier: Apache-2.0
2 + # SPDX-FileCopyrightText: 2025 DBVisor
3 +
4 + defmodule SQL.Adapters.ANSI do
5 + @moduledoc """
6 + A SQL adapter for [ANSI](https://blog.ansi.org/sql-standard-iso-iec-9075-2023-ansi-x3-135/).
7 + """
8 + @moduledoc since: "0.2.0"
9 +
10 + use SQL.Token
11 +
12 + @doc false
13 + def token_to_string(value, mod \\ __MODULE__)
14 + def token_to_string(value, mod) when is_struct(value) do
15 + to_string(%{value | module: mod})
16 + end
17 + def token_to_string({tag, _, [{:parens, _, _} = value]}, mod) when tag in ~w[integer float update]a do
18 + "#{mod.token_to_string(tag)}#{mod.token_to_string(value)}"
19 + end
20 + def token_to_string({tag, _, value}, _mod) when tag in ~w[ident integer float]a do
21 + "#{value}"
22 + end
23 + def token_to_string({tag, _}, mod) do
24 + mod.token_to_string(tag)
25 + end
26 + def token_to_string({:comment, _, value}, _mod) do
27 + "-- #{value}"
28 + end
29 + def token_to_string({:comments, _, value}, _mod) do
30 + "\\* #{value} *\\"
31 + end
32 + def token_to_string({:double_quote, _, value}, _mod) do
33 + "\"#{value}\""
34 + end
35 + def token_to_string({:quote, _, value}, _mod) do
36 + "'#{value}'"
37 + end
38 + def token_to_string({:parens, _, value}, mod) do
39 + "(#{mod.token_to_string(value)})"
40 + end
41 + def token_to_string({:bracket, _, value}, mod) do
42 + "[#{mod.token_to_string(value)}]"
43 + end
44 + def token_to_string({:colon, _, value}, mod) do
45 + "; #{mod.token_to_string(value)}"
46 + end
47 + def token_to_string({:comma, _, value}, mod) do
48 + ", #{mod.token_to_string(value)}"
49 + end
50 + def token_to_string({tag, _, []}, mod) do
51 + mod.token_to_string(tag)
52 + end
53 + def token_to_string({tag, _, [[_ | _] = left, right]}, mod) when tag in ~w[join]a do
54 + "#{mod.token_to_string(left)} #{mod.token_to_string(tag)} #{mod.token_to_string(right)}"
55 + end
56 + def token_to_string({tag, _, [{:with = t, _, [left, right]}]}, mod) when tag in ~w[to]a do
57 + "#{mod.token_to_string(tag)} #{mod.token_to_string(left)} #{mod.token_to_string(t)} #{mod.token_to_string(right)}"
58 + end
59 + def token_to_string({tag, _, value}, mod) when tag in ~w[select from fetch limit where order offset group having with join by distinct create type drop insert alter table add into delete update start grant revoke set declare open close commit rollback references recursive]a do
60 + "#{mod.token_to_string(tag)} #{mod.token_to_string(value)}"
61 + end
62 + def token_to_string({:on = tag, _, [source, as, value]}, mod) do
63 + "#{mod.token_to_string(source)} #{mod.token_to_string(as)} #{mod.token_to_string(tag)} #{mod.token_to_string(value)}"
64 + end
65 + def token_to_string({tag, _, [left, [{:all = t, _, right}]]}, mod) when tag in ~w[union except intersect]a do
66 + "#{mod.token_to_string(left)} #{mod.token_to_string(tag)} #{mod.token_to_string(t)} #{mod.token_to_string(right)}"
67 + end
68 + def token_to_string({:between = tag, _, [{:not = t, _, right}, left]}, mod) do
69 + "#{mod.token_to_string(right)} #{mod.token_to_string(t)} #{mod.token_to_string(tag)} #{mod.token_to_string(left)}"
70 + end
71 + def token_to_string({tag, _, [left, right]}, mod) when tag in ~w[:: [\] <> <= >= != || + - ^ * / % < > = like ilike as union except intersect between and or on is not in cursor for to]a do
72 + "#{mod.token_to_string(left)} #{mod.token_to_string(tag)} #{mod.token_to_string(right)}"
73 + end
74 + def token_to_string({tag, _, [{:parens, _, _} = value]}, mod) when tag not in ~w[in on]a do
75 + "#{mod.token_to_string(tag)}#{mod.token_to_string(value)}"
76 + end
77 + def token_to_string({tag, _, values}, mod) when tag in ~w[not all between symmetric absolute relative forward backward on in for without]a do
78 + "#{mod.token_to_string(tag)} #{mod.token_to_string(values)}"
79 + end
80 + def token_to_string({tag, _, [left, right]}, mod) when tag in ~w[.]a do
81 + "#{mod.token_to_string(left)}.#{mod.token_to_string(right)}"
82 + end
83 + def token_to_string({tag, _, [left]}, mod) when tag in ~w[not]a do
84 + "#{mod.token_to_string(left)} #{mod.token_to_string(tag)}"
85 + end
86 + def token_to_string({tag, _, [left]}, mod) when tag in ~w[asc desc isnull notnull]a do
87 + "#{mod.token_to_string(left)} #{mod.token_to_string(tag)}"
88 + end
89 + def token_to_string({:binding, _, [idx]}, _mod) when is_integer(idx) do
90 + "?"
91 + end
92 + def token_to_string({:binding, _, value}, _mod) do
93 + "{{#{value}}}"
94 + end
95 + def token_to_string(:asterisk, _mod) do
96 + "*"
97 + end
98 + def token_to_string(value, _mod) when is_atom(value) do
99 + "#{value}"
100 + end
101 + def token_to_string(value, _mod) when is_binary(value) do
102 + "'#{value}'"
103 + end
104 + def token_to_string(values, mod) when is_list(values) do
105 + values
106 + |> Enum.reduce([], fn
107 + token, [] = acc -> [acc | mod.token_to_string(token)]
108 + {:comma, _, _} = token, acc -> [acc | mod.token_to_string(token)]
109 + token, acc -> [acc, " " | mod.token_to_string(token)]
110 + end)
111 + end
112 + end
Loading more files…