feature request: a non-blocking driver

[azjezz]

With the arrival of PHP 8.1, async code in PHP land doesn't have to use Promise or fall into the callback hell that we got trapped into before.

If cycle would provide an integration with a non-blocking database driver, this would mean that the ORM could be used to do multiple operations in parallel at the application level, and would allow for handling multiple connections as the worker level ( spiral/roadrunner-worker#19 ).

Currently, there's 2 available packages that allow you to create a non-blocking database connection:

1. amphp/mysql: https://github.com/amphp/mysql/tree/v3 ( branch v3 )
2. amphp/postgres: https://github.com/amphp/postgres/tree/v2 ( branch v2 )

[roxblnfk]

I agree with you and i hope to see the task (in the internal task manager) of completely redesigning DB in the foreseeable future.

Related:

• Улучшение работы с транзакциями spiral-modules/database#56

[azjezz]

spiral-modules/database#56 is different, as far as i understand ( google translation lol ), it's about connection pool, which would allow executing multiple queries in parallel, however, this issue is more about non-blocking connections, so that when a query is running, it allows for other callbacks within an event loop to continue execution.

[roxblnfk]

spiral-modules/database#56 is different

It is different but related. I think without connection pool we won't be able to support properly a non-blocking database drivers

[azjezz]

Hmm, not really.

pool would allow executing multiple queries at the same time, while a non-blocking driver allows executing a query without blocking the event loop, so you can execute a query, send an http request, and execute a shell command, in parallel.

[roxblnfk]

At the same time it is necessary to block the using connection or provide another connection if a second query must be executed in parallel with the first.

[azjezz]

Not really, assuming the driver is non-blocking, and we are not using a pool, given this code:

use Psl\Async;
use Psl\Shell;

[$output, $users] = Async\parallel([
  fn() => Shell\execute('some-command', ['arg']),
  fn() => $db->query('SELECT * FROM users'])->fetchAll()
]);

both query and execute will run in parallel, since shell\execute is non blocking, query will start as execute() waits for the output of the command, same will happen if we reverse the tasks, execute will start while query is waiting for connection to respond.

---

Attempting this using only non-blocking drives:

use Psl\Async;
use Psl\Shell;

[$articles, $users] = Async\parallel([
  fn() => $db->query('SELECT * FROM articles'])->fetchAll()
  fn() => $db->query('SELECT * FROM users'])->fetchAll()
]);

will also work, but with a big difference, that is the second query will not actually start until the first one is finished, this is achieved using queued operations in a lower level ( https://github.com/amphp/postgres/blob/v2/src/Connection.php#L66-L77 ), by waiting for any previous operations to finish before starting a new one.

But, when using a connection pool, this will running concurrently, given that the query method retrieves a new connection for each query.

[azjezz]

so supporting just non-blocking drivers, would be huge, as currently it blocks spiral/roadrunner-worker#19.

pools will allow for concurrent queries, but they are not as important as non-blocking drivers.

ref: doctrine/dbal#5117