PHPackages                             devsrealm/tonics-query-builder - 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. devsrealm/tonics-query-builder

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

devsrealm/tonics-query-builder
==============================

Tonics SQL Query Builder is a library for building SQL in a modular and a hookable manner

1.0.6(8mo ago)13.5k↓53%MITPHP

Since Nov 11Pushed 8mo ago1 watchersCompare

[ Source](https://github.com/devsrealm/tonics-query-builder)[ Packagist](https://packagist.org/packages/devsrealm/tonics-query-builder)[ RSS](/packages/devsrealm-tonics-query-builder/feed)WikiDiscussions master Synced yesterday

READMEChangelog (7)Dependencies (2)Versions (8)Used By (0)

Tonics Query Builder
====================

[](#tonics-query-builder)

A small, hookable SQL query builder with pluggable transformers for different databases. This README shows accurate usage based on the Postgres transformer spec and tests.

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

[](#installation)

Install via Composer:

```
composer require devsrealm/tonics-query-builder
```

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

[](#requirements)

- PHP 7.4+
- PDO extension
- A PDO driver for your database (pgsql for PostgreSQL, mysql for MySQL/MariaDB)

Quick Start (PostgreSQL)
------------------------

[](#quick-start-postgresql)

```
use Devsrealm\TonicsQueryBuilder\TonicsQueryBuilder;
use Devsrealm\TonicsQueryBuilder\Transformers\Postgres\PostgresTonicsQueryTransformer;
use Devsrealm\TonicsQueryBuilder\Transformers\Postgres\PostgresTables;

$pdo = new PDO('pgsql:host=localhost;port=5432;dbname=yourdb', 'user', 'pass', [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
]);

$tables = (new PostgresTables())
    ->addTable('users', ['id','username','email','created_at','logins'])
    ->addTable('posts', ['id','user_id','title','content','metadata','created_at']);

$qb = new TonicsQueryBuilder($pdo, new PostgresTonicsQueryTransformer(), $tables);

// Build and execute a SELECT with date filter, LIKE, order, limit/offset
$q = $qb->getNewQuery();
$users = $q->Select($tables->pickTable('users', ['id','username']))
    ->From($tables->getTable('users'))
    ->WhereDate('created_at', '>=', '2025-01-01')
    ->WhereLike('username', 'ali')
    ->OrderByAsc('"username"')
    ->Take(10)
    ->Skip(0)
    ->FetchResult();
```

### Date/Time

[](#datetime)

```
// CAST for date/time filters and TO_CHAR for formatting
$q = $qb->getNewQuery();
$q->Select()->DateFormat('created_at', '%Y-%m-%d %H:%i:%s')->As('formatted_date')
    ->From('users')
    ->WhereTime('created_at', '>=', '10:00:00');

$row = $q->FetchFirst();
```

### String pattern matching

[](#string-pattern-matching)

```
$q = $qb->getNewQuery();
$q->Select('username')
    ->From('users')
    ->WhereLike('username', 'john')   // username LIKE '%john%'
    ->WhereStarts('email', 'admin')   // email LIKE 'admin%'
    ->WhereEnds('username', '123');   // username LIKE '%123'

$rows = $q->FetchResult();
```

### JSON helpers (jsonb)

[](#json-helpers-jsonb)

```
// Extract JSON field
$q = $qb->getNewQuery();
$q->Select('title')
  ->Select($q->Q()->JsonExtract('metadata', 'author'))->As('author')
  ->From('posts')
  ->Where('title', '=', 'First Post');
$first = $q->FetchFirst();

// Nested path and containment filter
$q = $qb->getNewQuery();
$posts = $q->Select('title')
    ->From('posts')
    ->WhereJsonContains('metadata', 'tags', '["featured"]', '$.')
    ->OrderByAsc('"title"')
    ->FetchResult();

// Update/merge/remove JSON
$q = $qb->getNewQuery();
$q->Update('posts')
  ->Set('metadata', $q->Q()->JsonSet('metadata', 'author', '"Updated Author"'))
  ->Where('title', '=', 'First Post')
  ->Exec();

$q = $qb->getNewQuery();
$q->Select()->JsonMergePatch('metadata', '{"new_field": "value"}');
$sql = $q->getSqlString();
```

### Upsert (ON CONFLICT)

[](#upsert-on-conflict)

```
$q = $qb->getNewQuery();
$data = [
  ['username' => 'alice', 'email' => 'alice@example.com', 'logins' => 1],
  ['username' => 'bob',   'email' => 'bob@example.com',   'logins' => 1],
];

// Specify conflict columns
$q->InsertOnDuplicate('users', $data, [
  'conflict' => ['username'],
  'set' => ['email', 'logins']
]);

// Or, specify a constraint name
$q->InsertOnDuplicate('users', $data, [
  'constraint' => 'users_pkey',
  'set' => ['email', 'logins']
]);

// Batch large datasets with chunk size (e.g., 50)
$q->InsertOnDuplicate('users', $data, [
  'conflict' => ['username'],
  'set' => ['email']
], 50);
```

### Null-safe equals

[](#null-safe-equals)

```
// MySQL `` is transformed to Postgres `IS NOT DISTINCT FROM`
$q = $qb->getNewQuery();
$withNullEmails = $q->Select('username')
  ->From('users')
  ->Where('email', '', null)
  ->OrderByAsc('"username"')
  ->FetchResult();
```

### Raw queries with PostgreSQL-style placeholders

[](#raw-queries-with-postgresql-style-placeholders)

If you need to execute raw SQL with PostgreSQL's native `$1, $2, $3` placeholder syntax (instead of PDO's `?`), use the `runPg()` or `rowPg()` methods:

```
// Execute with multiple results
$q = $qb->getNewQuery();
$result = $q->runPg(
    "SELECT EXISTS(SELECT 1 FROM migrations WHERE migration = $1) AS result",
    $migrationName
);

// Execute and get single row
$q = $qb->getNewQuery();
$user = $q->rowPg(
    "SELECT * FROM users WHERE id = $1 AND status = $2",
    123,
    'active'
);
```

These methods automatically convert PostgreSQL-style placeholders to PDO format before execution.

### Executing multiple SQL statements

[](#executing-multiple-sql-statements)

For migrations, schema creation, or running SQL scripts with multiple statements separated by semicolons, use `execRaw()`:

```
$q = $qb->getNewQuery();
$q->execRaw(=', '2025-01-01')
    ->OrderByAsc('`username`')
    ->Take(10)
    ->FetchResult();
```

Testing
-------

[](#testing)

This repo ships with Kahlan specs. To run tests (requires a local PostgreSQL and PHP):

- Windows (cmd.exe):

```
vendor\bin\kahlan.bat
```

- Linux/macOS:

```
vendor/bin/kahlan
```

See `TESTING.md` for environment variables and setup details.

License
-------

[](#license)

MIT

###  Health Score

37

—

LowBetter than 81% of packages

Maintenance62

Regular maintenance activity

Popularity23

Limited adoption so far

Community7

Small or concentrated contributor base

Maturity44

Maturing project, gaining track record

 Bus Factor1

Top contributor holds 100% 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 ~59 days

Recently: every ~38 days

Total

7

Last Release

241d ago

### Community

Maintainers

![](https://www.gravatar.com/avatar/0cf8f4cd756c2ce702e44d7e0542ca9e5d2cd9f4cf28fb3759081d19fe727894?d=identicon)[Tonics](/maintainers/Tonics)

---

Top Contributors

[![devsrealm](https://avatars.githubusercontent.com/u/37757164?v=4)](https://github.com/devsrealm "devsrealm (10 commits)")

### Embed Badge

![Health badge](/badges/devsrealm-tonics-query-builder/health.svg)

```
[![Health](https://phpackages.com/badges/devsrealm-tonics-query-builder/health.svg)](https://phpackages.com/packages/devsrealm-tonics-query-builder)
```

###  Alternatives

[jdorn/sql-formatter

a PHP SQL highlighting library

3.9k117.2M118](/packages/jdorn-sql-formatter)[propel/propel1

Propel is an open-source Object-Relational Mapping (ORM) for PHP5.

8351.6M87](/packages/propel-propel1)[jfelder/oracledb

Oracle DB driver for Laravel

11518.4k](/packages/jfelder-oracledb)

PHPackages © 2026

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