Stars: 142
Forks: 21
Pull Requests: 13
Issues: 74
Watchers: 13
Last Updated: 2021-11-04 04:21:58
A powerful, fast, cross-platform SQL Builder for PHP. Convert your structured data into SQL queries with a fluent style interface and targeting on all the mainstream database (MySQL, PostgreSQL, SQLite)
License: Other
Languages: PHP, C, Shell, CSS, HTML, JavaScript, M4
If you're looking for something that is not an ORM but can generate SQL for you, you just found the right one.
SQLBuilder is not an ORM (Object relational mapping) system, but a toolset that helps you generate cross-platform SQL queries in PHP.
SQLBuilder is a stand-alone library, you can simply install it through composer or just require them (the class files) with your autoloader, and it has no dependencies.
Here is a short example of using Universal SelectQuery
use SQLBuilder\ArgumentArray;
use SQLBuilder\Universal\Query\SelectQuery;
use SQLBuilder\Driver\MySQLDriver;
use SQLBuilder\Driver\PgSQLDriver;
use SQLBuilder\Driver\SQLiteDriver;
$mysql = new MySQLDriver;
$args = new ArgumentArray;
$query = new SelectQuery;
$query->select(array('id', 'name', 'phone', 'address','confirmed'))
->from('users', 'u')
->partitions('u1', 'u2', 'u3')
->where()
->is('confirmed', true)
->in('id', [1,2,3])
;
$query
->join('posts')
->as('p')
->on('p.user_id = u.id')
;
$query
->orderBy('rand()')
->orderBy('id', 'DESC')
;
$sql = $query->toSql($mysql, $args);
var_dump($sql);
var_dump($args);
Unlike other SQL utilities, SQLBuilder let you define the quote style and the parameter marker type. there are 2 parameter marker type you can choose:
?
):id
, :name
, :address
, :p1
)The above two are supported by PDO directly, and the first one is also
supported by mysqli
, pgsql
extension.
The API is dead simple, easy to remember, you can just define one query, then pass different query driver to the query object to get a different SQL string for your targettting platform.
It also supports cross-platform query generation, there are three types of query (currently): Universal, MySQL, PgSQL. The Universal queries are cross-platform, you can use them to create a cross-platform PHP API of your database system, and the supported platforms are: MySQL, PgSQL and SQLite.
Universql Queries:
To see the implementation details, you can check the source code inside Universal namespace: https://github.com/c9s/SQLBuilder/tree/master/SQLBuilder/Universal/Query
MySQL Queries:
For MySQL platform, the implementation is according to the specification of MySQL 5.6.
For PostgreSQL platform, the implementation is according to the specification of PostgreSQL 9.2.
composer require corneltek/sqlbuilder
composer install
Copy the phpunit.xml
file for your local configuration:
phpunit -c your-phpunit.xml tests
To test with mysql database:
mysql -uroot -p
CREATE DATABASE sqlbuilder CHARSET utf8;
GRANT ALL PRIVILEGES ON sqlbuilder.* TO 'testing'@'localhost' identified by '';
--- or use this to remove password for testing account
SET PASSWORD FOR testing@localhost=PASSWORD('');
To test with pgsql database:
sudo -u postgres createdb sqlbuilder
Yo-An Lin (c9s) [email protected]