Is this how lazy connecting of a database connection is supposed to be?

Question

So I did some research on good way of connecting to the database in OOP. If you look at my connect() method in my database model, I am only connecting when I'm about to query and if there is no connection already. I think it's called lazy connecting, and I stumbled upon it on a SO answer.

Isn't it meant to establish only 1 database connection for the entire application?

If I do new Database() in file A and new Database() in file B, there still would be two connections.

I'm using a micro MVC framework if that's of any help.

class Database
{
    private $pdo;
    private $host;
    private $databse;
    private $username;
    private $password;

    public function __construct()
    {
        $this->host     = Config::get('mysql/host');
        $this->database = Config::get('mysql/database');
        $this->username = Config::get('mysql/username');
        $this->password = Config::get('mysql/password');
    }

    public function query($sql, $params)
    {
        $this->connect();

        $sth    = $this->pdo->prepare($sql);
        $params = is_array($params) ? $params : [$params];

        if ($sth->execute($params)) {
            return $sth->fetch(PDO::FETCH_OBJ);
        }
    }

    private function connect()
    {
        if (!$this->pdo) {
            try {
                $this->pdo = new PDO('mysql:host=' . $this->host . ';dbname=' . $this->database . ';charset=utf8', $this->username, $this->password);
            } catch (Exception $e) {
                die($e->getMessage());
            }
        }
    }
}

Should this be moved to https://codereview.stackexchange.com/? — Goose, Nov 08 '17 at 21:57

stepotronic · Answer 1 · 2017-11-13T15:15:54.870

1

I would love to comment the above answers, but reputation keeps me from doing so. I however have to make sure people don't read those and believe it is true or good practise.

On Topic: The idea of a single database connection is good until it isn't. You might have to connect to a second one and then a singleton will be hindering you from doing so. The configuration should also be done with real parameters:

You want to be able to make more than one connection and cannot because the class Config does not allow it.
This class is hard coupled to the Config class
The need of magic strings floating around your code like 'mysql/host' is not a good idea either. Someone might come along and like to call it differently in the configuration. If you only use parameters to configure that problem vanishes.
You want to be able to clearly see the dependencies a class has. Hiding the fact that it needs Config and with it 4 parameters will only force you to look into the implementation every time you want to use it.

About storing connections in the Session:

What would be the point? You cannot secure it for another PHP run.
Serialisation of it is impossible, because it is a resource.
Like above: You are potentially hiding dependencies if you store away objects in the Session on work with them globally.

http://php.net/manual/en/intro.session.php

edited Nov 13 '17 at 15:15

answered Nov 08 '17 at 21:34

stepotronic

71
1
5

To respond to your answers (and downvote). **A single db connection is very useful. When needing a second connection, OP would naturally discover that passing database connection info via the `Config` class would need adjustments. In my opinion this was not the main focus of the question. **Why store the connection in a Session var? Agreed that the connection needs to be re-established with each page load and a global variable would serve. For me, using something like `$_SESSION['db']` is a mater of convenience for storing a global variable and easier to maintain. – mseifert Nov 14 '17 at 05:08
I disagree, because a huge bit of maintainability comes from the fact that resources and objects have a clear path through the code. Using a variable like $_SESSION means you have a requirement in your code that is not obvious. The code is not reusable because of the dependency to $_SESSION. What if you want to run the code in a context that does not have one? I do get the point: "cross the bridge when you get to it". However you don't even need to build this anymore since there are good frameworks out there that make this already easier and you can focus on the important business code. – stepotronic Nov 23 '17 at 17:57
Fair enough. I've remove the reference to $_SESSION as an example of a global variable. That aside, not everyone chooses frameworks for solutions. For a large percent of applications, I stand by the usefulness of reusing the connection in a simple global variable. Thanks for the feedback on coding methodology. – mseifert Nov 23 '17 at 21:02

score 0 · Answer 2 · answered Apr 22 '18 at 22:34

Hope it's not too late to shed some light on this matter. You are mixing two concepts - 1. lazy loading (in this case lazy connecting), and 2. persistent connection.

The posted code correctly establishes an actual database connection ONLY when a query is made. The correctness of the code hinges on how the code is used. Consider the following pseudo calls:

instantiate the Database class, which invokes the constructor
explicitly call the connect function
make a query

The above sequence nullifies the benefit of the lazy connection because a connection is made regardless. If Step 2 is removed, however, the query function will call connect, which will then create a real connection to the database.

Why is this useful? In a complex project, the connection handler is often included in some header file. Database queries are made in various components. By the time a query is made, the database connection has to be ready. As a result, a connection is created even on pages that don't load anything from the database. Keep in mind that TCP connections are valuable server resources. Establishing too many unused connections is just wasteful. A lazy connecting method addresses exactly this issue.

Persistent connection is a different concept. It means that at the end of a page session the connection is not closed. When a new connection is needed, PHP looks for an "identical" connection to reuse.

For the sake of completeness, I'd like to point out that persistent connection is also not the same as connection pooling, though a persistent connection can be viewed as a connection pool of 1.

score 0 · Answer 3 · answered Nov 18 '21 at 07:45

There are option https://www.php.net/manual/en/pdo.connections.php PDO::ATTR_PERSISTENT.

If you use this option then php don't close connection, just reset a connection and reuse it.(like connection pool)

If really you want to use lazy database connection, ( only communication db server when need to do something) then you can only just make your code like https://github.com/thumbtack/querycache

score -1 · Answer 4 · edited May 23 '17 at 12:21

-1

To only have one instance of a class you need the singleton pattern

Creating the Singleton design pattern in PHP5

You could easilly add this to your existing class though.

edited May 23 '17 at 12:21

Community

1
1

answered May 11 '14 at 17:24

Jonathan

1,542
3
16
24

I know about singleton. Static classes are hard to test and I've read that lazy connecting is preferred instead. (http://stackoverflow.com/questions/4595964/is-there-a-use-case-for-singletons-with-database-access-in-php/4596323#4596323) – Kid Diamond May 11 '14 at 17:24

mseifert · Accepted Answer · 2017-11-23T20:58:58.253

-2

If you store the result of new Database() in a global variable then you can reuse this and have a single connection for the entire application.

You are correct that if you do new Database() in file A and new Database() in file B, there would be two connections.

The overall concept of your code is similar to what I use and it works well.

edited Nov 23 '17 at 20:58

answered May 11 '14 at 17:23

mseifert

5,390
9
38
100

Is this how lazy connecting of a database connection is supposed to be?

5 Answers5