finesse/query-scribe

Light SQL query builder designed for extensibility and flexibility

v0.9.3 2019-01-19 03:47 UTC

Requires

• php: >=7.0

Requires (Dev)

• phpunit/phpunit: ^6.4

MIT

• Surgie Finesse

• database
• sql
• mysql
• sqlite
• query builder

README

Provides a convenient object syntax to build SQL queries. Compiles the queries to an SQL text with values for binding. Doesn't perform queries to database.

$query = (new Query)
    ->from('posts')
    ->where('level', '>', 3)
    ->whereIn('category_id', function ($query) {
        $query
            ->addSelect('id')
            ->from('categories')
            ->where('categories.name', 'Interesting');
    })
    ->where(new Raw('MONTH(date)'), 4)
    ->orderBy('date', 'desc')
    ->limit(10);
    
$prefixer = new TablePrefixer('demo_');
$grammar = new MySQLGrammar();
$compiled = $grammar->compile($query->apply($prefixer));

echo $compiled->getSQL();
/*
    SELECT *
    FROM `demo_posts`
    WHERE
        `level` > ? AND
        `category_id` IN (
            SELECT `id`
            FROM `demo_categories`
            WHERE `demo_categories`.`name` = ?
        ) AND
        (MONTH(date)) = ?
    ORDER BY `date` DESC
    LIMIT ?
 */

echo $compiled->getBindings();
/*
    [3, 'Interesting', 4, 10]
 */

To perform compiled queries to a database, use a database connector like PDO, MicroDB or DBAL or use a ready database abstraction like MiniDB or Wired.

Key features:

• The builder has a single responsibility: build SQL.
• Designed for further extension. You may build a database tool or an ORM on top of it without major problems. Examples will come soon.
• Very flexible. You can pass a raw SQL or a subquery almost everywhere (see the PHPDoc comments in the code to know where you can pass them).
• Smart table prefixes which consider table aliases (don't work in raw expressions).
• All the values go to bindings, even from subqueries.
• No dependencies. Requires only PHP ≥ 7.

Supported SQL dialects:

• MySQL
• SQLite
• SQL Server (not fully supported)
• Maybe any other, didn't test it

If you need a dialect support please extend the CommonGrammar class and make a pull request.

Documentation

The documentation is available at queryscribe.readthedocs.io.

Also all the classes, methods and properties has a PHPDoc comment in the code.

Versions compatibility

The project follows the Semantic Versioning.

License

MIT. See the LICENSE file for details.