GitHub - gklp/pipernate: Pipernate - PHP Object Relational Mapping Framework: fluent Criteria API, relations, transactions and pluggable caching.

Pipernate v2

A lightweight PHP ORM — fluent queries, relations, transactions, caching.

Pipernate is a lightweight ORM for PHP. It maps database tables to plain PHP objects and gives you a fluent, chainable query API, relations with eager/lazy loading, transactions, native SQL with stored-procedure support, and an optional pluggable cache layer.

Originally written in 2012 by two friends, Pipernate v2 is a ground-up rewrite for PHP 8.2+: PSR-4, zero runtime dependencies, PDO with prepared statements everywhere, and support for MySQL, MariaDB, PostgreSQL and SQLite behind one grammar abstraction — the same model and query code runs unchanged on all four.

$orm = new Pipernate(Config::mysql('127.0.0.1', 'shop', 'root', getenv('DB_PASS')));

$users = $orm->query(User::class)
    ->where('status', 'active')                 // equals
    ->between('age', 18, 65)                     // range
    ->notIn('country', ['XX', 'YY'])            // negated set
    ->whereAny(fn ($q) => $q                     // ( … OR … ) group
        ->where('role', 'admin')
        ->whereGte('reputation', 1000))
    ->orderBy('created_at', 'DESC')
    ->limit(0, 20)
    ->with('posts.comments')                     // eager, nested, N+1-free
    ->cache(300)                                 // cached read
    ->list();

1. Requirements
2. Installation
3. Supported databases & driver selection
- 3.1 Custom DSNs
4. Defining Pipernate Database Models
5. CRUD
- 5.1 Bulk writes
6. Query API (Criteria)
7. Relations
8. Native queries
9. Stored procedures
10. Transactions
11. Caching
12. Exceptions
13. Configuration reference
14. Model generator (CLI)
15. Development & testing
16. Authors & license

1. Requirements

PHP 8.2+ with pdo and the driver extension for your database (pdo_mysql, pdo_pgsql, or pdo_sqlite)
Composer

2. Installation

composer require gklp/pipernate

3. Supported databases & driver selection

Database	PDO extension	`Config` factory	Dialect notes
MySQL	`pdo_mysql`	`Config::mysql(...)`	backtick identifiers, `LIMIT ?, ?`
MariaDB	`pdo_mysql`	`Config::mariadb(...)`	wire-compatible with MySQL
PostgreSQL	`pdo_pgsql`	`Config::postgres(...)`	`"` identifiers, `boolean`, `INSERT … RETURNING`
SQLite	`pdo_sqlite`	`Config::sqlite(...)`	ideal for tests (`:memory:`)

How the driver is selected. There is no separate "driver" setting — you pick the database by choosing a Config factory. Each factory builds the correct DSN, and Pipernate reads the DSN scheme to select the SQL grammar automatically. Swap databases by swapping the factory; your models and queries do not change.

use Pipernate\Config;

Config::mysql(string $host, string $database, string $username, string $password, int $port = 3306, string $charset = 'utf8mb4'): Config
Config::mariadb(string $host, string $database, string $username, string $password, int $port = 3306, string $charset = 'utf8mb4'): Config
Config::postgres(string $host, string $database, string $username, string $password, int $port = 5432): Config
Config::sqlite(string $path = ':memory:'): Config

Pass the returned Config to Pipernate (optionally a cache and a clock):

$config = Config::postgres('127.0.0.1', 'shop', 'app', getenv('DB_PASS'));
$orm = new Pipernate($config);

3.1 Custom DSNs

When you need a DSN the factories don't build (extra PDO options or a driver-specific parameter), construct Config directly:

new Config(string $dsn, ?string $username = null, ?string $password = null, array $pdoOptions = [])

$config = new Config(
    dsn: 'mysql:host=127.0.0.1;port=3306;dbname=shop;charset=utf8mb4',
    username: 'root',
    password: getenv('DB_PASS'),
    pdoOptions: [PDO::ATTR_PERSISTENT => true],   // merged over Pipernate's defaults
);

The grammar is still chosen from the DSN scheme (mysql: / pgsql: / sqlite:), so a hand-built DSN behaves exactly like the matching factory.

ℹ️ Dialect differences (identifier quoting, LIMIT/OFFSET, boolean representation, and generated-id retrieval) are handled internally, so save(), the query builder and relations behave identically across all four databases.

4. Defining Pipernate Database Models

A model is a plain PHP class that maps to one database table: the class is the table, each instance is a row, and its public typed properties are the columns. Extend Pipernate\Model\Model and declare your columns as properties — no base constructor, no getters/setters, no configuration files.

use Pipernate\Model\Model;
use Pipernate\Model\Attributes\Id;
use Pipernate\Model\Attributes\Table;

#[Table('users')]                 // optional; defaults to snake_case of the class
final class User extends Model
{
    #[Id]                         // optional; defaults to a property named "id"
    public int $id;
    public string $name;
    public int $age;
    public bool $active;
    public ?string $nickname;     // nullable column
}

// Work with it as a plain object:
$user = new User();
$user->name = 'Gokalp';
$user->age = 34;
$user->active = true;
$user->nickname = null;
$orm->save($user);                // INSERT; $user->id is populated

4.1 Columns, table & primary key

Table name — #[Table('users')], or the snake_case of the class short name when omitted (BlogPost → blog_post).
Primary key — #[Id] on a property, or a property literally named id. Required for update()/delete().
Columns — every public non-static property is a column. Types are enforced on the way out (see hydration below). Relation properties are protected and are not columns (see Relations).
Falsy values are safe — a value of 0, false, "0" or explicit null is persisted correctly. Only uninitialized typed properties are skipped on INSERT (so the database can apply defaults / auto-increment). This fixes a classic v1 bug where empty() silently dropped falsy values.
Hydration & types — result rows are coerced to each property's declared type (int, float, bool, string). Booleans handle every dialect's spelling (Postgres t/f, MySQL/SQLite 0/1).

$model->toArray() returns the initialized column values as an associative array.

4.2 Attribute casting

Cast columns between their database form and a richer PHP type. Backed enums and DateTimeImmutable properties are cast automatically from their type; JSON is declared with #[Cast('json')].

use Pipernate\Model\Attributes\Cast;

#[Table('articles')]
final class Article extends Model
{
    #[Id] public int $id;
    public string $title;

    #[Cast('json')]
    public array $meta;                       // JSON text <-> PHP array

    public Status $status;                    // backed enum  (inferred)
    public ?\DateTimeImmutable $published_at;  // datetime     (inferred)
}

Casts apply on read (hydration) and write (save/update/bulk). Enum and datetime values also work directly as bound query values:

$orm->query(Article::class)->where('status', Status::Active)->list();

#[Cast] types: 'json', 'datetime', or a backed-enum class-string.

4.3 Timestamps

#[Timestamps] fills created_at on insert and updated_at on every insert and update. Declare the columns (string, or DateTimeImmutable with a datetime cast):

use Pipernate\Model\Attributes\Timestamps;

#[Timestamps]
final class Post extends Model
{
    #[Id] public int $id;
    public string $title;
    public string $created_at;
    public string $updated_at;
}

Custom column names: #[Timestamps(createdAt: 'inserted_at', updatedAt: 'changed_at')]. An explicitly-set created_at is preserved. For deterministic time in tests, inject a clock: new Pipernate($config, $cache, $clock).

5. CRUD

A full create → read → update → delete lifecycle for a simple model:

#[Table('users')]
final class User extends Model
{
    #[Id] public int $id;
    public string $name;
    public string $email;
    public bool $active;
}

$orm = new Pipernate(Config::sqlite());

// CREATE — sign a new user up
$user = new User();
$user->name = 'Gokalp';
$user->email = 'gokalp@example.com';
$user->active = true;
$orm->save($user);                          // INSERT; $user->id is now set (e.g. 1)

// READ — fetch it back by primary key
$user = $orm->query(User::class)->where('id', 1)->single();
echo $user->email;                          // 'gokalp@example.com'

// UPDATE — deactivate the account
$user->active = false;
$affected = $orm->update($user);            // affected row count (1); throws if the PK is unset

// DELETE — remove the row
$orm->delete($user);

save() uses the right mechanism per dialect automatically (lastInsertId() for MySQL/MariaDB/SQLite, INSERT … RETURNING for PostgreSQL). update() and delete() act on the model's primary key.

5.1 Bulk writes

save()/update()/delete() touch one row per call — perfect for a single entity, wasteful for hundreds. Bulk writes do the same work in a single round-trip to the database.

saveAll() — insert many rows in ONE INSERT statement. Use it when you're importing, seeding, or creating a batch of records: 500 users become one query instead of 500. All models must be the same class with the same columns set.

$users = [];
foreach ($rows as $row) {
    $u = new User();
    $u->name = $row['name'];
    $u->email = $row['email'];
    $u->active = true;
    $users[] = $u;
}

$orm->saveAll($users);   // one INSERT ... VALUES (…),(…),(…)

Generated ids are not written back (unreliable across dialects for multi-row inserts). If you need each row's id, use save() in a loop. Batches that exceed the driver's bind-parameter budget are chunked automatically and executed atomically inside a transaction — pass 50,000 models without thinking about it. The budget defaults per driver (60,000 for MySQL/MariaDB/PostgreSQL, 999 for SQLite) and can be tuned: Config::mysql(...)->withMaxBindParameters(20000).

upsert() — insert, or update the existing row on a unique-key conflict. Use it for idempotent writes: imports that may re-run, sync jobs, "create or refresh" flows.

$user = new User();
$user->email = 'gokalp@example.com';   // UNIQUE column
$user->name = 'Gokalp';
$orm->upsert($user, by: ['email']);    // INSERT … ON CONFLICT/ON DUPLICATE KEY

by: names the conflict-target columns for PostgreSQL/SQLite (defaults to the primary key); MySQL matches against any unique index. On conflict every set column is overwritten except the conflict columns, the primary key and created_at; updated_at is stamped as usual.

Criteria update() / delete() — change or remove many rows by condition, without loading them into PHP first. Use it for "sweeping" operations: deactivate every minor, delete all spam, bump a counter. Fetching those rows into models just to modify them would be pointless work.

// Deactivate everyone under 18 — one UPDATE, no models loaded
$orm->query(User::class)->whereLt('age', 18)->update(['active' => false]);

// Delete all flagged accounts — one DELETE
$orm->query(User::class)->where('status', 'spam')->delete();

Both return the affected row count. Criteria update() also stamps updated_at on models that use #[Timestamps].

6. Query API (Criteria)

The Criteria API is how you read data without writing SQL. Instead of building query strings by hand, you start from a model and describe what you want by chaining methods — filters, ordering, limits, relations — then call a terminal method that runs the query and hands you back models (or arrays).

$orm->query(User::class) returns a Criteria. Every builder method (where, orderBy, limit, with, …) refines the query and returns the same Criteria, so calls chain fluently. Nothing hits the database until you call a terminal (list, single, count, …), which compiles the query and executes it once:

$users = $orm->query(User::class)   // start
    ->where('status', 'active')     // builder — refine
    ->orderBy('name')               // builder — refine
    ->limit(0, 20)                  // builder — refine
    ->list();                       // terminal — run, returns list<User>

It's safe by default: values you pass are always sent as bound parameters (never concatenated into the SQL), and column/table names are validated before they're quoted — so queries built this way are not open to SQL injection. When a query is too complex for the builder, drop to raw SQL with the native API.

6.1 Where and comparison operators

Four equivalent ways to express a comparison — pick whichever reads best:

use Pipernate\Query\Operator;

$orm->query(User::class)->where('status', 'active');        // equals shortcut (2 args)
$orm->query(User::class)->where('age', '>=', 18);           // explicit operator (string)
$orm->query(User::class)->where('age', Operator::Gte, 18);  // Operator enum — typo-proof, autocompleted
$orm->query(User::class)->whereGte('age', 18);              // typed helper method

Supported operators: =, !=, <>, <, <=, >, >= — an unknown one throws InvalidOperatorException (no silent fallback to =). Typed helpers cover the common cases: whereGt, whereGte, whereLt, whereLte, whereNot.

Chained where calls are combined with AND:

$orm->query(User::class)
    ->where('status', 'active')
    ->whereGte('age', 18)
    ->whereLt('age', 65)
    ->list();

6.2 Other conditions: null, in, like

$orm->query(User::class)->whereNull('nickname');            // nickname IS NULL
$orm->query(User::class)->whereNotNull('nickname');         // nickname IS NOT NULL
$orm->query(User::class)->in('id', [1, 2, 3]);              // id IN (?, ?, ?)
$orm->query(User::class)->notIn('id', [1, 2, 3]);          // id NOT IN (?, ?, ?)
$orm->query(User::class)->like('name', 'gok%');            // name LIKE ?
$orm->query(User::class)->notLike('name', 'test%');        // name NOT LIKE ?
$orm->query(User::class)->between('age', 18, 65);          // age BETWEEN ? AND ? (inclusive)
$orm->query(User::class)->notBetween('age', 13, 17);       // age NOT BETWEEN ? AND ?

in() with an empty array matches nothing (1 = 0); notIn() with an empty array matches everything (1 = 1).

6.3 OR and grouped conditions

Top-level where calls are combined with AND. Introduce OR and parentheses with whereAny() (inner conditions ORed) and whereAll() (inner ANDed). The closure receives a builder exposing the same condition methods — including nested whereAny/whereAll — so any boolean tree is expressible.

// status = 'active' AND (role = 'admin' OR age >= 65)
$orm->query(User::class)
    ->where('status', 'active')
    ->whereAny(fn ($q) => $q
        ->where('role', 'admin')
        ->whereGte('age', 65))
    ->list();

// (role = 'admin' OR role = 'mod') AND (age >= 65 OR verified = 1)
$orm->query(User::class)
    ->whereAny(fn ($q) => $q->where('role', 'admin')->where('role', 'mod'))
    ->whereAny(fn ($q) => $q->whereGte('age', 65)->where('verified', 1))
    ->list();

// status = 'active' AND ((role = 'admin' AND verified = 1) OR age >= 65)
$orm->query(User::class)
    ->where('status', 'active')
    ->whereAny(fn ($q) => $q
        ->whereAll(fn ($q) => $q->where('role', 'admin')->where('verified', 1))
        ->whereGte('age', 65))
    ->list();

There are no orWhere/orIn/orBetween twins: the combinator is the group, so every existing condition method (in, notIn, like, between, whereNull, …) works unchanged inside a group. An empty group is a no-op.

6.4 Ordering, limiting, projection

use Pipernate\Query\OrderDirection;

$orm->query(User::class)
    ->orderBy('name')                        // ASC by default
    ->orderBy('age', 'DESC')                 // string direction
    ->orderBy('id', OrderDirection::Desc)    // or the enum
    ->limit(0, 20)                           // offset, count
    ->list();

// Projection: only specific columns
$orm->query(User::class)->fields('name', 'age')->asArray()->list();

// DISTINCT
$orm->query(User::class)->fields('status')->distinct()->asArray()->list();

6.5 Return shape: models or arrays

$orm->query(User::class)->list();                 // list<User> (default)
$orm->query(User::class)->asArray()->list();      // list<array<string,mixed>>
$orm->query(User::class)->asModel()->list();      // force models (default)

6.6 Terminals

$orm->query(User::class)->where('status','active')->list();     // list<User>|list<array>
$orm->query(User::class)->where('id', 7)->single();             // User|array|null
$orm->query(User::class)->where('name', 'Ada')->exists();       // bool (LIMIT 1 probe)
$orm->query(User::class)->where('status','active')->count();    // int
$orm->query(User::class)->sum('age');                           // int|float
$orm->query(User::class)->max('age');                           // mixed
$orm->query(User::class)->min('age');                           // mixed

// Pagination: one Page object with the items + the bookkeeping a UI needs
$page = $orm->query(User::class)->orderBy('name')->paginate(page: 2, perPage: 20);
$page->items;      // this page's models
$page->total;      // all matching rows
$page->lastPage;   // e.g. 5
$page->hasMore();  // bool

single() applies LIMIT 1 and returns the first row or null. Terminals run on a clone, so the builder can be reused afterwards.

6.7 Full method reference

Method	Effect
`where('col', $value)`	`col = ?` (equals shortcut)
`where('col', $op, $value)`	`col <op> ?` — `$op` is a string or `Operator` enum
`whereGt/whereGte/whereLt/whereLte/whereNot('col', $v)`	typed comparisons
`whereNull('col')` / `whereNotNull('col')`	`col IS [NOT] NULL`
`in('col', [...])` / `notIn('col', [...])`	`col [NOT] IN (?, …)`
`like('col', $p)` / `notLike('col', $p)`	`col [NOT] LIKE ?`
`between('col', $min, $max)` / `notBetween(...)`	`col [NOT] BETWEEN ? AND ?`
`whereAny(fn ($q) => …)`	`( … OR … )` group
`whereAll(fn ($q) => …)`	`( … AND … )` group (nestable)
`orderBy('col', $dir = 'ASC')`	`ORDER BY` — `$dir` is string or `OrderDirection`
`limit($offset, $count)`	dialect-aware `LIMIT`/`OFFSET`
`distinct()`	`SELECT DISTINCT`
`fields('a', 'b', …)`	restrict the projection
`asArray()` / `asModel()`	choose the return shape
`with('rel', 'rel.sub', …)`	eager-load relations (see below)
`cache($ttlSeconds)`	cache this query's result
`list()`	terminal — all rows
`single()`	terminal — first row or `null`
`exists()`	terminal — `bool`, LIMIT 1 probe
`paginate($page, $perPage)`	terminal — a `Page` (items + total + lastPage)
`count($col = '*')` / `sum` / `max` / `min`	terminal — aggregates
`update([...])` / `delete()`	terminal — bulk write, affected count

7. Relations

7.1 Declaring relations

Declare relations with JPA-style attributes on protected typed properties. protected is required: it keeps them out of the column mapping and enables lazy access via __get.

use Pipernate\Model\Attributes\{Id, Table, ManyToOne, OneToMany, OneToOne, ManyToMany};

#[Table('posts')]
final class Post extends Model
{
    #[Id] public int $id;
    public int $user_id;
    public string $title;

    // Many posts belong to one user (FK `user_id` on this table)
    #[ManyToOne(User::class, foreignKey: 'user_id')]
    protected ?User $author = null;

    // One post has many comments (FK `post_id` on the comments table)
    #[OneToMany(Comment::class, foreignKey: 'post_id')]
    protected array $comments = [];

    // One post has one cover (FK `post_id` on the covers table)
    #[OneToOne(Cover::class, foreignKey: 'post_id')]
    protected ?Cover $cover = null;

    // Many posts have many tags via the `post_tag` pivot table
    #[ManyToMany(Tag::class, pivotTable: 'post_tag', foreignPivotKey: 'post_id', relatedPivotKey: 'tag_id')]
    protected array $tags = [];
}

Attribute	FK location	Yields	Key params (defaults)
`#[ManyToOne]`	this model	single	`foreignKey`, `ownerKey='id'`
`#[OneToMany]`	target	list	`foreignKey`, `localKey='id'`
`#[OneToOne]`	target	single	`foreignKey`, `localKey='id'`
`#[ManyToMany]`	pivot table	list	`pivotTable`, `foreignPivotKey`, `relatedPivotKey`, `localKey='id'`, `relatedKey='id'`

7.2 Eager loading with with()

->with() batches each relation into one IN (…) query, so a list of parents never becomes N+1:

$posts = $orm->query(Post::class)->with('author', 'comments', 'tags')->list();

$posts[0]->author->name;   // already loaded — no query
$posts[0]->comments;       // array<Comment>, already loaded
$posts[0]->tags;           // array<Tag>, loaded via the pivot (2 queries total)

7.3 Nested (dot-notation) loading

Chain relations with dots. Each path segment is one batched query, so nesting stays N+1-free regardless of row count:

// comment -> post -> author, loaded in exactly 3 queries
$comments = $orm->query(Comment::class)->with('post.author')->list();
$comments[0]->post->author->name;

This also covers the "reach a distant table through an intermediate" case — no separate hasManyThrough construct is needed.

7.4 Lazy loading

Any model the ORM produced can load a relation on first access, then caches it:

$post = $orm->query(Post::class)->where('id', 7)->single();
$post->author;   // one query here; subsequent access is cached

A model you build yourself (new Post()) has no loader attached, so lazily accessing a relation on it throws RelationException — load it through the ORM with ->with() instead. Note: iterating a list and touching a relation lazily is N+1 by design; use ->with() to batch.

7.5 Pivots that carry data

If your junction table has extra columns (e.g. created_at, role), model it as its own entity instead of using #[ManyToMany] — no special feature needed:

#[Table('post_tag')]
final class PostTag extends Model
{
    #[Id] public int $id;
    public int $post_id;
    public int $tag_id;
    public string $created_at;                 // pivot data as a normal column

    #[ManyToOne(Post::class, foreignKey: 'post_id')] protected ?Post $post = null;
    #[ManyToOne(Tag::class,  foreignKey: 'tag_id')]  protected ?Tag  $tag  = null;
}

// On Post:
#[OneToMany(PostTag::class, foreignKey: 'post_id')]
protected array $postTags = [];

8. Native queries

For joins, CTEs, window functions, stored procedures — anything the builder can't express — drop to raw SQL with a fluent result API via $orm->sql(). Intent is explicit (list/single/scalar read, run write), and values are always bound parameters.

// Hydrate raw rows into a model
$users = $orm->sql(
        'SELECT u.* FROM users u JOIN orders o ON o.user_id = u.id WHERE o.total > ?',
        [100],
    )
    ->asModel(User::class)
    ->list();

// A single row (or null)
$row = $orm->sql('SELECT name, age FROM users WHERE id = ?', [7])->single();

// A single scalar value
$total = $orm->sql('SELECT COUNT(*) FROM users WHERE status = ?', ['active'])->scalar();

// A write — returns affected row count
$affected = $orm->sql('UPDATE users SET status = ? WHERE age < ?', ['minor', 18])->run();

Native NativeQuery methods: asModel($class), asArray(), cache($ttl, ...$tables), and terminals list(), single(), scalar(), listSets(), run().

8.1 Positional or named parameters

Use ? with a positional array, or :name with an associative array — whichever reads better (don't mix the two in one query):

// positional
$orm->sql('SELECT * FROM users WHERE age >= ? AND status = ?', [18, 'active'])->list();

// named
$orm->sql('SELECT * FROM users WHERE age >= :min AND status = :st', ['min' => 18, 'st' => 'active'])->list();

8.2 Caching native reads

Tag the query with the tables it depends on so a later write can drop it (native writes can't know which tables they touched):

$rows = $orm->sql('SELECT … JOIN …', [18])->cache(300, 'users', 'orders')->list();

$orm->sql('UPDATE users SET …')->run();
$orm->invalidate('users');   // drops cached queries tagged with 'users'

8.3 The execute() escape hatch

For DDL or fire-and-forget SQL, $orm->execute() runs a statement directly: row-returning statements (SELECT, WITH, SHOW, PRAGMA, EXPLAIN, VALUES, DESCRIBE) return their rows; everything else returns the affected row count. Writes made this way do not invalidate the cache — call invalidate() yourself.

$orm->execute('CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)');
$rows = $orm->execute('SELECT * FROM users WHERE age > ?', [18]);   // list<array>

8.4 Aggregation & reports (GROUP BY / HAVING)

The Criteria builder is deliberately entity-focused: its aggregate terminals (count, sum, max, min) reduce the filtered set to a single value, but it has no groupBy() / having(). Grouped results aren't entities — they're ad-hoc report rows — and HAVING's alias rules differ across dialects. So grouping lives in native sql(), where you write dialect-appropriate SQL and get plain array rows back:

$rows = $orm->sql(
    "SELECT status, COUNT(*) AS total, MAX(age) AS oldest
     FROM users
     WHERE deleted = 0
     GROUP BY status
     HAVING COUNT(*) >= ?
     ORDER BY total DESC",
    [5],
)->list();
// [['status' => 'active', 'total' => 128, 'oldest' => 71], …]

9. Stored procedures

Procedures work through the same sql() API and are robust out of the box.

Driver support. The CALL … @out examples below are MySQL/MariaDB syntax. PostgreSQL routines are typically functions — call them through the same API with SELECT * FROM my_function(?) (or CALL for true procedures); the @var session-variable trick is MySQL-specific. SQLite has no stored procedures at all.

Input parameters are bound exactly like any other native query — positional ? or named :name:

// IN parameter, positional
$users = $orm->sql('CALL users_by_status(?)', ['active'])
    ->asModel(User::class)->list();

// IN parameter, named
$users = $orm->sql('CALL users_by_status(:status)', ['status' => 'active'])
    ->asModel(User::class)->list();

Other shapes:

// No arguments, returns a result set hydrated to models
$users = $orm->sql('CALL active_users()')->asModel(User::class)->list();

// One row with several scalar columns
$stats = $orm->sql('CALL user_stats()')->single();   // ['total' => 5, 'max_age' => 34, …]

// IN + OUT parameters — pass the OUT as a session variable, then read it back
$orm->sql('CALL count_status(?, @cnt)', ['active'])->run();
$out = $orm->sql('SELECT @cnt AS cnt')->single();

// A procedure that emits several result sets
[$active, $inactive] = $orm->sql('CALL split_users()')->listSets();

Cursor hygiene — cursors are freed after every fetch, so a normal query right after a CALL won't hit "commands out of sync".
listSets() returns each non-empty result set in order (the trailing empty status set MySQL appends to a CALL is dropped).

CREATE PROCEDURE itself isn't supported by the prepared-statement protocol; create procedures via a migration or $orm->connection()->pdo()->exec(...). The CALLs go through the normal ORM path.

10. Transactions

Wrap writes in transaction(): it commits on success and rolls back (rethrowing) on any exception. The return value of the callback is returned.

$orderId = $orm->transaction(function (Pipernate $orm) {
    $order = $orm->save(new Order(...));
    $orm->save(new OrderLine(orderId: $order->id, ...));
    return $order->id;
    // throw anything here -> both writes roll back, exception rethrown
});

Nesting is supported via savepoints — calling transaction() inside another rolls back only the inner block on failure.
Deferred cache invalidation — invalidations from writes inside a transaction are held until the outermost commit and dropped on rollback, so a rolled-back write never orphans a valid cache entry.

If you need manual control instead of the closure, the connection exposes the raw verbs — transaction() is just a safe wrapper around this pattern:

$conn = $orm->connection();

$conn->beginTransaction();
try {
    $orm->save($order);
    $orm->save($line);
    $conn->commit();
} catch (\Throwable $e) {
    $conn->rollBack();
    throw $e;
}

$conn->hasActiveTransaction();   // just a check — returns false now (commit() closed it)

hasActiveTransaction() only reports state (a bool); commit() and rollBack() are what actually close a transaction. beginTransaction() nests via savepoints just like transaction() does, so it's safe to call inside an already-open transaction.

11. Caching

Pipernate can cache the result of a read query so repeated identical reads skip the database. It's completely opt-in — with no cache backend nothing is cached, and even with one, only the queries you mark with ->cache($ttl) are stored. Most importantly, cached results stay correct on their own: writing to a table automatically drops that table's cached queries, so you rarely think about expiration at all.

11.1 Enabling it

Give the ORM a cache backend (the second constructor argument), then opt in per query with ->cache($seconds):

use Pipernate\Cache\MemcachedCache;

$orm = new Pipernate($config, MemcachedCache::connect('127.0.0.1', 11211));

// Cached for 5 minutes; the next identical query is served from the cache.
$orm->query(User::class)->where('status', 'active')->cache(300)->list();

The result is stored under a key derived from the compiled SQL and its bound parameters, so where('status','active') and where('status','banned') never collide. ->cache() works on list(), single() and the aggregate terminals, and on native reads too.

11.2 Cache backends

All backends implement Pipernate\Cache\CacheInterface — a PSR-16-shaped contract (get / set / delete / clear / has). Write those five methods and you can plug in your own (Redis, APCu, …).

Backend	Best for	Notes
`ArrayCache`	tests, single-request memoization	in-process; nothing survives the request
`FileCache`	one server, no extra services	atomic writes (temp file + rename), Windows-safe
`MemcachedCache`	a cache shared across processes/servers	pure-PHP client, no `ext-memcached`; a dead server degrades to a miss, never an error

use Pipernate\Cache\ArrayCache;
use Pipernate\Cache\FileCache;
use Pipernate\Cache\MemcachedCache;

// In-process array — fastest, but wiped at the end of the request. Great for tests.
$cache = new ArrayCache();

// On-disk files — survives across requests, needs no extra service.
$cache = new FileCache(sys_get_temp_dir() . '/pipernate-cache');

// Shared memcached — one cache for many processes/servers (1s connect/IO timeouts).
$cache = MemcachedCache::connect('127.0.0.1', 11211, connectTimeout: 1.0, ioTimeout: 1.0);

$orm = new Pipernate($config, $cache);

11.3 Keeping the cache correct

You don't expire entries by hand after a write — Pipernate does it. Every table carries a version counter that is folded into the cache key of every query on that table. When save(), update() or delete() writes to a table, its version is bumped, which instantly orphans every cached query for that table; the stale entries are simply never read again and are reaped by their TTL later. So the query right after a save() already sees fresh data.

Two paths bypass this and need a manual nudge:

Raw writes via execute() or sql()->run() — Pipernate can't know which tables they touched, so call $orm->invalidate('users', 'orders') yourself.
Cached native reads must declare the tables they read so a later write can find them: ->cache(300, 'users', 'orders').

11.4 Caching strategies

Cache read-heavy, slow-changing queries — dashboards, counts, lookup/reference lists, reports. Don't bother caching a ->single() fetch by primary key; it's already cheap and the cache churn isn't worth it.
Treat the TTL as a staleness ceiling, not a guess. Writes made through the ORM invalidate instantly, so the TTL only bounds changes Pipernate can't see (another app, a cron job, raw SQL). Pick it accordingly — seconds for volatile data, minutes or hours for near-static data.
Tag native reports with their source tables so ORM writes still invalidate them; otherwise only the TTL protects you.
Skip per-user or per-request-unique queries — the hit rate is low and you just fill the cache with entries that are never reused.
An outage is safe. A MemcachedCache whose server is down degrades to cache misses, so queries get slower but never fail.

11.5 Security

Cached values are stored with serialize() and restored with unserialize() on read. Treat your cache server at the same trust level as your database — anyone who can write to the cache can influence what your app deserializes. ArrayCache and a private FileCache directory are inherently local and not exposed.

12. Exceptions

All exceptions extend Pipernate\Exception\PipernateException (which extends RuntimeException), so you can catch broadly or narrowly.

Exception	Thrown when
`ConnectionException`	the database connection can't be established
`QueryException`	a statement fails (carries `->sql` and `->params`)
`InvalidIdentifierException`	a table/column name fails validation (injection guard)
`InvalidOperatorException`	a comparison operator isn't supported
`ModelException`	unknown model class, missing PK, or PK unset on update/delete
`RelationException`	unknown relation, or lazy access on a loader-less model
`TransactionException`	commit/rollback with no active transaction
`CacheException`	file/socket/protocol cache failure (memcached degrades silently)

Credentials are never echoed: ConnectionException reports only the driver scheme, and passwords passed to Config::mysql/postgres/... are marked #[\SensitiveParameter] so they're redacted from stack traces.

13. Configuration reference

Config is an immutable value object.

Config::mysql(string $host, string $database, string $username, string $password, int $port = 3306, string $charset = 'utf8mb4'): Config
Config::mariadb(string $host, string $database, string $username, string $password, int $port = 3306, string $charset = 'utf8mb4'): Config
Config::postgres(string $host, string $database, string $username, string $password, int $port = 5432): Config
Config::sqlite(string $path = ':memory:'): Config
new Config(string $dsn, ?string $username = null, ?string $password = null, array $pdoOptions = [], ?int $maxBindParameters = null)

// Tune the saveAll() chunking budget (default: 60000, SQLite 999):
$config->withMaxBindParameters(int $n): Config

Pipernate — the entry point:

new Pipernate(Config $config, ?CacheInterface $cache = null, ?Clock $clock = null)

$orm->query(string $modelClass): Criteria
$orm->save(Model $m): Model
$orm->saveAll(array $models): int
$orm->upsert(Model $m, array $by = []): int
$orm->update(Model $m): int
$orm->delete(Model $m): int
$orm->sql(string $sql, array $params = []): NativeQuery
$orm->execute(string $sql, array $params = []): array|int
$orm->transaction(callable $fn): mixed
$orm->invalidate(string ...$tables): void
$orm->connection(): Connection

Debugging — attach a query logger to see every statement (replaces the legacy debug echos):

$orm->connection()->setQueryLogger(function (string $sql, array $params): void {
    error_log($sql . ' :: ' . json_encode($params));
});

Automatic reconnect. If the server drops the connection (idle timeout, restart, killed session — the classic "MySQL server has gone away"), the next statement transparently reconnects and retries once, so long-running workers survive. Inside a transaction there is never a silent retry: the failure surfaces as a QueryException and the transaction is rolled back, because already-executed statements would otherwise be lost.

14. Model generator (CLI)

Generate typed model classes from an existing schema — works on all four drivers, each through its native introspection (information_schema on MySQL/MariaDB/PostgreSQL, PRAGMA table_info on SQLite):

# MySQL / MariaDB (default driver)
PIPERNATE_DB_PASS=secret php bin/pipernate generate:models \
    --db=shop --out=./models --namespace="App\\Models"

# PostgreSQL
PIPERNATE_DB_PASS=secret php bin/pipernate generate:models \
    --driver=pgsql --db=shop --user=postgres --out=./models --namespace="App\\Models"

# SQLite (no server, no password)
php bin/pipernate generate:models \
    --driver=sqlite --path=./app.db --out=./models --namespace="App\\Models"

Flags — what each one does:

--driver=mysql              # mysql | mariadb | pgsql | sqlite     (default: mysql)
--db=shop                   # database/schema to read              (required except sqlite)
--path=./app.db             # SQLite database file                 (sqlite only, required)
--out=./models              # directory to write the model files   (required)
--namespace="App\Models"    # namespace for the generated classes  (required)
--host=127.0.0.1            # server host                          (default: 127.0.0.1)
--port=PORT                 # server port                          (default: 3306 / 5432)
--user=USER                 # user                                 (default: root / postgres)
--table=users               # generate one table only              (default: all tables)

The password is read from the PIPERNATE_DB_PASS environment variable (PIPERNATE_MYSQL_PASS is also accepted) so it never lands in your shell history or process list. Generated classes carry #[Table], #[Id], and typed (nullable-aware) properties — booleans map from tinyint(1) (MySQL) and boolean (PostgreSQL/SQLite).

15. Development & testing

composer install
vendor/bin/phpunit                 # unit + integration (in-memory SQLite, no services)

# End-to-end against real MySQL, MariaDB, PostgreSQL + memcached:
cp .env.example .env               # local, throwaway, gitignored
docker compose up -d               # mysql, mariadb, postgres, memcached
PIPERNATE_MYSQL_PASS=local-test-only-changeme \
PIPERNATE_MARIADB_PASS=local-test-only-changeme \
PIPERNATE_PG_PASS=local-test-only-changeme \
  vendor/bin/phpunit --group e2e
docker compose down -v

php examples/demo.php              # runnable tour of every feature (SQLite)

Unit/integration tests run against in-memory SQLite — fast, no setup.
E2E tests (--group e2e, excluded by default) run against real databases and skip gracefully when a service or extension is missing.
Test credentials are local, throwaway placeholders; real secrets live only in a gitignored .env.

16. Authors & license

Gökalp Kuşçu (gklp)
Mikail Özel (mike)

Licensed under the Apache License 2.0 — see LICENSE.

Name		Name	Last commit message	Last commit date
Latest commit History 38 Commits
.github/workflows		.github/workflows
bin		bin
docs		docs
examples		examples
src		src
tests		tests
.env.example		.env.example
.gitattributes		.gitattributes
.gitignore		.gitignore
CHANGELOG.md		CHANGELOG.md
LICENSE		LICENSE
README.md		README.md
composer.json		composer.json
docker-compose.yml		docker-compose.yml
phpstan.dist.neon		phpstan.dist.neon
phpunit.xml		phpunit.xml

Folders and files

Latest commit

History

Repository files navigation