Can we have sql queries inline?

[dvv]

Hi!

I discovered at https://github.com/alexdesousa/ayesql/blob/master/lib/ayesql/parser.ex#L36 that we have hidden ability to compile an sql string to queries!

I wonder whether you find it feasible to expose that so that we could do:

defmodule Foo do
  use AyeSQL, repo: Repo
  @sql """
  -- name: bar
  -- docs: bar
  select 1
  -- name: baz
  -- docs: baz
  select 2
  """
  defqueries(@sql)
end

TIA

[alexdesousa]

@dvv Yes! I'm actually working on that at the moment :) My goal is to be able to use EEx templates to generate dynamic queries and then pass these generated strings to a macro like defqueries with a string :)

TL;DR: I'm going to add several ways of instantiating strings both at runtime (like in my example) and at compile time (like you suggested).

For more context, I'm working on something at the moment where I need to generate a query that looks like this:

-- File: queries.sql.eex
<% [%{name: name, as: as} | rest] = @tables %>
SELECT
  <%= as %>.datetime AS datetime,
  <%= @calculation %> AS calculation
FROM
  <%= name %> AS <%= as %>
  <%= for table <- rest do %>
  INNER JOIN <%= table.name %> AS <%= table.as %>
    ON <%= table.as %>.datetime = <%= as %>.datetime
  <% end %>
WHERE
  <%= as %>.datetime BETWEEN :start_date AND :end_date

So my idea is to be able to instantiate this template with the dynamic parts using EEx.eval_string/2:

assigns = [
  calculation: "(a.value / b.value)",
  tables: [
    %{name: "table_a", as: "a"},
    %{name: "table_b", as: "b"}
  ]
]

function =
  "queries.sql.eex"
  |> File.read!()
  |> EEx.eval_string(assigns: assigns)
  |> AyeSQL.create_function()

...

function.([start_date: s, end_date: DateTime.utc_now()], [])

The PR I'm working at the moment (not yet published) will have the following API (this specifications are not final and might change):

• @spec defqueries(Path.t()) :: [Macro.t()]
• @spec defqueries(String.t(), keyword()) :: [Macro.t()]
• @spec create_function() :: (AyeSQL.Core.parameters(), AyeSQL.Core.options() -> {:ok, term()} | {:error, term()})

[dvv]

Very promising IMO!

[alexdesousa]

@dvv I managed to work on the PR during this weekend. In the end I didn't add defqueries/1 for strings because the API was ambiguous :grimacing: But the same functionality can be found in the module AyeSQL.Compiler or by using the brand new function AyeSQL.eval_query/2.

I'll merge the PR and publish the new version next week :slightly_smiling_face:

[dvv]

Thank you for the fix!

[dvv]

Am I right that I shall use some following boiler code to get things done?

defmodule JustAStubToDefineAHelpingMacro do
  defmacro definlinequeries(contents) do
    AyeSQL.Compiler.compile_queries(contents)
  end
end

defmodule Foo do
  use AyeSQL, repo: Repo
  import JustAStubToDefineAHelpingMacro, only: [definlinequeries: 1]
  definlinequeries """
  -- name: bar
  -- docs: bar
  select 1
  -- name: baz
  -- docs: baz
  select 2
  """
end

[alexdesousa]

@dvv Yep, that looks correct :slightly_smiling_face: I just checked your code by adding a dummy Repo:

defmodule Repo do
end

defmodule JustAStubToDefineAHelpingMacro do
  defmacro definlinequeries(contents) do
    AyeSQL.Compiler.compile_queries(contents)
  end
end

defmodule Foo do
  use AyeSQL, repo: Repo
  import JustAStubToDefineAHelpingMacro, only: [definlinequeries: 1]
  definlinequeries """
  -- name: bar
  -- docs: bar
  select 1
  -- name: baz
  -- docs: baz
  select 2
  """
end

And then tried the following (run: false to avoid calling the query runner):

iex> Foo.bar([], run: false)
{:ok, %AyeSQL.Query{arguments: [], statement: "select 1"}}

So, yeah, your macro works :slightly_smiling_face: :rocket:

[dvv]

Don't we want to ship kinda definlinequeries (or how they name it?) with AyeSQL per se? I believe it would be a bit tidier )

[alexdesousa]

@dvv I've been debating with the idea since you opened this issue. In the end, I decided against it because my main motivation when I first created the library was to Keep SQL in SQL files (it's one of the library objectives as stated in the README).

The main advantage AyeSQL has over Ecto is it makes writing complex queries easier, because you don't need to battle Ecto's DSL and use plain SQL instead. Additionally, a good thing about using SQL in files is you get syntax highlighting in your editor, which is useful for maintenance.

So, inline querying, though convenient at times, doesn't align completely with the library goals.

[dvv]

I see. While I tend to autogenerate sql queries and expose them to the world in form of Foo.bar(...) (so that "the guts" are obscure and hardly anyone cares of their syntax), I certainly do respect the author's vision. Thank you again!