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:

  • Foundation
  • ModelManager
  • Cli


The foundation package is a library that shares a database "physical" connection between clients through a session. Thare are different kind of clients, pametrized 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:

$iterator = $session
    ->query('select * from a_table where a_field = $*', ['a value'])

foreach ($iterator as $row) {

The code example above shows how to ask the QueryManager pooler to give us a client able to perform a query. The returned result iterator owns a reference to the session. Each time a result is fetched from the iterator, the converter pooler is used to get a converter client. This way, each converter can access the session which is useful to use Postgresql configuration dependant escaping functions. The following usefull poolers come configured with the default session builder:

  • QueryManager (can use PreparedQueryPooler to use prepared statements)
  • Converter
  • Observer (to take advantage of Postgresql listen/notify feature)
  • Inspector
  • 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: