Introduce Ecto-style API

[fishcakez]

Firstly we provide a mechanism to re-use prepared queries on multiple connections in the same pool. This means that Ecto (or another library) can use its owns global cache of prepared queries with a pool and if a query has not been prepared on the checked out connection it can be prepared and executed.

A naive driver can use this mechanism by trying to execute the query and return {:prepare, state} when the database says the query is not prepared. A driver could handle its own cache per connection if it proved beneficial.

Secondly we move query parameters outside of the query term so that it is easier to combine prepared queries and executed parameters.

This means that Ecto (or another library) can do the equivalent of:

{pool, opts} = repo.__pool__
case repo.fetch_query(key) do
  {:ok, query} ->
    DBConnection.execute(pool, query, params, opts)
  :error ->
    query = DBConnection.prepare!(pool, query, opts)
    repo.put_query(key, query)
    DBConnection.execute!(pool, query, params, opts)
end

Ecto also requires #2 and perhaps other changes.

[josevalim]

@fishcakez so this still requires the user to explicitly call prepare?

The approach I had in mind would simply pass a "reference" to the adapter and the adapter would take care of it. My concern with doing it explicitly is that now we need to call the pool/connection twice, no?

[fishcakez]

@josevalim we can call the pool/connection with:

{:ok, result} = DBConnection.run(pool, fn(conn) ->
   query = DBConnection.prepare!(conn, query, opts)
    repo.put_query(key, query)
    DBConnection.execute!(conn, query, params, opts)
end)
result

This is one checkout from the pool. run/3 is like transaction/3 except no begin/commit/rollback. The connection process is never called in DBConnection, state remains in the client process throughout run/3.

I wanted to try this approach first because the code is simple. Can you give an example of how you'd like to prepare?

[josevalim]

@fishcakez sounds good!

[fishcakez]

We could provide:

@spec prepare_execute(conn, query, params, opts) :: {:ok, query, result} | {:error, err}

This would delay decoding the result of execute until after check in, whereas decoding blocks the connection with the run/3 example (though there is no reason an adapter couldn't turn off decoding and do it manually).