PHP Persistence API

Winter does not force its data-access layer on you: you can open a connection yourself, as an ordinary bean. But the application lives for months and a database connection does not — and that is where PPA comes in: a pool that keeps connections alive, plus repositories, entities and migrations on top of it.

What PPA is

PPA (PHP Persistence API) is Winter’s database layer.

The problem. Working through bare PDO looks the same in every project: SQL is assembled out of strings, parameters are bound by hand, and the result arrives as an untyped array. The same query is written again in every place that needs it. Rename a column and no compiler objects, no editor helps — you find out at runtime. Forget a placeholder, concatenate a string, and you have an SQL injection.

To that, the resident runtime adds one of its own: the connection lives for weeks, and opening it is not enough — it has to be looked after.

The solution. PPA covers both, and it consists of four parts. Any one of them can be used on its own.

It is installed separately

PPA is a package, not part of the kernel: an application that only talks to Redis or to an external API should not carry an ORM, a connection pool and a migration engine along with it.

composer require flytachi/winter-ppa

Until it is installed, call db and the call make -R/-E/-C generators refuse honestly and name the command that fixes it, and /actuator/health simply shows no pool section. Everything else in the application works as usual.

Connection pool

Connections are opened ahead of time, handed out to coroutines for the duration of their work, and returned by themselves. The pool checks them before handing them over, replaces dead ones and rotates ones that got too old before the server closes them.

This is not an optimisation for speed but the thing without which a resident application does not survive a database restart. In detail — further down this page.

Repositories

A class bound to a table. The query is assembled with methods, not with a string:

// PDO
$stmt = $pdo->prepare(
  'SELECT * FROM users WHERE status = :status ORDER BY id DESC LIMIT 20'
);
$stmt->execute(['status' => 'active']);
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);   // array of arrays

// PPA
$users = UserRepository::instance()
  ->where(Qb::eq('status', 'active'))
  ->orderBy('id DESC')
  ->limit(20)
  ->findAll();                             // array of User

The difference is not only in length. Everything handed to Qb::eq() travels into a prepared statement separately from the SQL text — there is simply nothing to concatenate and no way to get an injection. The query is assembled piece by piece, so a condition can be added depending on the input without rewriting the SQL. And insert, update, delete, upsert and batch insert are already there — no need to write them for every table.

Entities

A table row is described by a class and the columns by attributes:

#[Table]
class User
{
  #[Id]
  public ?int $id = null;

  #[Varchar(255)]
  public string $email;

  #[Boolean]
  public bool $active = true;
}

A query returns objects like that rather than arrays: $user->email instead of $row['email'], with a type, autocompletion and go-to-definition. A typo in a field name becomes an error you see at once instead of an empty value.

Migrations

That same entity markup serves as the schema description: call db migrate compares it with the database and brings the database into line. There is no separate migrations directory to keep in agreement with the code — there is one source.

If you came from Java

Much of this is recognisable, but the correspondences are not one-to-one. A map, so you do not look for the familiar where it is not:

The main difference is one of worldview: an entity object knows nothing about the database. It is not tracked, not “dirty”, does not save itself. Changing a field is changing a field; for it to reach the database you call update().

That is less magic and less convenience in simple scenarios — and markedly fewer “why did this UPDATE even run” questions in complex ones.

One level down the stack sits CDO — an extended PDO. Literally: class CDO extends PDO, that is the same connection object with insert, update, delete, batch operations, driver detection and time-zone alignment with PHP added to it. PPA does not hide it: the pool hands out CDO itself, and you can drop down to that level at any moment and run your own SQL.

You can connect without PPA at all

The framework imposes nothing. A connection is an ordinary dependency, and it can be declared as a bean with Scope::Request: one connection per request, no pool involved. How that is done — for PDO, CDO and Redis — is shown in Basic connections.

That style is correct, and for many applications it is enough. What follows is what it lacks once the load grows.

What such a connection lacks

Scope::Request is correct, but it is paid for on every request:

The connection is opened afresh. A TCP handshake, TLS, authentication and, on PostgreSQL, a process fork on the server side — milliseconds on every request that were unavoidable in classic PHP and are wasted here.

Their number is unbounded. A thousand concurrent requests means a thousand connection attempts. The database answers too many connections, and what falls over is not just the spike but the whole application.

A database failure no longer heals itself. This one is the main point, and it is not obvious.

Those three things are exactly what the pool addresses.

The pool, briefly

The main thing PPA gives you, and the reason the layer exists at all. A coroutine takes a connection on its first database call and returns it automatically when it ends; nothing anywhere has to release one by hand.

worker
└── pool (per config class)
    ├── connection 1  ← coroutine A holds it for the request
    ├── connection 2  ← coroutine B
    └── connection 3    free

Connections are not merely reused — they are kept working: one that has been idle is probed before it is handed over, an aged one is replaced ahead of time, and when all are busy a request waits for a bounded time and gets a clear refusal instead of hanging forever.

How that works, how to size it and what to do when the pool fills up — Connection pool.

What it looks like as a whole

A repository is three properties: where to connect, what to hydrate into, which table.

use Flytachi\Winter\Ppa\Stereotype\Repository;

class UserRepository extends Repository
{
  protected string $dbConfigClassName = MainDbConfig::class;
  protected string $entityClassName   = User::class;
  public static string $table         = 'users';
}

That is enough for the static shortcuts, the query builder and writing to all work:

$user = UserRepository::instance()->findById(42);

$users = UserRepository::instance('u')
  ->joinLeft(OrderRepository::instance('o'), 'u.id = o.user_id')
  ->where(Qb::eq('u.status', 'active'))
  ->limit(20)
  ->findAll();

$user = new User();
$user->email = 'alice@example.com';

$id = new UserRepository()->insert($user);

The joined side is a repository too, not a string with a table name. The table name stays in one place, and the joined repository can carry conditions of its own — it is then substituted as a subquery, and its parameters travel into the outer query by themselves.

The stereotype is chosen by the amount of access needed — a read-only repository will not let you write by accident:

A one-off query can also be made without a repository — straight from the configuration:

MainDbConfig::cte()
  ->from('orders o')
  ->where(Qb::eq('o.status', 'new'))
  ->findAll();

MainDbConfig::instance()->query('SELECT version()');   // straight down to CDO

Query examples

Six of the most common tasks, from a simple selection to a recursive tree walk. Under each example is the SQL it produces.

1. A filtered list

The most common query there is: a list page with a condition and a sort.

$users = UserRepository::instance('u')
  ->where(Qb::eq('u.status', 'active'))
  ->orderBy('u.created_at DESC')
  ->limit(20)
  ->findAll();

SELECT u.id, u.name, u.email, u.status, u.created_at
FROM users u
WHERE u.status = :iqb0
ORDER BY u.created_at DESC
LIMIT 20

The column list came from the entity — * never appears in the queries. The value 'active' went out as the parameter :iqb0, not into the text.

2. Filters that may be absent

A search form: the user filled in only some of the fields, and the condition is assembled from what arrived.

$filter = Qb::empty();

if ($status !== null) {
  $filter->addAnd(Qb::eq('u.status', $status));
}
if ($domain !== null) {
  $filter->addAnd(Qb::like('u.email', "%@{$domain}", insensitive: true));
}

$users = UserRepository::instance('u')
  ->where($filter)
  ->orderBy('u.id DESC')
  ->limit(20)
  ->findAll();

SELECT u.id, u.name, u.email, u.status, u.created_at
FROM users u
WHERE (u.status = :iqb0 AND u.email ILIKE :iqb1)
ORDER BY u.id DESC
LIMIT 20

No WHERE 1=1 and no string concatenation: fields left empty simply add no conditions, and an empty filter adds no WHERE at all. insensitive: true expands to ILIKE on PostgreSQL and to a case-insensitive comparison on MySQL.

3. An aggregate over a related table

A “how many orders and for how much per customer” report — a join, a grouping and a filter on the aggregate.

$rows = OrderRepository::instance('o')
  ->joinInner(UserRepository::instance('u'), 'o.user_id = u.id')
  ->select('u.id, u.name, COUNT(o.id) AS orders_count, SUM(o.total) AS total_spent')
  ->where(Qb::eq('o.status', 'paid'))
  ->groupBy('u.id, u.name')
  ->having('SUM(o.total) > 10000')
  ->orderBy('total_spent DESC')
  ->limit(50)
  ->findAll(CustomerTotal::class);

SELECT u.id, u.name, COUNT(o.id) AS orders_count, SUM(o.total) AS total_spent
FROM orders o
INNER JOIN users u ON(o.user_id = u.id)
WHERE o.status = :iqb0
GROUP BY u.id, u.name
HAVING SUM(o.total) > 10000
ORDER BY total_spent DESC
LIMIT 50

Here select() was given by hand, so the columns come from it rather than from the entity. The hydration class was passed to findAll() — an ordinary class with the fields id, name, orders_count, total_spent; it corresponds to no table and carries no schema attributes.

4. A subquery as a join source

The previous report lost customers with no orders. To keep them, the aggregate is computed separately and attached with a left join.

$spent = OrderRepository::instance('o')
  ->select('o.user_id, SUM(o.total) AS total_spent')
  ->where(Qb::eq('o.status', 'paid'))
  ->groupBy('o.user_id');

$rows = UserRepository::instance('u')
  ->joinLeft($spent, 'u.id = o.user_id')
  ->select('u.id, u.name, COALESCE(o.total_spent, 0) AS total_spent')
  ->orderBy('total_spent DESC')
  ->limit(100)
  ->findAll(CustomerTotal::class);

SELECT u.id, u.name, COALESCE(o.total_spent, 0) AS total_spent
FROM users u
LEFT JOIN (SELECT o.user_id, SUM(o.total) AS total_spent
             FROM orders o
            WHERE o.status = :iqb0
            GROUP BY o.user_id) o ON(u.id = o.user_id)
ORDER BY total_spent DESC
LIMIT 100

The wrapping into a subquery appeared by itself: the joined repository has its own select, where and groupBy. Its :iqb0 parameter moved into the outer query — there is no numbering to think about.

The $spent variable is conveniently extracted into a repository method and reused across several reports.

5. A CTE — lifting the preparation to the top

The same thing, but with the subquery lifted into a named expression. It reads better, and if the result is needed twice, the database computes it once.

$recentBuyers = OrderRepository::instance('o')
  ->select('o.user_id, COUNT(*) AS cnt')
  ->where(Qb::gt('o.created_at', $since))
  ->groupBy('o.user_id');

$rows = UserRepository::instance('u')
  ->with('recent_buyers', $recentBuyers)
  ->joinInner('recent_buyers rb', 'rb.user_id = u.id')
  ->select('u.id, u.name, rb.cnt')
  ->where(Qb::gte('rb.cnt', 3))
  ->orderBy('rb.cnt DESC')
  ->findAll(BuyerActivity::class);

WITH recent_buyers AS (
     SELECT o.user_id, COUNT(*) AS cnt
       FROM orders o
      WHERE o.created_at > :iqb0
      GROUP BY o.user_id)
SELECT u.id, u.name, rb.cnt
FROM users u
INNER JOIN recent_buyers rb ON(rb.user_id = u.id)
WHERE rb.cnt >= :iqb1
ORDER BY rb.cnt DESC

with() takes a name and a repository; from there the name is referred to like an ordinary table. There can be several with() calls — they gather into one comma-separated WITH. A third argument passes a hint to the PostgreSQL planner: 'MATERIALIZED' or 'NOT MATERIALIZED'.

6. A recursive CTE — walking a tree

Categories, comments, an org chart — anything that references itself. The task: fetch a whole branch, from a given node downwards.

$anchor = CategoryRepository::instance()
  ->select('id, parent_id, name, 1 AS depth')
  ->where(Qb::eq('id', $rootId));

$tree = $anchor->union(
  CategoryRepository::instance('c')
      ->select('c.id, c.parent_id, c.name, t.depth + 1')
      ->joinInner('tree t', 'c.parent_id = t.id'),
);

$branch = CategoryRepository::instance()
  ->withRecursive('tree', $tree)
  ->select('id, name, depth')
  ->from('tree')
  ->orderBy('depth ASC, name ASC')
  ->findAll(CategoryNode::class);

WITH RECURSIVE tree AS (
     SELECT id, parent_id, name, 1 AS depth
       FROM categories
      WHERE id = :iqb0
      UNION
     SELECT c.id, c.parent_id, c.name, t.depth + 1
       FROM categories c
      INNER JOIN tree t ON(c.parent_id = t.id))
SELECT id, name, depth
FROM tree
ORDER BY depth ASC, name ASC

The construction reads as it stands: the first part is the starting node, the second is a step downwards, and UNION joins them. The depth field counts the nesting level and gives the sort order along the way.

When the builder is not enough

Window functions, LATERAL, a specific DBMS’s peculiarities — all of that is written as raw SQL through rawFetch(), and the result is hydrated into objects just the same:

use Flytachi\Winter\Cdo\CDOBind;

$rows = OrderRepository::instance()->rawFetch(
  'SELECT user_id, total,
          ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY total DESC) AS rn
     FROM orders
    WHERE created_at > :from',
  [new CDOBind('from', $since)],
  OrderRank::class,
);

To go lower still — down to the connection itself — use db(); see Repository.

The rest of this section