Creating one PDO and accessing it from different scripts

Question

I understand that PDO cannot be serialized into sessions, I can use singleton where the PDO is accessed through instantiation like in this example Use of PDO in classes. I've compacted the code and remove exception handling for example sake with all the necessary details for PDO connection.

class Database
{
public $db;
private static $instance;

private function __construct()
{
    $dsn = 'mysql:dbname=test;host=localhost;port=3306';
    $this->db = new PDO($dsn, 'user', 'password');
    echo 'You will see this when PDO is created or instantiated';
}

public static function getInstance()
{
    if (!isset(self::$instance))
    {
        $object = __CLASS__;
        self::$instance = new $object;
    }
    return self::$instance;
}
}

And then each page or script calls the static instance by using e.g:

$database = Database::getInstance();
$stmt = $database->db->prepare('SELECT * FROM table');
$stmt->execute();

Now this is all well and good but my understanding is that a new PDO is created every time the instance gets called. Is there a way, without using frameworks e.g. Zend Registry, to pass the PDO from page to page without creating a new PDO every time? I can't seem to find an answer to this question. Although it is a single instance it still seems rather redundant reconnecting to the database on every page. Thanks.

Solved

I think in the end it is a limitation of PHP, what I'm asking for cannot really be done. Thanks all for your help, appreciate it.

In your case, a new PDO instance gets once per session, not every time `Database::getInstance()` gets called. — Mark, Nov 25 '13 at 20:28
@YourCommonSense huh? Why should he return a PDO instance directly if he obviously has a wrapper for it? — Mark, Nov 25 '13 at 20:30
@YourCommonSense He's creating an instance of PDO in the constructor of his class, which is a database abstraction layer. So maybe he doesn't want to return a bare instance of PDO, but an instance of his class, which in turn holds an instance to PDO by way of a property? (note that he stated that a lot of code was removed for brevity) — Mark, Nov 25 '13 at 20:35
CommonSense have you considered that I have compacted this as an example and may want to add my own functions in that class? Thanks anyway. — Anthony Jr Xu, Nov 25 '13 at 20:39
ok thanks Rain do you have any resources you can point me to to read about this? — Anthony Jr Xu, Nov 25 '13 at 20:43

Bill Karwin · Accepted Answer · 2013-11-25T21:03:27.560

PHP resources are scoped to the request. That is, when you end the request, PHP cleans up resources. For example, closing files or database connections. You can't avoid the need to create a new PDO instance for each request.

But you can reduce the cost of creating that PDO instance a little bit. I assume you want to carry the PDO object from request to request to avoid overhead of opening a new connection.

MySQL connections are usually pretty quick, especially if you are connecting on localhost as you are doing. So it may be unnecessary to optimize it further.

But the overhead of creating a connection is still greater than zero, so if you want to reduce overhead a little bit more, you could use persistent connections:

$this->db = new PDO($dsn, 'user', 'password', array(PDO::ATTR_PERSISTENT => true));

How much will this improve your performance? It varies based on your level of traffic and how much work you do per request. The net benefit in your case may be insignificant.

Here's a blog where the author describes a benchmark and concludes he gets a worthwhile reduction in memory usage in PHP, and several times greater requests per second. http://blog.shay.co/pdo-persistent-connection-analysis/

But take the benchmark results from that blog with a grain of salt, because the test scripts used don't do anything besides connect. That is, they don't do any queries, they just connect. Persistent connections have no effect on other work, only on the speed of connection. In a real application, connecting is already a small percentage of the time relative to running queries and manipulating results in PHP. So reducing the overhead of part of the code that is already taking only, for instance, 2% of your time can have only a small effect.

Re comment from @AnthonyJrXu

I have honestly seldom see anyone use persistent connections with PDO. The performance difference is typically very little. Exception cases exist of course, so it might be worth your time to test and see how much benefit you get in your app.

But usually it's better to spend your time analyzing queries for the best indexes, or offloading some of your database queries to be memcached queries.

But what the OP asked for cannot be done (at least not in PHP), hence this answer shows the one of the few viable solutions to the problem. — Mark, Nov 25 '13 at 20:43
Thanks Bill, reading the blog now. But in your experience, you've never seen this been done before? — Anthony Jr Xu, Nov 25 '13 at 20:45
See edited answer above that should clarify how it relates to the OP's problem. But I don't expect @YourCommonSense to reverse his unwarranted downvote. — Bill Karwin, Nov 25 '13 at 20:46

score 0 · Answer 2 · answered Nov 25 '13 at 20:55

0

I think what you’re wanting is dependency injection in your application. You want to create a PDO instance and pass that instance throughout your application where you need a database connection. This can be in your index.php script if you’re using it as a front controller.

answered Nov 25 '13 at 20:55

Martin Bean

38,379
25
128
201

Martin, a front controller routes the user to whatever view he wants. A simpler example would be that I don't have a controller but I just reload index.php, even though it is the same page a new instance would still be called to reconnect to the database. Am I right to say this? Thanks alot. – Anthony Jr Xu Nov 25 '13 at 21:08
@Your Common Sense I'm sorry if you feel this is ambiguous, I thought I was rather clear in my explanation and examples thanks for your help though. – Anthony Jr Xu Nov 25 '13 at 21:13
@AnthonyJrXu Yes, a new PDO instance would be created on each page request, but the instance is also destroyed once a page has finished rendering. I think you need to look into the HTTP and PHP request life cycles a bit more. – Martin Bean Nov 25 '13 at 21:58
@AnthonyJrXu I don't "feel" it's ambiguous, but it is. And nowhere you were clear. Especially when talking of calling instance and mentioning frameworks (as they obviously won't help you either). – Your Common Sense Nov 26 '13 at 06:56

Creating one PDO and accessing it from different scripts

Solved

2 Answers2