Download the PHP package pinoox/devdb without Composer

DevDB

DevDB is a development-only database component for PHP projects. It gives you a small local database with a raw SQL translator for common development queries, so you can build and test features without installing MySQL, PostgreSQL, or SQLite.

DevDB uses SQLite when available and automatically falls back to a zero-dependency JSON database when it is not.

DevDB is designed for local development, prototypes, tests, demos, package examples, and single-user tooling. It is not a production database.

Why DevDB?

Use DevDB when you want a project to run immediately on a developer machine, CI job, demo environment, or package example without asking people to install and configure a database server first.

DevDB is useful for:

• local development without MySQL, PostgreSQL, or SQLite setup
• package examples and documentation demos
• quick prototypes and throwaway experiments
• tests that need simple persistent data
• single-user developer tools
• importing simple MySQL-style schema dumps for local inspection
• trying application flows before choosing the final database

DevDB is not meant for:

• production applications
• multi-user or high-concurrency workloads
• large datasets
• strict relational integrity
• full SQL compatibility
• performance benchmarking
• replacing SQLite, MySQL, PostgreSQL, or SQL Server in deployed systems

The short version: DevDB removes setup friction during development. Use it to start fast, test ideas, and keep examples portable. Switch to SQLite or a real database when correctness, scale, concurrency, or production reliability matters.

Table of Contents

• Why DevDB?
• Features
• Installation
• Normal PHP Usage
• PDO and mysqli-like Adapters
• Using DevDB in Pinoox
• Using DevDB in Laravel
• Storage Format
• Compatibility Matrix
• Raw SQL Support
• Schema SQL Support
• Snapshots and Change Manifests
• Standalone API
• Laravel-Compatible Connection
• CLI Commands
• Troubleshooting
• Performance Expectations
• Limitations
• Package Structure
• Development
• License

Features

• JSON-backed local database storage.
• SQLite-first local storage with automatic JSON fallback.
• Engine abstraction for SQLite and JSON storage backends.
• No external database service required.
• File-based schema, data, migrations, and sequence metadata.
• File locking for JSON writes.
• Auto-increment sequences.
• Named snapshots for quick save and restore during development.
• Change manifests for detecting external JSON file changes.
• Common CRUD query support.
• Raw SQL translator for common SELECT, INSERT, UPDATE, DELETE, and TRUNCATE statements.
• Multi-statement SQL dump execution for common MySQL exports.
• Lightweight EXPLAIN output for debugging translated queries.
• Strict development checks for NOT NULL, ENUM, UNIQUE, and simple foreign keys.
• PDO-like and mysqli-like adapters for plain PHP projects.
• SQL functions such as DATE, LOWER, COALESCE, CONCAT, ROUND, and more.
• A standalone PHP API through Pinoox\DevDB\DevDatabase.
• A Laravel-compatible connection class through Pinoox\Component\Database\Connections\DevDbConnection.
• Development-only by design.

Installation

Normal PHP Usage

Use Pinoox\DevDB\DevDatabase when you want DevDB as a standalone component outside any framework.

You can also run write statements and inspect the result:

PDO and mysqli-like Adapters

DevDB cannot replace PHP's built-in PDO or mysqli extensions transparently. Those extensions talk to real database drivers. For plain PHP projects, DevDB provides lightweight compatibility adapters with familiar method names.

PDO-like usage

Supported common methods include query(), exec(), prepare(), execute(), bindValue(), fetch(), fetchAll(), fetchColumn(), rowCount(), lastInsertId(), beginTransaction(), commit(), and rollBack().

mysqli-like usage

Supported common methods and properties include query(), fetch_assoc(), fetch_object(), fetch_array(), fetch_all(), num_rows, affected_rows, insert_id, real_escape_string(), begin_transaction(), commit(), and rollback().

Using DevDB in Pinoox

Install DevDB as a development dependency in your app or platform:

Recommended local environment:

With Pinoox integration enabled, normal app code can continue to use models, migrations, and database facades:

DevDB is intended for local development only. Production environments should use a real database connection.

Using DevDB in Laravel

DevDB includes a Laravel-compatible connection class. You can register it manually through Laravel's database extension mechanism.

Example service provider:

Example config/database.php connection:

Example .env:

Then you can use Laravel's database API:

Laravel migrations may require project-specific integration because DevDB stores schema metadata instead of executing all SQL DDL in JSON mode.

Storage Format

DevDB stores data under the configured path.

Default standalone example:

JSON engine files:

schema.json describes tables and columns. Each table has a JSON data file under data/. Auto-increment values are stored in meta/sequences.json.

Compatibility Matrix

DevDB aims to cover the SQL and query behavior developers commonly hit while building local apps, tests, examples, and demos. It is intentionally conservative: unsupported features fail with a clear DevDbException instead of silently pretending to be a full database server.

Raw SQL Support

DevDB translates common raw SQL statements into JSON operations.

Supported data statements:

• SELECT
• SELECT DISTINCT
• SELECT ... UNION SELECT ...
• SELECT ... UNION ALL SELECT ...
• INSERT
• INSERT INTO ... VALUES with or without an explicit column list
• INSERT INTO ... SELECT
• UPDATE
• DELETE
• TRUNCATE
• EXPLAIN SELECT ...

Supported SELECT features:

• table aliases
• column aliases
• INNER JOIN
• LEFT JOIN
• WHERE
• GROUP BY
• HAVING
• ORDER BY
• LIMIT
• OFFSET
• simple subqueries in IN (...)
• scalar subqueries in comparisons
• simple views created with CREATE VIEW ... AS SELECT ...

Supported operators and predicates:

• =
• ==
• !=
• <>
• >
• >=
• <
• <=
• LIKE
• NOT LIKE
• IN
• NOT IN
• BETWEEN
• NOT BETWEEN
• IS NULL
• IS NOT NULL
• AND
• OR
• NOT
• EXISTS
• NOT EXISTS
• scalar subquery comparisons

Supported development helpers:

• executeDump() for multi-statement SQL imports
• explain() for inspecting how DevDB understands a query
• strict constraint validation, enabled by default
• parenthesized boolean groups
• compatibility no-op handling for LOCK TABLES and UNLOCK TABLES

Supported aggregate functions:

• COUNT
• SUM
• AVG
• MIN
• MAX

Supported scalar functions:

• DATE
• TIME
• DATETIME
• TIMESTAMP
• YEAR
• MONTH
• DAY
• DAYOFMONTH
• HOUR
• MINUTE
• SECOND
• LOWER
• LCASE
• UPPER
• UCASE
• TRIM
• LTRIM
• RTRIM
• LENGTH
• CHAR_LENGTH
• CHARACTER_LENGTH
• COALESCE
• IFNULL
• NULLIF
• CONCAT
• SUBSTR
• SUBSTRING
• REPLACE
• ABS
• ROUND
• FLOOR
• CEIL
• CEILING
• CURRENT_DATE
• CURRENT_TIME
• CURRENT_TIMESTAMP
• NOW

Example:

Schema SQL Support

DevDB can translate common schema and introspection statements into metadata operations.

DevDB also accepts common MySQL dump compatibility syntax such as:

• SET NAMES utf8mb4
• SET FOREIGN_KEY_CHECKS = 0
• SQL comments in dump files
• multiple semicolon-separated statements through executeDump()
• backtick-quoted identifiers
• AUTO_INCREMENT
• table options after CREATE TABLE, such as ENGINE, AUTO_INCREMENT, CHARACTER SET, COLLATE, and ROW_FORMAT
• PRIMARY KEY (...) USING BTREE
• UNIQUE INDEX ... USING BTREE
• INDEX ... USING BTREE
• FOREIGN KEY ... REFERENCES ... ON DELETE ... ON UPDATE ...
• ENUM(...)
• CREATE VIEW ... AS SELECT ...
• CREATE OR REPLACE VIEW ... AS SELECT ...
• DROP VIEW
• DROP VIEW IF EXISTS
• LOCK TABLES ...
• UNLOCK TABLES
• CREATE DATABASE, DROP DATABASE, and USE as local compatibility no-op statements
• SHOW DATABASES

In strict mode, DevDB validates common development constraints while inserting or updating rows:

• NOT NULL
• ENUM(...)
• primary keys
• unique indexes
• simple foreign keys where the referenced table exists

Supported schema statements:

• SET ...
• CREATE TABLE
• CREATE TABLE IF NOT EXISTS
• DROP TABLE
• DROP TABLE IF EXISTS
• ALTER TABLE ... ADD COLUMN
• ALTER TABLE ... DROP COLUMN
• ALTER TABLE ... RENAME COLUMN ... TO ...
• ALTER TABLE ... RENAME TO ...
• ALTER TABLE ... ADD PRIMARY KEY (...)
• ALTER TABLE ... ADD UNIQUE (...)
• ALTER TABLE ... ADD INDEX (...)
• CREATE INDEX ... ON ... (...)
• CREATE UNIQUE INDEX ... ON ... (...)
• DROP INDEX ...
• DROP INDEX ... ON ...

Supported introspection statements:

• SHOW TABLES
• SHOW TABLES LIKE 'pattern'
• DESCRIBE table
• DESC table
• SHOW COLUMNS FROM table
• SHOW INDEX FROM table
• SHOW INDEXES FROM table
• SHOW KEYS FROM table

Example:

Snapshots and Change Manifests

DevDB can create named snapshots of the full schema, data, and metadata export.

List and delete snapshots:

DevDB can also write a manifest of tracked files and later detect whether any tracked JSON data changed.

Tracked files include schema.json, table data files, and core metadata files. Snapshot files and the manifest file itself are ignored to avoid false positives.

Standalone API

DevDatabase::open()

createTable()

select()

selectOne()

statement()

execute()

executeDump()

executeDump() splits and executes multi-statement SQL dumps. It ignores common SQL comments and accepts compatibility statements such as SET NAMES utf8mb4, SET FOREIGN_KEY_CHECKS = 0, CREATE DATABASE, DROP DATABASE, and USE as local no-op operations.

explain()

explain() returns a small debug plan with the parsed table, alias, joins, filters, ordering, and limits. It is useful when a raw query does not behave the way you expected.

strict()

Strict mode is enabled by default. It validates NOT NULL, ENUM, UNIQUE, primary key, and simple foreign key constraints. Disable it only for loose development imports where you prefer loading imperfect fixture data over failing fast.

store()

clear()

snapshot()

snapshots()

restoreSnapshot()

deleteSnapshot()

writeManifest()

hasChangesSinceManifest()

Laravel-Compatible Connection

The lower-level connection class is:

Example:

CLI Commands

Available command names and what they do:

Check Status

Use devdb:status to see where DevDB stores data, which engine is active, and which tables exist.

JSON output is useful for tools and automation:

Inspect a Table

Use devdb:inspect when you want to quickly check a table structure and a few rows:

Limit the number of previewed rows:

Return JSON instead of a console table:

Export as JSON

Use devdb:export to create a portable DevDB backup or debug artifact:

If no file is provided, the JSON is printed to the console:

Export for phpMyAdmin or MySQL

Use devdb:export:mysql when you want to inspect DevDB data in phpMyAdmin. The command generates a MySQL-compatible SQL file:

Then import storage/devdb/devdb.sql into phpMyAdmin.

By default the dump includes DROP TABLE IF EXISTS statements so repeated imports are easier during development. Disable that with:

Export only schema or only data:

Export specific tables:

Import from MySQL or phpMyAdmin

Use devdb:import:mysql when you have a MySQL dump and want to load it into DevDB:

For loose fixture imports where you do not want strict constraints to block loading:

Sync Directly to MySQL

Use devdb:sync:mysql when you have a local MySQL or MariaDB server and want DevDB copied into it automatically:

You can also pass a full PDO DSN:

Available MySQL options:

Direct sync requires the pdo_mysql PHP extension. If it is not available, use devdb:export:mysql and import the SQL file manually through phpMyAdmin.

Preview a sync:

Check and Repair DevDB

Use devdb:doctor to inspect storage health:

Use devdb:repair to recreate missing data files, refresh metadata, update stale sequences, and write a fresh manifest:

Snapshots

Snapshots are useful before running destructive experiments or imports:

Clear DevDB

Use devdb:clear to delete the local DevDB data and metadata:

By default, DevDB creates a snapshot before clearing. Disable that with:

Skip the confirmation prompt in scripts:

Run Seeders

In a Pinoox host application, devdb:seed forces the database connection to DevDB and runs app seeders:

Run only one seeder class:

Continue when a seeder fails:

Environment Variables

The commands read the normal DevDB environment values:

The MySQL sync command can also read:

Command registration depends on the host application or framework.

Troubleshooting

SQLite is not installed

DevDB uses SQLite when pdo_sqlite is available. If it is not available, DevDB automatically uses the JSON engine. No extra setup is required.

MySQL sync fails

Direct sync requires pdo_mysql and a reachable MySQL or MariaDB database. If pdo_mysql is missing, run:

Then import the file manually through phpMyAdmin.

A query is not supported

DevDB throws a clear exception for unsupported SQL. Try simplifying the query, using the SQLite engine, or syncing to MySQL for exact server behavior.

IDs look wrong after editing JSON files

Run:

This refreshes sequence metadata from table data.

Performance Expectations

DevDB is optimized for local development, small demos, tests, and package examples.

• SQLite engine is preferred when available.
• JSON fallback is zero-dependency and best for small to medium local datasets.
• Large tables, high concurrency, and production workloads should use SQLite/MySQL/PostgreSQL directly.
• JSON writes use file locking, but they are not a substitute for real database locks.
• Use devdb:sync:mysql or devdb:export:mysql when you need phpMyAdmin, MySQL tooling, or exact SQL-server behavior.

Limitations

DevDB is intentionally development-first, not a replacement for a full SQL server.

The remaining limitations are intentionally narrow:

• recursive CTE queries
• correlated subqueries that reference the outer row
• full SQL optimizer/query planner behavior
• complete coverage of every vendor-specific SQL function
• complete trigger/procedure execution semantics
• isolation-level transaction behavior
• real database locks
• production workloads

DevDB supports common UNION, UNION ALL, simple subqueries, scalar subqueries, simple views, compatibility locks, many MySQL-style functions, CASE, ON DUPLICATE KEY UPDATE, ROW_NUMBER(), simple foreign key validation, and basic ON DELETE CASCADE / SET NULL behavior. Trigger, procedure, and function definitions from dumps are accepted as metadata for compatibility, but they are not executed. When a query is still not supported, DevDB throws a clear exception. For exact SQL-server behavior, use SQLite, MySQL, PostgreSQL, or another real database engine.

Package Structure

Development

Validate the package:

Run the package test suite:

Run the full quality gate:

Run only one layer:

Lint source files:

Run the host project's DevDB test suite when integrating the package with a framework.

License

MIT