README

An async SQLite driver for PHP using the Amp framework with multi-worker architecture. Provides non-blocking database access by isolating SQLite connections in separate worker processes/threads, enabling concurrent query execution while maintaining SQLite's file-based integrity.

Requirements

PHP 8.2 or higher
amphp/amp ^3.1
amphp/parallel ^2.0
amphp/sql ^2.0
amphp/sql-common ^2.0
amphp/sync ^2.0
amphp/log ^2.0
ext-sqlite3
ext-pcntl
ext-parallel (optional, for thread-based workers)

Installation

Install the library via Composer:

composer require sinkinov/amphp-sqlite

Synchronous API

Important: This library provides a synchronous API - all methods like query() and execute() return results directly (not Promises). The async worker operations are handled internally with automatic await() calls.

This means you can use it like regular PDO:

// Returns SqliteResult directly, not a Promise!
$result = $connection->query('SELECT * FROM users');

foreach ($result as $row) {
    echo $row['name'] . "\n";
}

No need for Amp\trap(), Amp\Loop::run(), or manual await() calls - the library handles everything internally.

Quick Start

<?php

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

use Sinkinov\AmphpSqlite\SqliteConnector;
use Sinkinov\AmphpSqlite\Config\SqliteConfig;

// Create configuration
$config = SqliteConfig::fromPath(__DIR__ . '/database.sqlite', maxWorkers: 4);

// Create connector and worker pool
$connector = SqliteConnector::fromConfig($config);
$pool = SqliteConnector::createWorkerPool(4);
$connection = $connector->connect($pool);

// Execute a query
$result = $connection->query('SELECT * FROM users WHERE active = 1');

// Iterate through results
foreach ($result as $row) {
    echo "User: {$row['name']}\n";
}

// Execute an INSERT statement
$connection->execute('INSERT INTO posts (title, content) VALUES (:title, :content)', [
    'title' => 'My First Post',
    'content' => 'Hello, World!'
]);

// Clean up (optional, PHP will clean up automatically)
$connection->close();
$pool->shutdown();

Features

🚀 Multi-Worker Architecture

Three-tier worker pool with separate pools for read, write, and transaction operations
Automatic WAL mode for write workers to improve concurrency
Work-stealing task queue for efficient load distribution
Thread or process mode (auto-detected based on available extensions)

⚡ High Performance

Atomic transactions for fast 1-3 query operations (30-40% faster)
Concurrent query execution with true parallelism
Read-only connection wrapper for safe read operations
Connection pooling for optimal resource utilization

🔒 Transaction Support

Callback-based atomic transactions via transactional()
Long-running transactions via beginTransaction()
Nested transactions using SAVEPOINT mechanism
Automatic rollback on errors or uncommitted transactions

📝 Structured Logging

Monolog integration for query and operation logging
Long transaction warnings (>1 second) with suggestions
Worker lifecycle tracking for debugging
Optional query logger for performance monitoring

Usage Examples

Atomic Transaction (Fast)

use Sinkinov\AmphpSqlite\SqliteConnector;
use Sinkinov\AmphpSqlite\Config\SqliteConfig;

$config = SqliteConfig::fromPath('/path/to/db.sqlite');
$connector = SqliteConnector::fromConfig($config);
$pool = SqliteConnector::createWorkerPool(4);
$connection = $connector->connect($pool);

// Execute multiple queries as a single atomic operation
$result = $connection->transactional(function ($executor) {
    $executor->execute('INSERT INTO users (name) VALUES (:name)', ['name' => 'Alice']);
    $executor->execute('UPDATE stats SET user_count = user_count + 1');
    return $executor->query('SELECT COUNT(*) as count FROM users')->fetchRow();
});

Long Transaction with Prepared Statements

// Assuming $connection is already created
$transaction = $connection->beginTransaction();

try {
    // Use prepared statements for efficiency
    $stmt = $transaction->prepare('INSERT INTO orders (user_id, amount) VALUES (:uid, :amt)');

    foreach ($orders as $order) {
        $stmt->execute(['uid' => $order['userId'], 'amt' => $order['amount']]);
    }

    $transaction->commit();
} catch (\Throwable $e) {
    $transaction->rollback();
    throw $e;
}

Read-Write Separation

use Sinkinov\AmphpSqlite\SqliteConnector;
use Sinkinov\AmphpSqlite\Config\SqliteConfig;

$config = SqliteConfig::fromPath('/path/to/db.sqlite', maxWorkers: 6)
    ->withWriteWorkerCount(1)
    ->withTransactionWorkerCount(2);

// Remaining 3 workers are for reads
$rwConnection = SqliteConnector::fromConfig($config)->connectReadWrite($pool);

// Reads go to read pool
$users = $rwConnection->query('SELECT * FROM users');

// Writes go to write pool
$rwConnection->execute('INSERT INTO logs (message) VALUES (:msg)', ['msg' => 'Hello']);

Nested Transactions (Savepoints)

// Assuming $connection is already created
$transaction = $connection->beginTransaction();

try {
    $transaction->execute('INSERT INTO orders (user_id) VALUES (:uid)', ['uid' => 1]);

    // Create nested transaction (SAVEPOINT)
    $nested = $transaction->beginTransaction();

    try {
        $nested->execute('UPDATE inventory SET count = count - 1 WHERE product_id = :pid', ['pid' => 123]);
        $nested->commit();
    } catch (\Throwable $e) {
        $nested->rollback();
    }

    $transaction->commit();
} catch (\Throwable $e) {
    $transaction->rollback();
}

Query Logging

use Monolog\Logger;
use Monolog\Handler\StreamHandler;
use Sinkinov\AmphpSqlite\Config\SqliteConfig;

// Create a logger
$logger = new Logger('amphp-sqlite', [
    new StreamHandler('php://stderr')
]);

// Pass logger to configuration
$config = SqliteConfig::fromPath('/path/to/db.sqlite')
    ->withQueryLogger($logger);

// Create connection with logging enabled
$connector = SqliteConnector::fromConfig($config);
$connection = $connector->connect($pool);

// Long transactions will automatically log warnings
$transaction = $connection->beginTransaction();
// ... operations that take > 1 second ...
$transaction->commit(); // Logs warning: "Transaction took 1.5s, consider using transactional() for better performance"

Development Setup

For development and contribution, we recommend using Docker to ensure consistent dependencies:

# Build Docker image (includes ext-parallel and ext-pcntl)
make build

# Install dependencies
make install

# Run tests
make test

# Run static analysis (PHPStan level 10)
make analyse

# Check code style
make check-style

# Fix code style issues
make fix-style

See CLAUDE.md for detailed development instructions.

Documentation

For detailed documentation, please visit the docs/ directory:

License

This library is released under the MIT License.

Contributing

Contributions are welcome! Please feel free to submit a Pull Request.

Support

If you find a bug or have a feature request, please create an issue on GitHub.

sinkinov / amphp-sqlite

Maintainers

Details