[erlang-questions] Multiple db connection drivers

[Erisa Dervishi]

Hi everybody,

I am an erlang newbie, and I am doing a survey on existing Erlang db access drivers. I am most interested in drivers that can handle and process multiple concurrent db requests. Are there any working Erlang multithread db access drivers for MySql or PostgreSQL? What about BerkleyDB? Or any other Erlang driver that can handle db requests in parallel? 

There are plenty of drivers out there, but no documentation at all, and I am not experienced enough to find out the ones I'm interested in. 

I would be really glad if you could provide me some links or valuable suggestions.

Thanks in advance!

Cheers,

Erisa

[Antoine Koener]

Hi,

Have a look at http://sqlrelay.sourceforge.net/about.html
_______________________________________________
erlang-questions mailing list
erlang-q...@erlang.org
http://erlang.org/mailman/listinfo/erlang-questions

[Max Bourinov]

For PostgreSQL we use:

 * https://github.com/wg/epgsql - driver

 * https://github.com/josephwecker/epgsql_pool - connection pool (which is gen_server)

It runs in production under heavy load. Works very well.

Best regards,

Max

[Garrett Smith]

Hi Erisa,

Erlang has so many amazing features and benefits, it's hard to keep track!

Database interfaces, however, is not on that list :)

That's a broad, sweeping statement that leaves me open to easy
criticism! But I think if you're coming from a Java or .NET
background, it's hard to not scratch your head at the state of the
available libraries.

Erisa, I would suggest first identifying which database(s) you'd like
to target, and then dig into what's available. You are likely to find
something that works, even works very well. If it's not clear which
database to look at, post your use cases and you'll probably get a
deluge of suggestions here.

Keep in mind that Erlang does not have a tradition of "ORMs" the way
Java, Ruby, Python, etc. do. You'll need to dive into the details of
whatever data stores you end up working with. I personally think this
is a benefit, but it does take more energy up front to consider your
requirements and learn the technology.

Garrett

[Tim Watson]

On 17 February 2012 15:13, Garrett Smith <g...@rre.tt> wrote:

Hi Erisa,

Erlang has so many amazing features and benefits, it's hard to keep track!

Database interfaces, however, is not on that list :)

+1 - this is most annoying. We have a custom OCI based Oracle driver at work which is lightning fast, but I'm not allowed to 'replicate' so I decided to write it from scratch but haven't had the time to get properly started. As soon as I began, I thought to myself  'what should the API look like' and kind of despaired that there isn't an equivalent to JDBC/ADO.NET that I could just conform to.   

 

That's a broad, sweeping statement that leaves me open to easy
criticism! But I think if you're coming from a Java or .NET
background, it's hard to not scratch your head at the state of the
available libraries.

The pgsql libraries mentioned are actually very good and the built in ODBC works fine if you can live with it. I'm pretty sure I've heard (on this list) that people have used it successfully in production for years without issue. Like I said we've got a fabulous Oracle/OCI driver that's not once died in production for the last three years, but I'm now thinking that any rewrite would be better off done once the NIF/Native-Process changes come in. 

 

Keep in mind that Erlang does not have a tradition of "ORMs" the way
Java, Ruby, Python, etc. do. You'll need to dive into the details of
whatever data stores you end up working with. I personally think this
is a benefit, but it does take more energy up front to consider your
requirements and learn the technology.

There is ORM-like stuff in Erlyweb and Chicago Boss. Personally I think ORMs are pants and avoid them like the plague regardless of the language I'm using, but that's just me. Anyway, I'm not sure what the 'O' part would mean in Erlang, as a mapping from db rows to tuples/lists is probably just fine for most jobs. 

I'd also suggest that the OP take a look at the source code for zotonic, which does some interesting things with a fairly low level data model backed by postgres (not much unlike the schema our oracle driver is hitting) and works very well.

[Tim Watson]

On 16 February 2012 14:15, Erisa Dervishi <eris...@gmail.com> wrote:
> Hi everybody,
>
> I am an erlang newbie, and I am doing a survey on existing Erlang db access
> drivers. I am most interested in drivers that can handle and process
> multiple concurrent db requests. Are there any working Erlang multithread db
> access drivers for MySql or PostgreSQL? What about BerkleyDB? Or any other
> Erlang driver that can handle db requests in parallel?

Max has suggested good pgsql drivers. It is worth baring in mind that
'multiple requests in parallel' is a broad requirement. Most JDBC
drivers, for example, offer thread safe Connection objects because
they serialise access, which means only one thread can proceed at
once. In practise, you need to use a connection per thread, which is
prohibitively expensive (esp. in terms of setup/teardown) and is the
reason all production java code ends up using connection pools. The
same is going to be true for erlang database drivers.

SqlRelay is an interesting one. If you use it, just bare in mind each
erlang port (linked to the external C program which is in turn,
connected to one of the connection daemons) is going to communicate
synchronously over stdio, so you'll need to do the same thing (pooling
the ports) there I would've thought.

Like Max, I can highly recommend Will's pgsql driver and there are a
number of other good ones out there, including some derived from his.

> There are plenty of drivers out there, but no documentation at all, and I am
> not experienced enough to find out the ones I'm interested in.
> I would be really glad if you could provide me some links or valuable
> suggestions.
>
> Thanks in advance!
> Cheers,
> Erisa
>

[Michael Truog]

On 02/17/2012 05:53 PM, Tim Watson wrote:
> On 16 February 2012 14:15, Erisa Dervishi <eris...@gmail.com> wrote:
>> Hi everybody,
>>
>> I am an erlang newbie, and I am doing a survey on existing Erlang db access
>> drivers. I am most interested in drivers that can handle and process
>> multiple concurrent db requests. Are there any working Erlang multithread db
>> access drivers for MySql or PostgreSQL? What about BerkleyDB? Or any other
>> Erlang driver that can handle db requests in parallel?

> SqlRelay is an interesting one. If you use it, just bare in mind each
> erlang port (linked to the external C program which is in turn,
> connected to one of the connection daemons) is going to communicate
> synchronously over stdio, so you'll need to do the same thing (pooling
> the ports) there I would've thought.

If you want to get more concurrency in your usage of an erlang port, you can use CloudI (http://cloudi.org) to provide a configured number of tcp connections to the local OS process (configured as the number of threads for a CloudI service running as a job). The CloudI API would then provide some simple message passing functions to use in each thread that could interface with SqlRelay. So, that approach could help you avoid contention, but still provide the fault-tolerance of an erlang port (i.e., avoiding the use of a port driver or NIF).

There is native Erlang code available for PostgreSQL (I agree that Will's pgsql is currently the best) and MySQL (many versions of this exist from many authors). There is a BerkleyDB port driver in EDTK, but I am not sure how much usage it has now. It seems best to stick with native Erlang code for database drivers, whenever possible, so that you have fault-tolerance and simpler concurrency.

- Michael