bonsaicode / database
A fast MySQL wrapper (mysqli) with printf-style placeholders that escape and type-check values, e.g. %i, %s, %k for IN lists and %j for JSON.
Requires
- php: >=8.1
- ext-json: *
- ext-mysqli: *
Requires (Dev)
- phpunit/phpunit: ^10.5 || ^11.5
Suggests
None
Provides
None
Conflicts
None
Replaces
None
README
A fast MySQL wrapper (mysqli) with printf-style placeholders that escape and type-check values.
$db = new BonsaiCode\Db( [ 'server' => 'localhost', 'user' => 'app', 'password' => $password, 'name' => 'app' ] );
$account = $db->qfRowMap( 'SELECT * FROM account WHERE pk = %i AND email = %s', $pk, $email );
$names = $db->qfColumn( 'SELECT name FROM company WHERE pk IN (%k) ORDER BY name', $companyPks );
$db->qf( 'INSERT INTO log (fkAccount, action, data) VALUES (%i, %s, %j)', $pk, 'login', [ 'ip' => $ip ] );
It is as fast as PDO used the usual way and about twice as fast as PDO with native prepared statements; see BENCHMARKS.md.
Requirements
- PHP 8.1 or higher, with the
mysqli(mysqlnd) andjsonextensions - MySQL 5.7 or 8.x (tested on 5.7 and 8.4)
Installation
$ composer require "bonsaicode/database"
Connecting
$db = new BonsaiCode\Db( [
'server' => 'localhost',
'user' => 'app',
'password' => $password,
'name' => 'app', # database
'port' => 3306, # optional
'socket' => '/path/mysql.sock',# optional
'ssl' => [ 'key' => ..., 'cert' => ..., 'ca' => ... ], # optional
'charset' => 'utf8mb4', # optional, default utf8mb4
] );
A failed connection throws BonsaiCode\DbException (the message never contains the password). Catch it to show a maintenance page.
Results come back with native types: integer columns as int, FLOAT/DOUBLE as float, DECIMAL as string.
Placeholders
| Value | Becomes | |
|---|---|---|
%i | integer (5, -5, '12') | 5, or NULL if not an integer; an array throws DbException |
%d | number (1.5, '2.50') | 1.5, or NULL if not numeric; an array throws DbException |
%s | string | 'escaped', or NULL for null or '' |
%t | string | 'escaped', never NULL (null becomes '') |
%b | binary / blob | 'escaped', NULL only for null |
%j | anything JSON-encodable | '{"escaped":"json"}', or NULL for null |
%k | array of integers | 1,2,3 for IN (%k); NULL for an empty array (matches nothing) |
%u | array of strings | 'a','b' for IN (%u); NULL for an empty array |
%v | table or column name | `name` |
%f | raw SQL, e.g. UNHEX('...') | inserted as is, NULL for ''. Never pass user input to %f. |
- Placeholders inside quoted strings (
'...',"...") and backtick identifiers are left alone, soDATE_FORMAT(d, '%Y-%m-%d')is safe. - Comments (
-- ...,# ...,/* ... */) are skipped as MySQL does: quotes and placeholders inside them are ignored, so an apostrophe in-- it doesn'tis fine. Executable comments (/*! ... */,/*+ ... */) are treated as SQL. - Any other
%(for example7 % 3) is left as is. - A query called with no values is sent exactly as written.
- The number of values must match the number of placeholders, otherwise
DbExceptionis thrown.
Methods
| Method | Returns |
|---|---|
qf( $sql, ...$values ) | mysqli_result for SELECT (call ->free()), true for other statements |
qfField( $sql, ...$values ) | first column of the first row, or null |
qfRow( $sql, ...$values ) | first row [1, "v1"], or null |
qfRowMap( $sql, ...$values ) | first row ["pk" => 1, "name" => "v1"], or null |
qfList( $sql, ...$values ) | all rows as numeric arrays |
qfListMap( $sql, ...$values ) | all rows as associative arrays |
qfColumn( $sql, ...$values ) | first column of every row |
qfColumnString( $sql, $delimiter, ...$values ) | first column of every row, joined |
qfMap( $sql, ...$values ) | first column => second column |
qfMapMap( $sql, $keyColumn, ...$values ) | rows keyed by $keyColumn (without that column) |
format( $sql, ...$values ) | the final SQL, without running it |
insertId(), affectedRows() | from the last statement |
begin(), commit(), rollback() | transaction control |
transaction( fn( Db $db ) => ... ) | runs the function in a transaction: commits, or rolls back and rethrows |
tableExists( $table ) | bool |
str( $s ), strNull( $s ), col( $name ) | escaping helpers for building SQL by hand |
Db::caseInsensitive( $expr ) | CONVERT(expr USING utf8mb4) COLLATE utf8mb4_general_ci |
$db->con | the underlying mysqli connection, e.g. for prepared statements in a hot loop |
Errors
- A MySQL error in a query throws
mysqli_sql_exception(PHP 8.1+'s defaultmysqli_report()mode). If your application turns that off withmysqli_report( MYSQLI_REPORT_OFF ), the query methods returnnull(qfColumn():[]), raise anE_USER_WARNING, anderror()returns the message with a stack trace. - Programming errors always throw
DbException: wrong number of values, a non-integer in%k, an array passed to%i,%dor%s, a value%jcannot encode, an empty%v.
Debugging
$db->debug = BonsaiCode\Db::DEBUG_OUT_GO; # print the next query's SQL and run it
$db->debug = BonsaiCode\Db::DEBUG_LOG_GO; # error_log() it and run it
# *_STOP variants (DEBUG_OUT_STOP, DEBUG_LOG_STOP, DEBUG_BOTH_STOP) show the SQL and stop the script before running it
Or call format() to get the SQL without running it.
Moving from AHDb
Db is the class formerly copied into each project as class/AHDb.php.
What behaves differently (all were bugs or unsafe in AHDb):
| AHDb | Db |
|---|---|
%i turned negative numbers into NULL | negative integers are kept |
%k with an empty array called exit() | NULL, so IN (NULL) matches nothing |
%u with an empty array produced IN (), a syntax error | NULL |
a non-integer in %k called exit() | throws DbException |
too few values gave a warning and NULL; extra values were ignored | throws DbException |
placeholders inside "...", `...` or after \' were replaced | left alone |
%v and col() also ran real_escape_string(), adding backslashes to identifiers | only backticks are doubled |
%j stored NULL if JSON encoding failed | throws DbException |
qfColumnString() with values did not work | fixed |
tableExists( 'a_b' ) also matched aXb (_ is a LIKE wildcard) | exact match |
a failed connection called die() with a maintenance message | throws DbException |
query errors were echoed with a stack trace when display_errors was on | trigger_error(), which follows display_errors / log_errors (only when mysqli exceptions are off) |
qf() returned false on error | null (only when mysqli exceptions are off) |
maskStrings() was public | removed (formatting is one pass) |
global classes AHDb, DatabaseException | BonsaiCode\Db, BonsaiCode\DbException |
Tests
The tests need a MySQL server where they can create and drop dbtest_* tables. With Docker, from this folder in Git Bash:
docker network create bc-dbtest
docker run -d --rm --name bc-mysql84 --network bc-dbtest -e MYSQL_ROOT_PASSWORD=test -e MYSQL_DATABASE=dbtest mysql:8.4
# wait until MySQL accepts TCP connections (its first start takes 15-30 seconds)
until docker exec bc-mysql84 mysql -h127.0.0.1 -uroot -ptest -e 'SELECT 1' >/dev/null 2>&1; do sleep 2; done
# give the server a certificate issued to bc-mysql84, signed by the CA MySQL generated, and load it
docker exec bc-mysql84 sh -c 'cd /var/lib/mysql && openssl req -newkey rsa:2048 -nodes -keyout server-key.pem -subj /CN=bc-mysql84 -addext subjectAltName=DNS:bc-mysql84 -out /tmp/server.csr 2>/dev/null && openssl x509 -req -in /tmp/server.csr -CA ca.pem -CAkey ca-key.pem -CAcreateserial -days 1 -copy_extensions copy -out server-cert.pem 2>/dev/null && chown mysql:mysql server-key.pem server-cert.pem'
docker exec bc-mysql84 mysql -h127.0.0.1 -uroot -ptest -e 'ALTER INSTANCE RELOAD TLS'
MSYS_NO_PATHCONV=1 docker run --rm -v "$(pwd -W):/pkg" -w /pkg --entrypoint composer emedicall-php:dev install
MSYS_NO_PATHCONV=1 docker run --rm --network bc-dbtest --volumes-from bc-mysql84:ro -e DB_HOST=bc-mysql84 -e DB_PASSWORD=test -e DB_SSL_CA=/var/lib/mysql/ca.pem -v "$(pwd -W):/pkg" -w /pkg --entrypoint php emedicall-php:dev vendor/bin/phpunit
Settings come from DB_HOST, DB_PORT, DB_USER, DB_PASSWORD, DB_NAME; without a server the tests are skipped.
DB_SSL_CA is the server's CA file (--volumes-from lets the test container read it from the MySQL container); without it the CA-file test is skipped.
The CA test checks the server's certificate, including that it was issued to the host name it connects to, which is why the server gets a certificate for bc-mysql84: the one MySQL generates on first start is issued to a generic name and fails that check.
Clean up with docker rm -f bc-mysql84 && docker network rm bc-dbtest.