[ruby-talk:18697] Re: dbi and class design question

markus jais <[e-mail]> writes:

> Is it a good idea to create a database (e.g. called "dbh") handle in
> each method and call at the end dbh.disconncect..
... 
> on the other hand: are the performace disadvantages with this
...
> another idea would be to make dbh global because several classes
> need a database handle.

Yet one more idea, which is probably better in most circumstances, is
to have a global connection pool (which can be a singleton object to
avoid the stigma of having a global :). When a method needs to talk
with a database, it calls the connection pool for a handle. When it
has finished, it passes the handle back to the pool. The pool keeps
these connections open and available for use, so there's almost no
overhead when a method asks for a connection.

This approach makes writing database access stuff easy, as all the
DB-layer methods look something like:

   def getXYZ
     db = Pool.getConnection

     begin
        res = db.query ....   # or whatever

     ensure
        db.close
     end
   end

In a single threaded application, this isn't much different to using
the global, except the discipline of having to signal the end of your
use of the connection with a 'close' will help structure your code. If
you start to handle multiple things concurrently, though, this
approach will start to shine.

Using this approach does have some downsides. I've seen Java
programmers (connection pooling is common in Java-based server
applications) forget that they're dealing with a transactional
database, and fail to group things together properly. For example,
there may be a transaction which debits one account and credits
another. They might (wrongly) write a method to do the debit, another
to do the credit, and have each use it's own DB connection. Obviously
in this case the connection needs to be shared between the two so that
the debit and credit happen in the same transaction.

Anyway, writing a simple connection pool in Ruby should be just a few
lines of code.


Regards


Dave