A short focus on Pomm’s Foundation

Posted 3 years ago.

As its name indicates, Foundation is Pomm’s main package. It makes developers able to easily manage and use database connections. There are incidentally some comparisons with Doctrine’s DBAL which Foundation may looks like at first glance but Foundation is at opposite corners of what DBAL is even though it can be used the same way. To understand why, let’s focus on one of Foundation’s client: the query manager. (reach TL;DR).

Simple is beautiful

In order to use Pomm’s Foundation, adding a single line in composer.json is enough:

composer.phar require "pomm-project/foundation:2.0.*@dev"

Now it is time to fire up an editor and play a bit with Postgres:

<?php

require __DIR__ . '/vendor/autoload.php';

$pomm = new PommProject\Foundation\Pomm([ 'my_db' => ['dsn' => 'pgsql://user:pass@host:port/db_name'] ]);

$integers = $pomm['my_db'] ->getQueryManager() ->query("select generate_series(1, $*)", [ 3 ]) ;

foreach ($integers as $integer) { var_dump($integer); }

That’s merely 4 lines of code. The first one triggers composer’s autoloading. The second one creates a Pomm instance declaring a session builder in the same time by using a DSN syntax. The third line deserves a bit of explanations:

$integers = $pomm['my_db']
    ->getQueryManager()
    ->query("select generate_series(1, $*)", [ 3 ])
    ;

This opens a session to the database and calls a query manager to send a parametrized query to the server. The parameter is the $* in the query string. The second argument is the array of parameters passed to the query. This is enough to protect this query against SQL injection.

The result of this query is stored in a $integers variable. This variable is a scrollable iterator on results. It lazily fetches results upon request hence saving precious amount of memory.

Doing the same thing in a psql console throws up the following result:

select generate_series(1, 3);
┌─────────────────┐
│ generate_series │
├─────────────────┤
│               1 │
│               2 │
│               3 │
└─────────────────┘
(3 rows)

The fourth line of code is traversing the result and it dumps each result, here is the first one:

array(1) {
  ["generate_series"]=>
  int(1)
}

Each row is an associative array indexed by field name, this may sound familiar to PDO and DBAL users. A closer look shows the result is an integer because Postgres generate_series returns a set of integers. The result’s row values have been converted on the fly to a PHP equivalent. The query manager uses internally a converter client. In order to illustrate this, let’s rewrite our query to use the timestamp form of generate_series

<?php
$integers = $pomm['my_db']
    ->getQueryManager()
    ->query(
        "select generate_series($*::timestamptz, $*::timestamptz, $*::interval)",
        [
            new \Datetime("yesterday"),
            new \Datetime("now"),
            DateInterval::createFromDateString('2 hours')
        ]
    );

This time, the query is a bit different. The function generate_series will generate a set composed of timestamps between given bounds spaced with 2 hours interval. Arguments are cast to SQL types using Postgres’s :: syntax. Pomm leverages this syntax to convert arguments from a PHP type to a string understandable by Postgres on the fly . Fetching a random value from this result set shows the converter is also converting timestamps back to PHP DateTime instances:

array(1) {
  ["generate_series"]=>
  object(DateTime)#31 (3) {
    ["date"]=>
    string(26) "2015-08-12 20:00:00.000000"
    ["timezone_type"]=>
    int(1)
    ["timezone"]=>
    string(6) "+00:00"
  }
}

TL;DR

In this article we barely scratched the surface of Pomm’s Foundation package. The example has shown:

  • How to easily manage and open connections.
  • How to perform queries protected against SQL injection attacks.
  • That Foundation’s query manager returns a lazy iterator (that is traversable, scrollable, countable and JSON Serializable btw).
  • That Foundation has a built-in efficient converter system that converts results and query parameters.

Under the cover, Foundation uses PHP’s native Postgresql library to ensure maximum performances (by far better than PDO hence Doctrine’s DBAL) and leverage all Postgres features.

Foundation is the heart of Pomm’s framework that makes it extensible. This way, one can develop custom database model layer adapted to their need. The Query Manager is one of the built-in clients of Foundation which also provides a LISTEN/NOTIFY observer, a database inspector, transparent prepared queries and some others more.

Pomm’s model manager package is an extension of Foundation that provides developers with an « opposite corners like » to ORMs but this will be the subject of a different article.