Download the PHP package utopia-php/query without Composer

Utopia Query

A PHP library for building type-safe, dialect-aware queries and DDL statements. Provides a fluent builder API with parameterized output for MySQL, MariaDB, PostgreSQL, SQLite, ClickHouse, and MongoDB, plus wire protocol parsers and a serializable Query value object for passing query definitions between services.

Installation

Requires PHP 8.4+

Table of Contents

• Query Object
  • Filters
  • Ordering and Pagination
  • Logical Combinations
  • Spatial Queries
  • Vector Similarity
  • JSON Queries
  • Selection
  • Raw Expressions
  • Serialization
  • Helpers
• Query Builder
  • Basic Usage
  • Raw and Column Predicates
  • Aggregations
  • Statistical Aggregates
  • Bitwise Aggregates
  • Conditional Aggregates
  • String Aggregates
  • Group By Modifiers
  • Sequences
  • Joins
  • Unions and Set Operations
  • CTEs (Common Table Expressions)
  • Window Functions
  • CASE Expressions
  • Inserts
  • Updates
  • Deletes
  • Upsert
  • Locking
  • Transactions
  • EXPLAIN
  • Conditional Building
  • Builder Cloning and Callbacks
  • Debugging
  • Hooks
• Dialect-Specific Features
  • MySQL
  • MariaDB
  • PostgreSQL
  • SQLite
  • ClickHouse
  • MongoDB
  • Feature Matrix
• Schema Builder
  • Creating Tables
  • Altering Tables
  • CHECK Constraints
  • Generated Columns
  • Composite Primary Keys
  • Indexes
  • Foreign Keys
  • Partitions
  • Comments
  • Views
  • Procedures and Triggers
  • PostgreSQL Schema Extensions
  • ClickHouse Schema
  • SQLite Schema
  • MongoDB Schema
• Wire Protocol Parsers
  • SQL Parser
  • MySQL Parser
  • PostgreSQL Parser
  • MongoDB Parser
• Compiler Interface
• Contributing
• License

Query Object

The Query class is a serializable value object representing a single query predicate. It serves as the input to the builder's filter(), having(), and other methods.

Filters

Note: Query::contains() is deprecated — use Query::containsString() for string substring matching or Query::containsAny() for array/relationship attributes.

Ordering and Pagination

Logical Combinations

Spatial Queries

Vector Similarity

JSON Queries

Selection

Raw Expressions

Serialization

Queries serialize to JSON and can be parsed back:

Helpers

Query Builder

The builder generates parameterized queries from the fluent API. Every build(), insert(), update(), and delete() call returns a Statement with ->query (the query string), ->bindings (the parameter array), and ->readOnly (whether the query is read-only).

Six dialect implementations are provided:

• Utopia\Query\Builder\MySQL — MySQL
• Utopia\Query\Builder\MariaDB — MariaDB (extends MySQL with RETURNING, sequences, and dialect-specific spatial handling)
• Utopia\Query\Builder\PostgreSQL — PostgreSQL
• Utopia\Query\Builder\SQLite — SQLite
• Utopia\Query\Builder\ClickHouse — ClickHouse
• Utopia\Query\Builder\MongoDB — MongoDB (generates JSON operation documents)

MySQL, MariaDB, PostgreSQL, and SQLite extend Builder\SQL which adds locking, transactions, upsert, spatial queries, and full-text search. ClickHouse and MongoDB extend Builder directly with their own dialect-specific syntax.

Basic Usage

Batch mode — pass all queries at once:

Using with PDO:

Raw and Column Predicates

In addition to the typed filter() API, two escape hatches are available on every SQL dialect (MySQL, MariaDB, PostgreSQL, SQLite, ClickHouse). Both throw ValidationException on the MongoDB builder.

whereRaw() — emit a raw SQL fragment with its own bindings. The caller owns the SQL:

whereColumn() — typed column-to-column predicate with quoting. The operator is validated against ['=', '!=', '<>', '<', '>', '<=', '>=']:

Aggregations

Distinct:

Statistical Aggregates

Available on MySQL, PostgreSQL, SQLite, and ClickHouse via the StatisticalAggregates interface:

Bitwise Aggregates

Available on MySQL, PostgreSQL, SQLite, and ClickHouse via the BitwiseAggregates interface:

Conditional Aggregates

Available on MySQL, PostgreSQL, SQLite, and ClickHouse via the ConditionalAggregates interface:

Also available: avgWhen(), minWhen(), maxWhen().

String Aggregates

Available on MySQL, PostgreSQL, and ClickHouse via the StringAggregates interface:

Group By Modifiers

Available on MySQL, PostgreSQL, and ClickHouse via the GroupByModifiers interface:

Sequences

Available on MariaDB and PostgreSQL via the Sequences interface. Emits NEXTVAL() and CURRVAL() as select expressions:

Joins

Complex join conditions with joinWhere():

Full outer joins (PostgreSQL, ClickHouse):

Lateral joins (MySQL, MariaDB, PostgreSQL):

Unions and Set Operations

Also available: unionAll(), intersect(), intersectAll(), except(), exceptAll().

CTEs (Common Table Expressions)

Use withRecursive() for recursive CTEs, or withRecursiveSeedStep() to construct a recursive CTE from separate seed and step builders:

Window Functions

Prefix an orderBy column with - for descending order (e.g., ['-amount']).

Named window definitions allow reusing the same window across multiple expressions:

CASE Expressions

Build a CASE expression with Utopia\Query\Builder\Case\Expression, then pass it to selectCase() or setCase(). All columns are quoted by the dialect, and all values are bound as parameters:

Supported WHEN shapes:

• when(string $column, Operator $operator, mixed $value, mixed $then) — comparison. The operator is a closed enum of the six comparisons: Operator::Equal, Operator::NotEqual, Operator::LessThan, Operator::LessThanEqual, Operator::GreaterThan, Operator::GreaterThanEqual.
• whenNull(string $column, mixed $then) and whenNotNull(string $column, mixed $then).
• whenIn(string $column, array $values, mixed $then).
• whenRaw(string $condition, mixed $then, array $conditionBindings = []) — escape hatch for complex predicates. The caller owns the SQL fragment; the $then value is still bound.

Inserts

set() takes an associative row array. Calling it multiple times appends rows for a batch insert:

Updates

Deletes

Upsert

Available on MySQL, PostgreSQL, and SQLite builders (Builder\SQL subclasses):

onConflict() takes the conflict key columns and the columns to update on conflict:

Insert or ignore — skip rows that conflict instead of updating:

Upsert from SELECT — insert from a subquery with conflict resolution:

Locking

Available on MySQL, PostgreSQL, and SQLite builders:

Also available: forShare(), forUpdateSkipLocked(), forUpdateNoWait(), forShareSkipLocked(), forShareNoWait().

PostgreSQL also supports table-specific locking: forUpdateOf('accounts'), forShareOf('accounts').

Transactions

Available on MySQL, PostgreSQL, and SQLite builders:

EXPLAIN

Available on all builders. MySQL and PostgreSQL provide extended options:

Conditional Building

when() applies a callback only when the condition is true:

Builder Cloning and Callbacks

Cloning creates a deep copy of the builder, useful for branching from a shared base:

Build callbacks run before or after building:

Debugging

toRawSql() inlines bindings for inspection (not for execution):

Hooks

Hooks extend the builder with reusable, testable classes for attribute resolution and condition injection.

Attribute hooks map virtual field names to real column names:

Filter hooks inject conditions into every query:

Custom filter hooks implement Hook\Filter:

Join filter hooks inject per-join conditions with placement control (ON vs WHERE):

The built-in Tenant hook implements both Filter and JoinFilter — it automatically applies ON placement for LEFT/RIGHT joins and WHERE placement for INNER/CROSS joins.

Write hooks decorate rows before writes and run callbacks after create/update/delete operations:

Dialect-Specific Features

MySQL

Spatial queries — uses ST_Distance(), ST_Intersects(), ST_Contains(), etc.:

All spatial predicates: filterDistance, filterIntersects, filterNotIntersects, filterCrosses, filterNotCrosses, filterOverlaps, filterNotOverlaps, filterTouches, filterNotTouches, filterCovers, filterNotCovers, filterSpatialEquals, filterNotSpatialEquals.

JSON operations:

JSON mutation methods: setJsonAppend, setJsonPrepend, setJsonInsert, setJsonRemove, setJsonIntersect, setJsonDiff, setJsonUnique, setJsonPath.

Query hints:

Full-text search — MATCH() AGAINST(? IN BOOLEAN MODE):

UPDATE with JOIN:

DELETE with JOIN:

MariaDB

Extends MySQL with MariaDB-specific features and spatial handling:

• Uses ST_DISTANCE_SPHERE() for meter-based distance calculations.
• Uses ST_GeomFromText() without the axis-order parameter.
• Validates that distance-in-meters only works between POINT types.

All MySQL features (JSON, hints, lateral joins, UPDATE/DELETE JOIN, etc.) are inherited.

RETURNING (MariaDB 10.5+) — get affected rows back from INSERT, UPDATE, or DELETE:

returning() cannot be combined with upsert() — MariaDB does not support RETURNING with ON DUPLICATE KEY UPDATE. Doing so throws ValidationException. Clear the returning columns with returning([]) first, or issue a separate update() statement.

Sequences — native sequence support via nextVal() and currVal():

PostgreSQL

Spatial queries — uses PostGIS functions with geography casting for meter-based distance:

Vector search — uses pgvector operators (<=>, <->, <#>):

Metrics: VectorMetric::Cosine (<=>), VectorMetric::Euclidean (<->), VectorMetric::Dot (<#>).

JSON operations — uses native JSONB operators:

Full-text search — to_tsvector() @@ websearch_to_tsquery():

Regex — uses PostgreSQL ~ operator instead of REGEXP. String matching uses ILIKE for case-insensitive comparison.

RETURNING — get affected rows back from INSERT/UPDATE/DELETE:

DISTINCT ON — select the first row per group:

Aggregate FILTER — per-aggregate WHERE clause (SQL standard):

Ordered-set aggregates:

MERGE — SQL standard MERGE statement:

UPDATE FROM / DELETE USING:

Sequences — native sequence support via nextVal() / currVal() (see Sequences).

Recursive CTEs — both withRecursive() and withRecursiveSeedStep() compile to standard WITH RECURSIVE syntax.

Table sampling:

SQLite

Extends Builder\SQL with SQLite-specific behavior:

• JSON support via json_each() and json_extract(). setJsonPath compiles to json_set.
• Conditional aggregates using CASE WHEN syntax.
• INSERT OR IGNORE for insertOrIgnore().
• Regex and full-text search throw UnsupportedException.
• Spatial queries throw UnsupportedException.

ClickHouse

FINAL — force merging of data parts:

SAMPLE — approximate query processing:

PREWHERE — filter before reading columns (optimization for wide tables):

SETTINGS:

LIMIT BY — limit rows per group:

ARRAY JOIN — unnest array columns into rows:

ASOF JOIN — join on the closest matching row (time-series). Requires one or more equi-join pairs plus exactly one inequality condition:

asofLeftJoin() takes the same arguments and emits ASOF LEFT JOIN, preserving left rows with no match. AsofOperator variants: LessThan, LessThanEqual, GreaterThan, GreaterThanEqual.

ORDER BY ... WITH FILL — fill gaps in ordered results:

Approximate aggregates — ClickHouse-native probabilistic functions:

quantiles() computes multiple quantile levels in a single pass. Levels are validated to be in [0, 1]; the array must be non-empty.

Additional approximate aggregates: argMin(), argMax(), topK(), topKWeighted(), anyValue(), anyLastValue(), groupUniqArray(), groupArrayMovingAvg(), groupArrayMovingSum().

String matching — uses native ClickHouse functions instead of LIKE:

Regex — uses match() function instead of REGEXP.

Time bucketing — groups rows into fixed-width windows on a timestamp column. Allowed intervals: 1m, 5m, 15m, 1h, 1d, 1w, 1M. Compiles to toStartOfMinute / toStartOfFiveMinutes / toStartOfFifteenMinutes / toStartOfHour / toStartOfDay / toStartOfWeek / toStartOfMonth:

Other dialects throw UnsupportedException from compileGroupByTimeBucket. Re-emit the bucket function via selectRaw / orderByRaw when you need to reference it in the SELECT list or ORDER BY (same pattern as groupByRaw).

Named-typed bindings — opt into ClickHouse {name:Type} placeholders for safe parameterization over the HTTP interface. Off by default; positional ? placeholders remain the default and behave identically to every other dialect:

Unregistered columns fall through to value-based inference: int → Int64, float → Float64, bool → UInt8, null → Nullable(String), DateTimeInterface → DateTime64(3), everything else → String. Register types via withParamType($column, $type) or withParamTypes($map) whenever the inference rule doesn't match the column's ClickHouse declaration. The positional $bindings array is still exposed on the resulting Statement for callers that prefer it.

UPDATE — compiles to ALTER TABLE ... UPDATE with mandatory WHERE:

DELETE — two forms. delete() defaults to the lightweight DELETE FROM … form, which marks rows deleted via a mask and is async by default. Opt into the heavier mutation form (ALTER TABLE … DELETE) when you need parts rewritten on disk; the two are not interchangeable, so the builder never auto-translates between them.

The trailing SETTINGS clause is whatever the caller registers via settings() — the builder does not auto-pair a sync setting to a chosen delete mode.

Note: Full-text search (Query::search()) is not supported in ClickHouse and throws UnsupportedException. The ClickHouse builder also forces all join filter hook conditions to WHERE placement, since ClickHouse does not support subqueries in JOIN ON.

MongoDB

The MongoDB builder generates JSON operation documents instead of SQL. The Statement->query contains a JSON-encoded operation and Statement->bindings contains parameter values. whereRaw() and whereColumn() are not supported and throw ValidationException.

Basic queries:

Array operations:

Field update operations:

Advanced array push with position, slice, and sort modifiers:

Conditional array updates with array filters:

Upsert:

Pipeline aggregation stages:

Atlas Search:

Table sampling:

Full-text search (non-Atlas):

Feature Matrix

Unsupported features are not on the class — consumers type-hint the interface to check capability (e.g., if ($builder instanceof Spatial)).

Schema Builder

The schema builder generates DDL statements for table creation, alteration, indexes, views, and more.

Creating Tables

Schema::table($name) returns a fluent builder. Column-adding methods (id, string, integer, …) return a Column you can chain modifiers on; the column also exposes the table-level builder so you can keep chaining sibling columns or terminal calls without breaking the chain. Terminal methods (create, createIfNotExists, alter, drop, dropIfExists, truncate, rename) compile and return a Statement.

Use createIfNotExists() to add IF NOT EXISTS:

Available column types: id, uuid, string, text, mediumText, longText, tinyInteger, smallInteger, integer, bigInteger, serial, bigSerial, smallSerial, float, decimal, boolean, datetime, timestamp, json, binary, enum, point, linestring, polygon, vector (PostgreSQL only), timestamps.

Column modifiers: nullable(), default($value), defaultRaw($expression), unsigned(), unique(), primary(), autoIncrement(), after($column), comment($text), collation($collation), check($expression), generatedAs($expression) + stored() / virtual(), ttl($expression) (ClickHouse), userType($name) (PostgreSQL).

Raw default expressions — use defaultRaw($expression) for dialect-specific server-generated defaults that default() would otherwise quote as a string literal (now(), CURRENT_TIMESTAMP, gen_random_uuid(), generateUUIDv4(), UUID(), …). The expression is emitted verbatim and must come from a trusted source; it must not be empty or contain a semicolon. Takes precedence over default() when both are set.

SERIAL types — auto-incrementing integers. PostgreSQL emits native SERIAL / BIGSERIAL / SMALLSERIAL; MySQL/MariaDB compile to INT AUTO_INCREMENT / BIGINT AUTO_INCREMENT / SMALLINT AUTO_INCREMENT; SQLite maps to INTEGER. ClickHouse and MongoDB throw UnsupportedException:

Altering Tables

addColumn(string $name, ColumnType $type, ?int $lengthOrPrecision = null) and modifyColumn(...) take the ColumnType enum directly. The addIndex(...) overload takes the IndexType enum.

CHECK Constraints

Typed CHECK constraints are supported at both the table and column level on MySQL 8.0.16+, MariaDB, PostgreSQL, and SQLite. ClickHouse throws UnsupportedException.

Constraint names are validated as standard SQL identifiers; expressions are emitted verbatim and must come from trusted sources — never from untrusted input.

Generated Columns

Generated columns compute their value from an expression. Both STORED and VIRTUAL are supported on MySQL, MariaDB, and SQLite. PostgreSQL supports only STORED (calling virtual() and compiling for PostgreSQL throws UnsupportedException). ClickHouse throws UnsupportedException for generated columns.

Composite Primary Keys

Declare a primary key across two or more columns with Table::primary([...]). Mixing a column-level ->primary() with Table::primary([...]) throws ValidationException. MongoDB throws UnsupportedException.

Indexes

PostgreSQL supports index methods, operator classes, and concurrent creation:

Foreign Keys

Available actions: ForeignKeyAction::Cascade, SetNull, SetDefault, Restrict, NoAction.

Partitions

Available on MySQL, PostgreSQL, and ClickHouse:

Partition strategies: partitionByRange($expression), partitionByList($expression), partitionByHash($expression, ?int $partitions = null). The optional partition count on partitionByHash() emits PARTITIONS <count> (MySQL/MariaDB HASH/KEY semantics) and must be >= 1:

Comments

Table and column comments are available via the TableComments and ColumnComments interfaces:

Views

Procedures and Triggers

PostgreSQL Schema Extensions

Type differences from MySQL: INTEGER (not INT), DOUBLE PRECISION (not DOUBLE), BOOLEAN (not TINYINT(1)), JSONB (not JSON), BYTEA (not BLOB), SERIAL / BIGSERIAL / SMALLSERIAL for auto-incrementing ints, VECTOR(n) for pgvector, GEOMETRY(type, srid) for PostGIS. Enums use TEXT CHECK (col IN (...)) (or a user-defined enum type via userType()).

ClickHouse Schema

ClickHouse uses Nullable(type) wrapping for nullable columns, Enum8(...) for enums, Tuple(Float64, Float64) for points, and TYPE minmax GRANULARITY 3 for indexes. Foreign keys, stored procedures, triggers, generated columns, and CHECK constraints throw UnsupportedException.

Supports the TableComments, ColumnComments, DropPartition, Views, and Databases interfaces.

Engine selection — choose from 10 variants of the Engine enum:

The 10 variants: MergeTree, ReplacingMergeTree, SummingMergeTree, AggregatingMergeTree, CollapsingMergeTree, ReplicatedMergeTree, Memory, Log, TinyLog, StripeLog.

TTL — table-level and column-level time-to-live expressions:

TTL expressions are emitted verbatim; they must not be empty or contain semicolons. Dialects other than ClickHouse throw UnsupportedException.

Skip-index algorithms — every ClickHouse index is a data-skipping index that accelerates WHERE pruning by letting the engine skip whole granules. Pick the algorithm that matches the column shape via the algorithm argument on Table::index():

The 6 algorithms are MinMax, Set, BloomFilter, NgramBloomFilter, TokenBloomFilter, Inverted. Algorithm-specific arguments are passed via algorithmArgs and rendered verbatim — supply them from trusted (developer-controlled) source. Other dialects ignore the ClickHouse-only algorithm / algorithmArgs / granularity arguments.

MinMax and Inverted take no parenthesised arguments in ClickHouse DDL — passing algorithmArgs for them throws ValidationException. Skip indexes can also be added via ALTER TABLE … ADD INDEX by calling alter() on the builder.

Engine SETTINGS — emit SETTINGS k=v after the TTL clause:

Setting names must match [A-Za-z_][A-Za-z0-9_]*; string values are restricted to [A-Za-z0-9_.\-+/]*. Use ints / floats / booleans for everything else. Other dialects ignore the call.

LowCardinality — wrap a column type in LowCardinality(...) for compact dictionary-encoded storage on string columns with a small number of distinct values (status enums, type discriminators, country codes, category labels):

Nullable is applied outside LowCardinality to match ClickHouse's required wrapping order. The lowCardinality() method is only available on the ClickHouse builder — callers on other dialects (MySQL, PostgreSQL, SQLite, MongoDB) cannot reach this method at all.

FixedString(N) — fixed-length string column. Use for ISO codes, hash digests, and other values whose byte length is known and constant:

Length must be at least 1. The fixedString() method is only available on the ClickHouse builder — the type has no portable mapping.

Column-level CODEC — append one or more compression codecs to a column. Multiple codec() calls accumulate and emit CODEC(c1, c2, ...):

Each codec string is emitted verbatim; supply codec arguments inline ('Delta(4)', 'ZSTD(3)'). Codec strings must not be empty or contain a semicolon. The codec() method is only available on the ClickHouse builder.

SAMPLE BY — declare a sampling expression for approximate-query support (SELECT ... SAMPLE k). Emitted after ORDER BY and before TTL / SETTINGS:

The expression is emitted verbatim and must not be empty or contain a semicolon. SAMPLE BY only applies to engines that take an ORDER BY clause (the MergeTree family); using it with Memory, Log, TinyLog, or StripeLog throws UnsupportedException. The sampleBy() method is only available on the ClickHouse builder.

UInt8 / Int8 via tinyInteger() and UInt16 / Int16 via smallInteger() — small integer columns are useful for bounded enumerations, percentage values, scroll depth, and similar fields where the value range fits well below 32 bits. Storing them as UInt8 saves 75% of the disk and memory footprint compared to the default UInt32 produced by integer()->unsigned():

tinyInteger() and smallInteger() are on the base builder, so the same calls map to TINYINT / SMALLINT on MySQL, SMALLINT on PostgreSQL (both shapes — PostgreSQL has no TINYINT), and INTEGER on SQLite.

Array(T) and Tuple(...) column types — model multi-valued attributes (tags, labels, parallel-array nested records) and fixed-arity composites (geo points, key/value pairs) directly on the builder:

The element type runs back through the standard column-type compiler, so the parent column's unsigned() and precision flags carry through to the inner type. LowCardinality(...) is rejected on these columns because ClickHouse only permits it on scalar types. Nullable(Array(...)) is also rejected — use an empty array [] as the missing-value sentinel. Nullable(Tuple(...)) is rejected for the same reason (ClickHouse marks it experimental and gates it behind allow_experimental_nullable_tuple_type); use Tuple(Nullable(T1), Nullable(T2), ...) instead. Both array() and tuple() are only available on the ClickHouse builder.

decimal(precision, scale) — fixed-point numeric column for monetary or precision-sensitive values where binary floating-point error is unacceptable:

decimal() is on the base builder: ClickHouse emits Decimal(P, S), MySQL and PostgreSQL emit DECIMAL(P, S), SQLite emits NUMERIC(P, S), and MongoDB maps to the decimal BSON type. Scale must not be negative or exceed precision.

UUID column type with defaultRaw() — UUIDs are a first-class, fixed-width identifier type in ClickHouse and PostgreSQL, and a 36-character string elsewhere. Pair with defaultRaw() to attach a server-generated default expression that the standard default() would otherwise quote as a literal:

uuid() compiles to the native UUID type on ClickHouse and PostgreSQL, CHAR(36) on MySQL, TEXT on SQLite, and the string BSON type on MongoDB. defaultRaw(string) is on the base Column and emits the expression verbatim — use for generateUUIDv4() (ClickHouse), gen_random_uuid() (PostgreSQL), UUID() (MySQL), now(), CURRENT_TIMESTAMP, and similar dialect-specific server-generated defaults. The expression must come from a trusted source; it must not be empty or contain a semicolon. defaultRaw() takes precedence over default() when both are set.

Raw expressions in ORDER BY — MergeTree ORDER BY clauses routinely include scalar function calls (toDate(ts), cityHash64(...), intHash32(user_id)) to control sparse-index cardinality. orderBy(array) restricts each entry to a plain identifier; use orderByRaw(string) to emit the full tuple verbatim:

The expression is emitted verbatim and must come from a trusted source. orderByRaw() takes precedence over orderBy() when both are set. Mirrors the existing partitionBy(string) convention. Only available on the ClickHouse builder.

rawColumn() passthrough — Table::rawColumn(string $definition) is the standard escape hatch for column types the builder does not yet model. It is honoured on every dialect, including ClickHouse:

These OLAP-shaped modifiers live on the ClickHouse-specific Column\ClickHouse and Table\ClickHouse builders. Because the methods only exist on the dialect's own builder subclasses, calling ->lowCardinality(), ->sampleBy(), ->array(), ->tuple(), or ->orderByRaw() on a MySQL, PostgreSQL, SQLite, or MongoDB builder fails at the type level, with no runtime branch needed.

SQLite Schema

SQLite uses simplified type mappings: INTEGER for booleans, TEXT for datetimes/JSON, REAL for floats, BLOB for binary. Auto-increment uses AUTOINCREMENT. Vector and spatial types are not supported. Foreign keys, stored procedures, and triggers throw UnsupportedException. SERIAL types map to INTEGER. Both STORED and VIRTUAL generated columns are supported.

MongoDB Schema

The MongoDB schema generates JSON commands for collection management with BSON type validation.

Creating collections with JSON Schema validation:

Altering collections:

Indexes:

Collection operations:

Views:

Database management:

Column types map to BSON types: string → string, integer/bigInteger → int, float/double → double, boolean → bool, datetime/timestamp → date, json → object, binary → binData. Composite primary keys, CHECK constraints, generated columns, SERIAL types, and user-defined types all throw UnsupportedException.

Wire Protocol Parsers

The Parser interface classifies raw database traffic into query types (Read, Write, TransactionBegin, TransactionEnd, Unknown). This is useful for connection proxies, audit logging, and read/write splitting.

SQL Parser

The abstract Parser\SQL class provides keyword-based classification for SQL dialects:

Read keywords: SELECT, SHOW, DESCRIBE, DESC, EXPLAIN, WITH (when followed by a read), TABLE, VALUES.

Write keywords: INSERT, UPDATE, DELETE, ALTER, DROP, CREATE, TRUNCATE, RENAME, REPLACE, LOAD, GRANT, REVOKE, MERGE, CALL, EXECUTE, DO, HANDLER, IMPORT.

Transaction keywords: BEGIN, START → TransactionBegin; COMMIT, ROLLBACK, SAVEPOINT, RELEASE → TransactionEnd.

Special handling: COPY is classified based on direction (FROM STDIN = Write, TO STDOUT = Read). SET is classified as TransactionEnd (session configuration).

MySQL Parser

Parses MySQL wire protocol binary packets:

Recognizes MySQL command bytes including COM_QUERY (classifies via SQL text), COM_STMT_PREPARE, COM_STMT_EXECUTE, COM_INIT_DB, COM_QUIT, and others.

PostgreSQL Parser

Parses PostgreSQL wire protocol messages:

Handles message types including Q (simple query), P (parse/prepared statement), X (terminate), and startup messages.

MongoDB Parser

Parses MongoDB OP_MSG binary protocol messages:

Extracts the command name from BSON documents and classifies:

Read commands: find, aggregate, count, distinct, listCollections, listDatabases, listIndexes, dbStats, collStats, explain, getMore, serverStatus, buildInfo, connectionStatus, ping, isMaster, hello.

Write commands: insert, update, delete, findAndModify, create, drop, createIndexes, dropIndexes, dropDatabase, renameCollection.

Transaction detection: checks for startTransaction: true in the BSON document (TransactionBegin) or commitTransaction/abortTransaction commands (TransactionEnd).

Compiler Interface

The Compiler interface lets you build custom backends. Each Query dispatches to the correct compiler method via $query->compile($compiler):

This is the pattern used by utopia-php/database — it implements Compiler for each supported database engine, keeping application code decoupled from storage backends.

Contributing

All code contributions should go through a pull request and be approved by a core developer before being merged.

Integration tests require Docker:

License

This project is licensed under the MIT License. See the LICENSE file for details.