By Yanko Simeonoff


About me


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

    In MySQL triggers can't update the table which they are assigned to.

  • Extensions (PostGIS, HStore, ….)
  • Data types: JSON, XML, text, UUID, timestamps with time zone, periods, 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('SELECT * FROM orders WHERE id=1', PDO::FETCH_ASSOC);
    $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 here?"

    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 = $student_model->findWhere($where);

    But it has a lot more


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

    Custom projections

    public function getNearestFrom($name)
        $sql = <<<SQL
      :station_table station
        LEFT JOIN :station_table near_station ON near_station.name <> station.name
        station.name = $*
        near_station.available_slots > 0
      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

    <?php // MyDB/PublicSchema/ComputerModel.php
        public function createProjection()
            return parent::createProjection()
                    "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
        UNION ALL
        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 │

    LISTEN/NOTIFY support

    PostgreSQL support LISTEN/NOTIFY events

      ->attachAction(function($event_name, $data, $session) {
        // do something
    SELECT FROM pg_notify('my_event', 'some data here');

    Can I use it?

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


    Thank you!

    It was my first presentation and I'm still alive!