Practical PHP Patterns: Database Session State

In the realm of web applications, the Database Session State pattern is a rarely followed alternative for the goal of maintaining the user session's state across different requests. We have seen earlier the two most common patterns - Client and Server Session State - which persist data on the client itself or in a data structure on the server addressable by a key saved on the client. This data structure is usually kept in the central memory of the server or on another fast storage mechanism.

Using a database

The Database Session State prescribe to store the data directly in the database instead. This design choice raises some issues and advantages, in a trade-off typical of each of this Session State pattern.

For starters, commonly session data is considered local to the user's transaction until it is committed, and can be thrown away at any moment if the user simply wals away from it by closing the browser. The usual example of session data is the shopping cart filled with various products that will be checked out only when the user completes the transaction.

Worries

Thus, how we can distinguish session data from permanent one if both are kept in the database? The bought products from the pending ones?

A first solution is to introduce isPending fields in the relative tables or models, that are valorized when the data is not already considered permanent.

Needless to say, this solution is very invasive as it modified the schema of the database for an orthogonal concern like the lifecycle of data.
A more orthogonal solution is a separate set of tables (at the relational level) or model (at the object-oriented one), which contain the transient data and can be optimized independently.

For example, this week we were working on licensing management in a Zend Framework web application; the total number of user logged in at the same time was kept in an additional table with a name like LoggedUsers, especially suited for this purpose. This table contained a timestamp field and a reference to each session id, to allow checking the number of users logged in simultaneously, which as part of the licence mechanism must not exceed a maximum. This mechanism was put in place without cluttering the main users table at the same time: that table contained data that changes far less often than the login's one.

Implementation

When you put session state directly in a database, you'll probably have to manage some additional code for rules that must be applied either only on session or persistent state. This is the case with most of the user transactions which can be completed in more than one step, while the final result saved in the original table would have to conform to all the constraints like the presence of mandatory values and relationships. That's why I prefer to keep a separate model for transactions, which can either be saved in the session or in the database when they are completed and correct.

In general, state that must be shared between different user, even when it changes often, is the greatest candidate for this pattern. Counting the logged in users for licensing issues was an example of this situation.

A final alternative to the whole set of Session State patterns we encountered it to not have any session data and prescribe independent requests, with the state of the application kept in the database but no client sessions that span over multiple HTTP requests. The REST paradigm follows this architecture with its tenet of stateless interactions, and typical RESTful applications create resources for everything that will be alternatively kept in session data. HTTP was created as a stateless protocol, and REST follows its original definition very closely.

The issues of stale data

When you keep session state in a database, cleaning out obsolete data becomes an immediate concern. Stale data can bring the data storage to an halt when it begin to grow everyday incontrollably.
Databases are persistent by nature and don't have, by definition, garbage collecting mechanisms like the one of PHP sessions, which are cleaned after a configurable idle time and at every server restart (which does not happen very often however). Thus you must implement manually every kind of automated periodical cleaning, for example with a cron script or poormanscron-like one which is executed periodically.

Performance

With this pattern, you gain on one side of performance since the application layer is stateless and can be replicated at will: all the relevant state is kept in the database. For example, you don't have to worry about a user being sent to different HTTP servers in requests since they are all equivalent and pull the necessary data from the database by design. The application layer commonly becomes a bottleneck long before the database, due to its custom code, much less specialized than the database's one.

However, you pay back in the time needed to pull back data from another machine or memory segment in the front end server. Data must go back and forth between the database and PHP, and although "native drivers" are becoming more common, they're obviously not fast as RAM-based designs.

You should consider also the programming effort involved in a Session State pattern implementation, and not only its overall performance. In general, the less large the session state, the less is a problem sending it back and forth from the database. Moreover, clustering and failover mechainisms are simpler to design than with Server Session State, since again the server can be replicated and switched at will.

Example

In this small code sample, we persist the current products chosen by an user (referred via session id) in a database table, and provide some API methods to clear the table of old data.
The sample uses an in-memory SQLite database for didactic purpose, but you can easily change the PDO connection to use a real database.

<?php
class Cart
{
    private $connection;
    private $sessionId;

    public function __construct(PDO $connection, $sessionId)
    {
        $this->connection = $connection;
        $this->sessionId = $sessionId;
    }

    public function addProduct($name)
    {
        $stmt = $this->connection->prepare('INSERT INTO carts_products (sessionId, product) VALUES (:sessionId, :product)');
        $stmt->bindValue(':sessionId', $this->sessionId, PDO::PARAM_STR);
        $stmt->bindValue(':product', $name, PDO::PARAM_STR);
        return $stmt->execute();
    }

    public function getProducts()
    {
        $stmt = $this->connection->prepare('SELECT * FROM carts_products WHERE sessionId = :sessionId');
        $stmt->bindParam(':sessionId', $this->sessionId);
        $stmt->execute();
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }
}

$pdo = new PDO('sqlite::memory:');
$pdo->exec('CREATE TABLE carts_products (sessionId VARCHAR(255) NOT NULL, product VARCHAR(255) NOT NULL, PRIMARY KEY(sessionId, product))');
$cart = new Cart($pdo, 'df56...');
$cart->addProduct('Dune paperback novel');
$cart->addProduct('EEE Pc 701');
var_dump($cart->getProducts());