myxql support

[iautom8things]

Hi there! I just tried to give ayesql a spin with a mysql db I work with. I ran into the problem that it appears ayesql assumes the use of argument/variable bindings that are numbered like so: $1, $2. This is definitely the case with postgrex, but I believe MyXQL requires bindings with ? question marks:

This example is pulled from the MyXQL readme:

iex> MyXQL.query(pid, "INSERT INTO posts (`title`) VALUES (?), (?)", ["Post 2", "Post 3"])
%MyXQL.Result{columns: nil, connection_id: 11204, last_insert_id: 2, num_rows: 2, num_warnings: 0, rows: nil}

I get a "(1054) (ER_BAD_FIELD_ERROR) Unknown column '$1' in 'where clause'" error when attempting to use this with any query that has an argument.

[alexdesousa]

@iautom8things Hello there! I'm glad you're using AyeSQL :smile:

This type of issues can be solved with AyeSQL.Runner behaviour. Given the code you posted above, the AyeSQL.Runner would be something like this:

defmodule YourApp.Runner do
  use AyeSQL.Runner

  @impl AyeSQL
  def run(%AyeSQL.Query{statement: stmt, arguments: args}, options) do
    pool = Keyword.get(options, :conn, YourApp.Pool)
    stmt = transform_stmt(stmt)

    with {:ok, result} <- MyXQL.query(pool, stmt, args) do
      AyeSQL.Runner.handle_result(result)
    end
  end

  @spec transform_stmt(AyeSQL.Query.statement()) :: AyeSQL.Query.statement()
  defp transform_stmt(stmt) do
    Regex.replace(~r/\$(\d+)/, stmt, "?")
  end
end

And then in your query module you can set de runner. I don't know which way you're using, but these are both of them:

e.g. for a single queries file:

import AyeSQL, only: [defqueries: 3]

defqueries YourApp.Post, "query/post.sql", runner: YourApp.Runner

e.g. for a defined module:

defmodule YourApp.Post do
  import AyeSQL

  defqueries "query/post.sql", runner: YourApp.Runner
end

Note: This implementation assumes you're using a pool for your MyXQL connections. Otherwise you could have your connection pid somewhere else e.g. once you start the connection process, you can store the pid in a :persistent_term or an :ets or register a process name associated with that pid.

---

I hope in the near future I'm able to add two callbacks to this behaviour for preparing both the statement and the arguments. This will give a us more freedom when we're implementing custom runners.

I hope this helps :smile: