What is Pomm 2

Pomm 2 is a small database access framework for PHP dedicated to Postgresql. Pomm is different from an ORM. For convenience, Pomm library has been divided into small packages:


The foundation package is Pomm’s kernel. It shares a database "physical" connection between clients through a session. There are different kind of clients, parametrized statements, prepared queries, converter, database inspector. These different kind of clients are managed by pool managers abbreviated as "poolers".

Each client registered to the session can access the connection or summon other client's methods. The example below shows how the system work and how transparent it is:


$session = $pomm['my_session'];

// wait for a DB notification on channel pika
do {
    $notification = $session
        ->getObserver('pika') // Get the "pika" observer client.
        ->getNotification()   // Ask it to listen to notifications.
} while ($notification === null);
$payload = json_decode($notification['payload']);

// perform a query using data received in the notification
$iterator = $session
    ->query("SELECT count(*) AS total FROM blog.article WHERE slug ~* $*::text", [$payload->slug])

    "There are %d articles matching '%s' slug.\n",

The code example above uses Foundation’s observer client type to listen to notifications sent through Postgres server. When another process sends a notification NOTIFY "pika", '{"slug": "this-is-a-slug"}' it is caught by the script and query is then performed using the query manager client. Parameters are automatically escaped and converted from/to Postgres/PHP. This makes the use of Postgres secure and easy and it keeps the developers focused on the important parts.

Here is a list of some client types packaged with Foundation:

⤷ QueryManager
Send parametrized queries and convert results.
can use PreparedQueryPooler to use prepared statements.
⤷ Converter
Convert any type from/to Postgres.
⤷ Observer
Listen to notifications sent through Postgres.
⤷ Inspector
Introspect database objects.
⤷ Listener
Trigger code on events within Pomm.

Model Manager

Pomm's model manager is a package adding extra clients to Foundation in order to map database records with object oriented entities. This greatly eases all the write operations and enhances data conversion, allowing the use of composite types and embedded entities.

Each Model class maps a database relation to a FlexibleEntity using a Projection. There is more about this here. A typical model file looks like the following:

// …
class NewsModel extends Model
    use WriteQueries; // ← This model can perform write SQL statements.

    public function __construct()
        $this->structure = new NewsStructure; // ← natural structure of the mapped relation.
        $this->flexible_entity_class = "\Model\PommProject\PommSchema\News";
    }   // ↑ The flexible entity associated with this model
The actual code above has been stripped of `namespace` and `use` statements for readability. Using this model class is as easy as using any other Foundation's clients:
// …
$iterator = $session
    ->findWhere('content LIKE $*', '%pika%') // ← parameter escaping
    // ↑ SELECT $projection FROM pomm.news WHERE content LIKE '%pika%'

foreach ($iterator as $news) { //← results are loaded and converted on demand
    printf(            // ↑ news are object oriented entities
        "date: %s, title « %s ».\n",  // ↓ timestamps are converted to DateTime
        $news->getCreatedAt()->format("Y-m-d"), // ← automatic accessor
        $news["title"] // ← equivalent to getTitle()

Each entity's model class defines the projection that is used by the queries to hydrate them. The projection is represented by a Projection instance initialized by the model class upon relation natural definition. It is possible to tune this projection by overloading the model's createProjection() method. Let's imagine we are interested in the age of a publication instead of just showing the publication date:

    public function createProjection()
        return parent::createProjection()
            ->setField('age', 'age(%:published_at:%)', 'interval')
Now all News entities returned by queries will have an extra field age containing the age of the publication in a \DateInterval. All the default queries are sensible to the projection:
Read queries trait
Write queries trait
It may appear weird at first glance an update query uses the projection but actually Postgresql UPDATE statements can return a project through the RETURNING statements.

It is now possible to create a custom accessor in the News entity to display the interval easily:

    public function getAge()
        $interval = $this->get('age');

        if ($interval->y > 0) {
            return $interval->y > 1
                ? sprintf("%d years ago", $interval->y)
                : 'last year'
        } elseif ($interval->m > 0) {
            return $interval->m > 1
                ? sprintf("%d months ago", $interval->m)
                : 'last month'
        } elseif ($interval->d > 0) {
            return $interval->d > 1
                ? sprintf("%d days ago", $interval->d)
                : 'yesterday'
        } else { return 'today'; }

Command Line Interface (CLI)

The CLI package crafts a command line thats uses Foundation and ModelManager clients to inspect the database structure or to generate classes for the model manager.

$ php vendor/bin/pomm.php pomm:inspect:relation pomm_project news pomm
Relation pomm.news
| pk | name         | type      | default | notnull | comment                                                               |
| *  | slug         | varchar   |         | yes     | The slugs are generated automatically from the title using a trigger. |
|    | title        | varchar   |         | yes     | Titles are rendered in a h1 tag.                                      |
|    | created_at   | timestamp | now()   | yes     |                                                                       |
|    | published_at | timestamp | now()   | no      | When null, the news is not published.                                 |
|    | content      | text      |         | yes     |                                                                       |
As shown above, comments are first class citizen of the CLI. There are immensely useful for the developers. Even though developers have been brain storming on the structure of the database, it is foolish to assume one can keep in mind the wood ball of details it contains. Postgresql is capable to set comments on almost everything (schemas, table, rows, constraints, indexes, functions etc.) Pomm takes advantage of this feature to self document the database.

The code generator keeps the same respect for comments. Its main goal is to generate relations structure classes every time they change in the database, but it can also generates model and entity classes if they do not exist, it can even overwrite them if one forces it to do so.

$ php vendor/bin/pomm.php pomm:generate:relation-all -d sources/lib -a Model -v pomm_project news pomm
 ✓  Overwriting file 'sources/lib/Model/PommProject/PommSchema/AutoStructure/News.php'.
 ✗  Preserving existing model file 'sources/lib/Model/PommProject/PommSchema/NewsModel.php'.
 ✗  Preserving existing entity file 'sources/lib/Model/PommProject/PommSchema/News.php'. 
The generated structure file looks like the following (namespace related statements and comments have been removed for clarity):
class News extends RowStructure
    public function __construct()
            ->addField('slug', 'varchar')
            ->addField('title', 'varchar')
            ->addField('created_at', 'timestamp')
            ->addField('published_at', 'timestamp')
            ->addField('content', 'text')

What else…

Pomm2 is usable directly from any PHP development but it also integrates with Silex and Symfony™ frameworks. PommServiceProvider and PommBundle proposes a nifty panel in the web debug toolbar giving useful informations about queries sent to the server. It is even possible to display an EXPLAIN execution plan: