Design Patterns: How to create database object/connection only when needed?

Question

I've a simple application, say it has some classes and an "extra" one that handles database requests. Currently i'm creating the database object everytime the app is used, but in some cases there's no need for a database connection. I'm doing it like this (PHP btw):

$db = new Database();    
$foo = new Foo($db); // passing the db

But sometimes the $foo object does not need db access, as only methods without database actions are called. So my question is: What's the professional way to handle situations like this / how to create the db connection/object only when needed ?

My goal is to avoid unnecessary database connections.

Solution 1

Note: Although the direct answer to ops question, "when can I only create / connect to the database when required and not on every request" is inject it when you need it, simply saying that is not helpful. I'm explaining here how you actually go about that correctly, as there really isn't a lot of useful information out there in a non-specific-framework context to help in this regard.

---

Updated: The 'old' answer to this question can be see below. This encouraged the service locator pattern which is very controversial and to many an 'anti-pattern'. New answer added with what I've learned from researching. Please read the old answer first to see how this progressed.

New Answer

After using pimple for a while, I learned much about how it works, and how it's not actually that amazing after all. It's still pretty cool, but the reason it's only 80 lines of code is because it basically allows the creation of an array of closures. Pimple is used a lot as a service locator (because it's so limited in what it can actually do), and this is an "anti-pattern".

Firstly, what is a service locator?

The service locator pattern is a design pattern used in software development to encapsulate the processes involved in obtaining a service with a strong abstraction layer. This pattern uses a central registry known as the "service locator" which on request returns the information necessary to perform a certain task.

I was creating pimple in the bootstrap, defining dependencies, and then passing this container to each and every single class I instantiated.

Why is a service locator bad?

What's the problem with this you say? The main problem is that this approach hides dependencies from the class. So if a developer is coming to update this class and they haven't seen it before, they're going to see a container object containing an unknown amount of objects. Also, testing this class is going to be a bit of a nightmare.

Why did I do this originally? Because I thought that after the controller is where you start doing your dependency injection. This is wrong. You start it straight away at the controller level.

If this is how things work in my application:

Front Controller --> Bootstrap --> Router --> Controller/Method --> Model [Services|Domain Objects|Mappers] --> Controller --> View --> Template

...then the dependency injection container should start working right away at the first controller level.

So really, if I were to still use pimple, I would be defining what controllers are going to be created, and what they need. So you would inject the view and anything from the model layer into the controller so it can use it. This is Inversion Of Control and makes testing much easier. From the Aurn wiki, (which I'll talk about soon):

In real life you wouldn't build a house by transporting the entire hardware store (hopefully) to the construction site so you can access any parts you need. Instead, the foreman (__construct()) asks for the specific parts that will be needed (Door and Window) and goes about procuring them. Your objects should function in the same way; they should ask only for the specific dependencies required to do their jobs. Giving the House access to the entire hardware store is at best poor OOP style and at worst a maintainability nightmare. - From the Auryn Wiki

Enter Auryn

On that note, I'd like to introduce you to something brilliant called Auryn, written by Rdlowrey that I was introduced to over the weekend.

Auryn 'auto-wires' class dependencies based on the class constructor signature. What this means that, for each class requested, Auryn finds it, figures out what it needs in the constructor, creates what it needs first and then creates an instance of the class you asked for originally. Here's how it works:

The Provider recursively instantiates class dependencies based on the parameter type-hints specified in their constructor method signatures.

...and if you know anything about PHP's reflection, you'll know some people call it 'slow'. So here's what Auryn does about that:

You may have heard that "reflection is slow". Let's clear something up: anything can be "too slow" if you're doing it wrong. Reflection is an order of magnitude faster than disk access and several orders of magnitude faster than retrieving information (for example) from a remote database. Additionally, each reflection offers the opportunity to cache the results if you're worried about speed. Auryn caches any reflections it generates to minimize the potential performance impact.

So now we've skipped the "reflection is slow" argument, here's how I've been using it.

How I use Auryn

• I make Auryn part of my autoloader. This is so that when a class is asked for, Auryn can go away and read the class and it's dependencies, and it's dependencies' dependencies (etc), and return them all into the class for instantiation. I create the Auyrn object.

  $injector = new \Auryn\Provider(new \Auryn\ReflectionPool);
  

• I use a Database Interface as a requirement in the constructor of my database class. So I tell Auryn which concrete implementation to use (this is the part you change if you want to instantiate a different type of database, at a single point in your code, and it'll all still work).

  $injector->alias('Library\Database\DatabaseInterface', 'Library\Database\MySQL');
  

If I wanted to change to MongoDB and I'd written a class for it, I'd simple change Library\Database\MySQL to Library\Database\MongoDB.

• Then, I pass the $injector into my router, and when creating the controller / method, this is where the dependencies are automatically resolved.

  public function dispatch($injector)
  {
      // Make sure file / controller exists
      // Make sure method called exists
      // etc...
  
      // Create the controller with it's required dependencies
      $class = $injector->make($controller);
      // Call the method (action) in the controller
      $class->$action();
  }
  

Finally, answer OP's question

Okay, so using this technique, let's say you have the User controller which requires the User Service (let's say UserModel) which requires Database access.

class UserController
{
    protected $userModel;

    public function __construct(Model\UserModel $userModel)
    {
        $this->userModel = $userModel;
    }
}

class UserModel
{
    protected $db;

    public function __construct(Library\DatabaseInterface $db)
    {
        $this->db = $db;
    }
}

If you use the code in the router, Auryn will do the following:

• Create the Library\DatabaseInterface, using MySQL as the concrete class (alias'd in the boostrap)
• Create the 'UserModel' with the previously created Database injected into it
• Create the UserController with the previously created UserModel injected into it

That's the recursion right there, and this is the 'auto-wiring' I was talking about earlier. And this solves OPs problem, because only when the class hierarchy contains the database object as a constructor requirement is the object insantiated, not upon every request.

Also, each class has exactly the requirements they need to function in the constructor, so there are no hidden dependencies like there were with the service locator pattern.

RE: How to make it so that the connect method is called when required. This is really simple.

1. Make sure that in the constructor of your Database class, you don't instantiate the object, you just pass in it's settings (host, dbname, user, password).

2. Have a connect method which actually performs the new PDO() object, using the classes' settings.

   class MySQL implements DatabaseInterface
   {
       private $host;
       // ...
   
       public function __construct($host, $db, $user, $pass)
       {
           $this->host = $host;
           // etc
       }
   
       public function connect()
       {
           // Return new PDO object with $this->host, $this->db etc
       }
   }
   

3. So now, every class you pass the database to will have this object, but will not have the connection yet because connect() hasn't been called.

4. In the relevant model which has access to the Database class, you call $this->db->connect(); and then continue with what you want to do.

In essence, you still pass your database object to the classes that require it, using the methods I have described previously, but to decide when to perform the connection on a method-by-method basis, you just run the connect method in the required one. No you don't need a singleton. You just tell it when to connect when you want it to, and it doesn't when you don't tell it to connect.

---

Old Answer

I'm going to explain a little more in-depth about Dependency Injection Containers, and how they can may help your situation. Note: Understanding the principles of 'MVC' will help significantly here.

The Problem

You want to create some objects, but only certain ones need access to the database. What you're currently doing is creating the database object on each request, which is totally unnecessary, and also totally common before using things like DiC containers.

Two Example Objects

Here's an example of two objects that you may want to create. One needs database access, another doesn't need database access.

/**
 * @note: This class requires database access
 */
class User
{
    private $database;

    // Note you require the *interface* here, so that the database type
    // can be switched in the container and this will still work :)
    public function __construct(DatabaseInterface $database)
    {
        $this->database = $database;
    }
}

/**
 * @note This class doesn't require database access
 */
class Logger
{
    // It doesn't matter what this one does, it just doesn't need DB access
    public function __construct() { }
}

So, what's the best way to create these objects and handle their relevant dependencies, and also pass in a database object only to the relevant class? Well, lucky for us, these two work together in harmony when using a Dependency Injection Container.

Enter Pimple

Pimple is a really cool dependency injection container (by the makers of the Symfony2 framework) that utilises PHP 5.3+'s closures.

The way that pimple does it is really cool - the object you want isn't instantiated until you ask for it directly. So you can set up a load of new objects, but until you ask for them, they aren't created!

Here's a really simple pimple example, that you create in your boostrap:

// Create the container
$container = new Pimple();

// Create the database - note this isn't *actually* created until you call for it
$container['datastore'] = function() {
    return new Database('host','db','user','pass');
};

Then, you add your User object and your Logger object here.

// Create user object with database requirement
// See how we're passing on the container, so we can use $container['datastore']?
$container['User'] = function($container) {
    return new User($container['datastore']);
};

// And your logger that doesn't need anything
$container['Logger'] = function() {
    return new Logger();
};

Awesome! So.. how do I actually use the $container object?

Good question! So you've already created the $container object in your bootstrap and set up the objects and their required dependencies. In your routing mechanism, you pass the container to your controller.

Note: example rudimentary code

router->route('controller', 'method', $container);

In your controller, you access the $container parameter passed in, and when you ask for the user object from it, you get back a new User object (factory-style), with the database object already injected!

class HomeController extends Controller
{
    /**
     * I'm guessing 'index' is your default action called
     *
     * @route /home/index
     * @note  Dependant on .htaccess / routing mechanism
     */
    public function index($container)
    {
        // So, I want a new User object with database access
        $user = $container['User'];

       // Say whaaat?! That's it? .. Yep. That's it.
    }
}

What you've solved

So, you've now killed multiple birds (not just two) with one stone.

• Creating a DB object on each request - Not any more! It's only created when you ask for it because of the closures Pimple uses
• Removing 'new' keywords from your controller - Yep, that's right. You've handed this responsibility over to the container.

Note: Before I continue, I want to point out how significant bullet point two is. Without this container, let's say you created 50 user objects throughout your application. Then one day, you want to add a new parameter. OMG - you now need to go through your whole application and add this parameter to every new User(). However, with the DiC - if you're using $container['user'] everywhere, you just add this third param to the container once, and that's it. Yes, that totally is awesome.

• The ability to switch out databases - You heard me, the whole point of this is that if you wanted to change from MySQL to PostgreSQL - you change the code in your container to return a new different type of database you've coded, and as long as it all returns the same sort of stuff, that's it! The ability to swap out concrete implementations that everyone always harps on about.

The Important Part

This is one way of using the container, and it's just a start. There are many ways to make this better - for example, instead of handing the container over to every method, you could use reflection / some sort of mapping to decide what parts of the container are required. Automate this and you're golden.

I hope you found this useful. The way I've done it here has at least cut significant amounts of development time for me, and it's good fun to boot!

Solution 2

This is approximately what I use.

class Database {

    protected static $connection;

    // this could be public if you wanted to be able to get at the core database
    // set the class variable if it hasn't been done and return it
    protected function getConnection(){
        if (!isset(self::$connection)){
            self::$connection = new mysqli($args);
        }
        return self::$connection;
    }
    // proxy property get to contained object 
    public function __get($property){
        return $this->getConnection()->__get($property);
    }
    // proxy property set to contained object
    public function __set($property, $value){
        $this->getConnection()->__set($property, $value);
    }

    // proxy method calls to the contained object
    public function __call($method, $args){
        return call_user_func_array(array($this->getConnection(), $method), $args);
    }

    // proxy static method calls to the contained object
    public function __callStatic($method, $args){
        $connClass = get_class($this->getConnection());
        return call_user_func_array(array($connClass, $method), $args);
    }
}

Note it only works if there is a single database in play. If you wanted multiple different databases it would be possible to extend this but beware of late static binding in the getConnection method.

Solution 3

Here is an example of a simple approach:

class Database {
  public $connection = null ;

  public function __construct($autosetup = false){
    if ($autosetup){
      $this->setConnection() ;
    }
  }

  public function getProducts(){//Move it to another class if you wish
    $this->query($sql_to_get_products);
  }

  public function query($sql) {
    if (!$connection || !$connection->ping()){
      $this->setupConnection() ;
    }
    return $this->connection->query($sql);
  }

  public function setConnection(){
    $this->connection = new MySQLi($a, $b, $c, $d) ;
  }

  public function connectionAvailable(){
    return ($connection && $connection->ping()) ;
  }
}

Solution 4

Look into using a dependency injection container, something like Pimple would be nice place to start. With a dependency injection container you 'teach' the container how to create the objects in your application, they're not instantiated until you ask for them. With Pimple, you can configure a resource to be shared so that it's only ever instantiated once during the request no matter how often you ask the container for it.

You can setup your classes to accept the container in their constructor or use a setter method to inject into your class.

A simplified example could look like this:

<?php

// somewhere in your application bootstrap

$container = new Pimple();
$container['db'] = $container->share(
  function ($c) {
    return new Database();
  }
);

// somewhere else in your application

$foo = new Foo($container);

// somewhere in the Foo class definition

$bar = $this->container['db']->getBars();

Hope it helps.

View more solutions

Comments

[tereško]

This answer might give some pointers. Also, it might be a good idea to separate class that does domain logic from classes that deal with persistence. You could benefit from implementing data mapper pattern.

[seva.lapsha]

Generalizing the topic - there is a design pattern of lazy initialization.

[Henrique Barcelos]

If Foo does not need DB access, than it should not need one to create it, that is, this shouldn't be a constructor parameter...

[Sliq]

@HenriqueBarcelos SOME methods of foo dont need db access, while others do. Please read the question again.

[Henrique Barcelos]

I read it. I'm just saying if it is not absolutely REQUIRED for the functioning of the class, so it should not be a constructor parameter. And so, if you need to use a method with DB access, then you should use a setter before call it.

[Sven]

@HenriqueBarcelos: I object the idea of having a setter for the database injection. A setter for dependencies states "optional dependency", i.e. the class can work on it's own without anything provided to the setter. If this is not the case, like in "some methods need the database object", then constructor injection is the way to go. The database object doesn't need to connect until it's first use.

[Henrique Barcelos]

This is perfectly valid in this case. In other problems, when the creation of the dependent object is expensive, what I suggested could be a better fit. I just think that is a bit strange you have to create an object that does DB connection to use a function that do not require DB access, even if it does not open the connection.

[Jimbo]

You could use Dependency Injection and a DiC to inject the database object, currently not connected, and only in the methods you require do you run the injected database object's 'connect' method. That would work for you.

[Sliq]

@Jimbo Sounds very interesting, can you write a full answer including code example for this ? It's a candidate for the bounty answer!

[Jimbo]

@Panique Done. Once you get your DiC working, it's really simple to do things like this. I hope that helps!

[Sliq]

This looks really good, but i think the getProducts() method would better fit into another class, outside of the Database class (something like ProductHandler). And then, it gets complicated: How can the getProducts() method within ProductHandler get db access without asking for it in every method... [please comment if i'm making it overcomplicated]

[sybear]

It is fine - some basic checks even in every function, that connection exists and it is possible to proceed. Trust me, it does not consume a lot of resources, but makes it safer and allows you to report errors. And you are right about getProducts() it should be moved somewhere else, I just wrote it as an example.

[Lepidosteus]

@Panique: your models' (eg ProductHandler) methods and all are going to use a DB->query(/**/) function to make their requests, make the connection check inside that query function. Your models should not know about the status of the connection. See the edit I made to the answer.

[sybear]

@Philipp in our case an instance of Database is passed to other classes, so singleton is not really relevant here. Btw stackoverflow.com/questions/137975/…

[Sliq]

I would instantly do it like this, but this "Singletons are bad" talk gives me a bad feeling.

[Philipp]

Ok - so give me a good reason, why we need more than one database? Sure, one could say singletons are good in every case, but they fit perfectly in this case of lazy loading and make thinks simple. If you don't like the singleton concept, you could still change the singleton method against a factory which handels the db the right way.

[Philipp]

They aren't bad in general - the are bad, if you use them wrong of inflationary.

[Hugo Delsing]

Any reason for a singleton and not just a static?

[Jimbo]

So, you're passing your $container not only to your controller (somewhere else in your application), but also maybe a model as well (somewhere in the foo class). Doesn't this create tight coupling between the controller / [model/otherobject] where as pulling it out in the controller and then using only what you need in the model allow those classes to be more modular and re-usable?

[Jimbo]

Extra point: in testing, how are you going to test each Foo object? Mock a container each time? Isn't the point of IoC to pass in only what you need to your created object, so you can detach it and use it elsewhere if needed (in another application, say).

[Sliq]

@Philipp Good point! But i have seen setups that really have SEVERAL database connections, as they have split user data on server A, messages on server B, logging on server C, etc. ... Seems valid!

[Sven]

Great introduction to dependency injection containers, but you really missed the point of "My goal is to avoid unnecessary database connections." - because this would also be the case if there were simply two or more scripts for the webpage, with only one of them dealing with users. DI is another important step, but the really important stuff is to NOT DO SOMETHING in the constructor, at least no heavy work - like connecting to a database. Other answers cover this much better.

[Sven]

The singleton will not really help with eventually NOT creating a database connection if there is no query executed. Any other implementation will do this the same way - and does not cripple you with a singleton that cannot be really tested itself and mocked in the classes that are supposed to use it. Only creating one instance of a class should not be enforced by the class itself, but by the part that creates the object construction tree. That's what dependency injection containers can help you with.

[Sven]

This example is nearly as bad as the Singleton example above. You DO NOT pass the dependency container into the object you create to allow it to fetch all the dependencies it needs from there. How would you ever know which objects are really needed? The correct solution is to configure Pimple how to create ANY object of long lifetime, including the Foo, and pass an instance of the database as a constructor parameter. But this does not explain whether or not instantiating the database object will instantly connect to the database or not.

[Sliq]

Hmmm, it mainly about the connection to the database, which is by far the most intensive/expensive part of the script, not about creating objects. So, to put this into reality, if you have high traffic site, but only 50% of your page requests really need database connections, then this becomes interesting...

[Jimbo]

OP currently creates a new object/connection on each request. Using pimple / closures to contain this means that only when he asks for it is it created. His goal is "to avoid unnecessary database connections" - which he has right now, creating one on each request. The method I propose ensures that his goal is fulfilled.

[Jimbo]

As for the no-no in the constructor, how about running the connect method in the DI container?

[Sven]

There is a difference between "eventually creating the database object, which always creates the connection" and "creating the database object, which eventually creates the connection". Both can be combined, but your answer only addresses the first one, while the second one has the real benefit in terms of connection optimization.

[Sven]

The DI container does not have the task of doing anything other than creating objects and putting them together. Connecting to the database is out of scope, and really misplaced there. You should be able to use the object without any DI container.

[Sliq]

Hmm, i'm not sure that this answers the question! Can you show an example how this would be used in a "mixed" class that has methods that use the db and methods that dont use the db ?

[Danijel]

$db = new Database( 'host', 'user', 'password', 'dbname' ); does not opens a new connection to db, $result = $db->query( $query ); opens new connection, so if you create db object and pass to Foo object like $foo = new Foo($db); the actual connection is still not open.

[Jimbo]

Note: This was a post before learning about the service locator anti-pattern. Will update soon...

[Jimbo]

Singletons are just another form of global state - if you don't understand why "singletons are evil", google it.

[Loek Bergman]

It is great to read that you are enthusiastic about Pimple, but it sounds more like advertising then answering the question. However Pimple is not a DI container. It is an associative array and using the service locator pattern. Furthermore is it not simple to change databases. MySQL and Postgresql behave very differently and to make optimal use of both different systems should you have different code. Pimple can be very helpful, but it is not that powerful as you say. Java Spring might be, but do you know how slow that is?

[Jimbo]

@LoekBergman I understand. As I said, this was before learning about true DI containers and how pimple is practically just an associative array. I've started using Auryn and, with it's auto-wiring depending on class signatures, it's really powerful! When I say simple to change databases, I mean if you create your MySQL class and your MongoDB class, you only change how the db class gets instantiated at one point in your application to switch over. I'll update with what I've learned soon.

[Sliq]

A little note: This answer has 3 upvotes and 3 downvotes, which clearly shows how controversial this Singleton thing is... interesting.

[Loek Bergman]

@Jimbo: good to hear. Now for the autowiring based on class signatures: that is a whole other discussion. But Auryn is a step forward in comparison to Pimple considering DI. I always think that for DI the client only needs to know the interface and the container provides the current implementation. The client does not have to know and does not want to know. No interface required, no DI imo.

[Sven]

@LoekBergman: DI is the essence of everything. Do not mix up DI as the act of a class asking for other objects to be injected into, and the whole manual or automatic magic of DI containers. You can do DI without a DI container, but you can't use a DI container without first implementing DI in your code.

[Sven]

@LoekBergman: Regarding your criticism about Pimple: It is NOT a service locator. You can use it as one. You can use Auryn as a service locator, too. Auryn's author warns the user not to do this in the first wiki page. The only difference is that Auryn does a lot more automagic when resolving typehints, and Pimple does not. Which one is better depends on anyones preferences and might contain parameters like "performance" and "ease of use".

[Loek Bergman]

@Sven: If you take a look at the source of Pimple, then will you see that it extends ArrayAccess and is in fact an associative array. It uses the service locator pattern, we can't make anything else of it. The service locator pattern has also his good use, for instance presenting connections to the outside world, but with DI is the control in the application better established. I did not say Pimple is bad, I said that it is not an DI container. I agree with you that there are good reasons to use Pimple and other good reasons to use Auryn.

[Sven]

ArrayAccess is the API to configure parts of it - which has it's drawbacks. It LOOKS like an array, but it is not. And it only is a service locator if you inject the Pimple object into a productive class to let it fetch it's dependencies from Pimple. The same method would make any other DI container including Auryn be a service locator.

[Jimbo]

I have updated my post with what I have learned, the point is only instantiate the database object when requested. So I explained a good way of doing that using a real DiC. Honest opinions appreciated :)

[Jimbo]

If you ever go to any PHP conference and ask any knowledgeable developers who have had plenty of time with many different systems and architectures about singletons, they'll tell you to stay away. Don't want two database objects? Just don't instantiate two. Also, the reasons for not using them are all over stackoverflow.

[Sven]

Ok, I'll upvote your answer now, too, because of the effort that went into it. But it still fails to answer the original question for most of it's length. Only in the last code example of your new answer do you mention to not connect to the database in the constructor of the DB object, but "later". Apart from that, you still can improve explaining DI and DIC and why DIC and SL are different although they use the same object. Also, why it is ok to use a DIC in SL style in the controller class (hint: You have to start asking the DIC for objects at some clearly defined point, but not everywhere)

[Sliq]

I just awarded this answer as the best so far. Thanks, Jimbo! But an Auryn-less/native code example would be perfect, maybe i can add this later (have no time to try it out now).

[johnlemon]

@Jimbo, I understand how you inject the dependecies in the controller but how about the models? How does a controller or a service facilitate the injection of models if the $injector object is not passed to them?

[Jimbo]

@danip Your controller specifies which services from your model layer it will need in the constructor of the controller. Then, within your controller, set your service parameters using setters, and that's it :-)

[Orangepill]

Singleton like I have above is not the preferred way of solving this problem. the answers colin and vikingmaster supplied better follow best practice

[Sliq]

please do NOT use this code in any way, it's totally outdated (since PHP 5.5), insecure and removed from PHP 7.0+ !