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 you 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.
- Async execution of queries - Send any number of queries (SQL) to SQLite in parallel (automatic queue) and process their responses as soon as results come in. The Promise-based design provides a sane interface to working with async results.
- Lightweight, SOLID design - Provides a thin abstraction that is just good enough and does not get in your way. Future or custom commands and events require no changes to be supported.
- Good test coverage - Comes with an automated tests suite and is regularly tested against actual SQLite databases in the wild.
Table of contents
The following example code demonstrates how this library can be used to open an
existing SQLite database file (or automatically create it on first run) and then
INSERT a new record to the database:
$loop = React\EventLoop\Factory::create();
$factory = new Clue\React\SQLite\Factory($loop);
$db = $factory->openLazy('users.db');
$db->exec('CREATE TABLE IF NOT EXISTS foo (id INTEGER PRIMARY KEY AUTOINCREMENT, bar STRING)');
$name = 'Alice';
$db->query('INSERT INTO foo (bar) VALUES (?)', [$name])->then(
function (Clue\React\SQLite\Result $result) use ($name) {
echo 'New ID for ' . $name . ': ' . $result->insertId . PHP_EOL;
}
);
$db->quit();
$loop->run();See also the examples.
The Factory is responsible for opening your DatabaseInterface instance.
It also registers everything with the main EventLoop.
$loop = React\EventLoop\Factory::create();
$factory = new Clue\React\SQLite\Factory($loop);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;
});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 oustanding commands and will ensure that all
commands will be executed in 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 ensure 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]);The DatabaseInterface represents a connection that is responsible for
comunicating with your SQLite database wrapper, managing the connection state
and sending your database queries.
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.
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.
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();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 oustanding commands.
$db->close();Forcefully closing the connection should generally only be used as a last
resort. See also quit() as a safe alternative.
Besides defining a few methods, this interface also implements the
EventEmitterInterface which allows you to react to certain events:
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.
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.
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.0.1See 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 7+.
It's highly recommended to use PHP 7+ 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-sqlite3To run the test suite, you first need to clone this repo and then install all dependencies through Composer:
$ composer installTo run the test suite, go to the project root and run:
$ php vendor/bin/phpunitThis 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.
React
Typescript
Django
Laravel
Facebook
Microsoft
Google
Alibaba
D3
Tencent