About me

Me VarnaLab
  • Developer at Taylor & Hart
  • Problem solving enthusiast
  • Member of VarnaLab
  • Father of 2 kids
  • Dreamer

What I do for living

  • Working with databases for 15+ years

    PostgreSQL, DBase3, Interbase, MySQL, MongoDB, Firebird

  • Using ORMs for 8+ years

    Propel (ActiveRecord based), Doctrine (Hybernate based)

  • Developing with PHP for 13+ years

    Plain, Symfony1, Symfony2, other frameworks

Choose the right database for your project



  • We are talking about databases, aren't we?


PostgreSQL FTW!

  • Data is exactly as it should be

    CREATE TABLE xxx (id int not null);
    INSERT INTO xxx VALUES ('4555test');
    -- ERROR:  invalid input syntax for integer: "4555 test"
  • DDL can be transactional too

    DROP TABLE xxx;
  • Triggers are working (Unlike in MySQL triggers can't update the table which they are assigned to.)

More PostgreSQL features!

  • Extensions (PostGIS, HStore, ….)
  • Data types: JSONB (MySQL json type has nothing in common), XML, text, UUID, timestamps with time zone, interval, timestamp ranges, HStore, GIS related, inet4, inet6, custom types

What is ORM?

Object-relational mapping (ORM, O/RM, and O/R mapping tool) in computer science is a programming technique for converting data between incompatible type systems in object-oriented programming languages. This creates, in effect, a "virtual object database" that can be used from within the programming language.

Usage example

Without ORM

$stmt = mysql_query('SELECT * FROM orders WHERE id=1');
$order = mysql_fetch_assoc($stmt);
$date = new DateTime($order['date']);

  'UPDATE orders SET payment_type = ":paymentType" WHERE id = :id', [
    ':paymentType' => Payment::PAYMENT_TYPE_CASH,
    ':id' => 1

With PDO is better

$db = new PDO($dsn);
$db = mysql_query();
$order = $db->query('
  FROM orders
$date = new DateTime($order['date']);

$stmt = $db->prepare('
  UPDATE orders
  SET payment_type = ":paymentType"
  WHERE id = :id');
    ':paymentType' => Payment::PAYMENT_TYPE_CASH,
    ':id' => 1

But still …

there are a lot of strings to be parsed

Pros of using ORM

DB abstraction

  • No more raw SQL queries
  • No more "How to write the query for this db?"
  • No more "Is this syntax available in this db?"

Object oriented instead of using arrays and manual hydration

$order = OrderTable::findByPK(1);
$date = $order->getDate();


Why an ORM is not suitable?

Limited features because it should support various databases

  • How many people start a new project with the idea to switch later to another database?
  • Why I can't use feature X of the DB?
  • What else does the DB provide but I have to implement at the app level?

The cost of database abstraction (DBAL)

  • Another abstraction layer
  • Using DSL to query the database
  • Tightening to common supported DBs functions

What to choose then?

  • Choose the proper tool for your needs
  • Knowing the database you can benefit from it instead of tying yourself to an ORM

What is POMM?


"Imagine an ORM but upside down. That's what POMM is."

Some details about POMM

Version 1 is 10900 loc

Version 2 is 22809 loc

It's main purpose is to give object oriented interface on PostgreSQL, not to abstract the work with the database.

Why POMM instead of a standard ORM?

  • PDO is not needed anymore
  • It has all from ORM functionalities

    $where = \Pomm\Query\Where::create(
        'birth_date >= $*', [
            new \DateTime('1994-01-01')
        ->andWhere('gender = $*', ['M']);
    $students = $studentModel->findWhere($where);

But it has a lot more


$student = $studentModel->deleteByPK(['student_id' => 2]);

Delete a row and get the result in a single query

Custom projections

public function getNearestFrom($name)
  $sql = 'SELECT :projection
          FROM :station_table station
          WHERE station.name = $* AND near_station.available_slots > 0
          ORDER BY distance ASC';

  $projection = $this->createProjection()
    ->setField('distance', '%:coord:% <-> near_station.coord', 'float4');

  $sql = strtr($sql, [
    ':projection'    => $projection->formatFieldsWithFieldAlias('station')
    ':station_table' => $this->getStructure()->getRelation()

  return $this->query($sql, [$name], $projection);

Partial objects?

Why have to select all columns every time when you need only bunch of them?

CREATE TABLE very_large_table_with_lots_of_columns (
  pk bigserial primary key,
  name text,
  ... 50+ columns ...
  -- Don't tell me you don't have such tables :)
CREATE INDEX idx_vltwloc_for_selects
  ON very_large_table_with_lots_of_columns (pk, name);

With ORM you always do

SELECT * FROM very_large_table_with_lots_of_columns WHERE something

Yes, I know Doctrine has partial objects support

POMM in practice

Basic select

use MyDb\PublicSchema\ComputerModel;

$computers = $pomm['my_db']
    ->findWhere('$* >> any (interfaces)', [''])

Custom field types

                "not ''::inet >> all(%:interfaces:%)",
    c."computer_id" AS "computer_id",
    c."interfaces" AS "interfaces",
    NOT ''::inet >> ALL(c."interfaces") AS "is_public"
  public.computer c

Complex queries

Using CTE (recursive too)

  sub_department AS (
    SELECT d.*
    FROM department d
    WHERE d.department_id = 9
    SELECT d.*
    FROM department d
      JOIN sub_department sd ON d.department_id = sd.department_parent_id
SELECT sd.* FROM sub_department sd;

You don't need tables

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

WINDOW functions

  rank() over (partition by country order by production desc)
order by
  rank asc
│ name  │ country  │ production │ rank │
│ edno  │ bulgaria │       1512 │    1 │
│ un    │ france   │       2063 │    1 │
│ deux  │ france   │       1821 │    2 │
│ eins  │ germany  │       2211 │    1 │

Just use another custom projection


PostgreSQL support LISTEN/NOTIFY events

SELECT FROM pg_notify('my_event', 'some data here');

And you can very easily attach handler for them:

  ->attachAction(function($event_name, $data, $session) {
    // do something

Can I use it?

There is already integration for some of the most used frameworks:


Thank you!

You can reach me at: