MySQL/OTP + hnc provides connection pooling for MySQL/OTP using hnc. It contains convenience functions for executing SQL queries on a connection in a pool and lets you choose between two methods for creating and managing connection pools:
- Use it as a library that helps you supervise your own MySQL connection pools.
- Use it as an application that manages its own supervisor for connection pools.
Use mysql_hnc:child_spec/3
to get a child spec for a pool that you can use
in your own supervisor.
%% my own supervisor
init([]) ->
PoolOptions = #{size => {5, 10}},
MySqlOptions = [{user, "aladdin"}, {password, "sesame"}, {database, "test"},
{prepare, [{foo, "SELECT * FROM foo WHERE id=?"}]}],
ChildSpecs = [
%% MySQL pools
mysql_hnc:child_spec(pool1, PoolOptions, MySqlOptions),
%% other workers...
{some_other_worker, {some_other_worker, start_link, []},
permanent, 10, worker, [some_other_worker]}
],
{ok, #{strategy => one_for_one}, ChildSpecs}}.
This approach requires you to start the application mysql_hnc
. Typically
this is done by adding {applications, [mysql_hnc]}
to your .app.src
file and then relying on your favourite release tool for the rest.
Pools can be added at run-time using mysql_hnc:add_pool/3
.
Pools can also be created at start-up by defining configuration parameters for
mysql_hnc
. The name of each configuration parameter is the pool name and
the value is a pair on the form {PoolOptions, MySqlOptions}
.
Example:
Start your Erlang node with erl -config mypools.config
where mypools.config
is a file with the following contents:
[
{mysql_hnc, [
{pool1, {#{size => {5, 10}},
[{user, "aladdin"}, {password, "sesame"}, {database, "test"},
{prepare, [{foo, "SELECT * FROM foo WHERE id=?"}]}]}}
]}].
The most commonly used MySQL functions are available with wrappers in
mysql_hnc
.
1> mysql_hnc:query(pool1, "SELECT * FROM foo WHERE id=?", [42]).
{ok,[<<"id">>,<<"bar">>],[[42,<<"baz">>]]}
2> mysql_hnc:execute(pool1, foo, [42]).
{ok,[<<"id">>,<<"bar">>],[[42,<<"baz">>]]}
For transactions, the connection pid is passed to the transaction fun as the first parameter.
3> mysql_hnc:transaction(pool1, fun (Pid) ->
ok = mysql:query(Pid, "INSERT INTO foo VALUES (?, ?)", [1, <<"banana">>]),
ok = mysql:query(Pid, "INSERT INTO foo VALUES (?, ?)", [2, <<"kiwi">>]),
hello
end).
{atomic, hello}
Sometimes you need to checkout a connection to execute multiple queries on it,
without wrapping it in an SQL transaction. For this purpose you can use either
a pair of calls to checkout/1
and checkin/1
or a call to with/2
with a
fun as in this example:
4> mysql_hnc:with(pool1, fun (Pid) ->
{ok, _, [[OldTz]]} = mysql:query(Pid, "SELECT @@time_zone"),
ok = mysql:query(Pid, "SET time_zone = '+00:00'"),
%% Do some stuff in the UTC time zone...
ok = mysql:query(Pid, "SET time_zone = ?", [OldTz])
end).
ok
The function checkout/1
does not return a MySQL connection directly but a
connection identifier (actually, a hnc
worker identifier). You have to
unpack the real connection with a call to get_connection/1
first.
Accordingly, the function checkin/1
does not expect a MySQL connection but
the connection identifier from which it was unpacked.
Using erlang.mk, put this in your Makefile
:
DEPS = mysql_hnc
dep_mysql_hnc = git https://github.com/mysql-otp/mysql-otp-hnc 0.2.0
Using rebar, put this in your rebar.config
:
{deps, [
{mysql_hnc, ".*", {git, "https://github.com/mysql-otp/mysql-otp-hnc",
{tag, "0.2.0"}}}
]}.
GNU Lesser General Public License (LGPL) version 3 or any later version. Since the LGPL is a set of additional permissions on top of the GPL, both license texts are included in the files COPYING.LESSER and COPYING respectively.