/
ayesql.ex
351 lines (291 loc) · 9.13 KB
/
ayesql.ex
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
defmodule AyeSQL do
@moduledoc """
_AyeSQL_ is a library for using raw SQL.
> **Aye** _/ʌɪ/_ _exclamation (archaic dialect)_: said to express assent; yes.
## Overview
Inspired by Clojure library [Yesql](https://github.com/krisajenkins/yesql),
_AyeSQL_ tries to find a middle ground between strings with raw SQL queries and
SQL DSLs by:
- Keeping SQL in SQL files.
- Generating Elixir functions for every query.
- Supporting mandatory and optional named parameters.
- Allowing query composability with ease.
- Working out of the box with PostgreSQL using
[Ecto](https://github.com/elixir-ecto/ecto_sql) or
[Postgrex](https://github.com/elixir-ecto/postgrex):
- Being extended to support other databases using the behaviour
`AyeSQL.Runner`.
## Small Example
Let's say we have a
[SQL query](https://stackoverflow.com/questions/39556763/use-ecto-to-generate-series-in-postgres-and-also-retrieve-null-values-as-0)
to retrieve the click count of a certain type of link every day of the last `X`
days. In raw SQL this could be written as:
```sql
WITH computed_dates AS (
SELECT dates::date AS date
FROM generate_series(
current_date - $1::interval,
current_date - interval '1 day',
interval '1 day'
) AS dates
)
SELECT dates.date AS day, count(clicks.id) AS count
FROM computed_dates AS dates
LEFT JOIN clicks AS clicks ON date(clicks.inserted_at) = dates.date
WHERE clicks.link_id = $2
GROUP BY dates.date
ORDER BY dates.date;
```
The equivalent query in Ecto would be:
```elixir
dates = ~s(
SELECT generate_series(
current_date - ?::interval,
current_date - interval '1 day',
interval '1 day'
)::date AS d
)
from(
c in "clicks",
right_join: day in fragment(dates, ^days),
on: day.d == fragment("date(?)", c.inserted_at),
where: c.link_id = ^link_id
group_by: day.d,
order_by: day.d,
select: %{
day: fragment("date(?)", day.d),
count: count(c.id)
}
)
```
Using fragments can get convoluted and difficult to maintain. In AyeSQL, the
equivalent would be to create an SQL file with the query e.g. `queries.sql`:
```sql
-- name: get_day_interval
SELECT datetime::date AS date
FROM generate_series(
current_date - :days::interval, -- Named parameter :days
current_date - interval '1 day',
interval '1 day'
);
-- name: get_avg_clicks
-- docs: Gets average click count.
WITH computed_dates AS ( :get_day_interval ) -- Composing with another query
SELECT dates.date AS day, count(clicks.id) AS count
FROM computed_date AS dates
LEFT JOIN clicks AS clicks ON date(clicks.inserted_at) = dates.date
WHERE clicks.link_id = :link_id -- Named parameter :link_id
GROUP BY dates.date
ORDER BY dates.date;
```
In Elixir, we would load all the queries in this file by creating the following
module:
```elixir
defmodule Queries do
use AyeSQL, repo: MyRepo
defqueries("queries.sql") # File name with relative path to SQL file.
end
```
or using the macro `defqueries/3`:
```elixir
import AyeSQL, only: [defqueries: 3]
defqueries(Queries, "queries.sql", repo: MyRepo)
```
Both approaches will create a module called `Queries` with all the queries
defined in `queries.sql`.
And then we could execute the query as follows:
```elixir
iex> params = [
...> link_id: 42,
...> days: %Postgrex.Interval{secs: 864_000} # 10 days
...> ]
iex> Queries.get_avg_clicks(params)
{:ok,
[
%{day: ..., count: ...},
%{day: ..., count: ...},
%{day: ..., count: ...},
...
]
}
```
AyeSQL also allows you to choose the type of returned data structures.
Instead of the default map you can also pass an `into` option to your query
possible values are:
- an empty map: `Map.new()` or `%{}`
- an empty list: `Keyword.new()` or `[]`
- a struct
- `:raw` which returns the unmodified Postgrex result
```elixir
iex> Queries.get_avg_clicks(params, into: [])
{:ok,
[
[day: ..., count: ...],
[day: ..., count: ...],
[day: ..., count: ...],
...
]
}
```
```elixir
iex> defmodule AvgClicks do defstruct [:day, :count] end
iex> Queries.get_avg_clicks(params, into: AvgClicks)
{:ok,
[
%AvgClicks{day: ..., count: ...},
%AvgClicks{day: ..., count: ...},
%AvgClicks{day: ..., count: ...},
...
]
}
```
"""
alias AyeSQL.Compiler
alias AyeSQL.Query
@doc """
Uses `AyeSQL` for loading queries.
By default, supports the option `runner` (see `AyeSQL.Runner` behaviour).
Any other option will be passed to the runner.
"""
@spec __using__(keyword()) :: Macro.t()
defmacro __using__(options) do
{db_runner, db_options} = Keyword.pop(options, :runner, AyeSQL.Runner.Ecto)
quote do
import AyeSQL, only: [defqueries: 1]
@__db_runner__ unquote(db_runner)
@__db_options__ unquote(db_options)
@doc """
Runs the `query`. On error, fails.
"""
@spec run!(Query.t()) :: term() | no_return()
@spec run!(Query.t(), keyword()) :: term() | no_return()
def run!(query, options \\ [])
def run!(query, options) do
case run(query, options) do
{:ok, result} ->
result
{:error, reason} ->
raise RuntimeError, message: reason
end
end
@doc """
Runs the `query`.
"""
@spec run(Query.t()) :: {:ok, term()} | {:error, term()}
@spec run(Query.t(), keyword()) :: {:ok, term()} | {:error, term()}
def run(query, options \\ [])
def run(%Query{} = query, options) do
AyeSQL.run(@__db_runner__, query, options)
end
########################
# Helpers for inspection
@doc false
@spec __db_runner__() :: module()
def __db_runner__, do: @__db_runner__
@doc false
@spec __db_options__() :: term()
def __db_options__, do: @__db_options__
end
end
@doc """
Evaluates the `contents` of a string with a query and generates an anonyous
function that receives parameters and options.
"""
@spec eval_query(binary()) ::
(AyeSQL.Core.parameters(), AyeSQL.Core.options() ->
{:ok, AyeSQL.Query.t() | term()}
| {:error, AyeSQL.Error.t() | term()})
| no_return()
@spec eval_query(binary(), AyeSQL.Lexer.options()) ::
(AyeSQL.Core.parameters(), AyeSQL.Core.options() ->
{:ok, AyeSQL.Query.t() | term()}
| {:error, AyeSQL.Error.t() | term()})
| no_return()
defdelegate eval_query(contents, options \\ []), to: AyeSQL.Compiler
# Runs a `stmt` with some `args` in an `app`.
@doc false
@spec run(module(), Query.t(), keyword()) ::
{:ok, term()} | {:error, term()}
def run(module, query, options)
def run(module, %Query{} = query, options) do
module.run(query, options)
end
@doc """
Macro to load queries from a `file`.
Let's say we have the file `lib/sql/queries.sql` with the following contents:
```sql
-- name: get_user
-- docs: Gets user by username
SELECT *
FROM users
WHERE username = :username;
```
Then we can load our queries to Elixir using the macro `defqueries/1`:
```
# file: lib/queries.ex
defmodule Queries do
use AyeSQL, repo: MyRepo
defqueries("sql/queries.sql")
end
```
or the macro `defqueries/3`:
```
# file: lib/queries.ex
import AyeSQL, only: [defqueries: 3]
defqueries(Queries, "sql/queries.ex", repo: MyRepo)
```
And finally we can inspect the query:
```
iex(1)> Queries.get_user(username: "some_user", run: false)
{:ok,
%AyeSQL.Query{
statement: "SELECT * FROM user WHERE username = $1",
arguments: ["some_user"]
}
}
```
or run it:
```
iex(1)> Queries.get_user(username: "some_user")
{:ok,
[
%{username: ..., ...}
]
}
```
"""
@spec defqueries(Path.t()) :: [Macro.t()]
defmacro defqueries(relative) do
dirname = Path.dirname(__CALLER__.file)
filename = Path.expand("#{dirname}/#{relative}")
contents = File.read!(filename)
[
quote(do: @external_resource(unquote(filename))),
Compiler.compile_queries(contents)
]
end
@doc """
Macro to load queries from a `file` and create a module for them.
Same as `defqueries/1`, but creates a module e.g for the query file
`lib/sql/queries.sql` we can use this macro as follows:
```
# file: lib/queries.ex
import AyeSQL, only: [defqueries: 3]
defqueries(Queries, "sql/queries.sql", repo: MyRepo)
```
This will generate the module `Queries` and it'll contain all the SQL
statements included in `sql/queries.sql`.
"""
@spec defqueries(module(), Path.t(), keyword()) :: Macro.t()
defmacro defqueries(module, relative, options) do
quote do
defmodule unquote(module) do
@moduledoc """
This module defines functions for queries in `#{unquote(relative)}`
"""
use AyeSQL, unquote(options)
defqueries(unquote(relative))
end
end
end
end