PHPackages                             utopia-php/query - PHPackages - PHPackages  [Skip to content](#main-content)[PHPackages](/)[Directory](/)[Categories](/categories)[Trending](/trending)[Leaderboard](/leaderboard)[Changelog](/changelog)[Analyze](/analyze)[Collections](/collections)[Log in](/login)[Sign up](/register)

1. [Directory](/)
2. /
3. [Framework](/categories/framework)
4. /
5. utopia-php/query

ActiveLibrary[Framework](/categories/framework)

utopia-php/query
================

A simple library providing a query abstraction for filtering, ordering, and pagination

0.3.2(1mo ago)043.6k[1 PRs](https://github.com/utopia-php/query/pulls)2MITPHPPHP >=8.4CI passing

Since Mar 3Pushed 1mo agoCompare

[ Source](https://github.com/utopia-php/query)[ Packagist](https://packagist.org/packages/utopia-php/query)[ RSS](/packages/utopia-php-query/feed)WikiDiscussions main Synced today

READMEChangelog (5)Dependencies (9)Versions (15)Used By (2)

Utopia Query
============

[](#utopia-query)

[![Tests](https://github.com/utopia-php/query/actions/workflows/tests.yml/badge.svg)](https://github.com/utopia-php/query/actions/workflows/tests.yml)[![Integration Tests](https://github.com/utopia-php/query/actions/workflows/integration.yml/badge.svg)](https://github.com/utopia-php/query/actions/workflows/integration.yml)[![Linter](https://github.com/utopia-php/query/actions/workflows/linter.yml/badge.svg)](https://github.com/utopia-php/query/actions/workflows/linter.yml)[![Static Analysis](https://github.com/utopia-php/query/actions/workflows/static-analysis.yml/badge.svg)](https://github.com/utopia-php/query/actions/workflows/static-analysis.yml)

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
------------

[](#installation)

```
composer require utopia-php/query
```

**Requires PHP 8.4+**

Table of Contents
-----------------

[](#table-of-contents)

- [Query Object](#query-object)
    - [Filters](#filters)
    - [Ordering and Pagination](#ordering-and-pagination)
    - [Logical Combinations](#logical-combinations)
    - [Spatial Queries](#spatial-queries)
    - [Vector Similarity](#vector-similarity)
    - [JSON Queries](#json-queries)
    - [Selection](#selection)
    - [Raw Expressions](#raw-expressions)
    - [Serialization](#serialization)
    - [Helpers](#helpers)
- [Query Builder](#query-builder)
    - [Basic Usage](#basic-usage)
    - [Raw and Column Predicates](#raw-and-column-predicates)
    - [Aggregations](#aggregations)
    - [Statistical Aggregates](#statistical-aggregates)
    - [Bitwise Aggregates](#bitwise-aggregates)
    - [Conditional Aggregates](#conditional-aggregates)
    - [String Aggregates](#string-aggregates)
    - [Group By Modifiers](#group-by-modifiers)
    - [Sequences](#sequences)
    - [Joins](#joins)
    - [Unions and Set Operations](#unions-and-set-operations)
    - [CTEs (Common Table Expressions)](#ctes-common-table-expressions)
    - [Window Functions](#window-functions)
    - [CASE Expressions](#case-expressions)
    - [Inserts](#inserts)
    - [Updates](#updates)
    - [Deletes](#deletes)
    - [Upsert](#upsert)
    - [Locking](#locking)
    - [Transactions](#transactions)
    - [EXPLAIN](#explain)
    - [Conditional Building](#conditional-building)
    - [Builder Cloning and Callbacks](#builder-cloning-and-callbacks)
    - [Debugging](#debugging)
    - [Hooks](#hooks)
- [Dialect-Specific Features](#dialect-specific-features)
    - [MySQL](#mysql)
    - [MariaDB](#mariadb)
    - [PostgreSQL](#postgresql)
    - [SQLite](#sqlite)
    - [ClickHouse](#clickhouse)
    - [MongoDB](#mongodb)
    - [Feature Matrix](#feature-matrix)
- [Schema Builder](#schema-builder)
    - [Creating Tables](#creating-tables)
    - [Altering Tables](#altering-tables)
    - [CHECK Constraints](#check-constraints)
    - [Generated Columns](#generated-columns)
    - [Composite Primary Keys](#composite-primary-keys)
    - [Indexes](#indexes)
    - [Foreign Keys](#foreign-keys)
    - [Partitions](#partitions)
    - [Comments](#comments)
    - [Views](#views)
    - [Procedures and Triggers](#procedures-and-triggers)
    - [PostgreSQL Schema Extensions](#postgresql-schema-extensions)
    - [ClickHouse Schema](#clickhouse-schema)
    - [SQLite Schema](#sqlite-schema)
    - [MongoDB Schema](#mongodb-schema)
- [Wire Protocol Parsers](#wire-protocol-parsers)
    - [SQL Parser](#sql-parser)
    - [MySQL Parser](#mysql-parser)
    - [PostgreSQL Parser](#postgresql-parser)
    - [MongoDB Parser](#mongodb-parser)
- [Compiler Interface](#compiler-interface)
- [Contributing](#contributing)
- [License](#license)

Query Object
------------

[](#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.

```
use Utopia\Query\Query;
```

### Filters

[](#filters)

```
// Equality
Query::equal('status', ['active', 'pending']);
Query::notEqual('role', 'guest');

// Comparison
Query::greaterThan('age', 18);
Query::greaterThanEqual('score', 90);
Query::lessThan('price', 100);
Query::lessThanEqual('quantity', 0);

// Range
Query::between('createdAt', '2024-01-01', '2024-12-31');
Query::notBetween('priority', 1, 3);

// String matching
Query::startsWith('email', 'admin');
Query::endsWith('filename', '.pdf');
Query::search('content', 'hello world');
Query::regex('slug', '^[a-z0-9-]+$');

// Substring matching (LIKE '%value%')
Query::containsString('title', ['urgent', 'important']);

// Array / containment (for array or relationship attributes)
Query::containsAny('categories', ['news', 'blog']);
Query::containsAll('permissions', ['read', 'write']);
Query::notContains('labels', ['deprecated']);

// Null checks
Query::isNull('deletedAt');
Query::isNotNull('verifiedAt');

// Existence (compiles to IS NOT NULL / IS NULL)
Query::exists(['name', 'email']);
Query::notExists('legacyField');

// Date helpers
Query::createdAfter('2024-01-01');
Query::updatedBetween('2024-01-01', '2024-06-30');
```

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

### Ordering and Pagination

[](#ordering-and-pagination)

```
Query::orderAsc('createdAt');
Query::orderDesc('score');
Query::orderRandom();

Query::limit(25);
Query::offset(50);

Query::cursorAfter('doc_abc123');
Query::cursorBefore('doc_xyz789');
```

### Logical Combinations

[](#logical-combinations)

```
Query::and([
    Query::greaterThan('age', 18),
    Query::equal('status', ['active']),
]);

Query::or([
    Query::equal('role', ['admin']),
    Query::equal('role', ['moderator']),
]);
```

### Spatial Queries

[](#spatial-queries)

```
Query::distanceLessThan('location', [40.7128, -74.0060], 5000, meters: true);
Query::distanceGreaterThan('location', [51.5074, -0.1278], 100);

Query::intersects('area', [[0, 0], [1, 0], [1, 1], [0, 1], [0, 0]]);
Query::overlaps('region', [[0, 0], [2, 0], [2, 2], [0, 2], [0, 0]]);
Query::touches('boundary', [[0, 0], [1, 1]]);
Query::crosses('path', [[0, 0], [5, 5]]);
Query::covers('zone', [1.0, 2.0]);
Query::spatialEquals('geom', [3.0, 4.0]);
```

### Vector Similarity

[](#vector-similarity)

```
Query::vectorDot('embedding', [0.1, 0.2, 0.3, 0.4]);
Query::vectorCosine('embedding', [0.1, 0.2, 0.3, 0.4]);
Query::vectorEuclidean('embedding', [0.1, 0.2, 0.3, 0.4]);
```

### JSON Queries

[](#json-queries)

```
Query::jsonContains('tags', 'php');
Query::jsonNotContains('tags', 'legacy');
Query::jsonOverlaps('categories', ['news', 'blog']);
Query::jsonPath('metadata', 'address.city', '=', 'London');
```

### Selection

[](#selection)

```
Query::select(['name', 'email', 'createdAt']);
```

### Raw Expressions

[](#raw-expressions)

```
Query::raw('score > ? AND score < ?', [10, 100]);
```

### Serialization

[](#serialization)

Queries serialize to JSON and can be parsed back:

```
$query = Query::equal('status', ['active']);

// Serialize
$json = $query->toString();
// '{"method":"equal","attribute":"status","values":["active"]}'

// Parse back
$parsed = Query::parse($json);

// Parse multiple
$queries = Query::parseQueries([$json1, $json2]);
```

### Helpers

[](#helpers)

```
// Group queries by type — returns a ParsedQuery value object
$parsed = Query::groupByType($queries);
// $parsed->filters, $parsed->selections, $parsed->aggregations, $parsed->groupBy,
// $parsed->having, $parsed->joins, $parsed->unions, $parsed->limit, $parsed->offset,
// $parsed->cursor, $parsed->cursorDirection, $parsed->distinct

// Filter by method type
$cursors = Query::getByType($queries, [Method::CursorAfter, Method::CursorBefore]);

// Merge (later limit/offset/cursor overrides earlier)
$merged = Query::merge($defaultQueries, $userQueries);

// Diff — queries in A not in B
$unique = Query::diff($queriesA, $queriesB);

// Validate attributes against an allow-list
$errors = Query::validate($queries, ['name', 'age', 'status']);

// Page helper — returns [limit, offset] queries
[$limit, $offset] = Query::page(3, 10);
```

Query Builder
-------------

[](#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

[](#basic-usage)

```
use Utopia\Query\Builder\MySQL as Builder;
use Utopia\Query\Query;

$result = (new Builder())
    ->select(['name', 'email'])
    ->from('users')
    ->filter([
        Query::equal('status', ['active']),
        Query::greaterThan('age', 18),
    ])
    ->sortAsc('name')
    ->limit(25)
    ->offset(0)
    ->build();

$result->query;    // SELECT `name`, `email` FROM `users` WHERE `status` IN (?) AND `age` > ? ORDER BY `name` ASC LIMIT ? OFFSET ?
$result->bindings; // ['active', 18, 25, 0]
$result->readOnly; // true
```

**Batch mode** — pass all queries at once:

```
$result = (new Builder())
    ->from('users')
    ->queries([
        Query::select(['name', 'email']),
        Query::equal('status', ['active']),
        Query::orderAsc('name'),
        Query::limit(25),
    ])
    ->build();
```

**Using with PDO:**

```
$result = (new Builder())
    ->from('users')
    ->filter([Query::equal('status', ['active'])])
    ->limit(10)
    ->build();

$stmt = $pdo->prepare($result->query);
$stmt->execute($result->bindings);
$rows = $stmt->fetchAll();
```

### Raw and Column Predicates

[](#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:

```
use Utopia\Query\Builder\MySQL as Builder;

$result = (new Builder())
    ->from('users')
    ->whereRaw('LENGTH(`bio`) > ?', [100])
    ->build();

// SELECT * FROM `users` WHERE LENGTH(`bio`) > ?

```

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

```
// Correlated subquery for a lateral join
$topOrder = (new Builder())
    ->from('orders')
    ->select(['product', 'amount'])
    ->whereColumn('orders.user_id', '=', 'u.id')
    ->sortDesc('amount')
    ->limit(1);

$result = (new Builder())
    ->from('users', 'u')
    ->joinLateral($topOrder, 'top_order')
    ->build();
```

### Aggregations

[](#aggregations)

```
$result = (new Builder())
    ->from('orders')
    ->count('*', 'total')
    ->sum('price', 'total_price')
    ->select(['status'])
    ->groupBy(['status'])
    ->having([Query::greaterThan('total', 5)])
    ->build();

// SELECT COUNT(*) AS `total`, SUM(`price`) AS `total_price`, `status`
//   FROM `orders` GROUP BY `status` HAVING `total` > ?

```

**Distinct:**

```
$result = (new Builder())
    ->from('users')
    ->distinct()
    ->select(['country'])
    ->build();

// SELECT DISTINCT `country` FROM `users`
```

### Statistical Aggregates

[](#statistical-aggregates)

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

```
use Utopia\Query\Builder\PostgreSQL as Builder;

$result = (new Builder())
    ->from('measurements')
    ->stddev('value', 'std_dev')
    ->stddevPop('value', 'pop_std_dev')
    ->stddevSamp('value', 'samp_std_dev')
    ->variance('value', 'var')
    ->varPop('value', 'pop_var')
    ->varSamp('value', 'samp_var')
    ->build();
```

### Bitwise Aggregates

[](#bitwise-aggregates)

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

```
$result = (new Builder())
    ->from('permissions')
    ->bitAnd('flags', 'combined_and')
    ->bitOr('flags', 'combined_or')
    ->bitXor('flags', 'combined_xor')
    ->build();
```

### Conditional Aggregates

[](#conditional-aggregates)

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

```
use Utopia\Query\Builder\PostgreSQL as Builder;

$result = (new Builder())
    ->from('orders')
    ->countWhen('status = ?', 'active_count', 'active')
    ->sumWhen('amount', 'status = ?', 'active_total', 'active')
    ->build();

// PostgreSQL: COUNT(*) FILTER (WHERE status = ?) AS "active_count", SUM("amount") FILTER (WHERE status = ?) AS "active_total"
// MySQL:      COUNT(CASE WHEN status = ? THEN 1 END) AS `active_count`, SUM(CASE WHEN status = ? THEN `amount` END) AS `active_total`
// ClickHouse: countIf(status = ?) AS `active_count`, sumIf(`amount`, status = ?) AS `active_total`
```

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

### String Aggregates

[](#string-aggregates)

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

```
use Utopia\Query\Builder\MySQL as Builder;

// Concatenate values into a string
$result = (new Builder())
    ->from('tags')
    ->select(['post_id'])
    ->groupConcat('name', ', ', 'tag_list', orderBy: ['name'])
    ->groupBy(['post_id'])
    ->build();

// MySQL:      GROUP_CONCAT(`name` ORDER BY `name` ASC SEPARATOR ', ')
// PostgreSQL: STRING_AGG("name", ', ' ORDER BY "name" ASC)
// ClickHouse: arrayStringConcat(groupArray(`name`), ', ')

// JSON array aggregation
$result = (new Builder())
    ->from('items')
    ->jsonArrayAgg('name', 'names_json')
    ->build();

// JSON object aggregation from key/value pairs
$result = (new Builder())
    ->from('settings')
    ->jsonObjectAgg('key', 'value', 'settings_json')
    ->build();
```

### Group By Modifiers

[](#group-by-modifiers)

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

```
use Utopia\Query\Builder\MySQL as Builder;

// WITH ROLLUP — adds subtotal and grand total rows
$result = (new Builder())
    ->from('sales')
    ->select(['region', 'product'])
    ->sum('amount', 'total')
    ->groupBy(['region', 'product'])
    ->withRollup()
    ->build();

// WITH CUBE — adds subtotals for all dimension combinations (MySQL 8.0.1+, PostgreSQL, ClickHouse)
$result = (new Builder())
    ->from('sales')
    ->select(['region', 'product'])
    ->sum('amount', 'total')
    ->groupBy(['region', 'product'])
    ->withCube()
    ->build();

// WITH TOTALS (ClickHouse) — adds a totals row
use Utopia\Query\Builder\ClickHouse as ChBuilder;

$result = (new ChBuilder())
    ->from('events')
    ->select(['event_type'])
    ->count('*', 'cnt')
    ->groupBy(['event_type'])
    ->withTotals()
    ->build();
```

### Sequences

[](#sequences)

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

```
use Utopia\Query\Builder\PostgreSQL as Builder;

// Advance the sequence and return the next value
$result = (new Builder())
    ->nextVal('order_seq', 'next_id')
    ->build();

// PostgreSQL: SELECT nextval('order_seq') AS "next_id"
// MariaDB:    SELECT NEXTVAL(`order_seq`) AS `next_id`

// Return the session-local current value
$result = (new Builder())
    ->currVal('order_seq', 'current_id')
    ->build();
```

### Joins

[](#joins)

```
$result = (new Builder())
    ->from('users')
    ->join('orders', 'users.id', 'orders.user_id')
    ->leftJoin('profiles', 'users.id', 'profiles.user_id')
    ->rightJoin('notes', 'users.id', 'notes.user_id')
    ->crossJoin('colors')
    ->naturalJoin('defaults')
    ->build();

// SELECT * FROM `users`
//   JOIN `orders` ON `users`.`id` = `orders`.`user_id`
//   LEFT JOIN `profiles` ON `users`.`id` = `profiles`.`user_id`
//   RIGHT JOIN `notes` ON `users`.`id` = `notes`.`user_id`
//   CROSS JOIN `colors`
//   NATURAL JOIN `defaults`
```

**Complex join conditions** with `joinWhere()`:

```
use Utopia\Query\Builder\JoinType;

$result = (new Builder())
    ->from('users')
    ->joinWhere('orders', function ($join) {
        $join->on('users.id', 'orders.user_id')
            ->where('orders.status', '=', 'active');
    }, JoinType::Left)
    ->build();
```

**Full outer joins** (PostgreSQL, ClickHouse):

```
use Utopia\Query\Builder\PostgreSQL as Builder;

$result = (new Builder())
    ->from('left_table')
    ->fullOuterJoin('right_table', 'left_table.id', 'right_table.id')
    ->build();

// SELECT * FROM "left_table" FULL OUTER JOIN "right_table" ON "left_table"."id" = "right_table"."id"
```

**Lateral joins** (MySQL, MariaDB, PostgreSQL):

```
$sub = (new Builder())
    ->from('orders')
    ->whereColumn('orders.user_id', '=', 'users.id')
    ->limit(3);

$result = (new Builder())
    ->from('users')
    ->joinLateral($sub, 'recent_orders')
    ->build();
```

### Unions and Set Operations

[](#unions-and-set-operations)

```
$admins = (new Builder())->from('admins')->filter([Query::equal('role', ['admin'])]);

$result = (new Builder())
    ->from('users')
    ->filter([Query::equal('status', ['active'])])
    ->union($admins)
    ->build();

// SELECT * FROM `users` WHERE `status` IN (?)
//   UNION SELECT * FROM `admins` WHERE `role` IN (?)
```

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

### CTEs (Common Table Expressions)

[](#ctes-common-table-expressions)

```
$activeUsers = (new Builder())->from('users')->filter([Query::equal('status', ['active'])]);

$result = (new Builder())
    ->with('active_users', $activeUsers)
    ->from('active_users')
    ->select(['name'])
    ->build();

// WITH `active_users` AS (SELECT * FROM `users` WHERE `status` IN (?))
//   SELECT `name` FROM `active_users`
```

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

```
$seed = (new Builder())->from('employees')->filter([Query::isNull('manager_id')]);
$step = (new Builder())->from('employees')->join('org', 'employees.manager_id', 'org.id');

$result = (new Builder())
    ->withRecursiveSeedStep('org', $seed, $step)
    ->from('org')
    ->build();
```

### Window Functions

[](#window-functions)

```
$result = (new Builder())
    ->from('sales')
    ->select(['employee', 'amount'])
    ->selectWindow('ROW_NUMBER()', 'row_num', partitionBy: ['department'], orderBy: ['amount'])
    ->selectWindow('SUM(amount)', 'running_total', partitionBy: ['department'], orderBy: ['date'])
    ->build();

// SELECT `employee`, `amount`,
//   ROW_NUMBER() OVER (PARTITION BY `department` ORDER BY `amount` ASC) AS `row_num`,
//   SUM(amount) OVER (PARTITION BY `department` ORDER BY `date` ASC) AS `running_total`
//   FROM `sales`
```

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

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

```
$result = (new Builder())
    ->from('sales')
    ->select(['employee', 'amount'])
    ->window('w', partitionBy: ['department'], orderBy: ['date'])
    ->selectWindow('ROW_NUMBER()', 'row_num', windowName: 'w')
    ->selectWindow('SUM(amount)', 'running_total', windowName: 'w')
    ->build();

// SELECT `employee`, `amount`,
//   ROW_NUMBER() OVER `w` AS `row_num`,
//   SUM(amount) OVER `w` AS `running_total`
//   FROM `sales`
//   WINDOW `w` AS (PARTITION BY `department` ORDER BY `date` ASC)
```

### CASE 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:

```
use Utopia\Query\Builder\Case\Expression as CaseExpression;
use Utopia\Query\Builder\Case\Operator;

$case = (new CaseExpression())
    ->when('amount', Operator::GreaterThan, 1000, 'high')
    ->when('amount', Operator::GreaterThan, 100, 'medium')
    ->else('low')
    ->alias('priority');

$result = (new Builder())
    ->from('orders')
    ->select(['id'])
    ->selectCase($case)
    ->build();

// SELECT `id`, CASE WHEN `amount` > ? THEN ? WHEN `amount` > ? THEN ? ELSE ? END AS `priority`
//   FROM `orders`
```

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

[](#inserts)

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

```
// Single row
$result = (new Builder())
    ->into('users')
    ->set(['name' => 'Alice', 'email' => 'alice@example.com'])
    ->insert();

// Batch insert — one set() call per row
$result = (new Builder())
    ->into('users')
    ->set(['name' => 'Alice', 'email' => 'alice@example.com'])
    ->set(['name' => 'Bob', 'email' => 'bob@example.com'])
    ->insert();

// INSERT ... SELECT
$source = (new Builder())->from('archived_users')->filter([Query::equal('status', ['active'])]);

$result = (new Builder())
    ->into('users')
    ->fromSelect(['name', 'email'], $source)
    ->insertSelect();
```

### Updates

[](#updates)

```
$result = (new Builder())
    ->from('users')
    ->set(['status' => 'inactive'])
    ->setRaw('updated_at', 'NOW()')
    ->filter([Query::equal('id', [42])])
    ->update();

// UPDATE `users` SET `status` = ?, `updated_at` = NOW() WHERE `id` IN (?)
```

### Deletes

[](#deletes)

```
$result = (new Builder())
    ->from('users')
    ->filter([Query::equal('status', ['deleted'])])
    ->delete();

// DELETE FROM `users` WHERE `status` IN (?)
```

### Upsert

[](#upsert)

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

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

```
// MySQL — ON DUPLICATE KEY UPDATE
$result = (new Builder())
    ->into('counters')
    ->set(['key' => 'visits', 'value' => 1])
    ->onConflict(['key'], ['value'])
    ->upsert();

// PostgreSQL — ON CONFLICT (...) DO UPDATE SET
use Utopia\Query\Builder\PostgreSQL as PgBuilder;

$result = (new PgBuilder())
    ->into('counters')
    ->set(['key' => 'visits', 'value' => 1])
    ->onConflict(['key'], ['value'])
    ->upsert();
```

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

```
$result = (new Builder())
    ->into('counters')
    ->set(['key' => 'visits', 'value' => 1])
    ->onConflict(['key'], [])
    ->insertOrIgnore();

// MySQL:      INSERT IGNORE INTO `counters` ...
// PostgreSQL: INSERT INTO "counters" ... ON CONFLICT ("key") DO NOTHING
// SQLite:     INSERT OR IGNORE INTO `counters` ...
```

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

```
$source = (new Builder())->from('staging')->select(['key', 'value']);

$result = (new Builder())
    ->into('counters')
    ->fromSelect(['key', 'value'], $source)
    ->onConflict(['key'], ['value'])
    ->upsertSelect();
```

### Locking

[](#locking)

Available on MySQL, PostgreSQL, and SQLite builders:

```
$result = (new Builder())
    ->from('accounts')
    ->filter([Query::equal('id', [1])])
    ->forUpdate()
    ->build();

// SELECT * FROM `accounts` WHERE `id` IN (?) FOR UPDATE
```

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

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

### Transactions

[](#transactions)

Available on MySQL, PostgreSQL, and SQLite builders:

```
$builder = new Builder();

$builder->begin();            // BEGIN
$builder->savepoint('sp1');   // SAVEPOINT `sp1`
$builder->rollbackToSavepoint('sp1');
$builder->commit();           // COMMIT
$builder->rollback();         // ROLLBACK
```

### EXPLAIN

[](#explain)

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

```
use Utopia\Query\Builder\MySQL as Builder;

// Basic explain
$result = (new Builder())
    ->from('users')
    ->filter([Query::equal('status', ['active'])])
    ->explain();

// MySQL — with format
$result = (new Builder())
    ->from('users')
    ->explain(analyze: true, format: 'JSON');

// PostgreSQL — with analyze, verbose, buffers, format
use Utopia\Query\Builder\PostgreSQL as PgBuilder;

$result = (new PgBuilder())
    ->from('users')
    ->explain(analyze: true, verbose: true, buffers: true, format: 'JSON');
```

### Conditional Building

[](#conditional-building)

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

```
$result = (new Builder())
    ->from('users')
    ->when($filterActive, fn (Builder $b) => $b->filter([Query::equal('status', ['active'])]))
    ->build();
```

### Builder Cloning and Callbacks

[](#builder-cloning-and-callbacks)

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

```
$base = (new Builder())->from('users')->filter([Query::equal('status', ['active'])]);
$withLimit = $base->clone()->limit(10);
$withSort = $base->clone()->sortAsc('name');
```

**Build callbacks** run before or after building:

```
use Utopia\Query\Builder\Statement;

$result = (new Builder())
    ->from('users')
    ->beforeBuild(fn (Builder $b) => $b->filter([Query::isNotNull('email')]))
    ->afterBuild(fn (Statement $s) => new Statement("/* traced */ {$s->query}", $s->bindings, $s->readOnly))
    ->build();
```

### Debugging

[](#debugging)

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

```
$sql = (new Builder())
    ->from('users')
    ->filter([Query::equal('status', ['active'])])
    ->limit(10)
    ->toRawSql();

// SELECT * FROM `users` WHERE `status` IN ('active') LIMIT 10
```

### Hooks

[](#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:

```
use Utopia\Query\Hook\Attribute\Map;

$result = (new Builder())
    ->from('users')
    ->addHook(new Map([
        '$id' => '_uid',
        '$createdAt' => '_createdAt',
    ]))
    ->filter([Query::equal('$id', ['abc'])])
    ->build();

// SELECT * FROM `users` WHERE `_uid` IN (?)
```

**Filter hooks** inject conditions into every query:

```
use Utopia\Query\Hook\Filter\Tenant;

$result = (new Builder())
    ->from('users')
    ->addHook(new Tenant(['tenant_abc']))
    ->filter([Query::equal('status', ['active'])])
    ->build();

// SELECT * FROM `users`
//   WHERE `status` IN (?) AND `tenant_id` IN (?)
```

**Custom filter hooks** implement `Hook\Filter`:

```
use Utopia\Query\Builder\Condition;
use Utopia\Query\Hook\Filter;

class SoftDeleteHook implements Filter
{
    public function filter(string $table): Condition
    {
        return new Condition('deleted_at IS NULL');
    }
}
```

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

```
use Utopia\Query\Builder\Condition;
use Utopia\Query\Builder\JoinType;
use Utopia\Query\Hook\Join\Filter as JoinFilter;
use Utopia\Query\Hook\Join\Placement;

class ActiveJoinFilter implements JoinFilter
{
    public function filterJoin(string $table, JoinType $joinType): ?Condition
    {
        return new Condition(
            'active = ?',
            [1],
            match ($joinType) {
                JoinType::Left, JoinType::Right => Placement::On,
                default => Placement::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:

```
use Utopia\Query\Hook\Write;

class AuditHook implements Write
{
    public function decorateRow(array $row, array $metadata = []): array { /* ... */ }
    public function afterCreate(string $table, array $metadata, mixed $context): void { /* ... */ }
    public function afterUpdate(string $table, array $metadata, mixed $context): void { /* ... */ }
    public function afterBatchUpdate(string $table, array $updateData, array $metadata, mixed $context): void { /* ... */ }
    public function afterDelete(string $table, array $ids, mixed $context): void { /* ... */ }
}
```

Dialect-Specific Features
-------------------------

[](#dialect-specific-features)

### MySQL

[](#mysql)

```
use Utopia\Query\Builder\MySQL as Builder;
```

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

```
$result = (new Builder())
    ->from('stores')
    ->filterDistance('location', [40.7128, -74.0060], 'from('users')
    ->deleteUsing('old_users', '"users"."id" = "old_users"."id"')
    ->delete();
```

**Sequences** — native sequence support via `nextVal()` / `currVal()` (see [Sequences](#sequences)).

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

**Table sampling:**

```
$result = (new Builder())
    ->from('large_table')
    ->tablesample(10.0, 'BERNOULLI')
    ->count('*', 'approx')
    ->build();

// SELECT COUNT(*) AS "approx" FROM "large_table" TABLESAMPLE BERNOULLI (10)
```

### SQLite

[](#sqlite)

```
use Utopia\Query\Builder\SQLite as Builder;
```

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

[](#clickhouse)

```
use Utopia\Query\Builder\ClickHouse as Builder;
```

**FINAL** — force merging of data parts:

```
$result = (new Builder())
    ->from('events')
    ->final()
    ->build();

// SELECT * FROM `events` FINAL
```

**SAMPLE** — approximate query processing:

```
$result = (new Builder())
    ->from('events')
    ->sample(0.1)
    ->count('*', 'approx_total')
    ->build();

// SELECT COUNT(*) AS `approx_total` FROM `events` SAMPLE 0.1
```

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

```
$result = (new Builder())
    ->from('events')
    ->prewhere([Query::equal('event_type', ['click'])])
    ->filter([Query::greaterThan('count', 5)])
    ->build();

// SELECT * FROM `events` PREWHERE `event_type` IN (?) WHERE `count` > ?

```

**SETTINGS:**

```
$result = (new Builder())
    ->from('events')
    ->settings(['max_threads' => '4', 'optimize_read_in_order' => '1'])
    ->build();

// SELECT * FROM `events` SETTINGS max_threads=4, optimize_read_in_order=1
```

**LIMIT BY** — limit rows per group:

```
$result = (new Builder())
    ->from('events')
    ->select(['user_id', 'event_type'])
    ->limitBy(3, ['user_id'])
    ->build();

// SELECT `user_id`, `event_type` FROM `events` LIMIT 3 BY `user_id`
```

**ARRAY JOIN** — unnest array columns into rows:

```
$result = (new Builder())
    ->from('events')
    ->select(['name'])
    ->arrayJoin('tags', 'tag')
    ->build();

// SELECT `name`, `tags` AS `tag` FROM `events` ARRAY JOIN `tags` AS `tag`

// LEFT variant preserves rows with empty arrays
$result = (new Builder())
    ->from('events')
    ->leftArrayJoin('tags', 'tag')
    ->build();
```

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

```
use Utopia\Query\Builder\ClickHouse\AsofOperator;

// For each trade, find the most recent quote with the same symbol
$result = (new Builder())
    ->from('trades', 't')
    ->select(['t.symbol', 't.ts', 't.price', 'q.bid'])
    ->asofJoin(
        table: 'quotes',
        equiPairs: ['t.symbol' => 'q.symbol'],
        leftInequality: 't.ts',
        operator: AsofOperator::GreaterThanEqual,
        rightInequality: 'q.ts',
        alias: 'q',
    )
    ->sortAsc('t.ts')
    ->build();

// SELECT `t`.`symbol`, `t`.`ts`, `t`.`price`, `q`.`bid` FROM `trades` AS `t`
//   ASOF JOIN `quotes` AS `q`
//     ON `t`.`symbol` = `q`.`symbol` AND `t`.`ts` >= `q`.`ts`
//   ORDER BY `t`.`ts` ASC
```

`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:

```
$result = (new Builder())
    ->from('daily_stats')
    ->select(['date', 'count'])
    ->orderWithFill('date', 'ASC', from: '2024-01-01', to: '2024-01-31', step: 1)
    ->build();

// SELECT `date`, `count` FROM `daily_stats` ORDER BY `date` ASC WITH FILL FROM '2024-01-01' TO '2024-01-31' STEP 1
```

**Approximate aggregates** — ClickHouse-native probabilistic functions:

```
$result = (new Builder())
    ->from('events')
    ->quantile(0.95, 'response_time', 'p95')
    ->quantiles([0.25, 0.5, 0.75, 0.95], 'response_time', 'quartiles')
    ->quantileExact(0.99, 'response_time', 'p99')
    ->median('response_time', 'med')
    ->uniq('user_id', 'approx_users')
    ->uniqExact('user_id', 'exact_users')
    ->uniqCombined('user_id', 'combined_users')
    ->build();

// quantiles(0.25, 0.5, 0.75, 0.95)(`response_time`) AS `quartiles`
```

`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:

```
// startsWith/endsWith → native functions
Query::startsWith('name', 'Al');                 // startsWith(`name`, ?)
Query::endsWith('file', '.pdf');                 // endsWith(`file`, ?)

// containsString → position()
Query::containsString('tags', ['php']);          // position(`tags`, ?) > 0
```

**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`:

```
$result = (new Builder())
    ->from('events')
    ->selectRaw('toStartOfHour(`time`) AS `bucket`')
    ->count('*', 'cnt')
    ->groupByTimeBucket('time', '1h')
    ->orderByRaw('`bucket` ASC')
    ->build();

// SELECT COUNT(*) AS `cnt`, toStartOfHour(`time`) AS `bucket`
// FROM `events`
// GROUP BY toStartOfHour(`time`)
// ORDER BY `bucket` ASC
```

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:

```
$result = (new Builder())
    ->useNamedBindings()
    ->withParamTypes([
        'time' => 'DateTime64(3)',
        'tenant' => 'String',
        'value' => 'Int64',
    ])
    ->from('events')
    ->filter([
        Query::greaterThan('time', '2024-01-01 00:00:00'),
        Query::equal('tenant', ['acme']),
        Query::lessThanEqual('value', 100),
    ])
    ->build();

// SELECT * FROM `events`
// WHERE `time` > {param0:DateTime64(3)}
// AND `tenant` IN ({param1:String})
// AND `value` namedBindings;
// ['param0' => '2024-01-01 00:00:00', 'param1' => 'acme', 'param2' => 100]
```

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:

```
$result = (new Builder())
    ->from('events')
    ->set(['status' => 'archived'])
    ->filter([Query::lessThan('created_at', '2024-01-01')])
    ->update();

// ALTER TABLE `events` UPDATE `status` = ? WHERE `created_at` < ?

```

**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.

```
// Lightweight (default) — pair with `lightweight_deletes_sync = 0` for async
$result = (new Builder())
    ->from('audit_log')
    ->settings(['lightweight_deletes_sync' => '0'])
    ->filter([Query::lessThan('time', '2024-01-01 00:00:00')])
    ->delete();

// DELETE FROM `audit_log` WHERE `time` < ? SETTINGS lightweight_deletes_sync=0

// Mutation — opt in. Pair with `mutations_sync = 0` for async
$result = (new Builder())
    ->from('audit_log')
    ->deleteMode(Builder::DELETE_MODE_MUTATION)
    ->settings(['mutations_sync' => '0'])
    ->filter([Query::lessThan('time', '2024-01-01 00:00:00')])
    ->delete();

// ALTER TABLE `audit_log` DELETE WHERE `time` < ? SETTINGS mutations_sync=0
```

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

[](#mongodb)

```
use Utopia\Query\Builder\MongoDB as Builder;
```

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:**

```
$result = (new Builder())
    ->from('users')
    ->filter([
        Query::equal('status', ['active']),
        Query::greaterThan('age', 18),
    ])
    ->sortAsc('name')
    ->limit(25)
    ->build();

// Generates a find operation with filter, sort, limit, and projection
```

**Array operations:**

```
$result = (new Builder())
    ->from('users')
    ->filter([Query::equal('_id', ['user_1'])])
    ->push('tags', 'new-tag')
    ->update();

$result = (new Builder())
    ->from('users')
    ->filter([Query::equal('_id', ['user_1'])])
    ->pull('tags', 'old-tag')
    ->addToSet('roles', 'editor')
    ->increment('login_count', 1)
    ->update();
```

**Field update operations:**

```
$result = (new Builder())
    ->from('users')
    ->filter([Query::equal('_id', ['user_1'])])
    ->rename('old_field', 'new_field')
    ->multiply('score', 1.5)
    ->updateMin('low_score', 10)
    ->updateMax('high_score', 100)
    ->currentDate('last_modified')
    ->update();

// Array element removal
$result = (new Builder())
    ->from('lists')
    ->filter([Query::equal('_id', ['list_1'])])
    ->popFirst('items')   // Remove first element — $pop: -1
    ->popLast('queue')    // Remove last element — $pop: 1
    ->pullAll('tags', ['deprecated', 'old'])
    ->update();

// Remove fields entirely
$result = (new Builder())
    ->from('users')
    ->filter([Query::equal('_id', ['user_1'])])
    ->unsetFields('legacy_field', 'temp_data')
    ->update();
```

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

```
$result = (new Builder())
    ->from('feeds')
    ->filter([Query::equal('_id', ['feed_1'])])
    ->pushEach('items', [['score' => 5], ['score' => 3]], position: 0, slice: 10, sort: ['score' => -1])
    ->update();
```

**Conditional array updates** with array filters:

```
$result = (new Builder())
    ->from('orders')
    ->filter([Query::equal('_id', ['order_1'])])
    ->arrayFilter('elem', ['elem.status' => 'pending'])
    ->set(['items.$[elem].status' => 'shipped'])
    ->update();
```

**Upsert:**

```
$result = (new Builder())
    ->into('counters')
    ->set(['key' => 'visits', 'value' => 1])
    ->onConflict(['key'], ['value'])
    ->upsert();
```

**Pipeline aggregation stages:**

```
// Bucket — group documents into fixed-size ranges
$result = (new Builder())
    ->from('sales')
    ->bucket('price', [0, 50, 100, 500], defaultBucket: 'other', output: ['count' => ['$sum' => 1]])
    ->build();

// BucketAuto — automatically determine bucket boundaries
$result = (new Builder())
    ->from('sales')
    ->bucketAuto('price', 5, output: ['count' => ['$sum' => 1]])
    ->build();

// Facet — run multiple aggregation pipelines in parallel
$byStatus = (new Builder())->from('orders')->groupBy(['status'])->count('*', 'count');
$byRegion = (new Builder())->from('orders')->groupBy(['region'])->sum('amount', 'total');

$result = (new Builder())
    ->from('orders')
    ->facet(['by_status' => $byStatus, 'by_region' => $byRegion])
    ->build();

// GraphLookup — recursive graph traversal
$result = (new Builder())
    ->from('employees')
    ->graphLookup(
        from: 'employees',
        startWith: '$manager_id',
        connectFromField: 'manager_id',
        connectToField: '_id',
        as: 'reporting_chain',
        maxDepth: 5,
        depthField: 'level',
    )
    ->build();

// Merge results into another collection
$result = (new Builder())
    ->from('daily_stats')
    ->mergeIntoCollection('monthly_stats', on: ['month'], whenMatched: ['$set' => ['total' => '$total']])
    ->build();

// Output to a new collection
$result = (new Builder())
    ->from('raw_data')
    ->outputToCollection('processed_data', database: 'analytics')
    ->build();

// Replace the root document
$result = (new Builder())
    ->from('orders')
    ->replaceRoot('$shipping_address')
    ->build();
```

**Atlas Search:**

```
// Full-text search with Atlas Search
$result = (new Builder())
    ->from('articles')
    ->search(['text' => ['query' => 'mongodb', 'path' => 'content']], index: 'default')
    ->build();

// Search metadata (facet counts, etc.)
$result = (new Builder())
    ->from('articles')
    ->searchMeta(['facet' => ['facets' => ['categories' => ['type' => 'string', 'path' => 'category']]]], index: 'default')
    ->build();

// Atlas Vector Search
$result = (new Builder())
    ->from('documents')
    ->vectorSearch(
        path: 'embedding',
        queryVector: [0.1, 0.2, 0.3],
        numCandidates: 100,
        limit: 10,
        index: 'vector_index',
        filter: ['category' => 'tech'],
    )
    ->build();
```

**Table sampling:**

```
$result = (new Builder())
    ->from('large_collection')
    ->tablesample(10.0)
    ->build();
```

**Full-text search** (non-Atlas):

```
$result = (new Builder())
    ->from('articles')
    ->filterSearch('content', 'hello world')
    ->build();
```

### Feature Matrix

[](#feature-matrix)

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

FeatureBuilderSQLMySQLMariaDBPostgreSQLSQLiteClickHouseMongoDBSelects, Filters, Aggregates, Joins, Unions, CTEs, Inserts, Updates, Deletes, HooksxWindowsx`whereRaw` / `whereColumn`xxLocking, Transactions, UpsertxSpatial, Full-Text SearchxStatistical AggregatesxxxxxBitwise AggregatesxxxxxConditional AggregatesxxxxxJSON (incl. `setJsonPath`)xxxxHintsxxxLateral JoinsxxxString AggregatesxxxxGroup By ModifiersxxxxSequences (`nextVal`/`currVal`)xx`RETURNING`xxFull Outer JoinsxxTable SamplingxxxMergexVector SearchxDISTINCT ONxAggregate FILTERxOrdered-Set Aggregates (incl. `mode`)xPREWHERE, FINAL, SAMPLExLIMIT BYxARRAY JOINxASOF JOIN (typed operator)xWITH FILLx`groupByTimeBucket`xNamed-typed `{name:Type}` bindingsxApproximate Aggregates (incl. `quantiles`)xUpsert (Mongo-style)xFull-Text Search (Mongo)xField UpdatesxArray Push ModifiersxConditional Array UpdatesxPipeline StagesxAtlas SearchxSchema Builder
--------------

[](#schema-builder)

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

```
use Utopia\Query\Schema\MySQL as Schema;
use Utopia\Query\Schema\Table;
// or: PostgreSQL, ClickHouse, SQLite, MongoDB
```

### Creating Tables

[](#creating-tables)

```
$schema = new Schema();

$result = $schema->table('users')
    ->id()
    ->string('name', 255)
    ->string('email', 255)->unique()
    ->integer('age')->nullable()
    ->boolean('active')->default(true)
    ->json('metadata')
    ->timestamps()
    ->create();

$result->query; // CREATE TABLE `users` (...)
```

`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`:

```
$result = $schema->table('users')
    ->id()
    ->string('name', 255)
    ->createIfNotExists();
```

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`:

```
$result = $schema->table('orders')
    ->serial('id')->primary()
    ->bigSerial('external_id')
    ->create();
```

### Altering Tables

[](#altering-tables)

```
use Utopia\Query\Schema\ColumnType;

$result = $schema->table('users')
    ->string('phone', 20)->nullable()
    ->modifyColumn('name', ColumnType::String, 500)
    ->renameColumn('email', 'email_address')
    ->dropColumn('legacy_field')
    ->alter();
```

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

### CHECK Constraints

[](#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`.

```
$result = $schema->table('people')
    ->id()
    ->integer('age')->check('>= 0')                           // column-level
    ->string('email', 255)
    ->check('age_range', '`age` >= 0 AND `age` < 150')        // table-level
    ->create();
```

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)

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.

```
$result = $schema->table('boxes')
    ->id()
    ->integer('width')
    ->integer('height')
    ->integer('area')
        ->generatedAs('`width` * `height`')
        ->stored()
    ->integer('half_area')
        ->generatedAs('(`width` * `height`) / 2')
        ->virtual()
    ->create();
```

### Composite Primary Keys

[](#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`.

```
$result = $schema->table('order_items')
    ->integer('order_id')
    ->integer('product_id')
    ->integer('quantity')
    ->primary(['order_id', 'product_id'])
    ->create();
```

### Indexes

[](#indexes)

```
$result = $schema->createIndex('users', 'idx_email', ['email'], unique: true);
$result = $schema->dropIndex('users', 'idx_email');
```

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

```
use Utopia\Query\Schema\PostgreSQL as Schema;

$schema = new Schema();

// GIN trigram index
$result = $schema->createIndex('users', 'idx_name_trgm', ['name'],
    method: 'gin', operatorClass: 'gin_trgm_ops');

// HNSW vector index
$result = $schema->createIndex('documents', 'idx_embedding', ['embedding'],
    method: 'hnsw', operatorClass: 'vector_cosine_ops');

// Concurrent index creation (non-blocking)
$result = $schema->createIndex('users', 'idx_email', ['email'], concurrently: true);

// Concurrent index drop
$result = $schema->dropIndexConcurrently('idx_email');
```

### Foreign Keys

[](#foreign-keys)

```
use Utopia\Query\Schema\ForeignKeyAction;

$result = $schema->addForeignKey('orders', 'fk_user', 'user_id',
    'users', 'id', onDelete: ForeignKeyAction::Cascade);

$result = $schema->dropForeignKey('orders', 'fk_user');
```

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

### Partitions

[](#partitions)

Available on MySQL, PostgreSQL, and ClickHouse:

```
// Define partition strategy in table creation
$result = $schema->table('events')
    ->id()
    ->datetime('created_at')
    ->partitionByRange('created_at')
    ->create();

// Create a child partition (MySQL, PostgreSQL)
$result = $schema->createPartition('events', 'events_2024', "VALUES LESS THAN ('2025-01-01')");

// Drop a partition
$result = $schema->dropPartition('events', 'events_2024');
```

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

```
$result = $schema->table('users')
    ->id()
    ->integer('user_id')
    ->partitionByHash('`user_id`', 4)
    ->create();

// ... PARTITION BY HASH(`user_id`) PARTITIONS 4
```

### Comments

[](#comments)

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

```
// Table comments (MySQL, PostgreSQL, ClickHouse)
$result = $schema->commentOnTable('users', 'Main user accounts table');

// Column comments (PostgreSQL, ClickHouse)
$result = $schema->commentOnColumn('users', 'email', 'Primary contact email');
```

### Views

[](#views)

```
$query = (new Builder())->from('users')->filter([Query::equal('active', [true])]);

$result = $schema->createView('active_users', $query);
$result = $schema->createOrReplaceView('active_users', $query);
$result = $schema->dropView('active_users');
```

### Procedures and Triggers

[](#procedures-and-triggers)

```
use Utopia\Query\Schema\ParameterDirection;
use Utopia\Query\Schema\TriggerTiming;
use Utopia\Query\Schema\TriggerEvent;

// Procedure
$result = $schema->createProcedure('update_stats', [
    [ParameterDirection::In, 'user_id', 'INT'],
], 'UPDATE stats SET count = count + 1 WHERE id = user_id;');

// Trigger
$result = $schema->createTrigger('before_insert_users', 'users',
    TriggerTiming::Before, TriggerEvent::Insert,
    'SET NEW.created_at = NOW();');
```

### PostgreSQL Schema Extensions

[](#postgresql-schema-extensions)

```
use Utopia\Query\Schema\PostgreSQL as Schema;

$schema = new Schema();

// Extensions (e.g., pgvector, pg_trgm)
$result = $schema->createExtension('vector');
// CREATE EXTENSION IF NOT EXISTS "vector"

// Procedures → CREATE FUNCTION ... LANGUAGE plpgsql
$result = $schema->createProcedure('increment', [
    [ParameterDirection::In, 'p_id', 'INTEGER'],
], '
BEGIN
    UPDATE counters SET value = value + 1 WHERE id = p_id;
END;
');

// Custom types — reference from a column via Column::userType()
$result = $schema->createType('mood_type', ['happy', 'sad', 'angry']);

$result = $schema->table('users')
    ->id()
    ->string('mood')->userType('mood_type')
    ->create();

$result = $schema->dropType('mood_type');

// Sequences
$result = $schema->createSequence('order_seq', start: 1000, incrementBy: 1);
$result = $schema->dropSequence('order_seq');
$result = $schema->nextVal('order_seq');

// Collations
$result = $schema->createCollation('custom_collation', ['locale' => 'en-US-u-ks-level2']);

// Alter column type with optional USING expression
$result = $schema->alterColumnType('users', 'age', 'BIGINT', using: '"age"::BIGINT');

// DROP CONSTRAINT instead of DROP FOREIGN KEY
$result = $schema->dropForeignKey('orders', 'fk_user');
// ALTER TABLE "orders" DROP CONSTRAINT "fk_user"

// DROP INDEX without table name
$result = $schema->dropIndex('orders', 'idx_status');
// DROP INDEX "idx_status"
```

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-schema)

```
use Utopia\Query\Schema\ClickHouse as Schema;
use Utopia\Query\Schema\ClickHouse\Engine;

$schema = new Schema();

$result = $schema->table('events')
    ->string('event_id', 36)->primary()
    ->string('event_type', 50)
    ->integer('count')
    ->datetime('created_at')
    ->create();

// CREATE TABLE `events` (...) ENGINE = MergeTree() ORDER BY (...)
```

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:

```
// Standard MergeTree family
$schema->table('dedup')
    ->bigInteger('id')->primary()
    ->integer('version')
    ->engine(Engine::ReplacingMergeTree, 'version')
    ->create();
// ... ENGINE = ReplacingMergeTree(`version`) ORDER BY (`id`)

$schema->table('metrics')
    ->integer('key')->primary()
    ->bigInteger('total')->unsigned()
    ->engine(Engine::SummingMergeTree, 'total')
    ->create();

// CollapsingMergeTree requires a sign column (throws ValidationException otherwise)
// ReplicatedMergeTree requires zookeeper_path + replica_name
$schema->table('replicated')
    ->integer('id')->primary()
    ->engine(Engine::ReplicatedMergeTree, '/clickhouse/tables/events', 'replica_1')
    ->create();

// Non-MergeTree engines skip the ORDER BY tuple() fallback entirely
$schema->table('cache')
    ->integer('id')->primary()
    ->string('value')
    ->engine(Engine::Memory)
    ->create();
// CREATE TABLE `cache` (...) ENGINE = Memory
```

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

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

```
$schema->table('events')
    ->integer('id')->primary()
    ->datetime('ts')
    ->datetime('expires_at')->ttl('now() + INTERVAL 1 HOUR') // column-level
    ->ttl('ts + INTERVAL 1 DAY')                              // table-level
    ->create();

// Set the ORDER BY clause explicitly with `->orderBy([...])` if it should
// differ from the primary key.
```

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()`:

```
use Utopia\Query\Schema\ClickHouse\IndexAlgorithm;

$schema->table('events')
    ->bigInteger('id')->primary()
    ->string('user_id')
    ->string('country')
    ->string('text')
    // BloomFilter — high-cardinality strings with `=` / `IN` predicates
    ->index(['user_id'], algorithm: IndexAlgorithm::BloomFilter)
    // Set(N) — small fixed value sets, custom granularity
    ->index(['country'], algorithm: IndexAlgorithm::Set, algorithmArgs: [100], granularity: 4)
    // NgramBloomFilter(n, size_bytes, hashes, seed) — text search on `LIKE` / `match`
    ->index(['text'], algorithm: IndexAlgorithm::NgramBloomFilter, algorithmArgs: [4, 1024, 3, 0])
    // No algorithm specified → defaults to `TYPE minmax GRANULARITY 3`
    ->index(['id'])
    ->create();

// CREATE TABLE `events` (..., INDEX `idx_user_id` `user_id` TYPE bloom_filter GRANULARITY 1, ...)
```

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:

```
$schema->table('events')
    ->bigInteger('id')->primary()
    ->settings([
        'index_granularity' => 8192,
        'allow_nullable_key' => true, // booleans become 1/0
    ])
    ->create();

// CREATE TABLE `events` (...) ENGINE = MergeTree() ORDER BY (`id`)
//   SETTINGS index_granularity = 8192, allow_nullable_key = 1
```

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):

```
$schema->table('events')
    ->bigInteger('id')->primary()
    ->string('status')->lowCardinality()
    ->string('country')->lowCardinality()->nullable()
    ->create();

// CREATE TABLE `events` (`id` Int64, `status` LowCardinality(String),
//   `country` Nullable(LowCardinality(String))) ENGINE = MergeTree() ORDER BY (`id`)
```

`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:

```
$schema->table('locations')
    ->bigInteger('id')->primary()
    ->fixedString('country_code', 2)   // ISO 3166-1 alpha-2
    ->fixedString('currency_code', 3)  // ISO 4217
    ->fixedString('digest', 32)        // raw MD5
    ->create();

// CREATE TABLE `locations` (`id` Int64, `country_code` FixedString(2),
//   `currency_code` FixedString(3), `digest` FixedString(32))
//   ENGINE = MergeTree() ORDER BY (`id`)
```

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, ...)`:

```
$schema->table('metrics')
    ->bigInteger('id')->primary()
    ->datetime('ts', 3)->codec('Delta(4)')->codec('LZ4')   // monotonic timestamps
    ->bigInteger('value')->codec('T64')->codec('LZ4')      // integer column
    ->string('payload')->codec('ZSTD(3)')                  // text column
    ->create();

// CREATE TABLE `metrics` (`id` Int64,
//   `ts` DateTime64(3) CODEC(Delta(4), LZ4),
//   `value` Int64 CODEC(T64, LZ4),
//   `payload` String CODEC(ZSTD(3))) ENGINE = MergeTree() ORDER BY (`id`)
```

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`:

```
$schema->table('events')
    ->bigInteger('id')->primary()
    ->bigInteger('user_id')->unsigned()
    ->sampleBy('user_id')
    ->create();

// CREATE TABLE `events` (`id` Int64, `user_id` UInt64) ENGINE = MergeTree()
//   ORDER BY (`id`) SAMPLE BY user_id
```

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()`:

```
$schema->table('events')
    ->bigInteger('id')->primary()
    ->tinyInteger('scroll_depth')->unsigned() // 0–100 percentage
    ->smallInteger('year_offset')             // signed, fits years from epoch
    ->create();

// CREATE TABLE `events` (`id` Int64, `scroll_depth` UInt8, `year_offset` Int16)
//   ENGINE = MergeTree() ORDER BY (`id`)
```

`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:

```
use Utopia\Query\Schema\ColumnType;

$schema->table('events')
    ->bigInteger('id')->primary()
    ->array('meta.key', ColumnType::String)
    ->array('meta.value', ColumnType::String)
    ->array('user_ids', ColumnType::BigInteger)->unsigned()
    ->tuple('coords', [ColumnType::Float, ColumnType::Float])
    ->create();

// CREATE TABLE `events` (`id` Int64,
//   `meta.key` Array(String), `meta.value` Array(String),
//   `user_ids` Array(UInt64),
//   `coords` Tuple(Float64, Float64)) ENGINE = MergeTree() ORDER BY (`id`)
```

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:

```
$schema->table('orders')
    ->bigInteger('id')->primary()
    ->decimal('amount', precision: 18, scale: 3)
    ->decimal('rate', precision: 5, scale: 4)->nullable()
    ->create();

// CREATE TABLE `orders` (`id` Int64,
//   `amount` Decimal(18, 3),
//   `rate` Nullable(Decimal(5, 4))) ENGINE = MergeTree() ORDER BY (`id`)
```

`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:

```
$schema->table('events')
    ->uuid('event_id')->defaultRaw('generateUUIDv4()')->primary()
    ->datetime('ts', 3)
    ->create();

// CREATE TABLE `events` (`event_id` UUID DEFAULT generateUUIDv4(), `ts` DateTime64(3))
//   ENGINE = MergeTree() ORDER BY (`event_id`)
```

`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:

```
$schema->table('events')
    ->string('tenant')
    ->bigInteger('id')
    ->datetime('ts')
    ->orderByRaw('(`tenant`, toDate(`ts`), `id`)')
    ->create();

// CREATE TABLE `events` (`tenant` String, `id` Int64, `ts` DateTime)
//   ENGINE = MergeTree() ORDER BY (`tenant`, toDate(`ts`), `id`)
```

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:

```
$schema->table('events')
    ->bigInteger('id')->primary()
    ->rawColumn('`payload` JSON CODEC(ZSTD(3))')
    ->create();

// CREATE TABLE `events` (`id` Int64, `payload` JSON CODEC(ZSTD(3))) ...
```

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-schema)

```
use Utopia\Query\Schema\SQLite as 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

[](#mongodb-schema)

```
use Utopia\Query\Schema\MongoDB as Schema;
```

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

**Creating collections** with JSON Schema validation:

```
$schema = new Schema();

$result = $schema->table('users')
    ->string('name', 255)
    ->string('email', 255)->unique()
    ->integer('age')->nullable()
    ->boolean('active')->default(true)
    ->json('metadata')
    ->create();

// Generates a create command with bsonType validators
```

**Altering collections:**

```
$result = $schema->table('users')
    ->string('phone', 20)->nullable()
    ->alter();

// Generates a collMod command to update the validator
```

**Indexes:**

```
$result = $schema->createIndex('users', 'idx_email', ['email'], unique: true);
$result = $schema->dropIndex('users', 'idx_email');
```

**Collection operations:**

```
$result = $schema->table('users')->drop();
$result = $schema->table('old_name')->rename('new_name');
$result = $schema->table('users')->truncate();
$result = $schema->analyzeTable('users');
```

**Views:**

```
use Utopia\Query\Builder\MongoDB as Builder;

$query = (new Builder())->from('users')->filter([Query::equal('active', [true])]);
$result = $schema->createView('active_users', $query);
```

**Database management:**

```
$result = $schema->createDatabase('analytics');
$result = $schema->dropDatabase('analytics');
```

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
---------------------

[](#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.

```
use Utopia\Query\Parser;
use Utopia\Query\Type;
```

### SQL Parser

[](#sql-parser)

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

```
use Utopia\Query\Parser\SQL;

// Classify SQL text directly
$type = $parser->classifySQL('SELECT * FROM users');  // Type::Read
$type = $parser->classifySQL('INSERT INTO users ...');  // Type::Write
$type = $parser->classifySQL('BEGIN');  // Type::TransactionBegin
$type = $parser->classifySQL('COMMIT');  // Type::TransactionEnd
```

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

[](#mysql-parser)

Parses MySQL wire protocol binary packets:

```
use Utopia\Query\Parser\MySQL;

$parser = new MySQL();
$type = $parser->parse($rawPacketData);  // Type::Read, Write, TransactionBegin, etc.
```

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

[](#postgresql-parser)

Parses PostgreSQL wire protocol messages:

```
use Utopia\Query\Parser\PostgreSQL;

$parser = new PostgreSQL();
$type = $parser->parse($rawMessageData);  // Type::Read, Write, TransactionBegin, etc.
```

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

### MongoDB Parser

[](#mongodb-parser)

Parses MongoDB OP\_MSG binary protocol messages:

```
use Utopia\Query\Parser\MongoDB;

$parser = new MongoDB();
$type = $parser->parse($rawOpMsgData);  // Type::Read, Write, TransactionBegin, etc.
```

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
------------------

[](#compiler-interface)

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

```
use Utopia\Query\Compiler;
use Utopia\Query\Query;
use Utopia\Query\Method;

class MyCompiler implements Compiler
{
    public function compileFilter(Query $query): string { /* ... */ }
    public function compileOrder(Query $query): string { /* ... */ }
    public function compileLimit(Query $query): string { /* ... */ }
    public function compileOffset(Query $query): string { /* ... */ }
    public function compileSelect(Query $query): string { /* ... */ }
    public function compileCursor(Query $query): string { /* ... */ }
    public function compileAggregate(Query $query): string { /* ... */ }
    public function compileGroupBy(Query $query): string { /* ... */ }
    public function compileJoin(Query $query): string { /* ... */ }
}
```

This is the pattern used by [utopia-php/database](https://github.com/utopia-php/database) — it implements `Compiler` for each supported database engine, keeping application code decoupled from storage backends.

Contributing
------------

[](#contributing)

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

```
composer install       # Install dependencies
composer test          # Run tests
composer lint          # Check formatting
composer format        # Auto-format code
composer check         # Run static analysis (PHPStan level max)
```

**Integration tests** require Docker:

```
docker compose -f docker-compose.test.yml up -d   # Start MySQL, PostgreSQL, ClickHouse
composer test:integration                          # Run integration tests
docker compose -f docker-compose.test.yml down     # Stop containers
```

License
-------

[](#license)

This project is licensed under the MIT License. See the [LICENSE](LICENSE) file for details.

###  Health Score

48

—

FairBetter than 93% of packages

Maintenance91

Actively maintained with recent releases

Popularity28

Limited adoption so far

Community15

Small or concentrated contributor base

Maturity49

Maturing project, gaining track record

 Bus Factor1

Top contributor holds 85% of commits — single point of failure

How is this calculated?**Maintenance (25%)** — Last commit recency, latest release date, and issue-to-star ratio. Uses a 2-year decay window.

**Popularity (30%)** — Total and monthly downloads, GitHub stars, and forks. Logarithmic scaling prevents top-heavy scores.

**Community (15%)** — Contributors, dependents, forks, watchers, and maintainers. Measures real ecosystem engagement.

**Maturity (30%)** — Project age, version count, PHP version support, and release stability.

###  Release Activity

Cadence

Every ~15 days

Total

6

Last Release

44d ago

### Community

Maintainers

![](https://www.gravatar.com/avatar/023f08a9df59f81cc4a04b1cebd20f45ede5db53ef2f9e9ad3d75f4c69be66b8?d=identicon)[eldadfux](/maintainers/eldadfux)

---

Top Contributors

[![abnegate](https://avatars.githubusercontent.com/u/5857008?v=4)](https://github.com/abnegate "abnegate (238 commits)")[![lohanidamodar](https://avatars.githubusercontent.com/u/6360216?v=4)](https://github.com/lohanidamodar "lohanidamodar (36 commits)")[![Copilot](https://avatars.githubusercontent.com/in/1143301?v=4)](https://github.com/Copilot "Copilot (6 commits)")

---

Tags

phpframeworkqueryupfutopia

###  Code Quality

TestsPHPUnit

Static AnalysisPHPStan

Code StyleLaravel Pint

Type Coverage Yes

### Embed Badge

![Health badge](/badges/utopia-php-query/health.svg)

```
[![Health](https://phpackages.com/badges/utopia-php-query/health.svg)](https://phpackages.com/packages/utopia-php-query)
```

###  Alternatives

[utopia-php/domains

Utopia Domains library is simple and lite library for parsing web domains. This library is aiming to be as simple and easy to learn and use.

57691.6k18](/packages/utopia-php-domains)[utopia-php/database

A simple library to manage application persistence using multiple database adapters

74410.0k15](/packages/utopia-php-database)[utopia-php/cli

A simple CLI library to manage command line applications

42492.7k17](/packages/utopia-php-cli)[utopia-php/cache

A simple cache library to manage application cache storing, loading and purging

31483.1k19](/packages/utopia-php-cache)[utopia-php/storage

A simple Storage library to manage application storage

30303.0k12](/packages/utopia-php-storage)[utopia-php/system

A simple library for obtaining information about the host's system.

25316.9k32](/packages/utopia-php-system)

PHPackages © 2026

[Directory](/)[Categories](/categories)[Trending](/trending)[Changelog](/changelog)[Analyze](/analyze)