Download the PHP package yetidevworks/yetisql without Composer

YetiSQL

A pure-PHP, SQLite-compatible embedded SQL database. Zero extensions, a single committable file, and a PDO-shaped API. Runs anywhere PHP 8.3+ runs — including restricted shared hosting and locked-down containers where pdo_sqlite isn't available — and is a natural fit for flat-file projects where the whole database is one file you can commit to git.

Scope & honesty. YetiSQL speaks SQLite's SQL dialect and stores data in its own single-file binary format (*.ysql). It is not byte-compatible with the sqlite3 file format, and it is not a literal \PDO subclass (real PDO drivers are C extensions). Pure PHP will never match the C engine on raw per-row speed; the goal is portability and correctness, using every PHP-level trick (page cache, lazy column decoding, compiled plan cache, index planning, WAL) to be as fast as pure PHP allows — and for indexed, set-oriented work it gets surprisingly close. Compatibility is validated by differential testing against the real pdo_sqlite extension.

Install

Quick start

The constants use the same numeric values as \PDO, so \PDO::FETCH_ASSOC and YetiSQL\PDO::FETCH_ASSOC are interchangeable.

Doctrine DBAL

YetiSQL ships a Doctrine DBAL driver, so it plugs into the DBAL stack (DriverManager, QueryBuilder, schema manager, transactions). The driver extends AbstractSQLiteDriver, so DBAL reuses its SQLite platform and grammar.

DBAL's QueryBuilder CRUD, prepared statements, joins, transactions, and the schema manager (listTableNames, listTableColumns) are validated against the real DBAL stack in the test suite — this is the project's v1 compatibility gate.

Eloquent / Laravel

YetiSQL ships an Eloquent (illuminate/database) driver. Because YetiSQL speaks the SQLite dialect, the connection extends Laravel's SQLiteConnection, reusing its SQLite query grammar, schema grammar, and processor. YetiSql::register() wires a yetisql driver into a Capsule (or a Laravel app's DatabaseManager).

In a full Laravel app, call YetiSql::register(app('db')) from a service provider's boot() and set a connection with 'driver' => 'yetisql'. The schema builder (migrations + introspection), query builder, Eloquent models with relationships and eager loading, and transactions are validated against the real Eloquent stack in the test suite.

CLI

A small sqlite3-style shell ships in bin/yetisql:

What works today

• DDL — CREATE TABLE (type affinity, PRIMARY KEY, AUTOINCREMENT, NOT NULL, DEFAULT, UNIQUE, COLLATE, FOREIGN KEY … REFERENCES, and generated columns GENERATED ALWAYS AS (…) STORED/VIRTUAL), CREATE INDEX with real index B-trees the planner uses for equality/range/IN lookups, CREATE VIEW, CREATE TRIGGER, DROP TABLE/INDEX/VIEW/TRIGGER, and ALTER TABLE (RENAME TO, RENAME COLUMN, ADD COLUMN, DROP COLUMN).
• DML — INSERT (multi-row, OR REPLACE/OR IGNORE, INSERT … SELECT, DEFAULT VALUES), UPDATE, DELETE, all with an optional RETURNING clause, and positional (?, ?N) and named (:n/@n/$n) parameters.
• Constraints & integrity — UNIQUE / PRIMARY KEY enforcement backed by auto-created unique indexes (SQLite's sqlite_autoindex_* model) with full REPLACE / OR IGNORE conflict resolution, NOT NULL, and FOREIGN KEY enforcement under PRAGMA foreign_keys=ON (child existence checks; ON DELETE/ON UPDATE NO ACTION/RESTRICT/CASCADE/SET NULL/SET DEFAULT; composite and self-referential keys).
• Queries — SELECT with WHERE, INNER/LEFT/CROSS/comma joins, GROUP BY / HAVING, aggregates (count/sum/avg/min/max/total/group_concat), window functions (ROW_NUMBER/RANK/DENSE_RANK/NTILE/PERCENT_RANK/CUME_DIST, LAG/LEAD/FIRST_VALUE/LAST_VALUE/NTH_VALUE, running/PARTITION BY aggregates, and explicit ROWS/RANGE/GROUPS frames), CTEs (WITH, incl. WITH RECURSIVE), correlated and scalar subqueries, EXISTS, IN (…), ORDER BY (incl. positional and alias), LIMIT/OFFSET, DISTINCT, compound UNION/UNION ALL/INTERSECT/EXCEPT, CASE, CAST, LIKE/GLOB, subqueries in FROM (derived tables) and views, table-valued PRAGMA functions (pragma_table_info(…) etc.), and SQLite's affinity-aware comparison and three-valued logic.
• Views & triggers — CREATE VIEW (incl. explicit column lists and views over views); BEFORE/AFTER row triggers on INSERT/UPDATE/DELETE with NEW/OLD, WHEN gating, UPDATE OF, multi-statement bodies, and cascades, plus INSTEAD OF triggers that make views writable (non-recursive, matching recursive_triggers=OFF).
• Functions — a broad scalar set (abs, length, lower/upper, substr, trim family, replace, instr, round, coalesce, ifnull, nullif, typeof, hex, quote, printf/format, char/unicode, iif, …), plus the JSON1 surface (json, json_extract, json_type, json_valid, json_quote, json_array, json_object, json_array_length, json_set/insert/replace/remove/patch, the ->/->> operators, the json_group_array/json_group_object aggregates, and the json_each/json_tree table-valued functions).
• Transactions & durability — BEGIN/COMMIT/ROLLBACK with a crash-safe rollback journal or true WAL mode (PRAGMA journal_mode=WAL), and flock() (many readers / one writer). A crash mid-write rolls back cleanly under either mode.
• EXPLAIN — EXPLAIN <select> returns the compiled VDBE bytecode program and EXPLAIN QUERY PLAN returns the scan summary (see VDBE & EXPLAIN below).
• PRAGMA — table_info/table_xinfo, index_list, foreign_key_list, table_list, database_list, journal_mode (incl. wal), wal_checkpoint, foreign_keys, busy_timeout, and common settings.

Storage & durability

A YetiSQL database is one page-based binary file (app.ysql, default 4 KB pages) holding B+-trees for each table keyed by rowid, with overflow pages for large values. Multiple processes can safely share one file with single-writer / multiple-reader semantics — see Concurrency below (note: advisory flock() has known caveats on some network filesystems).

Two durability modes are available:

• Rollback journal (default). On commit the original page images are written to app.ysql-journal and fsync'd before the main file is updated; a crash during commit restores the pre-commit state on next open.
• WAL (PRAGMA journal_mode=WAL). Commits append the new page images to app.ysql-wal as frames terminated by a commit marker, leaving the main file untouched; reads merge the log over the main file. Recovery on open replays the WAL's committed prefix and discards any half-written trailing transaction. PRAGMA wal_checkpoint (and closing the database) folds the log back into the main file. WAL turns each commit's two fsyncs + journal churn into a single sequential append + fsync, so many small commits get ~3–4× faster (see Performance).

Performance

SQLite's C engine is the gold standard; pure-PHP YetiSQL is, unavoidably, slower on raw per-row work. benchmarks/bench.php measures how much, and shows what index-based planning buys. Representative run (in-memory, 5000 rows, PHP 8.3):

(The join and correlated-subquery ratios are dominated by SQLite rounding to ~0 ms at this scale; treat them as "fast enough to be noise on the C engine," not a literal multiple.)

(One measurement gotcha: a loaded xdebug in debug/develop mode inflates YetiSQL's side of this benchmark roughly 3× while barely touching the C engine. If your numbers look far worse than the table, check php -v for xdebug and re-run with -dxdebug.mode=off.)

Takeaways:

• Index planning is the headline. The YetiSQL+idx vs YetiSQL scan gap is what persistent indexes buy: selective COUNT is ~5×, range queries ~2.5×, and index-accelerated correlated subqueries stay faster than the no-index count-map path. Equality joins without a persistent index can still use a transient hash table, and unindexed equality COUNT(*) correlated subqueries can build a transient count map, so both avoid the old full-inner-scan-per-outer-row cost. Covered COUNT(*) over full tables, rowid ranges, and leading-column index predicates count B-tree cells directly without fetching rows, and UPDATE only re-indexes columns that change.
• For indexed, set-oriented work YetiSQL matches or beats SQLite in this in-memory harness — indexed COUNT and range queries land at parity, and the group-by aggregate is ~6× faster (covered counts and the compiled per-row loop never leave the process). Point operations, inserts, and updates stay an order of magnitude behind: the irreducible per-row cost of an interpreter in pure PHP against C.

Durability — WAL vs rollback journal (file-backed, 2000 single-statement commits):

WAL replaces two fsyncs + journal create/delete per commit with one fsync and a sequential append, so the win scales with the number of commits (a single big transaction commits once and sees no difference; in-memory never touches disk).

vs MySQL / MariaDB (benchmarks/bench_mysql.php, both durable: YetiSQL file-backed with a rollback journal, MariaDB 12.3 over the local socket with InnoDB's default per-commit fsync; 5000 rows, PHP 8.3):

This is apples-to-oranges by design, and that's the point: YetiSQL runs in-process while MariaDB is a client-server engine, so every statement crosses a socket to a separate process. That round-trip is exactly what an embedded database exists to avoid, and it shows: YetiSQL wins seven of the nine workloads here (Yeti/MySQL < 1) despite being pure PHP — bulk insert, point lookups, per-row updates, index builds, and every scan/aggregate column, where MariaDB pays IPC on each of hundreds of statements while YetiSQL's covered counts never leave the process. The remaining gaps are the tiny join and correlated-subquery rows, which are ~0.1–1.4 ms on MariaDB — the large ratio is sub-millisecond absolute time. The takeaway isn't "faster than MySQL"; it's that for the workloads where an app would reach for an embedded database in the first place, a zero-dependency pure-PHP engine is a genuine alternative.

vs other pure-PHP engines (benchmarks/bench_engines.php). Among pure-PHP libraries that actually parse and execute SQL — as opposed to flat-file document/key-value stores like SleekDB or query builders that wrap PDO over a C engine — the only other actively-maintained one is vimeo/php-mysql-engine. Both run fully in-memory, so this isolates the engine itself (5000 rows, medians):

Both engines return identical results on every query; the gap is real work. That said, this is not a like-for-like fight: php-mysql-engine is built as a unit-test double — an in-memory MySQL simulator for testing app code — optimised for dialect fidelity, not throughput, so it stores tables as PHP arrays and scans them linearly with no index planner (hence the ~9900× join, which it runs as a full nested-loop cross product). The honest takeaway is not "YetiSQL is hundreds of times faster than the competition"; it is that YetiSQL is the only pure-PHP option that is both a real SQL engine and built for speed, with persistent B-tree storage and indexes. The engine worth measuring YetiSQL against is SQLite's C core (above), not the other PHP libraries.

Hot-path optimisations already in place: an LRU page cache, a parsed-page cache (so repeated reads skip re-decoding), per-page decoded-key caches (binary searches and scans never re-decode cell bytes), an allocation-free record codec, single-pass page encoding, binary-search rowid lookups, lazy row decoding (columns materialize on first access), lazy/early-stop index scans, multi-column index prefix seeks, bulk bottom-up index builds sorted via packed memcmp keys, index-driven joins and correlated subqueries, covered table/index counts, a transient hash table for unindexed equality joins, transient count maps for unindexed equality COUNT(*) correlated subqueries, plus compiled-closure and memoized access-plan caches so re-executed prepared statements skip re-planning entirely.

VDBE & EXPLAIN

YetiSQL includes a small register virtual machine in the SQLite VDBE tradition. EXPLAIN compiles a single-table SELECT to a bytecode program and returns the disassembly:

PRAGMA vdbe=on routes compilable single-table scans through the VM instead of the tree-walker. It is off by default: in PHP a register-interpreter loop runs a bit slower than the existing composed-closure compilation, so the closure path stays the default hot loop. The VM's value is the VDBE architecture and EXPLAIN introspection; its results are verified against pdo_sqlite, so the bytecode and the interpreter agree row-for-row.

Architecture

The default execution model is a tree-walking interpreter with a compiled-closure fast path for the per-row hot loop; the VDBE bytecode compiler + register VM (above) is an alternative engine used for EXPLAIN and available opt-in via PRAGMA vdbe=on.

Roadmap

Working: secondary-index, multi-column-index, and rowid query planning (equality, range, IN, BETWEEN) with automatic index maintenance on writes; UNIQUE and PRIMARY KEY constraint enforcement (backed by auto-created unique indexes, like SQLite's sqlite_autoindex_*) with REPLACE / INSERT OR REPLACE / INSERT OR IGNORE / UPDATE OR REPLACE/IGNORE conflict resolution; index-driven joins and index-accelerated/count-map correlated subqueries; covering-index counts (persisted subtree row counts); CTEs (incl. recursive), window functions, views, triggers (incl. INSTEAD OF), ALTER TABLE, true WAL mode, a VDBE bytecode compiler with EXPLAIN; the JSON1 functions (json, json_extract, json_type, json_valid, json_quote, json_array, json_object, json_array_length, json_set/insert/replace/remove/patch), the -> and ->> operators, the json_group_array/json_group_object aggregates, and the json_each/json_tree table-valued functions; RETURNING on INSERT/UPDATE/DELETE; generated columns (GENERATED ALWAYS AS, STORED and VIRTUAL); FOREIGN KEY enforcement under PRAGMA foreign_keys=ON (child existence checks plus ON DELETE/ON UPDATE NO ACTION/RESTRICT/CASCADE/SET NULL/SET DEFAULT, composite and self-referential keys, pragma_foreign_key_list); and Doctrine DBAL and Eloquent adapters.

Not yet implemented (planned): FTS5 extension and full VDBE execution of every query (today the VM covers single-table scans and the tree-walker handles the rest). Byte-level sqlite3 file interop is out of scope by design. Foreign keys are enforced immediately: DEFERRABLE / INITIALLY DEFERRED clauses parse and run but behave as immediate constraints.

Generated columns are computed and stored on every write for both STORED and VIRTUAL kinds — value-identical to SQLite for the deterministic expressions they're allowed to use, so a VIRTUAL column occupies storage it wouldn't under SQLite (no query-result difference).

JSON1 has two documented divergences from SQLite, both stemming from YetiSQL's value model having no JSON "subtype": (1) feeding one JSON function's text output into another embeds it as a string rather than as JSON (e.g. json_array(json('[1]')) yields ["[1]"], not [[1]]); plain-column inputs match. (2) json() normalizes numeric literals while re-minifying, so a redundant trailing zero is dropped (json('[2.50]') → [2.5]); extracted and constructed values are unaffected.

Concurrency

YetiSQL is safe for multiple processes (e.g. concurrent PHP-FPM requests) sharing one .ysql file, with single-writer / multiple-reader semantics like SQLite's rollback-journal mode:

• Writers are serialized by an advisory flock(LOCK_EX) held only for the duration of a write transaction (a single statement in autocommit). On entering a write transaction the pager re-reads the committed header under the lock and drops any stale page cache, so a writer always operates on the latest committed state — no lost updates or B-tree corruption when two processes write at once.
• Opening a connection never blocks on another connection's write. A crashed writer's "hot" journal is recovered under a non-blocking lock (a live writer's in-flight commit is left alone).
• Reused connections stay coherent: a read picks up other processes' commits (including schema changes) rather than serving stale cached pages, validated by a change-counter / WAL-size check.
• PRAGMA busy_timeout = <ms> controls how long a contended write waits for the lock before raising database is locked (default 5000 ms) — it fails fast instead of hanging.

Because writes take a whole-file exclusive lock, a write-heavy workload (e.g. tracking a counter on every page view) serializes all writers through one lock — correct, but a throughput ceiling, the same as SQLite's default journal mode. Cross-process coherence relies on local-filesystem flock semantics (not guaranteed over some network filesystems). This is verified by a forked multi-process test suite (tests/Concurrency) that asserts no lost updates or corruption under heavy contention in both journal and WAL modes.

Testing

The suite (385 tests) includes unit tests (storage engine, codecs, WAL recovery), a sqllogictest-style conformance corpus, a differential oracle that runs identical SQL against the real pdo_sqlite extension and asserts the results match (covering joins, CTEs, window functions, views, triggers, ALTER TABLE, JSON1, RETURNING, generated columns, foreign keys, and the VDBE VM), a forked multi-process concurrency suite (tests/Concurrency, ext-pcntl) that asserts no lost updates or corruption under heavy contention in both journal and WAL modes, and Doctrine DBAL and Eloquent integration suites that drive YetiSQL through the real ORM stacks (QueryBuilder, schema manager/migrations, relationships, transactions). The differential oracle is the project's correctness gate: new features ship only once they match pdo_sqlite row-for-row.

License

MIT © YetiDevWorks / Andy Miller