[erlang-questions] Newbie Question: Module with DB Connection as a "global state"

[Garrett Smith]

Hi Michael,

On Wed, Oct 5, 2011 at 8:36 AM, Michael Weibel - Amiado Group
<michael.weibel@REDACTED> wrote:
> Hi all,
>
> I'm new to erlang, new to this list and I hope here's the right place to ask this question.

Welcome!

>
> I'm currently implementing a module for ejabberd where I need to log certain packets to a MySQL DB and I'm using the MySQL Native Driver for it.
>
> My current implementation is on github:
> https://github.com/amiadogroup/mod_log_chat_mysql5/blob/master/src/mod_log_chat_mysql5.erl
>
> In this file you see that I'm opening the MySQL Connection in the function "open_mysql_connection" and assign it to a ETS Table because I didn't find another way to store the DB Reference (I tried something with records but didn't succeed).
>
> This worked pretty well but I ran into the problem that after some days/weeks the ets table didn't have the DB Reference anymore.
> To prevent this, I want to create functions which setup the DB Connection again, if it doesn't have it anymore.

You typically don't want to do this. There's a much, much better way...

> As I'm trying to do this I run into the problem that the informations about how to connect to the db is only in the init-function available and I don't really want to store this information also in the ets table.

The "go to" pattern in Erlang for what you're trying to do is a
supervised process. Using standard OTP facilities, you get this:

- Registration of process IDs using atoms (analogous to you storing a
connection PID in ets)
- Well defined "init" logic that's automatically applied when a
process needs to be restarted
- Encapsulation of the messy details of handling service provisioning

Fortunately, the MySQL driver (at the more recent ones - there are
several lineages out there) provides an OTP compliant connection
process, so you can do something as simple as this:

-module(my_db).

-export([start_link/0, fetch/1]).

start_link() ->
    Host = "localhost",
    Port = 3306,
    Db = "`my-db`",
    User = "root",
    Password = "password",
    mysql:start_link(?MODULE, Host, Port, User, Password, Db, fun mysql_log/4).

fetch(Sql) ->
    mysql:fetch(?MODULE, Sql).

mysql_log(_Module, _Line, _Level, _FormatFun) ->
    ok.

This is a registered process (i.e. there's one of them at runtime)
that provides access to your database.

The connection config in start_link/0 would typically come from an
application config, which you can read using application:get_env/2.
You can also create a start_link that takes the params.

To use this module as a service, you need to plug it into your
supervisory hierarchy. Here's what your top-level supervisor might
look like:

-module(my_app_sup).

-behaviour(supervisor).

-export([start_link/0]).

-export([init/1]).

-define(SERVER, ?MODULE).

start_link() ->
    supervisor:start_link({local, ?SERVER}, ?MODULE, []).

init([]) ->
    {ok, {{one_for_one, 5, 5},
          [{my_db, {my_db, start_link, []},
            permanent, 5000, worker, [my_db]}]}}.

If your my_db service crashes, the supervisor will restart it. The
"config" that you'd use for that restart would come from either the
OTP app config. (You *could* provide it in the supervisor child spec,
but I wouldn't go that way.)

Erlang gives you very nice facilities to create a mini "service
oriented architecture" -- you want to look for ways to create very
opaque services that have drop-dead simple start/restart semantics
(i.e. simple as pushing a button) and let supervisors monitor and
recover failed services as needed.

If for some reason you needed to dynamically configure your MySQL
connection params, you could store the config in a database, external
file, etc. and force a restart of your my_db process to re-read that
config on init.

Garrett