PHPackages                             darkspock/just-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. [Database & ORM](/categories/database)
4. /
5. darkspock/just-query

ActiveLibrary[Database & ORM](/categories/database)

darkspock/just-query
====================

Production-grade PHP Query Builder with MySQL and PostgreSQL support. Safe bulk operations, conditional clauses, JSON schema, query profiler, and index hints. Zero framework coupling.

v1.0.0(yesterday)00BSD-3-ClausePHPPHP >=8.3

Since Apr 2Pushed yesterdayCompare

[ Source](https://github.com/darkspock/just-query)[ Packagist](https://packagist.org/packages/darkspock/just-query)[ Docs](https://github.com/darkspock/FastPHPQueryBuilder)[ RSS](/packages/darkspock-just-query/feed)WikiDiscussions main Synced today

READMEChangelogDependencies (4)Versions (3)Used By (0)

JustQuery
=========

[](#justquery)

A high-performance PHP Query Builder built for production SaaS applications where query control, observability, and deploy safety matter.

Full MySQL and PostgreSQL support. Zero framework coupling. Drop into any PHP 8.3+ project.

Why This Exists
---------------

[](#why-this-exists)

JustQuery is a fork of the excellent [yiisoft/db](https://github.com/yiisoft/db) and [yiisoft/db-mysql](https://github.com/yiisoft/db-mysql) libraries.

We maintain this fork because our production SaaS (CoverManager) needs capabilities that don't fit the upstream project's scope or release timeline:

### Features rejected or out-of-scope upstream

[](#features-rejected-or-out-of-scope-upstream)

- **`FORCE INDEX` / `USE INDEX` / `IGNORE INDEX`** — MySQL optimizer hints are critical for large tables where the query planner makes poor index choices. This was rejected upstream as too MySQL-specific. For us, it's the difference between a 50ms query and a 12-second table scan on a 40M-row table.
- **Configurable schema provider with JSON mode** — Upstream requires either database introspection or a cache layer (Redis/Memcached) for type casting. In rolling deployments where schema changes happen before code deploys, this causes downtime. Our JSON schema mode reads column definitions from committed JSON files — zero DB overhead, zero cache dependency, fully deterministic.
- **Built-in query profiler** — Upstream delegates profiling to external Yii packages. We need a self-contained profiler that works in any framework (CodeIgniter 3, standalone scripts, queue workers) with zero dependencies.
- **Computed column protection** — Upstream doesn't know which columns are computed/generated. Our schema provider marks columns as computed, and the query builder automatically excludes them from INSERT and UPDATE operations. No more "Column 'total\_amount' is a generated column" errors.
- **Shared PDO connections** — Our legacy CodeIgniter 3 app already has a PDO connection. Upstream creates its own. We need to pass in an existing `\PDO` instance and share it.

### Performance and operational priorities

[](#performance-and-operational-priorities)

Upstream yiisoft/db is designed for the Yii 3 framework ecosystem. It prioritizes broad compatibility and clean abstractions. We prioritize:

- **Query execution control** — index hints, optimizer hints, and direct control over the SQL the database sees.
- **Zero-overhead schema** — JSON schema files that ship with code, not runtime introspection.
- **Observability** — every query profiled with timing, params, and errors — in any PHP runtime.
- **Static analysis** — PHPStan level max with zero baseline, enforced in CI.
- **Minimal dependencies** — only PSR interfaces (psr/log, psr/simple-cache). No framework coupling.

Benchmarks
----------

[](#benchmarks)

Compared against Eloquent 12.x and Doctrine DBAL 4.x (PHP 8.5, MySQL 8.0, 50k+ rows). Ties Doctrine on reads, **26x faster on batch inserts**, and matches Eloquent's code brevity — without the ORM overhead. [Full comparison](comparison.md).

Requirements
------------

[](#requirements)

- PHP >= 8.3
- PDO + pdo\_mysql and/or pdo\_pgsql

Installation
------------

[](#installation)

```
composer require darkspock/just-query
```

Connection
----------

[](#connection)

### MySQL

[](#mysql)

```
use JustQuery\Driver\Mysql\Connection;

$db = Connection::fromDsn(
    dsn: 'mysql:host=127.0.0.1;dbname=myapp;port=3306',
    username: 'root',
    password: 'secret',
);

$db->open();
```

### PostgreSQL

[](#postgresql)

```
use JustQuery\Driver\Pgsql\Connection;

$db = Connection::fromDsn(
    dsn: 'pgsql:host=127.0.0.1;dbname=myapp;port=5432',
    username: 'postgres',
    password: 'secret',
);

$db->open();
```

### Shared PDO (existing connection)

[](#shared-pdo-existing-connection)

```
use JustQuery\Driver\Mysql\Connection;

// Reuse a PDO instance from your framework (CodeIgniter, Laravel, etc.)
// Use the matching Connection class for your driver.
$db = Connection::fromPdo($existingPdo);
```

### Advanced Configuration

[](#advanced-configuration)

```
use JustQuery\Cache\SchemaCache;
use JustQuery\Driver\Mysql\{Connection, Driver};

$driver = new Driver(
    'mysql:host=127.0.0.1;dbname=myapp;port=3306',
    'root',
    'secret',
);

$schemaCache = new SchemaCache($psr16Cache);
$db = new Connection($driver, $schemaCache);
```

### Connection Lifecycle

[](#connection-lifecycle)

```
$db->open();                    // Establish connection
$db->isActive();                // true if connected
$db->close();                   // Close connection
$db->getDriverName();           // 'mysql' or 'pgsql'
$db->getLastInsertId();         // Last auto-increment ID
$db->getSchema();               // SchemaInterface
$db->getTableSchema('users');   // TableSchemaInterface for a specific table
$db->getQueryBuilder();         // QueryBuilderInterface
$db->getQuoter();               // QuoterInterface
```

### Table Prefix

[](#table-prefix)

```
$db->setTablePrefix('tbl_');
// Now {{%users}} resolves to tbl_users in queries
$db->getTablePrefix(); // 'tbl_'
```

Framework Integration
---------------------

[](#framework-integration)

### Laravel

[](#laravel)

Register JustQuery as a singleton in a Service Provider. This reuses Laravel's existing PDO connection:

```
// app/Providers/AppServiceProvider.php
use Illuminate\Support\ServiceProvider;
use JustQuery\Driver\Mysql\Connection;
use JustQuery\Schema\Provider\{SchemaProvider, SchemaMode};

class AppServiceProvider extends ServiceProvider
{
    public function register(): void
    {
        $this->app->singleton(Connection::class, function ($app) {
            $pdo = $app['db']->connection()->getPdo();
            $db = Connection::fromPdo($pdo);

            $db->setSchemaProvider(new SchemaProvider(
                SchemaMode::JSON,
                jsonPath: base_path('database/schema/'),
            ));

            return $db;
        });
    }
}
```

Usage anywhere via dependency injection or the container:

```
use JustQuery\Driver\Mysql\Connection;
use JustQuery\Query\Query;

class UserController extends Controller
{
    public function index(Connection $db)
    {
        return (new Query($db))
            ->from('users')
            ->where(['status' => 'active'])
            ->all();
    }
}
```

### Symfony

[](#symfony)

Register JustQuery as a service in `services.yaml`:

```
# config/services.yaml
services:
    JustQuery\Driver\Mysql\Connection:
        factory: ['JustQuery\Driver\Mysql\Connection', 'fromDsn']
        arguments:
            $dsn: '%env(DATABASE_DSN)%'
            $username: '%env(DATABASE_USER)%'
            $password: '%env(DATABASE_PASSWORD)%'

    JustQuery\Schema\Provider\SchemaProvider:
        arguments:
            $mode: !php/enum JustQuery\Schema\Provider\SchemaMode::JSON
            $jsonPath: '%kernel.project_dir%/config/schema/'
```

Or reuse Doctrine's existing PDO connection:

```
# config/services.yaml
services:
    JustQuery\Driver\Mysql\Connection:
        factory: ['@App\Factory\JustQueryFactory', 'create']
```

```
// src/Factory/JustQueryFactory.php
namespace App\Factory;

use Doctrine\DBAL\Connection as DoctrineConnection;
use JustQuery\Driver\Mysql\Connection;

class JustQueryFactory
{
    public function __construct(private DoctrineConnection $doctrine) {}

    public function create(): Connection
    {
        return Connection::fromPdo(
            $this->doctrine->getNativeConnection(),
        );
    }
}
```

Usage via autowiring:

```
use JustQuery\Driver\Mysql\Connection;
use JustQuery\Query\Query;

class UserController extends AbstractController
{
    public function index(Connection $db): JsonResponse
    {
        $users = (new Query($db))
            ->from('users')
            ->where(['status' => 'active'])
            ->all();

        return $this->json($users);
    }
}
```

Quick Start
-----------

[](#quick-start)

```
use JustQuery\Query\Query;

// SELECT
$users = (new Query($db))
    ->from('users')
    ->where(['status' => 'active'])
    ->orderBy(['created_at' => SORT_DESC])
    ->limit(10)
    ->all();

// INSERT
$db->createCommand()->insert('users', [
    'name' => 'John',
    'is_active' => true,
    'config' => ['role' => 'admin'],
])->execute();

// UPDATE
$db->createCommand()->update('users', ['name' => 'Jane'], ['id' => 1])->execute();

// DELETE
$db->createCommand()->delete('users', ['id' => 1])->execute();
```

### Factory Methods

[](#factory-methods)

```
// Create a Query from the connection
$query = $db->createQuery();

// Shorthand: create a Query with SELECT columns
$query = $db->select(['id', 'name'])->from('users');

// Create a raw command
$command = $db->createCommand('SELECT * FROM users WHERE id = :id', [':id' => 1]);
```

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

[](#query-builder)

### WHERE Conditions

[](#where-conditions)

Every standard SQL comparison, plus array/JSON operators:

```
// Equality
$query->where(['status' => 'active']);
$query->where(['status' => null]);                // IS NULL

// Operators
$query->where(['>=', 'age', 18]);
$query->where(['between', 'age', 18, 65]);
$query->where(['like', 'name', 'John']);
$query->where(['in', 'id', [1, 2, 3]]);

// Logical
$query->where([
    'and',
    ['status' => 'active'],
    ['or', ['>', 'balance', 1000], ['role' => 'vip']],
]);

// Subquery in condition
$activeIds = (new Query($db))->select('id')->from('users')->where(['active' => 1]);
$query->where(['in', 'user_id', $activeIds]);

// EXISTS
$query->where(['exists', (new Query($db))->from('orders')->where('orders.user_id = users.id')]);

// JSON overlaps (MySQL)
$query->where(['json overlaps', 'tags', ['php', 'mysql']]);

// JSON contains — check if JSON column contains a value
$query->where(['json contains', 'options', 'en']);
$query->where(['json contains', 'options', ['role' => 'admin']]);
$query->where(['json contains', 'options', 'en', '$.languages']); // with path

// JSON length — compare length of JSON array/object
$query->where(['json length', 'tags', '>', 3]);
$query->where(['json length', 'data', '>=', 1, '$.items']); // with path

// Array overlaps (PostgreSQL)
$query->where(['array overlaps', 'tags', ['php', 'mysql']]);

// filterWhere — automatically ignores null/empty values
$query->filterWhere([
    'status' => $request->get('status'),   // skipped if null/empty
    'name'   => $request->get('name'),     // skipped if null/empty
]);
```

Full list of condition operators: `=`, `!=`, ``, `>`, `>=`, `whereIntegerInRaw('id', $userIds)
    ->all();

// NOT IN variant
$query->from('users')
    ->whereIntegerNotInRaw('id', $excludedIds)
    ->all();
```

**Safety**: all values are cast through `intval()`, so non-integer values become `0`. Empty arrays produce `WHERE 0=1` (IN) or are a no-op (NOT IN).

**When to use**: large lists of IDs from subqueries, caches, or external systems where binding overhead is measurable. For small lists (under ~100 values), normal `['in', 'id', $values]` is fine.

### Conditional Clauses (`when`)

[](#conditional-clauses-when)

Apply query clauses only when a condition is truthy. Eliminates verbose if/else blocks:

```
$query->from('users')
    ->when($request->status, fn($q, $status) => $q->andWhere(['status' => $status]))
    ->when($request->sortBy, fn($q, $sort) => $q->orderBy($sort), fn($q) => $q->orderBy('id'))
    ->when($request->limit, fn($q, $limit) => $q->limit($limit));
```

The second closure (optional) is the default, called when the condition is falsy.

### Incremental WHERE Building

[](#incremental-where-building)

`where()` sets the initial condition (throws `LogicException` if called twice). Use `andWhere()` / `orWhere()` to add conditions incrementally:

```
$query->from('users')
    ->where(['status' => 'active'])
    ->andWhere(['>', 'age', 18])
    ->orWhere(['role' => 'admin']);

// Overwrite WHERE without exception
$query->setWhere(['status' => 'banned']);
```

### Smart Filter Comparison

[](#smart-filter-comparison)

`andFilterCompare()` detects the operator from the value string:

```
// Useful for user-submitted filter forms
$query->from('products')
    ->where(['category' => 'electronics'])
    ->andFilterCompare('price', '>=100')   // price >= 100
    ->andFilterCompare('name', 'phone')    // name = 'phone'
    ->andFilterCompare('stock', '0');     // stock  0
```

Recognized prefixes: `>=`, ``, `from('users')->distinct()->select('country');

// Add columns to existing SELECT
$query->select('id')->addSelect(['name', 'email']);

// MySQL-specific: SQL_CALC_FOUND_ROWS
$query->from('users')->select('*', 'SQL_CALC_FOUND_ROWS');
// or
$query->selectOption('SQL_CALC_FOUND_ROWS');
```

### JOINs

[](#joins)

```
$query->from('orders o')
    ->innerJoin('users u', 'u.id = o.user_id')
    ->leftJoin('products p', 'p.id = o.product_id')
    ->rightJoin('categories c', 'c.id = p.category_id')
    ->where(['o.status' => 'confirmed']);

// Join with array condition (auto-quoted column names)
$query->innerJoin('users u', ['u.id' => 'o.user_id']);
```

### GROUP BY and HAVING

[](#group-by-and-having)

```
$query->from('orders')
    ->select(['user_id', 'total' => 'SUM(amount)'])
    ->groupBy('user_id')
    ->addGroupBy('status')             // add to existing GROUP BY
    ->having(['>', 'SUM(amount)', 1000])
    ->andHaving(['status' => 'completed'])
    ->orHaving(['>', 'COUNT(*)', 5]);

// Overwrite HAVING
$query->setHaving(['>', 'SUM(amount)', 500]);

// Filter HAVING (ignores null/empty values)
$query->filterHaving(['status' => $userInput]);
$query->andFilterHaving(['category' => $category]);
$query->orFilterHaving(['region' => $region]);
```

### ORDER BY

[](#order-by)

```
$query->orderBy(['created_at' => SORT_DESC]);
$query->addOrderBy(['name' => SORT_ASC]);  // add to existing ORDER BY
$query->orderBy('created_at DESC, name ASC'); // string format
```

### Aggregates

[](#aggregates)

```
(new Query($db))->from('users')->count('*');
(new Query($db))->from('orders')->sum('total');
(new Query($db))->from('users')->average('age');
(new Query($db))->from('users')->min('age');
(new Query($db))->from('users')->max('age');
```

### Result Methods

[](#result-methods)

```
$query = (new Query($db))->from('users')->where(['status' => 'active']);

$rows = $query->all();           // All rows as array of arrays
$row = $query->one();            // First row or null
$exists = $query->exists();      // true if any rows match
$ids = $query->column();         // First column of all rows as flat array
$value = $query->scalar();       // Single value (first column, first row)
```

### Index Results By Column

[](#index-results-by-column)

```
// Index results by the 'id' column
$users = (new Query($db))->from('users')->indexBy('id')->all();
// Result: [1 => ['id' => 1, 'name' => 'Alice'], 2 => ['id' => 2, 'name' => 'Bob']]

// Index by a closure
$users = (new Query($db))->from('users')
    ->indexBy(fn(array $row) => $row['email'])
    ->all();
```

### Result Callback

[](#result-callback)

Transform result rows before they are returned:

```
$users = (new Query($db))
    ->from('users')
    ->resultCallback(function (array $rows): array {
        foreach ($rows as &$row) {
            $row['name'] = strtoupper($row['name']);
        }
        return $rows;
    })
    ->all();
```

### FOR UPDATE / FOR SHARE

[](#for-update--for-share)

Lock rows for update within a transaction:

```
$query->from('accounts')
    ->where(['id' => 1])
    ->for('UPDATE');

// Multiple FOR clauses
$query->for('UPDATE')->addFor('NOWAIT');

// Overwrite FOR clause
$query->setFor('SHARE');
```

### Emulate Execution

[](#emulate-execution)

Skip actual DB execution (useful for conditional query building):

```
$query->from('users')
    ->emulateExecution(true);

$query->all();    // returns []
$query->one();    // returns null
$query->exists(); // returns false
$query->count();  // returns 0

$query->shouldEmulateExecution(); // true
```

### Subqueries

[](#subqueries)

Any `Query` object is embeddable as a subquery anywhere:

```
// In FROM
$sub = (new Query($db))->select('user_id, SUM(total) as total')->from('orders')->groupBy('user_id');
$query->from(['totals' => $sub])->where(['>', 'totals.total', 1000]);

// In SELECT
$query->select(['name', 'order_count' => (new Query($db))->select('COUNT(*)')->from('orders')->where('orders.user_id = users.id')]);
```

### Common Table Expressions (CTE)

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

```
$cte = (new Query($db))
    ->select(['id', 'parent_id', 'name'])
    ->from('categories')
    ->where(['parent_id' => null]);

$query->withQuery($cte, 'tree', recursive: true)
    ->from('tree');

// Add more CTEs
$query->addWithQuery($anotherCte, 'summary');
```

### UNION

[](#union)

```
$active = (new Query($db))->from('users')->where(['status' => 'active']);
$vip = (new Query($db))->from('users')->where(['role' => 'vip']);

$active->union($vip)->all();
$active->union($vip, all: true)->all(); // UNION ALL
```

### Batch Processing

[](#batch-processing)

```
foreach ((new Query($db))->from('users')->batch(1000) as $batch) {
    // $batch is an array of up to 1000 rows
}

foreach ((new Query($db))->from('users')->each() as $row) {
    // $row is a single row, fetched in batches internally
}
```

### Chunk By ID (Safe Iteration)

[](#chunk-by-id-safe-iteration)

Unlike `batch()` which uses OFFSET/LIMIT, `chunkById()` uses cursor-based pagination (`WHERE id > last_id`). This is safe when modifying records during iteration and faster on large tables:

```
(new Query($db))->from('users')->where(['active' => false])
    ->chunkById(100, function (array $rows) use ($db) {
        foreach ($rows as $row) {
            $db->createCommand()->update('users', ['active' => true], ['id' => $row['id']])->execute();
        }
    });

// Custom primary key column
(new Query($db))->from('orders')->chunkById(500, $callback, 'order_id');

// Stop early by returning false
(new Query($db))->from('users')->chunkById(100, function (array $rows) {
    // process...
    return false; // stops after this chunk
});
```

### Upsert

[](#upsert)

```
// MySQL: INSERT ... ON DUPLICATE KEY UPDATE
// PostgreSQL: INSERT ... ON CONFLICT DO UPDATE
$db->createCommand()->upsert('users', [
    'email' => 'john@example.com',
    'name' => 'John',
    'login_count' => new Expression('login_count + 1'),
])->execute();
```

### Increment / Decrement

[](#increment--decrement)

Atomic counter operations without writing Expression objects manually:

```
// Increment a single column
$db->createCommand()->increment('users', 'login_count', 1, ['id' => 1])->execute();

// Increment with extra columns to update
$db->createCommand()->increment('users', 'balance', 50.00, ['id' => 1], ['last_deposit' => '2024-01-15'])->execute();

// Decrement
$db->createCommand()->decrement('products', 'stock', 1, ['id' => 5])->execute();

// Increment multiple columns at once
$db->createCommand()->incrementEach('users', ['votes' => 5, 'balance' => 100], ['id' => 1])->execute();
```

### Batch Insert

[](#batch-insert)

```
$db->createCommand()->insertBatch('users', [
    ['name' => 'Alice', 'email' => 'alice@example.com'],
    ['name' => 'Bob', 'email' => 'bob@example.com'],
])->execute();
```

### Parameter Binding

[](#parameter-binding)

```
use JustQuery\Expression\Value\Param;
use JustQuery\Constant\DataType;

$query->from('users')
    ->where('status = :status')
    ->params([':status' => 'active'])
    ->addParams([':role' => 'admin']);

// On commands with explicit type
$command = $db->createCommand('SELECT * FROM users WHERE id = :id');
$command->bindValue(':id', 42, DataType::INTEGER);
$command->bindValues([':name' => 'John', ':profile' => new Param($blob, DataType::LOB)]);
```

### Raw SQL and Direct Command Queries

[](#raw-sql-and-direct-command-queries)

```
// Raw SQL
$row = $db->createCommand('SELECT * FROM users WHERE id = :id', [':id' => 1])->queryOne();

// Direct command query methods
$command = $db->createCommand('SELECT * FROM users');
$rows = $command->queryAll();       // All rows
$row = $command->queryOne();        // First row
$column = $command->queryColumn();  // First column as flat array
$value = $command->queryScalar();   // Single value

// SQL access
$command->getSql();     // The SQL with placeholders
$command->getRawSql();  // SQL with values inserted (for logging)
$command->setSql($sql); // Set new SQL (with quoting)
$command->setRawSql($sql); // Set SQL without modification

// Execute non-query (INSERT, UPDATE, DELETE)
$affectedRows = $command->execute();
```

Transactions
------------

[](#transactions)

```
// Automatic transaction with closure
$result = $db->transaction(function (ConnectionInterface $db) {
    $db->createCommand()->insert('orders', ['user_id' => 1, 'total' => 99.99])->execute();
    $db->createCommand()->update('users', ['order_count' => new Expression('order_count + 1')], ['id' => 1])->execute();
    return $db->getLastInsertId();
});

// Manual transaction
$transaction = $db->beginTransaction();
try {
    $db->createCommand()->insert('orders', ['total' => 50])->execute();
    $transaction->commit();
} catch (\Throwable $e) {
    $transaction->rollBack();
    throw $e;
}

// Check active transaction
$tx = $db->getTransaction(); // null if none active

// Savepoints
$db->setEnableSavepoint(true);
$db->isSavepointEnabled(); // true
```

Retry Handler
-------------

[](#retry-handler)

Handle transient database errors (deadlocks, connection drops) with automatic retry:

```
$command = $db->createCommand('INSERT INTO orders ...');
$command->setRetryHandler(function (\JustQuery\Exception\Exception $e, int $attempt): bool {
    // $attempt starts at 1
    if ($attempt > 3) {
        return false; // give up, throw the exception
    }
    // Retry on deadlock (MySQL error 1213)
    return str_contains($e->getMessage(), 'Deadlock');
});
$command->execute();
```

Index Hints (MySQL)
-------------------

[](#index-hints-mysql)

Control which indexes MySQL uses for query execution. Essential for large tables where the optimizer makes suboptimal choices.

```
// Force the optimizer to use a specific index
$query->from('users')
    ->forceIndex('users', 'idx_email')
    ->where(['email' => $email]);

// Suggest indexes (optimizer may still ignore)
$query->from('users')
    ->useIndex('users', ['idx_email', 'idx_status']);

// Prevent the optimizer from using an index
$query->from('users')
    ->ignoreIndex('users', 'idx_created_at');

// Multiple hints on the same table
$query->from('users')
    ->forceIndex('users', 'idx_email')
    ->ignoreIndex('users', 'idx_old_status');

// Hints on JOIN tables
$query->from('users')
    ->innerJoin('orders', 'users.id = orders.user_id')
    ->forceIndex('orders', 'idx_user_id');

// Works with aliases
$query->from(['u' => 'users'])
    ->forceIndex('users', 'idx_email')
    ->where(['u.email' => $email]);
```

Generated SQL:

```
SELECT * FROM `users` FORCE INDEX (`idx_email`) WHERE `email` = 'john@example.com'
SELECT * FROM `users` `u` FORCE INDEX (`idx_email`) WHERE `u`.`email` = 'john@example.com'
SELECT * FROM `users` INNER JOIN `orders` FORCE INDEX (`idx_user_id`) ON `users`.`id` = `orders`.`user_id`
```

Expression System
-----------------

[](#expression-system)

Build complex SQL safely using the Expression/Builder pattern. Every expression class has a corresponding Builder that generates the SQL.

### Raw Expression

[](#raw-expression)

```
use JustQuery\Expression\Expression;

$query->select([new Expression('COUNT(DISTINCT user_id) as unique_users')]);
$query->where(new Expression('DATE(created_at) = CURDATE()'));
```

### CASE Expression

[](#case-expression)

```
use JustQuery\Expression\Statement\CaseX;
use JustQuery\Expression\Statement\WhenThen;

$case = new CaseX(
    new WhenThen(['status' => 'active'], 'Active'),
    new WhenThen(['status' => 'banned'], 'Banned'),
    else: 'Unknown',
);
$query->select(['name', 'label' => $case]);
```

### Function Expressions

[](#function-expressions)

```
use JustQuery\Expression\Function\Greatest;
use JustQuery\Expression\Function\Least;
use JustQuery\Expression\Function\Length;
use JustQuery\Expression\Function\Longest;
use JustQuery\Expression\Function\Shortest;

// GREATEST / LEAST — returns max/min of multiple columns
$query->select([new Greatest('col1', 'col2', 'col3')]);
$query->select([new Least('price', 'sale_price')]);

// LENGTH — string length of a column
$query->select(['name', 'name_len' => new Length('name')]);

// LONGEST / SHORTEST — returns the longest/shortest string among columns
$query->select([new Longest('first_name', 'last_name')]);
$query->select([new Shortest('city', 'state')]);
```

### Value Expressions

[](#value-expressions)

```
use JustQuery\Expression\Value\JsonValue;
use JustQuery\Expression\Value\Value;
use JustQuery\Expression\Value\Param;
use JustQuery\Expression\Value\ColumnName;
use JustQuery\Expression\Value\DateTimeValue;
use JustQuery\Constant\DataType;

// JSON — encodes PHP array as JSON for the database
$query->where(['config' => new JsonValue(['role' => 'admin'])]);

// Value — wrap a raw PHP value for type-safe binding
$query->where(['=', 'score', new Value(42)]);

// Param — bind with explicit PDO data type
$query->where(['=', 'avatar', new Param($binaryData, DataType::LOB)]);

// ColumnName — reference a column name as an expression
$query->select([new ColumnName('users.name')]);

// DateTimeValue — bind DateTime objects
$query->where(['>=', 'created_at', new DateTimeValue(new \DateTimeImmutable('2024-01-01'))]);
```

### Composite Expressions

[](#composite-expressions)

```
use JustQuery\Expression\CompositeExpression;

// Group multiple expressions into one
$composite = new CompositeExpression('AND', [
    new Expression('age > 18'),
    new Expression('status = 1'),
]);
```

Schema Provider
---------------

[](#schema-provider)

Configure how the query builder understands your database schema:

```
use JustQuery\Schema\Provider\{SchemaProvider, SchemaMode};

// DISABLED — pure query builder, no type casting
$provider = new SchemaProvider(SchemaMode::DISABLED);

// JSON — read from JSON files, zero DB overhead, deploy-safe
$provider = new SchemaProvider(SchemaMode::JSON, jsonPath: '/path/to/schema/');

// CACHE — read from DB, cache in Redis/APCu (traditional approach)
$provider = new SchemaProvider(SchemaMode::CACHE, dbSchema: $schema);

// JSON_CACHE — JSON first, DB+cache fallback for unknown tables
$provider = new SchemaProvider(SchemaMode::JSON_CACHE, dbSchema: $schema, jsonPath: '/path/to/schema/');
```

### Why JSON Schema?

[](#why-json-schema)

In rolling deployments:

1. Database migration runs (adds column `score FLOAT GENERATED ALWAYS AS (...)`)
2. Old code is still running — it doesn't know about `score`
3. New code deploys — it reads `score` from JSON schema, knows it's computed, skips it in writes

With DB introspection (upstream approach), step 2 can fail if the cache is stale or cold. With JSON schema, the schema definition travels with the code.

### JSON Schema Format

[](#json-schema-format)

```
{
  "users": {
    "id":        {"type": "integer", "primaryKey": true, "autoIncrement": true},
    "name":      {"type": "string", "size": 255, "notNull": true},
    "is_active": {"type": "boolean"},
    "balance":   {"type": "float", "scale": 2},
    "options":   {"type": "json"},
    "score":     {"type": "float", "computed": true}
  }
}
```

The `type` defines the **PHP type you want**, not the MySQL column type. A `TEXT` column storing `"42"` with `"type": "integer"` returns `42` in PHP.

Supported types: `string`, `integer`, `float`, `boolean`, `json`.

### Automatic Type Casting

[](#automatic-type-casting)

PDO returns everything as strings. JustQuery casts automatically based on schema:

```
$row = $query->from('users')->where(['id' => 1])->withTypecasting()->one();

$row['id'];        // int(1)         — not string("1")
$row['is_active']; // bool(true)     — not string("1")
$row['balance'];   // float(1500.5)  — not string("1500.50")
$row['options'];   // array(...)     — not string('{"role":"admin"}')
```

### Granular Type Casting Control

[](#granular-type-casting-control)

```
// Enable type casting only when reading from DB
$command = $db->createCommand('SELECT * FROM users');
$command = $command->withPhpTypecasting();  // cast DB → PHP on reads

// Enable type casting only when writing to DB
$command = $command->withDbTypecasting();   // cast PHP → DB on inserts/updates

// Enable both at once
$command = $command->withTypecasting();

// On Query objects
$query = (new Query($db))->from('users')->withTypecasting();
```

### Computed Column Protection

[](#computed-column-protection)

Columns marked as `computed` or `autoIncrement` are automatically excluded from INSERT and UPDATE:

```
// Schema: score is GENERATED ALWAYS AS (reviews_sum / reviews_count)
$db->createCommand()->insert('products', [
    'name' => 'Widget',
    'price' => 9.99,
    'score' => 4.5,  // silently excluded — won't cause a MySQL error
])->execute();
```

Query Profiler
--------------

[](#query-profiler)

Zero-overhead when disabled. Nanosecond-precision timing when active.

```
use JustQuery\Profiler\QueryProfiler;

$profiler = new QueryProfiler();
$connection->setProfiler($profiler);

// ... run queries ...

$profiler->getCount();          // 47
$profiler->getTotalTime();      // 123.45 (ms)
$profiler->getSlowest(5);       // top 5 slowest queries
$profiler->getErrors();         // queries that threw exceptions
$profiler->getQueries();        // all queries: sql, time, params, error
$profiler->reset();             // clear collected data
```

Each query record contains:

FieldDescription`sql`The executed SQL statement`time`Execution time in milliseconds`params`Bound parameter values`error`Exception message if the query failedWhen no profiler is set, there is zero overhead — all profiler calls are nullsafe no-ops.

PostgreSQL Support
------------------

[](#postgresql-support)

Full PostgreSQL support with native types:

### RETURNING Clause

[](#returning-clause)

```
// INSERT and return primary key values
$pks = $db->createCommand()->insertReturningPks('users', ['name' => 'John', 'email' => 'john@example.com']);
// ['id' => 42]

// UPSERT and return specific columns
$row = $db->createCommand()->upsertReturning('users', $columns, true, ['id', 'name', 'email']);
// ['id' => 42, 'name' => 'John', 'email' => 'john@example.com']

// UPSERT and return just primary keys
$pks = $db->createCommand()->upsertReturningPks('users', $columns, true);
// ['id' => 42]
```

### Array Types

[](#array-types)

```
use JustQuery\Expression\Value\ArrayValue;

// ARRAY[1, 2, 3]::integer[]
$query->where(['=', 'ids', new ArrayValue([1, 2, 3], 'integer')]);
```

### Structured/Composite Types

[](#structuredcomposite-types)

```
use JustQuery\Expression\Value\StructuredValue;

// ROW(10, 'USD')::money_type
$query->where(['=', 'price', new StructuredValue(['amount' => 10, 'currency' => 'USD'], 'money_type')]);
```

### Range Types

[](#range-types)

Full support for all PostgreSQL range types:

```
use JustQuery\Driver\Pgsql\Expression\Int4RangeValue;
use JustQuery\Driver\Pgsql\Expression\DateRangeValue;

// int4range '[1, 10)'
new Int4RangeValue(1, 10);

// daterange '[2024-01-01, 2024-12-31]'
new DateRangeValue(new DateTimeImmutable('2024-01-01'), new DateTimeImmutable('2024-12-31'));
```

Supported: `int4range`, `int8range`, `numrange`, `daterange`, `tsrange`, `tstzrange`, and all corresponding multirange types.

### Array Merge

[](#array-merge)

```
use JustQuery\Expression\Function\ArrayMerge;

// Merge PostgreSQL arrays
$query->select([new ArrayMerge('tags1', 'tags2')]);
```

### Index Methods

[](#index-methods)

```
// CREATE INDEX ... USING GIN
$db->createCommand()->createIndex('users', 'idx_tags', 'tags', indexMethod: 'gin')->execute();
```

DDL Operations
--------------

[](#ddl-operations)

Full DDL support for both MySQL and PostgreSQL. All DDL methods are available on both the `QueryBuilder` and `Command` interfaces:

```
// Via Command (executes immediately)
$cmd = $db->createCommand();

// Via QueryBuilder (returns SQL string)
$qb = $db->getQueryBuilder();
```

### Tables

[](#tables)

```
$cmd->createTable('users', [
    'id' => ColumnBuilder::primaryKey(),
    'name' => ColumnBuilder::string(255)->notNull(),
    'email' => ColumnBuilder::string(255)->unique(),
    'balance' => ColumnBuilder::decimal(10, 2)->defaultValue(0),
])->execute();

$cmd->dropTable('users')->execute();
$cmd->dropTable('users', ifExists: true, cascade: true)->execute();
$cmd->renameTable('users', 'accounts')->execute();
$cmd->truncateTable('users')->execute();
```

### Columns

[](#columns)

```
$cmd->addColumn('users', 'age', ColumnBuilder::integer())->execute();
$cmd->alterColumn('users', 'name', ColumnBuilder::string(500))->execute();
$cmd->dropColumn('users', 'age')->execute();
$cmd->renameColumn('users', 'name', 'full_name')->execute();
```

### Indexes

[](#indexes)

```
$cmd->createIndex('users', 'idx_email', 'email', indexType: 'UNIQUE')->execute();
$cmd->createIndex('users', 'idx_tags', 'tags', indexMethod: 'gin')->execute(); // PostgreSQL GIN
$cmd->dropIndex('users', 'idx_email')->execute();
```

### Foreign Keys

[](#foreign-keys)

```
$cmd->addForeignKey('orders', 'fk_user', 'user_id', 'users', 'id', 'CASCADE', 'CASCADE')->execute();
$cmd->dropForeignKey('orders', 'fk_user')->execute();
```

### Constraints

[](#constraints)

```
$cmd->addPrimaryKey('users', 'pk_users', 'id')->execute();
$cmd->dropPrimaryKey('users', 'pk_users')->execute();
$cmd->addUnique('users', 'uq_email', 'email')->execute();
$cmd->dropUnique('users', 'uq_email')->execute();
$cmd->addCheck('users', 'ck_age', 'age >= 0')->execute();
$cmd->dropCheck('users', 'ck_age')->execute();
$cmd->addDefaultValue('users', 'df_status', 'status', 'active')->execute();
$cmd->dropDefaultValue('users', 'df_status')->execute();
```

### Comments

[](#comments)

```
$cmd->addCommentOnTable('users', 'Main user accounts table')->execute();
$cmd->addCommentOnColumn('users', 'balance', 'Account balance in cents')->execute();
$cmd->dropCommentFromTable('users')->execute();
$cmd->dropCommentFromColumn('users', 'balance')->execute();
```

### Views

[](#views)

```
$cmd->createView('active_users', (new Query($db))->from('users')->where(['status' => 'active']))->execute();
$cmd->dropView('active_users')->execute();
```

### Sequences and Integrity

[](#sequences-and-integrity)

```
// Reset auto-increment sequence
$cmd->resetSequence('users', 100)->execute(); // Next ID will be 100
$cmd->resetSequence('users')->execute();       // Next ID = max(id) + 1

// Disable/enable foreign key checks (useful for migrations)
$cmd->checkIntegrity('', 'users', false)->execute(); // Disable
$cmd->checkIntegrity('', 'users', true)->execute();  // Enable

// List all databases
$databases = $cmd->showDatabases(); // ['myapp', 'information_schema', ...]
```

Testing
-------

[](#testing)

```
# Start test databases
docker compose up -d

# Run tests
vendor/bin/phpunit

# Static analysis
vendor/bin/phpstan analyse
```

Project Status
--------------

[](#project-status)

AreaStatusMySQL query builderProductionPostgreSQL query builderProductionJSON schema providerProductionQuery profilerProductionIndex hints (MySQL)ProductionPHPStan level max0 errors, no baselineRoadmap
-------

[](#roadmap)

- **Query plan analysis** — `EXPLAIN` integration with automatic slow-query detection
- **Connection-level metrics** — connection pool stats, reconnection tracking
- **Read/write splitting** — automatic routing to read replicas
- **Query result caching** — PSR-16 cache layer with tag-based invalidation
- **MySQL 8.0+ optimizer hints** — `/*+ ... */` comment-style hints beyond index hints
- **Prepared statement caching** — reuse server-side prepared statements across queries

License
-------

[](#license)

BSD-3-Clause. See [LICENSE.md](LICENSE.md).

Attribution
-----------

[](#attribution)

This project is a fork of [yiisoft/db](https://github.com/yiisoft/db) v2.0.1 and [yiisoft/db-mysql](https://github.com/yiisoft/db-mysql), both created by [Yii Software](https://www.yiiframework.com/).

We are deeply grateful to the **Yii framework team and community** for building and open-sourcing an exceptionally well-designed database abstraction layer. The query builder architecture, the Expression/Builder pattern, the condition system, and the overall code quality of yiisoft/db are outstanding work that made this project possible.

Special thanks to:

- **Qiang Xue** ([@qiangxue](https://github.com/qiangxue)) — creator of Yii Framework
- **Alexander Makarov** ([@samdark](https://github.com/samdark)) — long-time Yii core maintainer
- **Sergei Tigrov** ([@Tigrov](https://github.com/Tigrov)) and **Sergei Predvoditelev** ([@vjik](https://github.com/vjik)) — primary maintainers of yiisoft/db who wrote the vast majority of the v2.0 codebase

The Yii project has been a cornerstone of the PHP ecosystem since 2008. If you are looking for a full-featured PHP framework, check out [yiiframework.com](https://www.yiiframework.com/).

Original licenses preserved in [LICENSE.md](LICENSE.md) and [LICENSE-mysql.md](LICENSE-mysql.md).

###  Health Score

40

—

FairBetter than 87% of packages

Maintenance100

Actively maintained with recent releases

Popularity0

Limited adoption so far

Community2

Small or concentrated contributor base

Maturity49

Maturing project, gaining track record

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

Unknown

Total

1

Last Release

1d ago

### Community

Maintainers

![](https://avatars.githubusercontent.com/u/69105849?v=4)[darkspock](/maintainers/darkspock)[@darkspock](https://github.com/darkspock)

---

Tags

schemaprofilerdatabasemysqlpostgresqlsqlpdoquery builderforce index

###  Code Quality

TestsPHPUnit

Static AnalysisPHPStan

Type Coverage Yes

### Embed Badge

![Health badge](/badges/darkspock-just-query/health.svg)

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

###  Alternatives

[doctrine/dbal

Powerful PHP database abstraction layer (DBAL) with many features for database schema introspection and management.

9.7k578.4M5.4k](/packages/doctrine-dbal)[cycle/database

DBAL, schema introspection, migration and pagination

64690.9k27](/packages/cycle-database)[tommyknocker/pdo-database-class

Framework-agnostic PHP database library with unified API for MySQL, MariaDB, PostgreSQL, SQLite, MSSQL, and Oracle. Query Builder, caching, sharding, window functions, CTEs, JSON, migrations, ActiveRecord, CLI tools, AI-powered analysis. Zero external dependencies.

845.7k](/packages/tommyknocker-pdo-database-class)[aura/sqlschema

Provides facilities to read table names and table columns from a database using PDO.

41234.1k4](/packages/aura-sqlschema)[delight-im/db

Safe and convenient SQL database access in a driver-agnostic way

49156.8k7](/packages/delight-im-db)

PHPackages © 2026

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