ReactMysql
Non-blocking MySQLi database access with PHP.
Designed to work with reactphp/react., (*1)
, (*2)
Quickstart
$db = new \DustinGraham\ReactMysql\Database(
['localhost', 'apache', 'apache', 'react_mysql_test']
);
$db->statement('SELECT * FROM simple_table WHERE id = :test', [':test' => 2])
->then(function(\mysqli_result $result)
{
$rows = $result->fetch_all(MYSQLI_ASSOC);
});
$db->shuttingDown = true;
$db->loop->run();
Setting shuttingDown to true will allow the loop to exit once the query has resolved., (*3)
Working
This is working. But it is nowhere near complete. Check out the example file
as well as the unit tests for more examples., (*4)
$ ./example
Creating database....done!
Run Query: 0
Found rows: 0
Run Query: 1
Found rows: 1
Current memory usage: 868.164K
Run Query: 2
Found rows: 1
Run Query: 3
Found rows: 1
Run Query: 4
Found rows: 0
Current memory usage: 868.164K
Run Query: 5
Found rows: 0
Current memory usage: 865.719K
Current memory usage: 865.719K
Current memory usage: 865.719K
Loop finished, all timers halted.
This won't work out of the box without the database configured.
You will also need to set up a database with some data to query., (*5)
Unit Tests
The example and unit tests expect a database called react_mysql_test which it
will populate with the proper tables each time it runs. It also expects localhost
and a user apache with password apache., (*6)
TODO
This is not production ready. Still tons to do on the query builder.
While I hate to reinvent the wheel, I have not found a lightweight
injectable query builder that is not tied to a massive framework., (*7)
Plans (Future Examples)
These are just plans for now. It may change wildly as we develop., (*8)
Current Development Example
Here is an example of what is currently working for the most part., (*9)
$db = new \DustinGraham\ReactMysql\Database(
['localhost', 'apache', 'apache', 'react_mysql_test']
);
$db->statement('SELECT * FROM simple_table WHERE id = :test', [':test' => 2])
->then(function(\mysqli_result $result)
{
$rows = $result->fetch_all(MYSQLI_ASSOC);
// Do something with $rows.
});
$db->shuttingDown = true;
$db->loop->run();
Original Big Picture Plans
Here are some examples of how it may be, eventually.
It would be nice to hide away some of the current boilerplate., (*10)
Connection::init($loop, ['db_host', 'db_user', 'db_pass', 'db_name']);
Connection::query(
'SELECT * FROM `table` WHERE `column` = ? AND `column2` = ?;',
['red', 'white']
)->then(function($result) { ... });
Connection::query(...) returns a promise.
$db = new Database();
$db->createCommand('SELECT * FROM table WHERE id = :id', [':id' => 1])
->execute()
->then(function($results) {
echo $results[0]->name;
});
And another idea..., (*11)
DB::loadModel('id', ' =', '3')->then(function($model) use ($socket) {
$socket->send('Your name is '.$model->name);
});
Difficulties
There were many difficulties., (*12)
At this point, I can not find any libraries that handle parameterized queries
without using PDO or prepared statements., (*13)
MYSQLI_ASYNC does not support prepared statements and parameter binding. So we had to write it ourselves., (*14)
The mysqli::real_escape_string requires a link. But, the link is one of many.
Last minute escaping once the command and connection were married from the pool.
Could potentially have one dedicated link for escaping., (*15)
Query Building Support
Many MySQL wrapper packages have been analyzed, but none are completely independent of
a connection object that could be found., (*16)
For now, we will escape parameters, but require the user to provide a sql query that quotes the parameters., (*17)
This is obviously sub-optimal since a variable like $created_at could be NOW() or '2016-01-01' or NULL., (*18)
The litmus test I have been using is the following query:, (*19)
INSERT INTO `simple_table` (`id`, `name`, `value`, `created_at`)
VALUES (NULL, 'John\'s Name', 7, NOW());
The key points here are:, (*20)
- Support for putting the parameter in quotes! This is the first step. The rest is intelligently knowing when not to quote.
- Support for a null value converted to NULL.
- Support for escaping the parameter using either \\' or '' is fine.
- Support for not escaping functions such as NOW()
- Support for recognizing integer values. Optional, since '7' will work fine.
Wrapper Options Reviewed
-
nilportugues/php-sql-query-builder - No connection required! But, odd syntax.
-
usmanhalalit/pixie - Requires connection. Pretty close to needs.
-
joshcam/PHP-MySQLi-Database-Class - Requires connection.
-
aviat4ion/Query - Requires connection.
-
rkrx/php-mysql-query-builder - Requires connection.
-
stefangabos/Zebra_Database - Requires connection, does more than needed.
-
indeyets/MySQL-Query-Builder - Not maintained. Odd syntax.
The nilportugues/php-sql-query-builder package is very close, but it does not quote the parameters., (*21)
Install
The recommended way to install this library is through Composer., (*22)
$ composer require dustingraham/react-mysql
PHP 5.4 and the php ext-mysqli extension is required., (*23)
Credits
Much appreciation to the hard work over at reactphp/react., (*24)
Inspired by similar projects:
- kaja47/async-mysql
- bixuehujin/reactphp-mysql, (*25)
License
DustinGraham/ReactMysql is released under the MIT license., (*26)
Wallogit.com
