README

Lightweight SQL query builder for PHP 8.1+, with zero mandatory dependencies (except the PDO extension).

Background: miGears is the open-source successor of TinyGears, a self-developed PHP framework. It was renamed and open-sourced recently because the name TinyGears is already taken in the open-source community.

Features

Minimalist API: $sql->select()->from('users')->filter(['status' => 1])->execute()
Zero global dependencies: accepts a PDO instance in the constructor, ready to use after new
Pure array returns: no object mapping, simple and straightforward
PSR-3 logging: a LoggerInterface is required and injected through the constructor
Domain exceptions: SqlException / RecordNotFoundException
Single file < 300 lines: every core file is short and easy to understand at a glance
High test coverage: integration tests with SQLite in-memory database

Boundaries

In scope

Building and executing SQL over a borrowed PDO: select() / insert() / update() / delete(), where() / filter() / bind() / orderBy() / limit() / groupBy(), single() / singleOrFail() / count() / paginate().
Returning rows as plain arrays, and naming failures as SqlException / RecordNotFoundException.

Not in scope (by design)

Owning or opening a connection — a PDO instance is passed in; this package never connects and holds no configuration.
Domain objects, mapping and hydration — migears/dao turns rows into Domains; this layer knows nothing about them.
Schema, migrations, transactions, connection pooling, or an ORM.
Filtering on qualified column names — filter() takes unqualified names only; use where() for JOIN queries.

Installation

composer require migears/sql

Requires: PHP 8.1+, PDO extension.

Quick Start

use MiGears\Sql\SqlBuilder;

$pdo = new PDO('mysql:host=localhost;dbname=app', 'user', 'pass');
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

// The logger is required and must be supplied by the caller.
$sql = new SqlBuilder($pdo, $logger);

SELECT

// Fetch all
$rows = $sql->select()->from('users')->execute();

// Specify columns + conditions
$rows = $sql->select(['id', 'name'])
    ->from('users')
    ->where('status = :status', ['status' => 1])
    ->orderBy('id DESC')
    ->limit(10)
    ->execute();

// filter style (key names with operator suffixes)
$rows = $sql->select()
    ->from('users')
    ->filter(['status' => 1, 'age>' => 18])
    ->execute();

// Single row
$user = $sql->select()->from('users')->filter(['id' => 1])->single();

// Single row, throws exception if not found
$user = $sql->select()->from('users')->filter(['id' => 1])->singleOrFail();

// Count
$count = $sql->select()->from('users')->filter(['status' => 1])->count();

// Group by
$rows = $sql->select(['status', 'COUNT(*) as cnt'])
    ->from('users')
    ->groupBy('status')
    ->execute();

// Separate bind() calls (equivalent to passing params to where())
$rows = $sql->select()
    ->from('users')
    ->where('status = :status AND age > :age')
    ->bind(['status' => 1])
    ->bind(['age' => 18])
    ->execute();

// Pagination
$result = $sql->select()->from('users')->paginate(1, 20);
// ['records' => [...], 'total' => 100]

Two degenerate inputs are refused instead of being handed to PDO, because each used to surface as a driver error that named the SQL rather than the call:

offset() without limit(). OFFSET n on its own is a syntax error on SQLite and MySQL, so toSql() throws SqlException (offset() requires limit()). Call limit() as well, or use paginate().
A condition that contributes no SQL while carrying bindings. where('', ['x' => 1]), and a bare bind() with no condition at all, throw SqlException naming the parameter that had no placeholder to land on. where('') on its own is still a legitimate way to clear a condition.

filter Operator Suffixes

Suffix	Operator	Example	Generated SQL
(none)	=	`['status' => 1]`	`status` = :f_status
`>`	>	`['age>' => 18]`	`age` > :f_age
`<`	<	`['age<' => 30]`	`age` < :f_age
`>=`	>=	`['age>=' => 18]`	`age` >= :f_age
`<=`	<=	`['age<=' => 30]`	`age` <= :f_age
`!` / `<>` / `><`	!=	`['status!' => 0]`	`status` != :f_status

Filter placeholders are named f_{field}, gaining a _2, _3, ... suffix when two conditions target the same field, so they never collide with where() / bind() parameters. filter() accepts unqualified column names only — ['u.id' => 1] is rejected; use where() for qualified names in JOIN queries.

Multi-Table / JOIN Queries

from() accepts any valid SQL table clause, including JOIN syntax and comma-separated tables. Use where() for join conditions.

// INNER JOIN via from()
$rows = $sql->select(['u.name', 'p.title'])
    ->from('users u INNER JOIN posts p ON u.id = p.user_id')
    ->where('u.status = :status', ['status' => 1])
    ->execute();

// LEFT JOIN
$rows = $sql->select(['u.name', 'p.title'])
    ->from('users u LEFT JOIN posts p ON u.id = p.user_id')
    ->execute();

// Comma-separated (implicit join)
$rows = $sql->select(['u.name', 'p.title'])
    ->from('users u, posts p')
    ->where('u.id = p.user_id', [])
    ->execute();

Design rationale: No dedicated join() methods — from() is flexible enough for any SQL syntax, keeping the API minimal.

from() is passed through verbatim, so it is neither quoted nor validated: treat it as a developer-controlled SQL fragment. The table name handed to insert(), update() and delete() is different — it is wrapped in backticks and must be a bare identifier (^\w+$), otherwise SqlException is thrown. Column names given to values() / set() follow the same rule.

INSERT

$id = $sql->insert('users')
    ->values(['name' => 'Alice', 'email' => 'alice@example.com'])
    ->execute()
    ->lastInsertId();

UPDATE

$affected = $sql->update('users')
    ->set(['name' => 'Bob', 'age' => 30])
    ->filter(['id' => 1])
    ->execute();

// Raw SET expression
$sql->update('users')
    ->set('counter = counter + 1')
    ->filter(['id' => 1])
    ->execute();

Like delete(), update() refuses to run without a condition, so update('users')->set([...])->execute() cannot rewrite every row by accident and throws SqlException. To update every row on purpose, pass an explicit condition:

$affected = $sql->update('users')->set(['status' => 0])->where('1 = 1')->execute();

The array and string forms of set() are mutually exclusive: the last call wins, so a raw expression and a value array never mix.

DELETE

$affected = $sql->delete('users')
    ->filter(['id' => 1])
    ->execute();

delete() refuses to run without a condition, so an accidental bare delete('users')->execute() cannot empty a table and throws SqlException instead. To delete every row on purpose, pass an explicit condition:

$affected = $sql->delete('users')->where('1 = 1')->execute();

Architecture

The SQL module is the bottom layer of miGears' three-layer data architecture:

Service Layer (business logic)
    ↓ calls
DAO Layer (receives/returns Domain objects) → migears/dao
    ↓ internally calls
SQL Layer (SQL + params → arrays) → migears/sql (this package)
    ↓
PDO / MySQL

The SQL layer knows nothing about Domain objects. It only executes SQL and returns arrays.

Logging

Inject any PSR-3 Logger implementation:

use Monolog\Logger;
use Monolog\Handler\StreamHandler;

$logger = new Logger('sql');
$logger->pushHandler(new StreamHandler('php://stdout'));

$sql = new SqlBuilder($pdo, $logger);

Exceptions

use MiGears\Sql\Exception\SqlException;
use MiGears\Sql\Exception\RecordNotFoundException;

try {
    $user = $sql->select()->from('users')->filter(['id' => 999])->singleOrFail();
} catch (RecordNotFoundException $e) {
    // Record not found
} catch (SqlException $e) {
    // SQL related error
}

License

MIT

migears/sql

轻量 SQL 查询构建器，PHP 8.1+，零强制依赖（除了 PDO 扩展）。

特性

极简 API：$sql->select()->from('users')->filter(['status' => 1])->execute()
零全局依赖：构造函数接收 PDO 实例，new 了就能用
纯数组返回：不做对象映射，简单直接
PSR-3 日志：LoggerInterface 为必填，通过构造函数注入
领域异常：SqlException / RecordNotFoundException
单文件 < 300 行：每个核心文件都很短，一眼看懂
高测试覆盖率：SQLite 内存数据库集成测试

边界

范围内

借助调用方传入的 PDO 构建并执行 SQL：select() / insert() / update() / delete()， where() / filter() / bind() / orderBy() / limit() / groupBy()， single() / singleOrFail() / count() / paginate()。
以纯数组返回行，并以 SqlException / RecordNotFoundException 命名失败。

范围外（刻意不做）

拥有或打开连接 —— PDO 由调用方传入；本包从不自行连接，也不持有配置。
Domain 对象、映射与 hydrate —— 由 migears/dao 把行变成 Domain；本层对之一无所知。
schema、迁移、事务、连接池，以及 ORM。
以带限定符的列名过滤 —— filter() 只接受未限定的列名；JOIN 查询请用 where()。

安装

composer require migears/sql

要求：PHP 8.1+，PDO 扩展。

快速开始

use MiGears\Sql\SqlBuilder;

$pdo = new PDO('mysql:host=localhost;dbname=app', 'user', 'pass');
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

// logger 为必填，须由调用方提供。
$sql = new SqlBuilder($pdo, $logger);

SELECT

// 查询所有
$rows = $sql->select()->from('users')->execute();

// 指定字段 + 条件
$rows = $sql->select(['id', 'name'])
    ->from('users')
    ->where('status = :status', ['status' => 1])
    ->orderBy('id DESC')
    ->limit(10)
    ->execute();

// filter 方式（键名带操作符后缀）
$rows = $sql->select()
    ->from('users')
    ->filter(['status' => 1, 'age>' => 18])
    ->execute();

// 单行
$user = $sql->select()->from('users')->filter(['id' => 1])->single();

// 单行，找不到抛异常
$user = $sql->select()->from('users')->filter(['id' => 1])->singleOrFail();

// 统计
$count = $sql->select()->from('users')->filter(['status' => 1])->count();

// 分组
$rows = $sql->select(['status', 'COUNT(*) as cnt'])
    ->from('users')
    ->groupBy('status')
    ->execute();

// 分开调用 bind()（与把参数传给 where() 等效）
$rows = $sql->select()
    ->from('users')
    ->where('status = :status AND age > :age')
    ->bind(['status' => 1])
    ->bind(['age' => 18])
    ->execute();

// 分页
$result = $sql->select()->from('users')->paginate(1, 20);
// ['records' => [...], 'total' => 100]

两种退化输入会被拒绝，而不是递给 PDO——因为它们此前都以驱动报错的形式浮现，点名的是 SQL，而不是那次调用：

不带 limit() 的 offset()。 单独一个 OFFSET n 在 SQLite 与 MySQL 上是语法错误，因此 toSql() 抛 SqlException（offset() requires limit()）。请一并调用 limit()，或改用 paginate()。
不产生任何 SQL、却带着绑定的条件。 where('', ['x' => 1])，以及完全不带条件就 bind()，会抛 SqlException 并点名那个没有占位符可落的参数。单独一个 where('') 仍是清空条件的正当写法。

filter 操作符后缀

后缀	操作符	示例	生成
（无）	=	`['status' => 1]`	`status` = :f_status
`>`	>	`['age>' => 18]`	`age` > :f_age
`<`	<	`['age<' => 30]`	`age` < :f_age
`>=`	>=	`['age>=' => 18]`	`age` >= :f_age
`<=`	<=	`['age<=' => 30]`	`age` <= :f_age
`!` / `<>` / `><`	!=	`['status!' => 0]`	`status` != :f_status

filter 占位符命名为 f_{字段名}，同一字段出现两个条件时追加 _2、_3 后缀，因此永远不会与 where() / bind() 的参数冲突。filter() 只接受非限定列名，['u.id' => 1] 会被拒绝；JOIN 查询中的限定列名请用 where()。

多表 / JOIN 查询

from() 接受任意合法 SQL 表子句，包括 JOIN 语法和逗号分隔的多表。用 where() 指定关联条件。

// INNER JOIN
$rows = $sql->select(['u.name', 'p.title'])
    ->from('users u INNER JOIN posts p ON u.id = p.user_id')
    ->where('u.status = :status', ['status' => 1])
    ->execute();

// LEFT JOIN
$rows = $sql->select(['u.name', 'p.title'])
    ->from('users u LEFT JOIN posts p ON u.id = p.user_id')
    ->execute();

// 逗号分隔（隐式连接）
$rows = $sql->select(['u.name', 'p.title'])
    ->from('users u, posts p')
    ->where('u.id = p.user_id', [])
    ->execute();

设计理由：不提供专门的 join() 方法 — from() 足以表达任意 SQL 语法，保持 API 极简。

from() 原样透传，因此既不加引号也不做校验：请把它当作开发者可控的 SQL 片段。传给 insert()、update()、delete() 的表名则不同 —— 它会被反引号包裹，必须是裸标识符（^\w+$），否则抛 SqlException。values() / set() 的列名遵循同一规则。

INSERT

$id = $sql->insert('users')
    ->values(['name' => 'Alice', 'email' => 'alice@example.com'])
    ->execute()
    ->lastInsertId();

UPDATE

$affected = $sql->update('users')
    ->set(['name' => 'Bob', 'age' => 30])
    ->filter(['id' => 1])
    ->execute();

// 原始 SET 表达式
$sql->update('users')
    ->set('counter = counter + 1')
    ->filter(['id' => 1])
    ->execute();

与 delete() 一样，update() 在没有任何条件时会拒绝执行，因此 update('users')->set([...])->execute() 不会误改全表，而是抛 SqlException。确实要更新全部行时，请显式给出条件：

$affected = $sql->update('users')->set(['status' => 0])->where('1 = 1')->execute();

set() 的数组形式与字符串形式互斥：以最后一次调用为准，原始表达式与键值数组不会混用。

DELETE

$affected = $sql->delete('users')
    ->filter(['id' => 1])
    ->execute();

delete() 在没有任何条件时会拒绝执行，因此误写的 delete('users')->execute() 不会清空整张表，而是抛 SqlException。确实要删除全部行时，请显式给出条件：

$affected = $sql->delete('users')->where('1 = 1')->execute();

架构

SQL 模块是 miGears 三层数据架构的最底层：

Service 层（业务逻辑）
    ↓ 调用
DAO 层（接收/返回 Domain 对象）→ migears/dao
    ↓ 内部调用
SQL 层（SQL + 参数 → 数组）→ migears/sql（本包）
    ↓
PDO / MySQL

SQL 层不知道 Domain 的存在，只执行 SQL 返回数组。

日志

注入任意 PSR-3 Logger 实现：

use Monolog\Logger;
use Monolog\Handler\StreamHandler;

$logger = new Logger('sql');
$logger->pushHandler(new StreamHandler('php://stdout'));

$sql = new SqlBuilder($pdo, $logger);

异常

use MiGears\Sql\Exception\SqlException;
use MiGears\Sql\Exception\RecordNotFoundException;

try {
    $user = $sql->select()->from('users')->filter(['id' => 999])->singleOrFail();
} catch (RecordNotFoundException $e) {
    // 记录不存在
} catch (SqlException $e) {
    // SQL 相关错误
}

许可证

MIT

migears / sql

Package info

Security

Statistics

README

Features

Boundaries

Installation

Quick Start

SELECT

filter Operator Suffixes

Multi-Table / JOIN Queries

INSERT

UPDATE

DELETE

Architecture

Logging

Exceptions

License

migears/sql

特性

边界

安装

快速开始

SELECT

filter 操作符后缀

多表 / JOIN 查询

INSERT

UPDATE

DELETE

架构

日志

异常

许可证