Download the PHP package jardissupport/dbquery without Composer

Jardis DbQuery

Part of Jardis — the Domain-Driven Design platform for PHP. You model your domain; Jardis generates the production-ready hexagonal code (DTOs, Command/Query handlers, repositories, persistence). This package is part of the open-source foundation that generated code runs on.

A fluent SQL query builder for PHP that generates dialect-aware SQL for MySQL, MariaDB, PostgreSQL, and SQLite. Full support for CTEs, window functions, subqueries, JSON columns, and prepared statements. SQL injection protection built in.

Features

• Dialect-Aware SQL — generates correct syntax for MySQL, MariaDB, PostgreSQL, and SQLite from a single builder
• Identifier Auto-Quoting — simple identifiers (ident, alias.ident) in WHERE/HAVING/ORDER BY/GROUP BY/SELECT are quoted per dialect; expressions stay raw (see Identifier Auto-Quoting)
• CTEs — with() and withRecursive() for common table expressions
• Window Functions — selectWindow(), window(), and selectWindowRef() for analytics queries
• Subqueries — subqueries in FROM, JOIN constraints, SELECT columns, and WHERE EXISTS / NOT EXISTS
• JSON Column Support — whereJson(), andJson(), orJson(), havingJson() for structured JSON field conditions
• Union / Union All — union() and unionAll() compose multiple SELECT statements
• Prepared Statements — sql($dialect, prepared: true) returns a DbPreparedQueryInterface with bound parameters
• SQL Injection Validation — bracket and expression validation built into sql() before generation
• INSERT Conflict Handling — DbInsert supports ON DUPLICATE KEY (MySQL) and ON CONFLICT (PostgreSQL)

Installation

Quick Start

Identifier Auto-Quoting

Simple identifiers are quoted automatically with the dialect's identifier quoting — MySQL/MariaDB/SQLite use backticks, PostgreSQL uses double quotes. This makes case-sensitive column names (e.g. createdAt from quoted DDL) work on PostgreSQL, which folds unquoted identifiers to lowercase (error 42703 before).

What is quoted — a string is a simple identifier iff it matches ^[A-Za-z_][A-Za-z0-9_]*(\.[A-Za-z_][A-Za-z0-9_]*)?$ (ident or alias.ident). Simple identifiers are quoted in these positions:

• where() / and() / or() condition fields
• having() fields
• orderBy() fields
• groupBy() columns
• the select() field list (per comma-separated item; for expr AS alias the expr and the alias are each quoted when they are simple identifiers)

What stays raw (byte-identical) — everything that is not a simple identifier:

• SQL literals and niladic functions that would otherwise match the pattern (case-insensitive): NULL, TRUE, FALSE, DEFAULT, CURRENT_TIMESTAMP, CURRENT_DATE, CURRENT_TIME, LOCALTIME, LOCALTIMESTAMP, CURRENT_USER, SESSION_USER — so UNION padding like select('id, NULL AS email') keeps NULL raw. A column literally named null must be passed pre-quoted (`null`/"null") or via Expression::raw('"null"'); qualified names (t.null) are always treated as identifiers.
• expressions and functions (YEAR(created), price * 1.19, COUNT(*))
• * and alias.*
• already quoted strings (`createdAt`, "createdAt")
• Expression::raw(...) — the explicit escape hatch: even a simple identifier inside Expression::raw() is never quoted
• JOIN ON constraints, window specifications (partitionBy(), windowOrderBy()) and CTE inner SQL other than what the inner builder itself quotes

Boundary — quoting makes the written identifier case-significant on PostgreSQL. The name you pass must match the DDL exactly when the DDL was quoted, or be all-lowercase when the DDL was unquoted. Declare aliases with an explicit AS (COUNT(*) AS orderCount) so alias definition and alias references are quoted consistently; use Expression::raw() where the raw string is required.

Advanced Usage

Upsert in every dialect

onDuplicateKeyUpdate() renders the same upsert on every dialect:

PostgreSQL and SQLite need a conflict target: call onConflict('col', ...) first. Without it sql() throws an InvalidArgumentException instead of silently dropping the clause. MySQL/MariaDB ignore the target, so one call chain is valid on all four dialects. onConflict()->doUpdate([...]) stays available and feeds the same clause on PostgreSQL/SQLite (both entry points merge; onDuplicateKeyUpdate() wins per column).

Literal substring search (LIKE with ESCAPE)

like() / notLike() take a pattern as given: % and _ in a user's search term act as wildcards. For a search for a literal term pass a LikePattern:

%, _ and the escape character ! inside the term are escaped in one place (LikePattern); the clause ESCAPE '!' is identical on MySQL, MariaDB, PostgreSQL and SQLite. Works in where/and/or/having and for SELECT, UPDATE and DELETE.

LikePattern implements JardisSupport\Contract\DbQuery\LikePatternInterface from jardissupport/contracts: code typed against the contracts interfaces passes it to like() / notLike() without a static-analysis error.

Documentation

Full documentation, guides, and API reference:

docs.jardis.io/en/support/dbquery

License

This package is licensed under the MIT License.

Jardis · Documentation · Headgent

AI-Assisted Development

This package ships with a skill for Claude Code, Cursor, Continue, and Aider. Install it in your consuming project:

More details: https://docs.jardis.io/en/skills