search

clue / reactphp-sqlite

Async SQLite database, lightweight non-blocking process wrapper around file-based database extension (ext-sqlite3), built on top of ReactPHP.
https://clue.engineering/2019/introducing-reactphp-sqlite
MIT License
51 stars 10 forks source link

readme

clue/reactphp-sqlite

CI status code coverage installs on Packagist

Async SQLite database, lightweight non-blocking process wrapper around file-based database extension (ext-sqlite3), built on top of ReactPHP.

SQLite is a widespread and efficient in-process database. It offers a common SQL interface to process queries to work with its relational data in memory or persist to a simple, portable database file. Its lightweight design makes it an ideal candidate for an embedded database in portable (CLI) applications, test environments and much more. This library provides a simple API to work with your SQLite database from within PHP. Because working with SQLite and the underlying filesystem is inherently blocking, this project is built as a lightweight non-blocking process wrapper around it, so you can query your data without blocking your main application.

Table of contents

Support us

We invest a lot of time developing, maintaining and updating our awesome open-source projects. You can help us sustain this high-quality of our work by becoming a sponsor on GitHub. Sponsors get numerous benefits in return, see our sponsoring page for details.

Let's take these projects to the next level together! 🚀

Quickstart example

The following example code demonstrates how this library can be used to open an existing SQLite database file (or automatically create it on the first run) and then INSERT a new record to the database:

<?php

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

$factory = new Clue\React\SQLite\Factory();
$db = $factory->openLazy(__DIR__ . '/users.db');

$db->exec('CREATE TABLE IF NOT EXISTS user (id INTEGER PRIMARY KEY AUTOINCREMENT, name STRING)');

$name = 'Alice';
$db->query('INSERT INTO user (name) VALUES (?)', [$name])->then(
    function (Clue\React\SQLite\Result $result) use ($name) {
        echo 'New ID for ' . $name . ': ' . $result->insertId . PHP_EOL;
    },
    function (Exception $e) {
        echo 'Error: ' . $e->getMessage() . PHP_EOL;
    }
);

$db->quit();

See also the examples.

Usage

Factory

The Factory is responsible for opening your DatabaseInterface instance.

$factory = new Clue\React\SQLite\Factory();

This class takes an optional LoopInterface|null $loop parameter that can be used to pass the event loop instance to use for this object. You can use a null value here in order to use the default loop. This value SHOULD NOT be given unless you're sure you want to explicitly use a given event loop instance.

This class takes an optional ?string $binary parameter that can be used to pass a custom PHP binary to use when spawning a child process. You can use a null value here in order to automatically detect the current PHP binary. You may want to pass a custom executable path if this automatic detection fails or if you explicitly want to run the child process with a different PHP version or environment than your parent process.

// advanced usage: pass custom PHP binary to use when spawning child process
$factory = new Clue\React\SQLite\Factory(null, '/usr/bin/php6.0');

Or you may use this parameter to pass an empty PHP binary path which will cause this project to not spawn a PHP child process for any database interactions at all. In this case, using SQLite will block the main process, but continues to provide the exact same async API. This can be useful if concurrent execution is not needed, especially when running behind a traditional web server (non-CLI SAPI).

// advanced usage: empty binary path runs blocking SQLite in same process
$factory = new Clue\React\SQLite\Factory(null, '');

open()

The open(string $filename, int $flags = null): PromiseInterface<DatabaseInterface> method can be used to open a new database connection for the given SQLite database file.

This method returns a promise that will resolve with a DatabaseInterface on success or will reject with an Exception on error. The SQLite extension is inherently blocking, so this method will spawn an SQLite worker process to run all SQLite commands and queries in a separate process without blocking the main process. On Windows, it uses a temporary network socket for this communication, on all other platforms, it communicates over standard process I/O pipes.

$factory->open('users.db')->then(function (DatabaseInterface $db) {
    // database ready
    // $db->query('INSERT INTO users (name) VALUES ("test")');
    // $db->quit();
}, function (Exception $e) {
    echo 'Error: ' . $e->getMessage() . PHP_EOL;
});

The $filename parameter is the path to the SQLite database file or :memory: to create a temporary in-memory database. As of PHP 7.0.10, an empty string can be given to create a private, temporary on-disk database. Relative paths will be resolved relative to the current working directory, so it's usually recommended to pass absolute paths instead to avoid any ambiguity.

$promise = $factory->open(__DIR__ . '/users.db');

The optional $flags parameter is used to determine how to open the SQLite database. By default, open uses SQLITE3_OPEN_READWRITE | SQLITE3_OPEN_CREATE.

$factory->open('users.db', SQLITE3_OPEN_READONLY)->then(function (DatabaseInterface $db) {
    // database ready (read-only)
    // $db->quit();
}, function (Exception $e) {
    echo 'Error: ' . $e->getMessage() . PHP_EOL;
});

openLazy()

The openLazy(string $filename, int $flags = null, array $options = []): DatabaseInterface method can be used to open a new database connection for the given SQLite database file.

$db = $factory->openLazy('users.db');

$db->query('INSERT INTO users (name) VALUES ("test")');
$db->quit();

This method immediately returns a "virtual" connection implementing the DatabaseInterface that can be used to interface with your SQLite database. Internally, it lazily creates the underlying database process only on demand once the first request is invoked on this instance and will queue all outstanding requests until the underlying database is ready. Additionally, it will only keep this underlying database in an "idle" state for 60s by default and will automatically end the underlying database when it is no longer needed.

From a consumer side, this means that you can start sending queries to the database right away while the underlying database process may still be outstanding. Because creating this underlying process may take some time, it will enqueue all outstanding commands and will ensure that all commands will be executed in the correct order once the database is ready. In other words, this "virtual" database behaves just like a "real" database as described in the DatabaseInterface and frees you from having to deal with its async resolution.

If the underlying database process fails, it will reject all outstanding commands and will return to the initial "idle" state. This means that you can keep sending additional commands at a later time which will again try to open a new underlying database. Note that this may require special care if you're using transactions that are kept open for longer than the idle period.

Note that creating the underlying database will be deferred until the first request is invoked. Accordingly, any eventual connection issues will be detected once this instance is first used. You can use the quit() method to ensure that the "virtual" connection will be soft-closed and no further commands can be enqueued. Similarly, calling quit() on this instance when not currently connected will succeed immediately and will not have to wait for an actual underlying connection.

Depending on your particular use case, you may prefer this method or the underlying open() method which resolves with a promise. For many simple use cases, it may be easier to create a lazy connection.

The $filename parameter is the path to the SQLite database file or :memory: to create a temporary in-memory database. As of PHP 7.0.10, an empty string can be given to create a private, temporary on-disk database. Relative paths will be resolved relative to the current working directory, so it's usually recommended to pass absolute paths instead to avoid any ambiguity.

$$db = $factory->openLazy(__DIR__ . '/users.db');

The optional $flags parameter is used to determine how to open the SQLite database. By default, open uses SQLITE3_OPEN_READWRITE | SQLITE3_OPEN_CREATE.

$db = $factory->openLazy('users.db', SQLITE3_OPEN_READONLY);

By default, this method will keep "idle" connection open for 60s and will then end the underlying connection. The next request after an "idle" connection ended will automatically create a new underlying connection. This ensures you always get a "fresh" connection and as such should not be confused with a "keepalive" or "heartbeat" mechanism, as this will not actively try to probe the connection. You can explicitly pass a custom idle timeout value in seconds (or use a negative number to not apply a timeout) like this:

$db = $factory->openLazy('users.db', null, ['idle' => 0.1]);

DatabaseInterface

The DatabaseInterface represents a connection that is responsible for communicating with your SQLite database wrapper, managing the connection state and sending your database queries.

exec()

The exec(string $query): PromiseInterface<Result> method can be used to execute an async query.

This method returns a promise that will resolve with a Result on success or will reject with an Exception on error. The SQLite wrapper is inherently sequential, so that all queries will be performed in order and outstanding queries will be put into a queue to be executed once the previous queries are completed.

$db->exec('CREATE TABLE test ...');
$db->exec('INSERT INTO test (id) VALUES (1)');

This method is specifically designed for queries that do not return a result set (such as a UPDATE or INSERT statement). Queries that do return a result set (such as from a SELECT or EXPLAIN statement) will not allow access to this data, so you're recommended to use the query() method instead.

$db->exec($query)->then(function (Result $result) {
    // this is an OK message in response to an UPDATE etc.
    if ($result->insertId !== 0) {
        var_dump('last insert ID', $result->insertId);
    }
    echo 'Query OK, ' . $result->changed . ' row(s) changed' . PHP_EOL;
}, function (Exception $error) {
    // the query was not executed successfully
    echo 'Error: ' . $error->getMessage() . PHP_EOL;
});

Unlike the query() method, this method does not support passing an array of placeholder parameters that will be bound to the query. If you want to pass user-supplied data, you're recommended to use the query() method instead.

query()

The query(string $query, array $params = []): PromiseInterface<Result> method can be used to perform an async query.

This method returns a promise that will resolve with a Result on success or will reject with an Exception on error. The SQLite wrapper is inherently sequential, so that all queries will be performed in order and outstanding queries will be put into a queue to be executed once the previous queries are completed.

$db->query('CREATE TABLE test ...');
$db->query('INSERT INTO test (id) VALUES (1)');

If this SQL statement returns a result set (such as from a SELECT statement), this method will buffer everything in memory until the result set is completed and will then resolve the resulting promise.

$db->query($query)->then(function (Result $result) {
    if (isset($result->rows)) {
        // this is a response to a SELECT etc. with some rows (0+)
        print_r($result->columns);
        print_r($result->rows);
        echo count($result->rows) . ' row(s) in set' . PHP_EOL;
    } else {
        // this is an OK message in response to an UPDATE etc.
        if ($result->insertId !== 0) {
            var_dump('last insert ID', $result->insertId);
        }
        echo 'Query OK, ' . $result->changed . ' row(s) changed' . PHP_EOL;
    }
}, function (Exception $error) {
    // the query was not executed successfully
    echo 'Error: ' . $error->getMessage() . PHP_EOL;
});

You can optionally pass an array of $params that will be bound to the query like this:

$db->query('SELECT * FROM user WHERE id > ?', [$id]);

Likewise, you can also use named placeholders that will be bound to the query like this:

$db->query('SELECT * FROM user WHERE id > :id', ['id' => $id]);

All placeholder values will automatically be mapped to the native SQLite datatypes and all result values will automatically be mapped to the native PHP datatypes. This conversion supports int, float, string and null. Any string that is valid UTF-8 without any control characters will be mapped to TEXT, binary strings will be mapped to BLOB. Both TEXT and BLOB will always be mapped to string . SQLite does not have a native boolean type, so true and false will be mapped to integer values 1 and 0 respectively.

quit()

The quit(): PromiseInterface<void, Exception> method can be used to quit (soft-close) the connection.

This method returns a promise that will resolve (with a void value) on success or will reject with an Exception on error. The SQLite wrapper is inherently sequential, so that all commands will be performed in order and outstanding commands will be put into a queue to be executed once the previous commands are completed.

$db->query('CREATE TABLE test ...');
$db->quit();

close()

The close(): void method can be used to force-close the connection.

Unlike the quit() method, this method will immediately force-close the connection and reject all outstanding commands.

$db->close();

Forcefully closing the connection should generally only be used as a last resort. See also quit() as a safe alternative.

Events

Besides defining a few methods, this interface also implements the EventEmitterInterface which allows you to react to certain events:

error event

The error event will be emitted once a fatal error occurs, such as when the connection is lost or is invalid. The event receives a single Exception argument for the error instance.

$db->on('error', function (Exception $e) {
    echo 'Error: ' . $e->getMessage() . PHP_EOL;
});

This event will only be triggered for fatal errors and will be followed by closing the connection. It is not to be confused with "soft" errors caused by invalid SQL queries.

close event

The close event will be emitted once the connection closes (terminates).

$db->on('close', function () {
    echo 'Connection closed' . PHP_EOL;
});

See also the close() method.

Install

The recommended way to install this library is through Composer. New to Composer?

This project follows SemVer. This will install the latest supported version:

composer require clue/reactphp-sqlite:^1.6

See also the CHANGELOG for details about version upgrades.

This project aims to run on any platform and thus only requires ext-sqlite3 and supports running on legacy PHP 5.4 through current PHP 8+. It's highly recommended to use the latest supported PHP version for this project.

This project is implemented as a lightweight process wrapper around the ext-sqlite3 PHP extension, so you'll have to make sure that you have a suitable version installed. On Debian/Ubuntu-based systems, you may simply install it like this:

sudo apt install php-sqlite3

Tests

To run the test suite, you first need to clone this repo and then install all dependencies through Composer:

composer install

To run the test suite, go to the project root and run:

vendor/bin/phpunit

The test suite is set up to always ensure 100% code coverage across all supported environments (except the platform-specific code that does not execute on Windows). If you have the Xdebug extension installed, you can also generate a code coverage report locally like this:

XDEBUG_MODE=coverage vendor/bin/phpunit --coverage-text

License

This project is released under the permissive MIT license.

Did you know that I offer custom development services and issuing invoices for sponsorships of releases and for contributions? Contact me (@clue) for details.