Database connection pooling with PHP and React (node.php)


Last saturday I meet a new hype: “React” also known as “node.php”. Basically it’s the same idea than node.js but built with PHP instead of javascript. Twitter was on fire with this new library (at least my timeline). The next sunday was a rainy day and because of that I spent the afternoon hacking a little bit with this new library. Basically I want to create a database connection pooling. It’s one of the things that I miss in PHP. I wrote a post here some time ago with this idea with one exotic experiment building one connection pooling using gearman. Today the idea is the same but now with React. Let’s start

First of all we install React. It’s a simple process using composer.

% echo '{ "require": { "react/react": "dev-master" } }' > composer.json
% composer install

Now we can start with our experiment. Imagine a simple query to PostgreSql using PDO:

CREATE TABLE users
(
  uid integer NOT NULL,
  name character varying(50),
  surname character varying(50),
  CONSTRAINT pk_users PRIMARY KEY (uid)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE users OWNER TO gonzalo;

INSERT INTO users(uid, name, surname) VALUES (0, 'Gonzalo', 'Ayuso');
INSERT INTO users(uid, name, surname) VALUES (1, 'Hans', 'Solo');
INSERT INTO users(uid, name, surname) VALUES (2, 'Luke', 'Skywalker');
$dbh = new PDO('pgsql:dbname=demo;host=vmserver', 'gonzalo', 'password');
$sql = "SELECT * FROM USERS";
$stmt = $dbh->prepare($sql);
$stmt->execute();
$data = $stmt->fetchAll();
print_r($data);

Now we are going to use the same interface but instead of using PDO we will use one server with React:

include "CPool.php";
define('NODEPHP', '127.0.0.1:1337');

$dbh = new CPool();
$sql = "SELECT * FROM USERS";
$stmt = $dbh->prepare($sql);
$stmt->execute();
$data = $stmt->fetchAll();
$stmt->closeCursor();
print_r($data);

Our CPool library:

class CPoolStatement
{
    private $stmt;
    function __construct($sql=null)
    {
        if (!is_null($sql)) {
            $url = "http://" . NODEPHP . "?" . http_build_query(array(
                    'action' => 'prepare',
                    'sql'    => $sql
                 ));
            $this->stmt = file_get_contents($url);
        }
    }

    public function getId()
    {
        return $this->stmt;
    }

    public function setId($id)
    {
        $this->stmt = $id;
    }

    public function execute($values=array())
    {
        $url = "http://" . NODEPHP . "?" . http_build_query(array(
                'action' => 'execute',
                'smtId'  => $this->stmt,
                'values' => $values
             ));
        $this->stmt = file_get_contents($url);
    }

    public function fetchAll()
    {
        $url = "http://" . NODEPHP . "?" . http_build_query(array(
                'action' => 'fetchAll',
                'smtId'  => $this->stmt
             ));
        return (file_get_contents($url));
    }

    public function closeCursor()
    {
        $url = "http://" . NODEPHP . "?" . http_build_query(array(
                'action' => 'closeCursor',
                'smtId'  => $this->stmt
             ));
        return (file_get_contents($url));
    }
}

class CPool
{
    function prepare($sql)
    {
        return new CPoolStatement($sql);
    }
}

We also can create one script that creates one statement

include "CPool.php";
define('NODEPHP', '127.0.0.1:1337');

$dbh = new CPool();
$sql = "SELECT * FROM USERS";
$stmt = $dbh->prepare($sql);

echo $stmt->getId();

And another script (another http request for example) to fetch the resultset. Notice that we can execute this script all the times that we want because the compiled statement persists in the node.php server (we don’t need to create it again and again within each request).

include "CPool.php";
define('NODEPHP', '127.0.0.1:1337');

$stmt = new CPoolStatement();
$stmt->setId(1);

$stmt->execute();
$data = $stmt->fetchAll();
print_r($data);

And basically that was my sunday afternoon experiment. As you can imagine the library is totally unstable. It’s only one experiment. We can add transaccions, comits, rollbacks, savepoints, … but I needed a walk and I stopped:). What do you think?

The code is available at github

About these ads

About Gonzalo Ayuso

Web Architect specialized in Open Source technologies. PHP, Python, JQuery, Dojo, PostgreSQL, CouchDB and node.js but always learning.

Posted on May 21, 2012, in databases, PDO, php, PostgreSQL, Technology and tagged , , , , . Bookmark the permalink. 7 Comments.

  1. Cool, but you should use a message queue (like zeromq) between both end points, as opposed to http dispatch.

  2. Gonzalo Ayuso

    Yes. I have done one experiment with with queues with the same idea. Not with ZeroMQ but Gearman. http://gonzalo123.wordpress.com/2010/11/01/database-connection-pooling-with-php-and-gearman/

    Anyway IMHO I preffer to use a TCP server here rather than a queue.

    Both are experiments not ready to use in production.

  3. Gonzalo,
    I am very interested to hear more results from your tests, I am at that point wher I am trying to implement some sort of connection pooling for php. After reading your previous post on your gearman project I got excited thinking that you had provided the missing link. Now I am wondering if this approach would be better or worse.

    looking forward to more comments about this topic here.

    Sincerly,
    Allan

  4. Hi, its me again with another thought.

    It appears that your statement about liking tcp rather than que prompted me to think about another product I recently read about. Could this CPool post a http request to a Tomcat server which is running the Database Connection Pooling. See the link here. http://viralpatel.net/blogs/database-connection-pooling-tomcat-eclipse-db/comment-page-1/#comment-34616

    This would possibly be a more robust way of database connection pooling to let Tomcat handle it.

    Interested to know what you think.
    Allan

    • This post is one experiment. React is one exotic library (probably another experiment). I don’t recommend to use those kind of exotic things under production environments. They are good to learn new things and to open our minds walking on the edge, but production is production.

      Anyway if you have a connection pooling with Java you don’t need this. You can use a java bridge and use your Java connection pooling from PHP google a little bit with “java bridge” http://php.net/manual/en/book.java.php. Probably is the best solution if you already have a running tomcat

      You can use Database’s connection pool. For example with PostgreSql you can use pgpool (http://www.pgpool.net/mediawiki/index.php/Main_Page). Oracle has also something similar with the listener. (I don’t how to do it with mysql)

  5. I appreciate your comment,

    With this in mind, I am now thinking about using a tomcat server to employ (database connection pooling) and calling it from php running on my apache server via the java bridge concept discused above. The link I am using is http://php-java-bridge.sourceforge.net/pjb/how_it_works.php

    Hoping it will be more production stable since its been around for a long time and reports high throughput.

    Thank you,
    Allan

  1. Pingback: database-connection-pooling-with-php-and-react-node-php | 不分享空間

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 998 other followers

%d bloggers like this: