Reactphp Sqlite Laravel Package

Getting Started

Minimal Setup

1. Install the package via Composer:

   composer require clue/reactphp-sqlite
   

2. Require the autoloader in your ReactPHP application:

   require __DIR__ . '/vendor/autoload.php';
   

3. Initialize the SQLite client in your ReactPHP loop:

   use React\EventLoop\Factory;
   use React\SQLite\Connection;
   
   $loop = Factory::create();
   $connection = new Connection($loop, __DIR__ . '/database.sqlite');
   

First Use Case: Async Query Execution

Run a simple non-blocking query:

$loop->futureTick(function () use ($connection) {
    $connection->query('SELECT * FROM users')
        ->then(function ($result) {
            foreach ($result as $row) {
                echo $row['username'] . "\n";
            }
        }, function ($error) {
            echo "Query failed: " . $error->getMessage() . "\n";
        });
});
$loop->run();

Implementation Patterns

Promise-Based Workflows

Leverage promises for chaining async operations:

$connection->query('SELECT * FROM posts WHERE user_id = ?', [1])
    ->then(function ($result) {
        return $connection->query('UPDATE posts SET read = 1 WHERE id IN (' .
            implode(',', array_column($result, 'id')) . ')');
    })
    ->then(function () {
        echo "Updated read statuses\n";
    });

Stream-Based Result Handling

Process large result sets efficiently:

$connection->query('SELECT * FROM logs')
    ->then(function ($result) {
        $result->on('data', function ($row) {
            // Process each row as it arrives
            $this->logProcessor->handle($row);
        });
        $result->on('end', function () {
            echo "Finished processing logs\n";
        });
    });

Transaction Management

Wrap operations in transactions:

$connection->beginTransaction()
    ->then(function ($tx) use ($connection) {
        return $tx->query('UPDATE accounts SET balance = balance - 100 WHERE id = 1')
            ->then(function () use ($tx) {
                return $tx->query('UPDATE accounts SET balance = balance + 100 WHERE id = 2');
            })
            ->then(function () use ($tx) {
                return $tx->commit();
            });
    });

Integration with ReactPHP Components

Combine with other ReactPHP libraries (e.g., HTTP servers):

$loop = Factory::create();
$connection = new Connection($loop, ':memory:');

// Simulate an HTTP request handler
$server = new React\Http\Server($loop);
$server->on('request', function ($request) use ($connection) {
    $connection->query('SELECT * FROM products')
        ->then(function ($result) {
            $response = new React\Http\Message\Response();
            $response->write(json_encode($result->fetchAll()));
            $request->respond($response);
        });
});
$server->listen(8080);
$loop->run();

Gotchas and Tips

Common Pitfalls

1. Blocking the Event Loop: Avoid synchronous operations (e.g., foreach loops without async processing) inside promise callbacks. Use streams or futureTick for CPU-heavy tasks.

   // ❌ Bad: Blocks the loop
   $result->then(function ($rows) {
       foreach ($rows as $row) { /* Heavy work */ }
   });
   
   // ✅ Good: Offload to worker
   $result->then(function ($rows) {
       $loop->futureTick(function () use ($rows) {
           foreach ($rows as $row) { /* Heavy work */ }
       });
   });
   

2. Connection Leaks: Always close connections explicitly in long-running apps:

   $connection->close();
   

   Or use a Deferred to ensure cleanup:

   $deferred = new React\Promise\Deferred();
   $loop->addPeriodicTimer(3600, function () use ($connection, $deferred) {
       $connection->close();
       $deferred->resolve();
   });
   

3. SQLite Locking: SQLite may block if multiple connections write simultaneously. Use PRAGMA busy_timeout to mitigate:

   $connection->query('PRAGMA busy_timeout = 5000;');
   

Debugging Tips

• Enable SQLite Logging:

  $connection->query('PRAGMA logging = 1;');
  

• Check for Pending Promises: Use React\Promise\Timer\TimeoutException to detect hung queries:

  $connection->query('SELECT * FROM large_table')
      ->otherwise(function ($error) {
          if ($error instanceof React\Promise\Timer\TimeoutException) {
              echo "Query timed out\n";
          }
      });
  

Extension Points

1. Custom Result Processors: Extend React\SQLite\Result to add domain-specific logic:

   class UserResult extends Result {
       public function getUsers() {
           return $this->fetchAll();
       }
   }
   

2. Query Builder Integration: Combine with a query builder (e.g., ReactPHP-QueryBuilder) for type safety:

   use React\SQLite\QueryBuilder;
   
   $query = new QueryBuilder($connection);
   $query->select('*')->from('users')->where('active', true);
   $result = $connection->query($query->getSQL(), $query->getParameters());
   

3. Connection Pooling: Reuse connections for performance-critical paths:

   $pool = new React\Promise\Pool($loop, [], function () use ($connection) {
       return $connection->query('SELECT * FROM cache');
   });
   

Configuration Quirks

• Database Path: Use :memory: for testing, but ensure the path is writable for persistent databases.
• Timeouts: Set query timeouts via PRAGMA timeout (default: 5000ms):

  $connection->query('PRAGMA timeout = 10000;');
  

• Encoding: SQLite uses UTF-8/UTF-16 by default. Specify encoding explicitly if needed:

  $connection->query('PRAGMA encoding = "UTF-8";');