Download the PHP package johnnyjoy/uda without Composer

Universal Data Abstractor (UDA)

Slim SQL execution for PHP repositories — one API across major databases, optional read cache, explicit queries under project control.

PHP APIs and services can keep SQL in repository classes — methods the team names, SQL visible in PR review. UDA is the engine under that layer: connect, run named-parameter SQL, use dialect-aware fluent builders when they help, and keep the same code when the database changes.

• Readable data layer — SQL stays in methods that are reviewable in PRs and explainable to ops.
• Many databases, one habit — Postgres today, SQL Server or Oracle tomorrow; edit uda.json, not every query file.
• Stable team contract — one handle (Database or Link), named params only, integration-tested engines in CI.
• More speed when needed — transparent read cache (Redis, Memcached, in-memory, …); enable in config, keep table hints on reads.
• Explicit query ownership — repositories and Link; domain methods, not generated find() helpers.

What UDA does

Supported databases

Per-engine config and CI detail: integration/README.md.

Informix note: Informix is not currently supported. The PHP PDO ecosystem for Informix has a fundamental compatibility gap: pdo_informix requires the IBM Informix Client SDK (CSDK), which is not included in the freely-available informix-developer-database Docker image and is not redistributable. The alternative, pdo_ibm (IBM DB2 CLI driver connecting via DRDA), ignores PDO::ATTR_EMULATE_PREPARES — it calls SQLNumResultCols and SQLDescribeParam unconditionally after every SQLPrepare, and Informix's DRDA server returns error -201 for both on pagination and MERGE statements. Support may be revisited when the PDO driver situation improves.

Install

PHP 8.2+, ext-pdo. MIT.

github.com/johnnyjoy/uda

Quick start

Repository with Link — SQL on the class; controllers call it:

See Configuration, Fluent queries, and Caching below. Full layer guide: building-your-dal.md.

Configuration

UDA loads one JSON file (.json extension). Production default: environment variable UDA_CONFIG points at the file. A path can also be passed to Database::connect().

Full schema (PDO options, init_sql, trace, templates): configuration.md.

Raw SQL

Named parameters only — positional ? is rejected before PDO.

The third argument on reads/writes is the table hint array (e.g. ['users']) — required for correct cache behaviour when caching is enabled. Fluent builders pick up hints from from() / into() / table().

Fluent queries

Builders compile dialect-aware SQL and run through the same Database → Driver → PDO path as raw SQL. Obtain builders only from $db->select() (etc.) — not new Select().

How chaining works

A fluent query is a chain of steps, then a terminator that finishes the job.

WHERE is a sub-chain. where(), whereIn(), whereExists(), and whereRaw() return a WhereBuilder. Stack predicates there (where chains to where on the same WhereBuilder).

What end() actually means. The name is easy to misread: end() does not mean “the query is done.” It means end the WHERE sub-chain — flush predicates onto the parent Select / Update / Delete and return that parent so chaining can continue.

Terminators (pick one to finish the chain)

Terminators are the last method. They compile the builder and run it (or, for toSql(), return an immutable Query\Sql value for reuse / logging).

select() — reads

insert() / update() / delete() — writes

upsert() — exec() only.

Avoid rows() when only one row is needed — use row() or value().

Builder entrypoints (chain steps vs terminators)

Upsert

Engine-specific merge/upsert SQL from one builder shape:

Capability matrix per engine: patterns.md.

Guardrails and unsafe()

update() and delete() without a where() clause throw QueryException by default. For intentional full-table writes (staging reset, flag flip on every row), opt in:

Reuse: bind, fluent templates, and fragments

UDA separates compiling SQL from binding values. Pick the pattern that matches how much of the query stays fixed.

Bind at execute time (same SQL, new values)

Keep the SQL text stable; pass a new parameter array on each call. The driver can reuse the prepared statement when the SQL string matches (same connection).

Same idea with a saved Sql template and fresh binds:

Fluent partial builder (reuse the FROM / SELECT shape)

Because chain steps clone the builder (see How chaining works), a shared from / column list and fork different where branches — each branch finishes with a read terminator (rows(), etc.); WhereBuilder ends the WHERE clause automatically:

$list itself was never mutated; each where() started a new branch from the same template. Values in ->where('col', $value) are bound when that branch’s terminator compiles and runs.

Saved subqueries and CTEs (fluent fragments)

Compile once with toSql(), or pass a live Select, into another builder:

fromSub(), joinSub(), leftJoinSub(), whereExists(), and with() / withRecursive() all accept Select or Sql. Use the same engine that compiled the fragment.

Run compiled SQL on another connection (e.g. replica) when the engine matches:

toSql() compiles only — it does not execute. If literals are baked in at compile time (->where('active', 1)), change binds by recompiling or by using a parameterized SQL string. See public-api.md (compiled Sql and safe fragments).

Safe SQL fragments (inList, identifiers, pagination)

For dynamic pieces inside raw SQL, use helpers so structure stays parameterized:

In fluent chains, whereRaw() merges named parameters into the builder bag. Reuse the same SQL fragment with different binds — no end() before rows() (the read terminator ends WHERE clause automatically):

More helpers: $db->q() (quoted identifiers), orderByAllowed(), limitOffset() — public-api.md § 4.

Bulk insert (multiple rows)

One row: chain ->set('column', $value) or ->values(['id' => 1, 'name' => 'Ada'])->exec(). RETURNING / OUTPUT on supported engines: patterns.md and public-api.md.

With Link, use $this->select(), $this->insert(), $this->fromSub(), etc. inside the repository class. More recipes: patterns.md.

Errors

UDA throws typed exceptions — map them in the HTTP or job layer, not by parsing message text.

QueryException exposes category() (guardrail, constraint, connection, syntax, unsupported, execution, binding), plus sqlState() and driverCode() when the driver provides them. Full mapping notes: public-api.md § 7.

Caching

Read caching is on in config, transparent in code. Repositories always call row / rows / builders the same way; UDA decides hit or miss on the read path.

How it is designed to work (cache-doctrine.md): interval caching — if cache exists and policy allows, use it and hit the DB at most every minIntervalSeconds; max age (ttlSeconds) ends an entry’s lifetime; metadata is evaluated per query key (no single global TTL); writes invalidate via table mtimes; optional stale-on-error serves last good cache when the DB is down (allowStaleOnError). v1 ships mtime invalidation + transparent reads; interval/max-age/stale-on-error policy fields in config are not enforced yet (caching.md).

Enable per connection in uda.json:

Details: cache-doctrine.md.

Link vs inject Database

Both expose raw SQL, builders, and transactions on the same pipeline. building-your-dal.md — Database or Link

Run in production

Typical setup: php-fpm (or equivalent) behind nginx/Apache, often in a container. Set UDA_CONFIG, mount uda.json, call Database::setQueryObserver() once in public/index.php when slow-query or error logging is required.

Long-lived workers (Octane, RoadRunner, Swoole): shared handle per worker — read architecture.md. Deploy checklist: skills/uda-config-deploy/SKILL.md.

Documentation

Map: docs/README.md

Agent skills

skills/ — checklists for any AI tool that loads skills. Start: uda-data-access/SKILL.md, uda-sql-and-cache/SKILL.md.

Contributing & license

docs/README.md#for-contributors · CHANGELOG.md