DbQuery

Write SQL that runs on every database, type-safe, fluent and injection-free.

Introduction

Writing SQL by hand is error-prone. Building SQL by string concatenation is dangerous. And ORMs often abstract so far that you lose control over the generated SQL.

jardissupport/dbquery takes a different approach: a fluent query builder that translates type-safe PHP method calls into correct, dialect-specific SQL. The same builder code generates MySQL, PostgreSQL or SQLite, with all the quirks of each dialect. Prepared statements with correct binding order are the standard, not the exception.

• Multi-dialect — MySQL/MariaDB, PostgreSQL and SQLite from one builder
• SELECT, INSERT, UPDATE, DELETE — four specialized builders with fluent API
• Prepared statements — automatic parameter binding, correct order guaranteed
• CTEs, window functions, JSON conditions — advanced SQL features are covered too
• Subqueries — in SELECT, FROM, WHERE IN and JOINs
• Bracket grouping — arbitrarily nested WHERE conditions
• SQL injection protection — validation and escaping built in

Installation

composer require jardissupport/dbquery

GitHub: jardisSupport/dbquery

AI

AI assistance included

This package ships the support-dbquery skill. Run composer require --dev jardis/dev-skills — and your agent knows the API of jardissupport/dbQuery.

Learn more about Jardis Skills →

Basic Usage

SELECT

use JardisSupport\DbQuery\DbQuery;

$query = (new DbQuery())
    ->select('id, name, email')
    ->from('users')
    ->where('status')->equals('active')
    ->orderBy('name')
    ->limit(10)
    ->sql('mysql', prepared: true);

// Execute with PDO
$stmt = $pdo->prepare($query->sql());
$stmt->execute($query->bindings());

INSERT

use JardisSupport\DbQuery\DbInsert;

$insert = (new DbInsert())
    ->into('users')
    ->fields('name', 'email', 'status')
    ->values('Anna', 'anna@example.com', 'active')
    ->values('Bob', 'bob@example.com', 'pending')
    ->sql('mysql', prepared: true);

UPDATE

use JardisSupport\DbQuery\DbUpdate;

$update = (new DbUpdate())
    ->table('users')
    ->set('status', 'inactive')
    ->set('updated_at', Expression::raw('NOW()'))
    ->where('last_login')->lower('2024-01-01')
    ->sql('mysql', prepared: true);

DELETE

use JardisSupport\DbQuery\DbDelete;

$delete = (new DbDelete())
    ->from('users')
    ->where('status')->equals('banned')
    ->and('created_at')->lower('2023-01-01')
    ->sql('mysql', prepared: true);

SQL Generation

Each builder has a sql() method with three parameters:

->sql(string $dialect, bool $prepared = true, ?string $version = null)

Parameter	Values	Description
$dialect	'mysql', 'mariadb', 'postgres', 'sqlite'	Target database
$prepared	true (default)	true → DbPreparedQuery, false → SQL string (debugging)
$version	null (default)	Database version (e.g. '8.4' for MySQL)

Prepared Statements (Production)

$prepared = $query->sql('mysql', prepared: true);

$prepared->sql();       // "SELECT * FROM `users` WHERE status = ?"
$prepared->bindings();  // ['active']
$prepared->type();      // 'mysql'

// With PDO
$stmt = $pdo->prepare($prepared->sql());
$stmt->execute($prepared->bindings());

Raw SQL (Debugging)

$raw = $query->sql('mysql', prepared: false);
// "SELECT * FROM `users` WHERE status = 'active'"

WHERE Conditions

Comparison Operators

->where('age')->equals(25)              // WHERE age = ?
->where('age')->notEquals(25)           // WHERE age != ?
->where('age')->greater(18)             // WHERE age > ?
->where('age')->greaterEquals(18)       // WHERE age >= ?
->where('age')->lower(65)              // WHERE age < ?
->where('age')->lowerEquals(65)        // WHERE age <= ?

NULL Checks

->where('deleted_at')->isNull()         // WHERE deleted_at IS NULL
->where('email')->isNotNull()          // WHERE email IS NOT NULL

LIKE

->where('name')->like('%anna%')         // WHERE name LIKE ?
->where('email')->notLike('%test%')    // WHERE email NOT LIKE ?

IN / NOT IN

->where('status')->in(['active', 'pending'])     // WHERE status IN (?, ?)
->where('role')->notIn(['banned', 'suspended'])  // WHERE role NOT IN (?, ?)

// Empty array: safe behavior
->where('id')->in([])                            // WHERE 1=0 (always false)
->where('id')->notIn([])                         // WHERE 1=1 (always true)

BETWEEN

->where('age')->between(18, 65)         // WHERE age BETWEEN ? AND ?
->where('age')->notBetween(18, 65)     // WHERE age NOT BETWEEN ? AND ?

AND / OR Chaining

->where('status')->equals('active')
->and('age')->greater(18)               // AND age > ?
->or('role')->equals('admin')           // OR role = ?

Grouping with Brackets

Opening brackets are passed as a parameter to where/and/or, closing brackets to the operator:

// WHERE (status = ? AND age > ?) OR role = ?
->where('(status')->equals('active')
->and('age')->greater(18, ')')
->or('role')->equals('admin')

// WHERE status = ? AND (role = ? OR role = ?)
->where('status')->equals('active')
->and('(role')->equals('admin')
->or('role')->equals('moderator', ')')

EXISTS / NOT EXISTS

$sub = (new DbQuery())
    ->select('1')
    ->from('orders', 'o')
    ->where('o.user_id')->equals(Expression::raw('u.id'));

(new DbQuery())
    ->select('*')
    ->from('users', 'u')
    ->where('u.active')->equals(true)
    ->exists($sub)
    // WHERE u.active = ? AND EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id)

Expressions — Raw SQL

For column references, function calls and calculated values that should not be bound as parameters:

use JardisSupport\DbQuery\Data\Expression;

// As field in WHERE
->where(Expression::raw('LOWER(name)'))->equals('anna')
->where(Expression::raw('YEAR(created_at)'))->equals(2024)
->where(Expression::raw('price * quantity'))->greater(1000)

// As value (NOT escaped)
->where('price')->greater(Expression::raw('cost * 1.2'))
->where('updated_at')->equals(Expression::raw('NOW()'))

// In UPDATE SET
->set('counter', Expression::raw('counter + 1'))
->set('updated_at', Expression::raw('NOW()'))

// In INSERT
$insert->set(['updated_at' => Expression::raw('NOW()')]);

For trusted values only

Expression content is not escaped. Never use user input in Expression::raw().

JOINs

All JOIN Types

->innerJoin('orders o', 'u.id = o.user_id')
->leftJoin('profiles', 'u.id = p.user_id', 'p')     // separate alias
->rightJoin('departments d', 'u.dept_id = d.id')
->fullJoin('archive a', 'u.id = a.user_id')          // PostgreSQL only
->crossJoin('settings')                               // CROSS JOIN without ON

Subquery JOINs

$orderStats = (new DbQuery())
    ->select('user_id, COUNT(*) as order_count, SUM(total) as revenue')
    ->from('orders')
    ->where('status')->equals('completed')
    ->groupBy('user_id');

(new DbQuery())
    ->select('u.name, s.order_count, s.revenue')
    ->from('users', 'u')
    ->leftJoin($orderStats, 'u.id = s.user_id', 's')

Dialect Restrictions

JOIN type	SELECT	UPDATE	DELETE
INNER JOIN	All	MySQL only	MySQL only
LEFT JOIN	All	MySQL only	MySQL only
RIGHT JOIN	All	—	—
FULL OUTER JOIN	PostgreSQL only	—	—
CROSS JOIN	All	—	—

Subqueries

FROM Subquery

$sub = (new DbQuery())
    ->select('dept, AVG(salary) as avg_salary')
    ->from('employees')
    ->groupBy('dept');

(new DbQuery())
    ->select('*')
    ->from($sub, 'dept_stats')
    ->where('dept_stats.avg_salary')->greater(50000)

SELECT Subquery (correlated)

$postCount = (new DbQuery())
    ->select('COUNT(*)')
    ->from('posts', 'p')
    ->where('p.user_id')->equals(Expression::raw('u.id'));

(new DbQuery())
    ->select('u.id, u.name')
    ->selectSubquery($postCount, 'post_count')
    ->from('users', 'u')
// → SELECT u.id, u.name, (SELECT COUNT(*) FROM posts p WHERE p.user_id = u.id) AS `post_count`

WHERE IN Subquery

$activeUserIds = (new DbQuery())
    ->select('user_id')
    ->from('orders')
    ->where('status')->equals('completed');

(new DbQuery())
    ->select('*')
    ->from('users')
    ->where('id')->in($activeUserIds)
// → WHERE id IN (SELECT user_id FROM orders WHERE status = ?)

CTEs (Common Table Expressions)

CTEs make complex queries readable by defining named intermediate results.

Simple CTE

$activeUsers = (new DbQuery())
    ->select('id, name, dept')
    ->from('users')
    ->where('status')->equals('active');

(new DbQuery())
    ->with('active', $activeUsers)
    ->select('dept, COUNT(*) as cnt')
    ->from('active')
    ->groupBy('dept')
    ->sql('mysql', prepared: true);
// → WITH `active` AS (SELECT id, name, dept FROM `users` WHERE status = ?)
//   SELECT dept, COUNT(*) as cnt FROM `active` GROUP BY dept

Multiple CTEs

->with('cte1', $query1)
->with('cte2', $query2)    // can reference cte1
->select('*')
->from('cte2')

Recursive CTEs

Ideal for tree structures (categories, org charts, bill of materials):

$recursive = (new DbQuery())
    ->select('id, parent_id, name, 0 as level')
    ->from('categories')
    ->where('parent_id')->isNull()
    ->union(
        (new DbQuery())
            ->select('c.id, c.parent_id, c.name, tree.level + 1')
            ->from('categories', 'c')
            ->innerJoin('category_tree', 'c.parent_id = tree.id', 'tree')
    );

(new DbQuery())
    ->withRecursive('category_tree', $recursive)
    ->select('*')
    ->from('category_tree')
    ->orderBy('level')

Binding order

CTE bindings are merged before the bindings of the main query. This is automatically correct. No manual sorting needed.

Window Functions

For ranking, running totals, comparisons with previous/next rows, without GROUP BY.

Inline Window

(new DbQuery())
    ->select('id, name, salary')
    ->selectWindow('ROW_NUMBER', 'rank')
        ->partitionBy('department')
        ->windowOrderBy('salary', 'DESC')
        ->endWindow()
    ->from('employees')

Generates: ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rank

With Arguments

// SUM with field
->selectWindow('SUM', 'running_total', 'amount')
    ->windowOrderBy('date')
    ->endWindow()
// → SUM(amount) OVER (ORDER BY date ASC) AS `running_total`

// LAG with multiple arguments
->selectWindow('LAG', 'prev_price', 'price, 1')
    ->windowOrderBy('date')
    ->endWindow()
// → LAG(price, 1) OVER (ORDER BY date ASC) AS `prev_price`

Frame Specification

->selectWindow('SUM', 'moving_avg', 'price')
    ->windowOrderBy('date')
    ->frame('ROWS', '2 PRECEDING', 'CURRENT ROW')
    ->endWindow()
// → SUM(price) OVER (ORDER BY date ASC ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)

Named Windows

When multiple functions share the same window:

(new DbQuery())
    ->select('id, name, salary')
    ->window('dept_window')
        ->partitionBy('department')
        ->windowOrderBy('salary', 'DESC')
        ->endWindow()
    ->selectWindowRef('ROW_NUMBER', 'dept_window', 'rank')
    ->selectWindowRef('DENSE_RANK', 'dept_window', 'dense_rank')
    ->from('employees')
// → ... WINDOW dept_window AS (PARTITION BY department ORDER BY salary DESC)

JSON Conditions

For queries on JSON/JSONB columns, automatically translated to dialect-specific SQL.

Extract JSON Value

->whereJson('metadata')->extract('$.priority')->equals('high')
->andJson('settings')->extract('$.theme.color')->notEquals('red')

Dialect	Generated SQL
MySQL	JSON_EXTRACT(metadata, '$.priority') = ?
PostgreSQL	metadata->>'priority' = ?
SQLite	JSON_EXTRACT(metadata, '$.priority') = ?

JSON Containment

->whereJson('tags')->contains('php')
->andJson('tags')->notContains('deprecated')
->whereJson('data')->contains('value', '$.nested.path')  // with path

JSON Array Length

->whereJson('items')->length()->greater(3)
->whereJson('data')->length('$.nested.array')->equals(0)

HAVING with JSON

->groupBy('user_id')
->havingJson('metadata')->extract('$.priority')->equals('high')

INSERT — Advanced Features

Multi-Row Insert

(new DbInsert())
    ->into('products')
    ->fields('sku', 'name', 'price')
    ->values('A-001', 'Widget', 9.99)
    ->values('A-002', 'Gadget', 19.99)
    ->values('A-003', 'Gizmo', 29.99)

INSERT with set() (associative)

(new DbInsert())
    ->into('users')
    ->set(['name' => 'Anna', 'email' => 'anna@example.com', 'status' => 'active'])

INSERT...SELECT

$activeUsers = (new DbQuery())
    ->select('name, email')
    ->from('users')
    ->where('status')->equals('active');

(new DbInsert())
    ->into('newsletter_subscribers')
    ->fields('name', 'email')
    ->fromSelect($activeUsers)

Upsert (Conflict Handling)

MySQL / MariaDB:

->onDuplicateKeyUpdate('price', Expression::raw('VALUES(price)'))
->onDuplicateKeyUpdate('stock', 42)

PostgreSQL:

->onConflict('sku')
->doUpdate(['price' => 8.99, 'name' => 'Updated Widget'])
// or:
->onConflict('sku')
->doNothing()

SQLite:

->orIgnore()   // INSERT OR IGNORE INTO
->replace()    // REPLACE INTO

UPDATE — Advanced Features

SET with Subquery

(new DbUpdate())
    ->table('products')
    ->set('avg_rating', (new DbQuery())
        ->select('AVG(rating)')
        ->from('reviews', 'r')
        ->where('r.product_id')->equals(Expression::raw('products.id'))
    )

UPDATE with JOIN (MySQL only)

(new DbUpdate())
    ->table('users', 'u')
    ->innerJoin('orders o', 'u.id = o.user_id')
    ->set('u.status', 'premium')
    ->where('o.total')->greater(1000)

UPDATE IGNORE (MySQL only)

(new DbUpdate())
    ->table('users')
    ->ignore()
    ->set('email', 'new@example.com')
    ->where('id')->equals(42)

DELETE — Advanced Features

DELETE with JOIN (MySQL only)

(new DbDelete())
    ->from('users', 'u')
    ->innerJoin('banned_emails b', 'u.email = b.email')
    ->where('u.created_at')->lower('2020-01-01')

GROUP BY, ORDER BY, LIMIT

// GROUP BY (variadic)
->groupBy('department', 'status')

// HAVING (SELECT only)
->having('COUNT(*)')->greater(5)

// ORDER BY (callable multiple times)
->orderBy('name')                    // ASC is default
->orderBy('created_at', 'DESC')

// LIMIT + OFFSET
->limit(10)                          // LIMIT 10
->limit(10, 20)                      // LIMIT 10 OFFSET 20

UNION

$active = (new DbQuery())->select('id, name')->from('users')->where('status')->equals('active');
$admins = (new DbQuery())->select('id, name')->from('admins');

// Deduplicated
$active->union($admins)

// All rows
$active->unionAll($admins)

Dialect Differences

Not all SQL features are available in all databases. The builder throws InvalidArgumentException when a feature is not supported in the chosen dialect.

Feature	MySQL/MariaDB	PostgreSQL	SQLite
FULL OUTER JOIN	—	✓	—
UPDATE/DELETE with JOIN	✓	—	—
UPDATE/DELETE ORDER BY + LIMIT	✓	—	—
UPDATE IGNORE	✓	—	—
ON DUPLICATE KEY UPDATE	✓	—	—
ON CONFLICT DO UPDATE/NOTHING	—	✓	✓
REPLACE INTO	✓	—	✓
Window functions	✓	✓	✓
CTEs (WITH)	✓	✓	✓
JSON conditions	✓	✓	✓
Boolean literals	1/0	TRUE/FALSE	1/0
Identifier quoting	`backtick`	"double"	`backtick`

Architecture

The package follows the Closure-Orchestrator-Pattern with a clear separation between builder (fluent API), state (data) and SQL generation:

DbQuery / DbInsert / DbUpdate / DbDelete    ← Fluent API (entry points)
├── QueryState / InsertState / ...           ← State objects (collecting data)
├── QueryCondition / QueryJsonCondition      ← Condition builder (WHERE/HAVING)
└── SqlBuilderFactory                        ← Creates dialect-specific SQL builders
    ├── MySql / PostgresSql / SqliteSql      ← SELECT SQL generation
    ├── InsertMySql / InsertPostgresSql / ...← INSERT SQL generation
    ├── UpdateMySql / UpdatePostgresSql / ...← UPDATE SQL generation
    └── DeleteMySql / DeletePostgresSql / ...← DELETE SQL generation

Directory Structure

src/
├── DbQuery.php                     ← SELECT builder
├── DbInsert.php                    ← INSERT builder
├── DbUpdate.php                    ← UPDATE builder
├── DbDelete.php                    ← DELETE builder
├── Command/
│   ├── Delete/                     ← DELETE SQL generation
│   │   ├── DeleteMySql.php
│   │   ├── DeletePostgresSql.php
│   │   └── DeleteSqliteSql.php
│   ├── Insert/                     ← INSERT SQL generation
│   │   ├── InsertMySql.php
│   │   ├── InsertPostgresSql.php
│   │   ├── InsertSqliteSql.php
│   │   └── Method/                 ← INSERT extensions (upsert etc.)
│   └── Update/                     ← UPDATE SQL generation
│       ├── UpdateMySql.php
│       ├── UpdatePostgresSql.php
│       ├── UpdateSqliteSql.php
│       └── Method/
├── Data/
│   ├── Contract/                   ← Internal state interfaces
│   │   ├── FromStateInterface.php
│   │   ├── JoinStateInterface.php
│   │   ├── LimitStateInterface.php
│   │   └── OrderByStateInterface.php
│   ├── Dialect.php                 ← Enum: mysql, mariadb, postgres, sqlite
│   ├── Expression.php              ← Raw SQL expressions
│   ├── DbPreparedQuery.php         ← Prepared statement (SQL + bindings)
│   ├── QueryResult.php             ← SELECT result
│   ├── ExecuteResult.php           ← INSERT/UPDATE/DELETE result
│   ├── QueryState.php              ← SELECT state
│   ├── InsertState.php             ← INSERT state
│   ├── UpdateState.php             ← UPDATE state
│   ├── DeleteState.php             ← DELETE state
│   ├── WindowSpec.php              ← Window definition
│   ├── WindowFunction.php          ← Inline window function
│   └── WindowReference.php         ← Named window reference
├── Query/
│   ├── SqlBuilder.php              ← Base SQL generation
│   ├── MySql.php                   ← MySQL-specific
│   ├── PostgresSql.php             ← PostgreSQL-specific
│   ├── Condition/
│   │   ├── QueryCondition.php      ← Standard operators
│   │   └── QueryJsonCondition.php  ← JSON operators
│   └── Builder/                    ← Clause builders (invokables)
│       ├── Clause/                 ← SQL clause builders (SELECT, FROM, JOIN, ...)
│       ├── Condition/              ← Condition resolution and validation
│       ├── Method/                 ← Fluent API methods (Where, OrderBy, ...)
│       └── Window/                 ← Window function builder
└── Factory/
    ├── SqlBuilderFactory.php       ← Dialect dispatch
    └── BuilderRegistry.php         ← Singleton cache + version overrides

API Reference

DbPreparedQuery

Method	Return	Description
sql()	string	SQL with ? placeholders
bindings()	array	Parameter values in correct order
type()	string	Dialect ('mysql', 'postgres', ...)

Dialect (Enum)

Method	Description
Dialect::fromString('mysql')	String → enum (throws on invalid value)
Dialect::tryFromString('mysql')	String → enum or null
->defaultVersion()	Default version ('8.0', '14', ...)
->supportedVersions()	All supported versions

QueryResult / ExecuteResult

// SELECT result
$result->fetchAll();    // array<int, array<string, mixed>>
$result->fetchOne();    // ?array<string, mixed>
$result->rowCount();    // int

// INSERT/UPDATE/DELETE result
$result->affectedRows();   // int
$result->lastInsertId();   // string|false

Complete Example

A realistic reporting query with CTEs, window functions, subqueries and JSON:

use JardisSupport\DbQuery\DbQuery;
use JardisSupport\DbQuery\Data\Expression;

// CTE: monthly revenue per customer
$monthlySales = (new DbQuery())
    ->select('customer_id, DATE_FORMAT(order_date, "%Y-%m") as month, SUM(total) as revenue')
    ->from('orders')
    ->where('status')->equals('completed')
    ->and('order_date')->greaterEquals('2024-01-01')
    ->groupBy('customer_id', 'DATE_FORMAT(order_date, "%Y-%m")');

// Main query with window function
$prepared = (new DbQuery())
    ->with('monthly', $monthlySales)
    ->select('m.customer_id, c.name, m.month, m.revenue')
    ->selectWindow('SUM', 'running_total', 'm.revenue')
        ->partitionBy('m.customer_id')
        ->windowOrderBy('m.month')
        ->endWindow()
    ->selectWindow('LAG', 'prev_month_revenue', 'm.revenue, 1')
        ->partitionBy('m.customer_id')
        ->windowOrderBy('m.month')
        ->endWindow()
    ->from('monthly', 'm')
    ->innerJoin('customers c', 'm.customer_id = c.id')
    ->whereJson('c.settings')->extract('$.tier')->in(['gold', 'platinum'])
    ->and('m.revenue')->greater(1000)
    ->orderBy('m.customer_id')
    ->orderBy('m.month')
    ->sql('mysql', prepared: true);

$stmt = $pdo->prepare($prepared->sql());
$stmt->execute($prepared->bindings());