README

SQLite integration for PHP Object Foundation. It provides:

Serializable Entity classes adapted for SQLite storage.
A lightweight JSON-based storage layer using PDO.
A simple SQL dialect helper to query JSON data.

Requirements

PHP 8.4+
ext-json
ext-pdo
ext-pdo_sqlite
beecubu/php-foundation-core
beecubu/php-foundation-helpers

Installation

composer require beecubu/php-foundation-sqlite

Entity

Entity extends the core Serializable and adapts IDs/dates/binary to SQLite:

id is a string (compatible with ObjectId format).
Dates are stored as timestamps.
Binary data is stored as base64.

<?php

use Beecubu\Foundation\SQLite\Entity;
use Beecubu\Foundation\Core\Property;

class Note extends Entity
{
    protected function properties(): void
    {
        parent::properties();
        $this->properties += [
            'title' => [Property::READ_WRITE, Property::IS_STRING],
            'body'  => [Property::READ_WRITE, Property::IS_STRING],
        ];
    }
}

ExplicitEntity

Use ExplicitEntity to always serialize the class name (_class) in stored JSON.

DB Connection

Configure the SQLite file path:

define('SQLITE_DATABASE', __DIR__.'/db.sqlite');

Then use DBConnection to get the PDO instance:

use Beecubu\Foundation\SQLite\DBConnection\DBConnection;

class AppDB extends DBConnection
{
}

$db = new AppDB();
$pdo = $db->db();

DBClient (JSON Storage)

DBClient stores documents in tables with schema:

id (TEXT PRIMARY KEY)
data (JSON)

It auto-creates tables on insert/update.

use Beecubu\Foundation\SQLite\Driver\DBClient;

$client = new DBClient(new AppDB());

// insert
$doc = (object)['title' => 'Hello', 'body' => 'World'];
$client->insert('notes', $doc);

// find
foreach ($client->find('notes', ['title' => 'Hello'], null) as $row) {
    // $row is stdClass
}

// find one as entity
$note = $client->findOne('notes', ['title' => 'Hello'], Note::class);

// update (partial: $set)
$client->update('notes', ['id' => $note->id], (object)['title' => 'Updated'], false);

// delete
$client->remove('notes', ['id' => $note->id]);

SQLDialect Criteria

The SQLite driver supports MongoDB-like criteria arrays for JSON fields:

$criteria = [
    'status' => ['$eqi' => 'active'],
    'count'  => ['$gte' => 10],
    '$or'    => [
        ['type' => 'a'],
        ['type' => 'b'],
    ],
];

Supported operators:

$eq, $eqi (case-insensitive), $ne
$gt, $gte, $lt, $lte
$in, $nin
$exists
$like (mapped to LIKE)
$regex (mapped to regexp(...) user-defined function)

Fields use dot notation for JSON paths: profile.name.

Regex notes:

$regex accepts either a raw PCRE pattern (e.g. '^Joan.*') or a delimited one with flags (e.g. '/^joan.*/i').
The SQLite connection registers a custom regexp function backed by preg_match and always routes $regex through it.
If you use delimiters, flags are extracted and passed as a separate argument. Example: '/^joan.*/i' ➜ pattern '^joan.*', flags 'i'.
If you don’t use delimiters, you can still do case-insensitive matches with inline flags like (?i) (e.g. (?i)^joan.*).

Examples:

// Case-sensitive (raw pattern)
['name' => ['$regex' => '^Joan.*']]

// Case-insensitive (flags via delimiters)
['name' => ['$regex' => '/^joan.*/i']]

// Case-insensitive (inline flag, no delimiters)
['name' => ['$regex' => '(?i)^joan.*']]

Notes

Tables are created lazily the first time you insert/update.
Uses WAL mode, busy timeout, and foreign key enforcement by default.

beecubu / php-foundation-sqlite

Maintainers

Details