When creating a DAO, when should the backend connection occur?

Question

I was thinking through some DAO design stuff with a teammate and came across an interesting question about which I have yet to find any best practices guidelines.

When creating a DAO object, when should the DB connection occur? We've talked through instantiating an object that creates a DB connection in a constructor (then uses it in subsequent methods) and making a static class that establishes a connection and tears it down after every method call.

Both have clear performance implications based on environment - a static object would be good for a few intermittent calls whereas instantiating an object that holds a connection would be great for a number of calls in a short amount of time for a small number of users.

Is there another way to do this? Perhaps something that would be better for performance across most situations?

Is this question specific to any technology? When not let an ORM and framework handle it? ie - Hibernate / Spring if you're working with Java. — bvulaj, May 04 '12 at 14:54
I'm currently working in Java, but I was wondering about the best practice for this in general (regardless of implementation language). — josh-cain, May 04 '12 at 15:13
Best practice, in general, is to have a transaction manager handling the connections. Although it's technically ok for your DAO to handle a connection, I prefer it to have no knowledge. As Brandon already recommended, ORMs are very nice and whatever framework you are using will probably already handle tx management out of the box, leaving your DAO to worry only about querying for your entities. — Josh Johnson, May 04 '12 at 15:29

score 1 · Accepted Answer · edited Jun 20 '20 at 09:12

_{Since you have tagged this as mvc, I will assume that your DAOs already exist within the model layer. As i understand it. the DAOs are responsible for for same task as Data Mappers, and provide the information from storage (which might or might not be SQL database) to your Domain Objects within the Model Layer.}

The best way to deal with this issue would be to have factory, which create the data access objects within your model layer. This would have several advantages:

decoupling from the class names of different DAOs
ability to know when first DAO is initialized
sharing single DB connection between all DAO instances

This is how I would implement something like it:

class DaoFactory
{
    protected $connection = null;
    protected $provider = null;

    public function __construct( $provider )
    {
        $this->provider = $provider;
    }

    public function create( $name )
    {
        if ( $connection === null )
        {
            $connection = call_user_func( $this->provider );
        }
        $instance = new $name;
        $instance->setConnection( $connection );
        return $instance
    }
}

And the usage would look something like this:

$connectionProvider = function()
{ 
    $instance = new \PDO('mysql:host=localhost;dbname=testdb;charset=utf8', 
                         'username', 'password');
    $instance->setAttribute(\PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION);
    $instance->setAttribute(\PDO::ATTR_EMULATE_PREPARES, false);
    return $instance;
};

$daoFactory = new DaoFactory( $connectionProvider );

Then you pass the $daoFactory variable to whichever object will require to create DAO. This way, if need arises, you can easily replace the factory (as long as it implements same create() method footprint), with something completely different. And your domain business logic is in no way impacted by such a change.

The objects which use this factory became completely unaware that there even exists a database connection, and that DAOs need it to function. And you can even end up with situations, when you don't even need to establish a connection to database.

^{Rant
Since you have data access objects on your codebase, it would indicate that you are trying to use OOP. Or at least something similar. But the static classes/functions/variables are not part of OOP paradigm. If you have a class with static-only methods and few static variables, what you have actually made are just simple php function with global variables, wrapped in a namespace, which just happens to look like class.}

^{PHP community tends to call this COP (class oriented programming) as a joke, but essentially it is procedural code.}

When creating a DAO, when should the backend connection occur?

1 Answers1