Let me wrap that Erlang library in Elixir’s GenServer behaviour, so I can refer to it in a way I used to in Elixir.
I will do start_link/1 and stop/1 to start the linked process and stop it by a pid.
Also, a client interface will have query/2 and query/3 to execute an arbitrary query given a pid, query as a String, and optional params for a query.
epgsql can run extended queries with params.
The extended query protocol combines parse, bind, and execute using the unnamed prepared statement and portal.
That’s what query/3 does.
This is what EpgsqlEx.
Proxy GenServer wrapper looks like:lib/epgsl_ex/proxy.
exAlright, recompile the code and give it a try:EpgsqlEx.
Proxy in actionNot bad.
We have EpgsqlEx.
Proxy running with a linked epgsql process handling the connection to PostgreSQL.
Time to move on to DBConnection!II.
The DBConnectionDevelopers can implement their own SQL adapters by using Ecto.
Adapters.
SQL and by implementing the callbacks required by Ecto.
Adapters.
SQL.
Connection for handling connections and performing queries.
The connection handling and pooling for SQL adapters should be built using the DBConnection library.
As of now, EpgsqlEx.
Proxy can establish a connection to a PostgreSQL database and interact with a server using a binary protocol over TCP.
But there is much more to do: connection pool, detect faulty connections and reconnect with exponential backoff, keep the connection alive, transactions, prepare/execute, cursors, and quite a few more.
That’s where DBConnection comes at a rescue.
Besides all that, it also a requirement to implement DBConnection behaviour in order to use EpgsqlEx as a driver for Ecto adapter.
In short, DBConnection is a behaviour module for implementing efficient database connection client processes, pools and transactions.
Alright, let’s get to work.
First I will add :db_connection dependency, get dependencies and compile the project:mix.
exsmix do deps.
get, compileNext, I need to define a module that will implement DBConnection callbacks.
I’ve looked at Postgrex and Mariaex drivers source code and figured out that EpgsqlEx.
Protocol is the best name for the module.
Also, there are quite a few callbacks to implement, but I plan to implement the minimum to get my code up and running, leaving the rest with a raise "Not implemented" implementation.
Using my magic gift of foresight, I plan to implement 8 callbacks:connect/1disconnect/2handle_execute/4ping/1checkin/1checkout/1handle_prepare/3handle_close/3I was using IEx helper function b/1 to print callback info.
Hex docs or source code should work as well.
b DBConnection.
connectGoing thru all callbacks one at a time, finally, I came up with this implementation for my EpgsqlEx.
Protocollib/epgsql_ex/protocol.
exFancy isn’t it.
Let me try it out.
DBConnection.
Query protocol not implemented for “SELECT * FROM users”Hmm… Looks like query string that I pass to DBConnection.
execute/4 and EpgsqlEx.
Protocol.
handle_execute/4 is not expected.
Let me take another look at the docs:OK.
Let’s see what each of those types are:Great!.conn is a pid, as I’ve expected.
params and result are any terms.
query is the only arg that DBConnection behaviour cares about — it must implement DBConnection.
Query protocol.
Given all that, I plan to define a module EpgsqlEx.
Query and a protocol implementation in a file lib/epgsql_ex/query.
exdefmodule EpgsqlEx.
Query do defstruct [:statement] defimpl DBConnection.
Query do def parse(query, _opts), do: query def describe(query, _opts), do: query def encode(_query, params, _opts), do: params def decode(_query, result, _opts), do: result end defimpl String.
Chars do alias EpgsqlEx.
Query def to_string(%{statement: sttm}) do case sttm do sttm when is_binary(sttm) -> IO.
iodata_to_binary(sttm) %{statement: %Query{} = q} -> String.
Chars.
to_string(q) end end endendAs you can see, I’ve also implemented String.
Chars protocol using my magic gift of foresight again.
I will need it later, when implementing SQL Adapter.
Also, I need to update my EpgsqlEx.
Protocol.
handle_execute/4 to accept new struct:lib/epgsql_ex/protocol.
exCan’t wait to see if it works.
Back to IEx:Hooray!.It’s working.
Now I have a bare minimum code to implement a DBConnection compliant driver for PostgreSQL, that in turn will be used by Ecto Adapter I will build in the next episode.
Bear with me.
III.
The adapterEctoSQL provides building blocks for writing SQL adapters for Ecto.
It features:The Ecto.
Adapters.
SQL module as an entry point for all SQL-based adaptersDefault implementations for Postgres (Ecto.
Adapters.
Postgres) and MySQL (Ecto.
Adapters.
MySQL)A test sandbox (Ecto.
Adapters.
SQL.
Sandbox) that concurrently run database tests inside transactionsSupport for database migrations via Mix tasksFirst thing I did, was to read Ecto.
Adapters.
SQL module documentation once again.
As I quoted at the very beginning of chapter two:Developers can implement their own SQL adapters by using Ecto.
Adapters.
SQL and by implementing the callbacks required by Ecto.
Adapters.
SQL.
Connection for handling connections and performing queries.
The connection handling and pooling for SQL adapters should be built using the DBConnection library.
Not wasting time thinking, I’m adding {:ecto_sql, “~> 3.
0.
5”} dependency and compiling my project:mix.
exsmix do deps.
get, compileWhen using Ecto.
Adapters.
SQL, the following options are required::driver (required) — the database driver library.
For example: :postgrex:migration_lock — the lock to use on migration locks.
For example: “FOR UPDATE”.
It may also be nil (for no lock).
The user can still override this by setting :migration_lock in the repository configurationOn a second attempt, I ended up with a module EpgsqlEx.
EctoAdapter and a hand full of warnings module EpgsqlEx.
EctoAdapter.
Connection is not availablelib/epgsql_ex/ecto_adapater.
exObviously, the next step I take is to define EpgsqlEx.
EctoAdapter.
Connection as required by Ecto.
Adapters.
SQL.
It must implement Ecto.
Adapters.
SQL.
Connection with 14 callbacks!.I will use the not implemented stub for most of the callbacks, implementing only the bare minimum.
Let`s give it a try.
lib/epgsql_ex/ecto_adapter/connection.
exAll OTP related callbacks are delegated to EpgsqlEx driver, which in turn utilizes DBConnection functionality.
To keep everything at its’ place, I need to update lib/epgsql_ex.
ex as follow:lib/epgsql_ex.
exTo make sure no typos, I will compile a project at this stage with mix compileAll good.
Let`s try it out!.I will create another project Demo in a side folder with a supervision tree by running mix new –sup demo.
Right from the start, I will add Ecto and EpgsqlEx as dependencies and compile the project with mix do deps.
get, compilemix.
exs (demo project)Now I will define User Ecto schema and a Repolib/user.
ex and lib/repo.
ex (demo project)Newly defined Repo has to be added to a supervision tree of the Demo application.
Head to lib/demo/application.
ex and update children list:lib/demo/application.
ex (demo project)Important part not to forget to update config/config.
exs with a Repo config:config/config.
exs (demo project)Those options will be passed to my EpgsqlEx driver via DBConnection hopefully.
Let me see if the Demo app will start at this point.
That’s impressive.
The application has successfully started.
It failed to query the DB for User , but that was expected.
:erlang.
iolist_to_binary(:TODO) is a good sign — that is my stub in EpgsqlEx.
EctoAdapter.
Connection.
all/1I wonder what my supervision tree looks like at this point.
:observer.
start() will give me a good picture.
:observer.
start()You can see DBConnection in action here.
That’s my connection pool created by DBConnection!.Take a look for example at linked pids 226, 237, 249:pid(0,226,0) — is a process for EpgsqlEx.
Protocolpid(0,237,0) — is a process for Epgsql.
Proxy, which is a wrapper for :epgsql Erlang modulepid(0,249,0) — is a process for :epgsql TCP socketYou can kill any of those processes, and see DBConnection restart lost processes with new ones.
Impressive!The next step I take is to implement EpgsqlEx.
EctoAdapter.
Connection.
all/1 under the EpgsqlEx project with a dummy result to satisfy Ecto expectations.
lib/epgsql_ex/ecto_adapter/connection.
ex (epgsql_ex project)Here we go — real data from PostgreSQL server.
Fantastic!.But Ecto expects different data, not the one :epgsql returns.
Quick look at lib/ecto/adapters/sql.
ex:556 and reading function docs in this module gave me a clear understanding that it expects Map like this: %{num_rows: integer(), rows: list([])}After a few attempts, I’ve figured out EpgsqlEx.
Query.
decode/3 is in charge of decoding driver results in a more readable format.
I will put a stub there for now:lib/epgsql_ex/query.
ex (epgsql_ex project)Fingers crossed.
Let see what I get.
Not bad at all.
With all the stubs I’m so close to success.
I need real data from DB.
In order to do that, I need two things:Transform :epgsql results to %{num_rows: integer(), rows: list([])} at lib/epgsql_ex/query.
ex and cast value typesTraverse AST of Ecto.
Query passed to EpgsqlEx.
EctoAdapter.
Connection at lib/epgsql_ex/ect_adapater/connection.
ex and build a query to be consumed by EpgsqlEx.
execute/4The first one seems to be simpler, so I will start with it.
Basically, I need to convert a tuple of 3 elements {:ok, <columns>, <rows>} to %{num_rows: integer(), rows: list([])} That should be easy with recursion.
I will take columns information and apply it to each row with a type cast function.
Notice 3rd element in a column tuple{:column, "id", :int4, 23, 4, -1, 0} That’s a type I need to cast a value to.
Here what EpgsqlEx.
Query.
decode/3 turned out to be:lib/epgsql_ex/query.
ex (epgsql_ex project)Real data from PostgreSQLNow the hard part:Traverse AST of Ecto.
Query passed to EpgsqlEx.
EctoAdapter.
Connection and build a query to be consumed by EpgsqlEx.
execute/4Honestly, that’s the hardest part of this whole exercise.
I’ve spent a couple of hours reading Ecto.
Adapters.
Postgres.
Connection source code.
All 1,135 lines of code went thru my brain, leaving me completely exhausted.
Absolutely empty and depressed.
Whoever wrote it (according to git blame José Valim it is) is a real smart ass.
This is my oversimplified version of a Connection behaviour implementation.
No comments.
Sorry…lib/epgsql_ex/ecto_adapter/connection.
ex (epgsql_ex project)Final check in iex console within Demo project:All doneThat’s it.
A naive implementation of PostgreSQL adapter and Ecto adapter is complete.
All wired up together, and capable to perform a simple action of selecting all users from a table.
That was fun!ConclusionIt’s damn hard to write a database driver considering all edge cases.
It’s even harder to write a reusable and pluggable adapter.
Thank you Elixir community for sharing all that hard work for free.
Source code: https://github.
com/paveltyk/epgsql_ex.