Search by

bonsaicode / database

ahdavis

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.

Package info

bitbucket.org/bonsaicode/database

pkg:composer/bonsaicode/database

Statistics

Installs: 11

Dependents: 0

Suggesters: 0

1.2.0 2026-11-21 13:54 UTC

This package is auto-updated.

Last update: 2026-11-21 13:56:45 UTC


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) and json extensions
  • 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

ValueBecomes
%iinteger (5, -5, '12')5, or NULL if not an integer; an array throws DbException
%dnumber (1.5, '2.50')1.5, or NULL if not numeric; an array throws DbException
%sstring'escaped', or NULL for null or ''
%tstring'escaped', never NULL (null becomes '')
%bbinary / blob'escaped', NULL only for null
%janything JSON-encodable'{"escaped":"json"}', or NULL for null
%karray of integers1,2,3 for IN (%k); NULL for an empty array (matches nothing)
%uarray of strings'a','b' for IN (%u); NULL for an empty array
%vtable or column name`name`
%fraw 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, so DATE_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't is fine. Executable comments (/*! ... */, /*+ ... */) are treated as SQL.
  • Any other % (for example 7 % 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 DbException is thrown.

Methods

MethodReturns
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->conthe 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 default mysqli_report() mode). If your application turns that off with mysqli_report( MYSQLI_REPORT_OFF ), the query methods return null (qfColumn(): []), raise an E_USER_WARNING, and error() 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, %d or %s, a value %j cannot 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):

AHDbDb
%i turned negative numbers into NULLnegative 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 errorNULL
a non-integer in %k called exit()throws DbException
too few values gave a warning and NULL; extra values were ignoredthrows DbException
placeholders inside "...", `...` or after \' were replacedleft alone
%v and col() also ran real_escape_string(), adding backslashes to identifiersonly backticks are doubled
%j stored NULL if JSON encoding failedthrows DbException
qfColumnString() with values did not workfixed
tableExists( 'a_b' ) also matched aXb (_ is a LIKE wildcard)exact match
a failed connection called die() with a maintenance messagethrows DbException
query errors were echoed with a stack trace when display_errors was ontrigger_error(), which follows display_errors / log_errors (only when mysqli exceptions are off)
qf() returned false on errornull (only when mysqli exceptions are off)
maskStrings() was publicremoved (formatting is one pass)
global classes AHDb, DatabaseExceptionBonsaiCode\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.