Design Patterns: How to Create Database Object/Connection Only When Needed

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

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.

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).

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
    }
}

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.
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!

Design Pattern for returning Connection Object

Probably either Factory Design Pattern or Abstract factory design pattern; They are conceptually almost same. UML diagram of it Factory Design Pattern

The Abstract Factory Design Pattern sounds fancier, but isn't anything more complex than the Factory Design Pattern. Abstract Factory Design Pattern.

Whats the best approach (design pattern) to access database in C#?

This is the best pattern. I advise not using an ORM. especially EF.

public class MyModel 
{
    public string Id {get;set;}
    //public valuetype PropertyA {get;set;}  //other properties
}

public interface IMyModelRepository
{
    List<MyModel> GetModels();

    MyModel GetModelById(string id);

    void AddMyModel(MyModel model);
    //other ways you want to get models etc
}

public class MyModelRepositorySql : IMyModelRepository
{
    public List<MyModel> GetModels()
    {
        //SqlConnection etc etc
        while (SqlDataReader.Read())
        {
           results.Add(this.populateModel(dr));
        }
        return results;
    }

    protected MyModel populateModel(SqlDataReader dr)
    {
        //map fields to datareader
    }

    public MyModel GetModelById(string id)
    {
        //sql conn etc
        this.populateModel(dr);
    }
}

Here's my reasoning:

Using the repository pattern allows you to inject ways of persisting your data which doesn't require a database. This is essential for unit testing, but also you will find it very useful if you can inject a mock repository into your project for integration testing.

Although ORMs might seem easy at first and save you a lot of typing, they cause problems in the long run. You only need to search stack overflow for entity framework questions to see the kind of knots people get themselves tied in when they hit a query that runs in a sub optimal way.

In any large project you will run across a data fetch requirement which requires some optimized way of retrieving data, which will muck up your carefully designed object graph/injectable generic repository or clever cutting edge ORM.

POCO objects are good. Complex objects (objects which have other objects as properties) are a pain in the arse when you attempt to serialise them or recursively add to the databases, etc. Keep your underlying data models POCO and only group them together in services or viewmodels using LINQ.

Well done for using GUID ids btw! Don't listen to those fools who think they will never run out of ints! (store as varchar(50) and let the DBA sort the indexing out) the problem with any DB generated id is you have to be able to create objects without connecting to the database.

Design Patterns to switch between databases with minimal code change

I would say whatever design pattern you use, The DAO layer should be always independent and it should not depend on any of the data-base type.

For example: If someone changes the DB connection string from Mysql to Oracle than it should not affect the service and controller layer.

Design pattern to choose different database

If they have no code in common, then they are really just two separate classes. You probably don't even want the admin code to be present in the browser since it can't be used there anyway.

You could have a single factory function that looks at an admin parameter and instantiates the appropriate database object and returns it, but I don't think I even see why you'd bother with that.

Just have each client instantiate the appropriate DB object. If there's any common code, you can have both implementations inherit from a common base and put the common code in the base class.

Right! I forgot to mention, but there are some common code... the connection and authentication is exactly the same, for example. Do you happen to know any design pattern that would perfect fit for this use case? Should I research about Factories?

I think a common base class with inheritance solves the problem here. It gives you a natural place to put the common code without having to include server-side code in the client implementation. The browser implementation and the server implementation would each be a subclass that are present only on their respective platforms. The common base class would be present and used in both places.

You just have the client instantiate the appropriate leaf class and the shared code lives in the base class. If you want to hide the actual class constructors, you can use a factory function (it's just a simple function that looks at conditions and makes an appropriate object for you and returns it), but I'm not sure there's any compelling reason to use a factory function here. You would only include the appropriate leaf class on the platform for which it is intended so there's no place where you might use one or the other in the same code.

Design Patterns with database usage

...and in real life all these pizzas and stores are just rows in
database.

Does database check for invariants, business specifications or some other custom rules provided by your classes (in oop term)? No. Databases are simply a storage for most applications.

Those rows are the result of a smart and reliable computation from your domain model and business logics.
Design patterns may help you to build a flexible code that could cover all those business rules.

we know that is impracticable to create a class for each product

Design patterns merely bring some abstraction on behavior specializations and don't care about strict nature of data.

Indeed, if a ChicagoPizza class is created, this is to specify some unique specialization, not for simply marking: "this pizza is a Chicago one".

I don't know many applications that deal with more than dozens objects related to the same theme, all having some strong and distinct specializations.

In case of creational patterns (as you mentioned), I'd say that if you have so many "objects" that differ only by their "nature", you should just end up with a simple kind of type attribute (on the base class) mapped in database rather than using extra subclasses in this case.

I don't find a gap at all between what is mentioned on programming books and the software reality.

Your domain model (hard-coded by definition) should almost always be favored over database thinking.

In conclusion:

I know they are all examples, but I think that is painful after you learn from a book

If "example" means distinct from reality, then they are not examples at all.

They are just a mini portion of a real software that could be made.

How to create multiple database connections for different databases in java

As you have not tagged your question with any of this, hibernate, JPA, ORM, I assume you are dealing with plain JDBC.

Having said that, I suggest you to have a DAO layer to deal with underlying databases, and leave the connection details to specific implementations. You can configure your connection strings in some .properties files, lets say.

[Complement]

You can also make use of DAO factory, an implementation of Abstract Factory or Factory Method pattern, whichever suits here.

[Links]

A very fine implementation of DAO and DAO Factory, by BalusC
Core J2EE Patterns -- arguably dated but might provide some idea.

im looking design pattern for developed project in java

The Command Pattern

That is the Command Pattern. The idea behind this pattern is to allow functionality to be passed around. A typical Command class contains a simple method, execute. For example:

public interface Command {
    public void execute();
}

public class MyCommand implements Command { 
    public void execute() {
        System.out.println("This is the output from MyCommand");
    }
}

This is useful when you aren't sure what functionality will be needed at run time, so you create a pool of commands and select them as needed, usually by some external configuration.

Another motivation for this pattern is that it allows you to chain your commands together in a dynamic data structure, allowing you to implement "undo" functionality very easily. Obviously this would create the need for a revert() method in our example.

Factory Pattern

It is also the Factory Pattern. This pattern allows you to abstract the details of an objects creation away from the classes that call it. In your case, you don't want internal knowledge of your Database connection details to be littered all over your app, so you wrap it up in the Factory method so that it simply churns out a connection object when you want it.

Note

You have an SQL Injection vulnerability in your code. This will allow users to insert their own SQL and run unauthorised queries on your database.
Doing it this way introduces a concept called "Global State" into your application. This is generally undesirable. Instead, I would recommend using a Dependency Injection framework to inject this object into your classes. That way, no code needs knowledge of your DB details; it's all stored in a config file.

Design Patterns: How to Create Database Object/Connection Only When Needed