Download the PHP package utopia-php/query without Composer

Utopia Query

[!IMPORTANT] This repository is a read-only mirror of packages/query in appwrite/appwrite. Development happens there — please open issues and pull requests against appwrite/appwrite.

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 a SQL tokenizer and AST for inspecting and rewriting existing SQL, 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
  • Join Queries
  • 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
  • Executors
  • 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
  • Databases and Maintenance
  • Procedures and Triggers
  • PostgreSQL Schema Extensions
  • ClickHouse Schema
  • SQLite Schema
  • MongoDB Schema
• SQL Tokenizer and AST
  • Tokenizer
  • Parsing to an AST
  • Serializing an AST
  • Walking and Rewriting
  • Builder Round-Trip
• Wire Protocol Classifiers
  • SQL Classifier
  • MySQL Classifier
  • PostgreSQL Classifier
  • MongoDB Classifier
• 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

Most text and containment filters have a negated counterpart: notSearch(), notStartsWith(), notEndsWith(), notContains(), notBetween().

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

Ordering and Pagination

The bundled builders page on a _cursor column, so they bind the cursor row's _cursor value; a row without one throws ValidationException. Consumers that compile cursors themselves read whichever columns they page on (utopia-php/database reads the document, the audit and usage adapters their ordering columns).

Join Queries

Every join names the joined collection and the alias it is read under. An empty alias reads the join under the collection's own name, and isAliasDefaulted() reports it, so a consumer can tell the caller why that name cannot serve as an alias. The flag is derived when the query is built (the alias equals the collection), so a toArray()/parse() round trip re-derives it, and a later setAttribute() on the built query keeps it. A join serialized after setAttribute() renamed its collection keeps its alias, which parse() then reads as an explicit one. A join with neither a collection nor an alias throws ValidationException. Conditioned joins take an ON list of on() column comparisons and the filters Method::isJoinCondition() accepts (comparisons, between, null checks, contains*, startsWith/endsWith and their negations, and and/or of those); anything else in the list (limit(), select(), orderAsc(), an aggregate, another join, search(), …) throws ValidationException.

Aggregates expose their alias the same way: Query::count('*', 'total')->getAlias() is 'total'.

Logical Combinations

A Query::elemMatch($attribute, $queries) factory also exists, for matching a single array element against all of the given predicates. It serializes and parses like any other query, but no bundled builder compiles it — every dialect throws UnsupportedException. It is only useful to consumers that implement their own Compiler.

Query::relationship($attribute, $queries) is the same kind of query: it carries queries written against the collection a relationship attribute points to, for consumers that populate related documents themselves (such as utopia-php/database). It serializes and parses its child queries, contributes them to shape() and fingerprint(), and is skipped by Query::validate(), since its children name another collection's attributes. No bundled builder compiles it.

Spatial Queries

Vector Similarity

JSON Queries

Selection

Most builder clauses have a Query factory equivalent, so a whole statement can be expressed as a serializable array and passed to queries() — aggregations (count, countDistinct, sum, avg, min, max, stddev, variance, bitAnd, …), groupBy, having, joins (join, leftJoin, rightJoin, crossJoin, fullOuterJoin, naturalJoin), set operations (union, unionAll), and groupByTimeBucket (ClickHouse only). Builder-only clauses with no Query counterpart include whereRaw(), whereColumn(), window(), and the locking methods.

Raw Expressions

The SQL builders compile it in parentheses, (score > ? AND score < ?), so an OR inside a raw condition cannot escape the conditions it is ANDed with.

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). Neither exists on the MongoDB builder: they are part of the RawSql capability, which only the SQL dialects and ClickHouse implement.

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

The fragment is enclosed in parentheses, as are havingRaw() and a join's whereRaw() and onRaw(), so an OR inside it cannot escape the other conditions, including the ones filter hooks add.

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

Aggregations

A having() predicate may reference an aggregate by the alias given to the aggregate call itself (total above); the builder expands that alias back to the underlying expression, since standard SQL does not allow HAVING to reference a SELECT alias.

Distinct:

Statistical Aggregates

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

Bitwise Aggregates

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

Conditional Aggregates

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

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

String Aggregates

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

Group By Modifiers

Each modifier is its own capability interface, because dialect support does not line up:

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, MariaDB, PostgreSQL, and SQLite via the Upsert and UpsertSelect interfaces. MongoDB implements Upsert with its own document semantics (see MongoDB):

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, MariaDB, PostgreSQL, and SQLite via the Locking interface:

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

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

Transactions

Available on MySQL, MariaDB, PostgreSQL, and SQLite via the Transactions interface:

EXPLAIN

Available on every SQL builder and ClickHouse. MySQL and PostgreSQL provide extended options:

Note: the MongoDB builder inherits explain() but merely prefixes EXPLAIN to the JSON operation document, which is not a valid MongoDB command. Use the driver's own explain support instead.

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). Strings are quoted for the dialect: MySQL, MariaDB and ClickHouse escape \ as well as '; PostgreSQL and SQLite, which read \ literally, double ' only.

Executors

The library never talks to a database itself — it emits Statement objects. Attaching an executor closure is optional and lets you collapse build-and-run into one call. The closure receives the Statement and returns rows or an affected-row count:

An executor can also be attached to a single Statement after the fact. withExecutor() returns a new Statement — Statement is readonly:

Calling execute() on a Statement with no executor throws BadMethodCallException. Schema::setExecutor() accepts the same closure shape for DDL statements.

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:

SQL builders only. Hook\Filter and Hook\Join\Filter return a Condition — a raw SQL expression plus bindings — so there is nowhere to put one in a MongoDB operation document. MongoDB::addHook() rejects them with UnsupportedException rather than accepting and dropping them; a silently ignored Hook\Filter\Tenant would leave every query unscoped. Hook\Attribute and Hook\Write are dialect-neutral and work everywhere. On MongoDB, express the constraint as a Query passed to filter().

Custom filter hooks implement Hook\Filter:

A hook's condition is ANDed with the statement's other conditions exactly as returned, so a hook that returns an OR encloses it: new Condition('(deleted_at IS NULL OR restored = 1)').

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:

Both conditions are enclosed in parentheses and ANDed after the WHERE clause, like whereRaw().

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.

Bulk insert — emit the canonical INSERT INTO <table> FORMAT <name> envelope together with the serialized row payload in a single typed call. The returned FormattedInsertStatement exposes ->query (the envelope) and ->body (the format-specific payload) so the caller can ship both to ClickHouse's HTTP interface without hand-assembling either side:

Ship the result over the HTTP interface by passing $statement->query as the ?query= parameter and $statement->body as the POST body. Columns are derived from the first row's keys; pass an explicit third argument to pin the order or fill missing keys with null:

The Format enum currently supports Format::JSONEachRow and Format::TabSeparated. JSONEachRow rows are encoded with JSON_THROW_ON_ERROR | JSON_UNESCAPED_SLASHES | JSON_UNESCAPED_UNICODE (slashes and non-ASCII are preserved verbatim); TabSeparated escapes \\, \t, \n, \r and emits \N for null. An empty row iterable produces an empty body, which ClickHouse accepts as a zero-row ingest. The iterable is consumed eagerly — pass a generator if you want to defer row construction, but the serialized body is materialized in full before the statement is returned.

bulkInsert() is the recommended entry point for FORMAT-based ingest — it covers the full envelope + body contract in one typed call. The lower-level insertFormat() setter pairs with insert() for the envelope-only path (returns body = null) and is retained for callers that stream the payload separately. Both paths share the same envelope emitter, so the resulting query is identical for equivalent inputs:

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.

Because there is no position in an operation document where a SQL fragment could go, the MongoDB builder does not implement RawSql — selectRaw(), selectCast(), orderByRaw(), groupByRaw(), havingRaw(), whereRaw(), whereColumn(), selectCase(), setCase(), setRaw(), conflictSetRaw() and insertColumnExpression() do not exist on it. It also does not implement CrossJoins ($lookup always joins on a field pair) or NegatedFullTextSearch ($text has no negated form). Use the typed set(), field-update and pipeline-stage methods instead.

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

Capability is expressed in the type, not at runtime: if a dialect cannot do something, the method is not on its builder. Check with instanceof against the capability interface (e.g. if ($builder instanceof Spatial)) rather than catching an exception.

UnsupportedException is therefore reserved for what the type system cannot exclude — an unsupported value arriving through a correctly typed API. Passing Query::regex(...) to filter() on SQLite, or ColumnType::Serial to addColumn() on ClickHouse, still raises it, because filter(array $queries) and addColumn(string $name, ColumnType $type) accept any query or column type by contract.

MongoDB implements the same Upsert and FullTextSearch interfaces as the SQL dialects, so instanceof checks pass, but both emit MongoDB operation documents with document semantics rather than SQL — see MongoDB. It does not implement NegatedFullTextSearch, because $text has no negated form.

Schema Builder

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

String literals. DDL takes no bound parameters, so comments, defaults, enum values and the schema builder's other string literals are inlined with \ and ' escaped, as are the AST serializer's. MySQL reads \ as an escape, so this relies on a connection charset in which byte 0x5C cannot occur inside a multibyte character, such as utf8mb4 or latin1. Under GBK, BIG5, SJIS/CP932 or GB18030 it can: a character ending in 0x5C absorbs the escaping backslash and the value closes its literal. Do not pass untrusted text into these literals over such a connection.

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, ClickHouse, MongoDB — only PostgreSQL takes a $dimensions argument), timestamps.

Column modifiers available on every dialect: nullable(), default($value), defaultRaw($expression), primary(), unsigned().

The rest are only on the dialects that emit them, so an unsupported combination is a type error rather than something silently dropped:

Likewise serial() / bigSerial() / smallSerial() are absent from the ClickHouse table, and dropColumn() / renameColumn() are absent from the MongoDB table.

The column factories still work everywhere: point($name, $srid), id() and serial() set the SRID and auto-increment flag intrinsically, so portable schema code is unaffected — only the standalone modifiers are scoped. Table\ClickHouse::vector() and Table\MongoDB::vector() take no $dimensions argument, since neither type records one.

Known gap. unsigned() is accepted everywhere but renders nothing on PostgreSQL and SQLite, which have no unsigned integer types — you get a signed column. This one is a deliberate dialect mapping via an overridable compileUnsigned() hook, not an oversight.

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; MongoDB maps them to the BSON int type. ClickHouse has no sequence type, so Table\ClickHouse does not expose these factories at all:

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.

addForeignKey()/dropForeignKey() come from the ForeignKeys interface (MySQL, PostgreSQL). Foreign keys can also be declared inline at table-creation time with foreignKey($column), which is additionally available on SQLite:

Partitions

The partitionBy* strategies below and createPartition() are available on MySQL and PostgreSQL only. ClickHouse partitions through its own Table\ClickHouse::partitionBy(string $expression) (see ClickHouse Schema) and implements DropPartition but neither Partitioning nor CreatePartition.

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

createView() and dropView() come from the Views interface, implemented on every dialect. createOrReplaceView() comes from ReplaceView (MySQL and PostgreSQL only).

Materialized views — via the MaterializedViews interface, currently ClickHouse only. The body accepts a Builder (whose bindings ride along on the returned Statement) or a raw SQL string. $targetTable is the ClickHouse destination table for the materialized aggregate:

Security: when $body is a string it is inlined verbatim with no escaping or validation. Pass only SQL you fully control, and prefer the Builder overload whenever any part of the body is parameterized.

Databases and Maintenance

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, generated columns, CHECK constraints, stored procedures, triggers and the serial() factories are all absent from the ClickHouse table and column classes rather than throwing — check with instanceof rather than catching. ttl() is exposed only on Column\ClickHouse.

Supports the TableComments, ColumnComments, DropPartition, Views, MaterializedViews, 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. Table-level ttl() exists only on Table\ClickHouse. Column-level ttl() is on the shared Column class but only ClickHouse reads it — on other dialects it is silently ignored rather than raising.

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 inside 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 Array/Tuple types throw UnsupportedException; spatial column types compile to TEXT with no geometry semantics. Foreign keys are supported inline at table creation via foreignKey($column); the ALTER-based addForeignKey()/dropForeignKey(), stored procedures, and triggers are absent from the class rather than throwing. 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. SERIAL types map to int. Composite primary keys, dropColumn()/renameColumn(), CHECK constraints, generated columns and userType() are not exposed on the MongoDB table and column classes — a JSON Schema validator has no equivalent for any of them, so enforce those constraints in application code.

SQL Tokenizer and AST

Everything above generates SQL. This layer goes the other way: it takes existing SQL text and turns it into an inspectable, rewritable tree. (For merely deciding whether a query reads or writes, without building a tree, see Wire Protocol Classifiers.) Use it to validate columns against an allow-list, inject tenant predicates into queries you did not author, rename tables, or translate a statement from one dialect's quoting to another's.

Tokenizer

Tokenizer::tokenize() returns a flat list of Token objects, each with a type (TokenType), a value, and a position. Tokenizer::filter() strips whitespace and comment tokens, leaving only meaningful ones:

TokenType covers Keyword, Identifier, QuotedIdentifier, Integer, Float, String, Boolean, Null, Operator, LeftParen, RightParen, Comma, Semicolon, Dot, Star, Placeholder, NamedPlaceholder, NumberedPlaceholder, LineComment, BlockComment, Whitespace, and Eof.

Dialect subclasses handle dialect-specific quoting and operators — Tokenizer\MySQL, Tokenizer\MariaDB, Tokenizer\PostgreSQL, Tokenizer\SQLite, and Tokenizer\ClickHouse.

Parsing to an AST

AST\Parser is a recursive-descent parser over the token list. It produces an AST\Statement\Select:

The node hierarchy is made of readonly value objects. Expressions implement the AST\Expression marker interface: Literal, Raw, Star, Placeholder, Reference\Column, Call\Func, and Expression\{Aliased, Between, Binary, CaseWhen, Cast, Conditional, Exists, In, Subquery, Unary, Window}. Structural nodes include Reference\Table, JoinClause, OrderByItem, SubquerySource, Definition\Cte, Definition\Window, and Specification\Window.

Statement\Select is immutable; use its with() helper to derive a modified copy.

Serializing an AST

AST\Serializer renders a Select back to SQL. Dialect subclasses override identifier quoting:

Subclasses are provided for MySQL, MariaDB, PostgreSQL, SQLite, and ClickHouse. serializeExpression() renders a single expression node in isolation.

Walking and Rewriting

AST\Walker applies a Visitor to every node and returns a new, possibly transformed Select. The Visitor interface has three methods — visitExpression(), visitTableReference(), and visitSelect() — each returning the original node to keep it or a replacement to swap it.

Three visitors ship with the library:

ColumnValidator also rejects SELECT * unless constructed with allowStar: true.

Builder Round-Trip

Any builder can hand out its AST and be reconstructed from one, so a fluent query can be rewritten through the visitor pipeline and then rebuilt:

The AST holds the query as written. Filter and join-filter hooks are applied when a builder compiles, so they are not in it: Builder::fromAst() returns a builder without them, and an AST serialized directly is unscoped. Add the hooks to the rebuilt builder, or AND the scope in with FilterInjector.

Wire Protocol Classifiers

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

Not a parser. A classifier reads a message's leading keyword (or, for document protocols, its first command name) and looks it up — it never builds a structure. To parse SQL text into a syntax tree you can inspect and rewrite, use AST\Parser instead.

SQL Classifier

The abstract Classifier\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, CREATE, DROP, ALTER, TRUNCATE, GRANT, REVOKE, LOCK, CALL, DO.

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

Anything else — including RENAME, REPLACE, LOAD, MERGE, and EXECUTE — falls through to Type::Unknown.

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

MySQL Classifier

Parses MySQL wire protocol binary packets:

Recognizes COM_QUERY (0x03, classified via its SQL text), COM_STMT_PREPARE (0x16), COM_STMT_EXECUTE (0x17), COM_STMT_SEND_LONG_DATA (0x18), COM_STMT_CLOSE (0x19), and COM_STMT_RESET (0x1A). The prepared-statement commands are routed to the primary.

PostgreSQL Classifier

Parses PostgreSQL wire protocol messages:

Handles message types Q (simple query, classified via its SQL text), P (parse), B (bind), and E (execute). Other message types, including terminate and startup messages, return Type::Unknown.

MongoDB Classifier

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

Development happens in packages/query in appwrite/appwrite. From the root of that repository:

Inside the package directory:

E2E tests require Docker. The compose file brings up MySQL, MariaDB, PostgreSQL (pgvector), ClickHouse, and MongoDB; SQLite runs in-memory with no container. Connection details are fixed in tests/E2E/IntegrationTestCase.php, so the containers must be up or the suite fails rather than skips:

License

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