PDOdb: one Query Builder, six SQL dialects

What the shared API should make portable

PDOdb addresses a practical requirement: describe a data-access operation once and execute it on the supported databases. That matters to a library, tool or product installed in different environments. An application does not need to switch its production database regularly for portability to have value.

PDO unifies connections and query execution, while SQL still differs. Pagination, UPSERT, JSON expressions and retrieving an inserted ID all need attention. If those differences spread through application code, each dialect multiplies the places to change and verify.

PDOdb concentrates this work around dialects. Its shared Query Builder supports MySQL, MariaDB, PostgreSQL, SQLite, Microsoft SQL Server and Oracle. Application code can remain shared when it uses compatible operations and accounts for the selected database versions. Changing the DSN alone does not make arbitrary SQL portable.

The dialect participates in query execution

The Query Builder assembles an operation and its parameters. A dialect defines identifier representation, SQL fragments and supported capabilities. ExecutionEngine and the connection prepare and execute it. That separation lets SQL-form checks complement result checks against a live database.

Responsibilities are also divided inside a dialect. A DML component builds writes, DDL handles schema changes, SqlFormatter formats query constructs, and FeatureSupport reports individual capabilities. Implementations can be separated and reused behind one contract consumed by the Query Builder.

This does not reduce every database to the smallest possible feature set. The API can retain operations supported by only some dialects. Their support boundaries must be explicit, so an unsupported call does not silently become a query with different meaning.

One selection, different SQL forms

An ordinary selection needs a table, fields, a filter, ordering and a page size. In the fragment below, $db is already connected to the chosen database and the caller has obtained and checked $accountId.

$rows = $db->find()
    ->from('events')
    ->select(['id', 'value'])
    ->where('account_id', $accountId)
    ->orderBy('id', 'ASC')
    ->limit(10)
    ->offset(20)
    ->get();

Pagination syntax belongs to the dialect. MySQL, MariaDB, PostgreSQL and SQLite use LIMIT/OFFSET; SQL Server and Oracle support OFFSET … FETCH. SQL Server requires ORDER BY for that form. The application call supplies it explicitly: a library should not guess which order the user needs.

Stable pagination also needs ordering that uniquely positions each row. Here it is id. Sorting by a nonunique field needs a tie-breaker; valid LIMIT syntax alone does not prevent omissions or repeats as data changes.

What stays inside the dialect

The SQL below shows the form for ten rows after skipping twenty. Identifier quoting and the filter are omitted to isolate the pagination difference.

-- MySQL / MariaDB / PostgreSQL / SQLite
SELECT id FROM events ORDER BY id LIMIT 10 OFFSET 20

-- MSSQL / Oracle
SELECT id FROM events ORDER BY id
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY

Quoting addresses another issue: events becomes `events`, "events" or [events], depending on the dialect. The current PDOdb Oracle convention uppercases that identifier and produces "EVENTS". This also affects expectations about result field names.

Values are bound as parameters. Table and column names or sort direction cannot be made safe with a value placeholder: application code must restrict user-selected identifiers to an allowed set. A raw expression needs the same care as handwritten SQL.

UPSERT: shared intent, explicit restrictions

Updating a row on conflict is another operation worth expressing once. PDOdb generates ON DUPLICATE KEY UPDATE for MySQL/MariaDB and ON CONFLICT with EXCLUDED references for PostgreSQL and SQLite.

Here id is the primary key, and a conflict should replace value. For that contract, a single call expresses one intent.

$db->find()
    ->table('events')
    ->onDuplicate(['value'])
    ->insert(['id' => 42, 'value' => 15]);

MSSQL and Oracle explicitly reject this insertion path. Their corresponding operation uses merge(), with a source, match condition and actions. Refusal preserves the API boundary: the library does not replace INSERT … ON CONFLICT with an arbitrary sequence of reads and writes.

Conflict behavior also differs between databases that support this call. MySQL reacts to primary or unique key violations; PostgreSQL requires a conflict target for DO UPDATE. The current Query Builder derives it from inserted columns: id when present, otherwise the first column. With multiple unique constraints, do not assume both databases select the same conflict.

Portable code therefore relies on a defined schema and write contract. If an application needs one method across all six databases, the choice of UPSERT/MERGE strategy must be explicit and testable.

An emulation must preserve the result

A capability can sometimes be expressed through another SQL construct. A conditional sum of amount over paid rows is one call: the application states the filter, the dialect chooses the SQL form.

use tommyknocker\pdodb\helpers\Db;

$row = $db->find()
    ->from('payments')
    ->select(['paid_sum' => Db::sum('amount')->filter('status', 'paid')])
    ->getOne();

-- PostgreSQL / SQLite: native FILTER
SUM(amount) FILTER (WHERE status = :status)

-- MySQL / MariaDB / MSSQL / Oracle: CASE
SUM(CASE WHEN status = :status THEN amount END)

PostgreSQL and SQLite emit FILTER; MySQL, MariaDB, SQL Server and Oracle move the condition into CASE. RawValueResolver checks supportsFilterClause() and selects the form. One call, different SQL.

Correctness depends on the result: does the sum match, and what happens to NULL or an empty matching set? Similar-looking SQL is insufficient. Each supported aggregate form needs its own checks; a successful SUM example does not establish equivalence for every aggregate.

This leads to three support choices: use a native construct; express the operation differently while preserving its agreed semantics; or refuse it. Refusal is necessary where automatic substitution cannot provide the expected behavior.

Why Oracle and SQL Server require separate work

A dialect extends beyond SELECT and quoting. Retrieving an identifier after insertion also depends on the database.

Assume the table is configured to generate a numeric primary key. For Oracle, this example uses an events_seq sequence to supply the key during insertion, for example through a trigger.

$id = $db->find()
    ->table('events')
    ->insert(['value' => 15]);

With key generation configured, MySQL, MariaDB, PostgreSQL, SQLite and SQL Server retrieve the identifier through PDO lastInsertId. For Oracle, PDOdb uses a separate path: the dialect reads CURRVAL from a table_seq sequence whose name is derived from the table. For events, this is events_seq. The application call remains the same; the database schema must follow the sequence convention.

SQL Server already required separate handling for OFFSET FETCH pagination and for refusing onDuplicate in favour of merge(). In this example, the IDENTITY column value is retrieved through lastInsertId.

$row = $db->find()
    ->from('events')
    ->select(['status' => Db::jsonGet('payload', ['status'])])
    ->getOne();

JSON is a second such boundary. One Db::jsonGet call yields a text scalar: JSON_UNQUOTE/JSON_EXTRACT in MySQL, the #>> operator in PostgreSQL, json_extract in SQLite, JSON_VALUE in SQL Server and Oracle. Missing fields, JSON null, SQL NULL, numbers and strings need explicit expectations. A scalar is not a JSON object: SQL Server and Oracle use JSON_QUERY for objects.

PDOdb CI has separate Oracle XE 21 and SQL Server 2022 environments. That matters for driver integration and execution: generating SQL on a machine without those servers is not a substitute. A database name does not imply every version is supported; the constructs used must match the selected environment.

How the shared contract is checked

The project checks both the library and examples. CI has jobs for MySQL, MariaDB, PostgreSQL, SQLite, MSSQL and Oracle, plus PHPStan level 8. The documentation is accompanied by executable examples in examples/. The test-examples.sh script runs them in environments available to each job.

Each check answers a different question. PHPStan checks type contracts, not SQL semantics. Query-string assertions detect generator changes. Integration tests execute SQL and inspect data: UPSERT tests, for example, check both the generated construct and the row’s new value. The unsupported MSSQL/Oracle path has expected-exception tests.

At preparation time, the master Codecov badge shows 65%. That measures source execution by tests, not the proportion of correctness proved. Executable examples help detect drift between documentation and the API; tests remain responsible for result assertions.

• Reads: row membership, order, page boundaries and value types.
• Writes: the intended conflict constraint, affected rows and values, and rollback on failure.
• JSON and expressions: missing values, NULL, types and boundary cases.
• Refusal: a clear error for the unsupported operation before its SQL executes.

These checks form the compatibility matrix. Discovered discrepancies need regression tests.

What a team gains and what it costs

A shared Query Builder provides one place for SQL construction rules and lets application code run in different supported environments. Portability comes from chosen operations, compatible schemas, defined result expectations and tests on the target databases.

That does not establish equal performance or automatic portability of an entire system. Raw SQL, migrations, collation, type details, indexes, locking and isolation levels remain part of the design. Establish those conditions before a migration rather than discovering them after switching drivers.

PDOdb currently has limited adoption; a team must learn its API and assess its reliance on project maintenance. Documentation with basic and advanced examples reduces onboarding cost without eliminating it. Framework independence also does not mean no Composer dependencies: the library uses PSR contracts.

I would evaluate adoption on several real application operations: does the same code run on the required databases, do results match, where is a separate strategy needed, and will the next developer understand that boundary? Such a trial gives a team better grounds for choosing than the length of a feature list.