Database Abstraction Layer

One API for every relational database, from a connection URI: MySQL, PostgreSQL, SQL Server, Oracle, SQLite, DuckDB and Cloudflare D1. Part of the AnyDataset project.

Learn more about Anydataset here.

Why

PDO gets you connected, but every database still differs: connection options, quoting, LIMIT versus TOP, date functions, transactions. And the things an application needs around the connection, such as caching, read replicas and a clean database for every test, are left to you.

AnyDataset-DB starts from a connection URI and handles the differences:

$executor = DatabaseExecutor::using(Factory::getDbInstance('mysql://user:pass@host/db'));
$iterator = $executor->getIterator('select * from users where id = :id', ['id' => 10]);

• Change the database with the URI. mysql://, pgsql://, sqlsrv://, oci8://, sqlite://, duckdb://, d1://. Cloudflare D1 and DuckDB are supported alongside the usual ones.
• Undo what a test changed. The Journal records every INSERT/UPDATE/DELETE with the rows before and after, and restores them on tearDown(), without recreating the database.
• Route queries. DatabaseRouter sends each query to a different connection by a regular expression, e.g. writes to the primary and reads to the replicas (Load Balance).

Features

• Connection based on URI
• Handles compatibility and code optimization across multiple databases (e.g., MySQL, PostgreSQL, MS SQL Server)
• Built-in Query Cache support using a PSR-16 compliant interface
• Enables connection routing based on regular expressions for queries (e.g., directing queries to different databases for specific tables)

Connection Based on URI

Database connections are defined using URL-based connection strings.

Supported drivers are listed below:

Database	Connection String	Factory Method
SQLite	sqlite:///path/to/file	getDbInstance()
DuckDB	duckdb:///path/to/file (duckdb:// for in-memory)	getDbInstance()
MySQL/MariaDB	mysql://username:password@hostname:port/database	getDbInstance()
PostgreSQL	pgsql://username:password@hostname:port/database	getDbInstance()
SQL Server (DbLib)	dblib://username:password@hostname:port/database	getDbInstance()
SQL Server (Sqlsrv)	sqlsrv://username:password@hostname:port/database	getDbInstance()
Oracle (OCI8)	oci8://username:password@hostname:port/database	getDbInstance()
Oracle (PDO OCI)	oci://username:password@hostname:port/database	getDbInstance()
Generic PDO	pdo://username:password@pdo_driver?PDO_PARAMETERS	getDbInstance()
Cloudflare D1	d1://account_id:api_token@api.cloudflare.com/database_id	getDbInstance()

Example usage:

<?php
$conn = \ByJG\AnyDataset\Db\Factory::getDbInstance("mysql://root:[email protected]/myschema");

Examples

• Getting Started
• Basic Query and Update
• Sql Statement Object
• Cache results
• Database Transaction
• Load Balance and Connection Pooling
• Database Helper
• Filtering the Query
• Entity Mapping

Advanced Topics

• Database Driver Interface
• DatabaseExecutor - Recommended API
• Executor Observers
• Journal (Record and Restore Changes)
• Passing Parameters to PDODriver
• Generic PDO Driver
• Driver: DuckDB
• Running Tests
• Installing the PHP database extensions
• Getting an Iterator from an existing PDO Statement
• Pre Fetch records
• Logging
• Deprecated Features

Database Specifics

• MySQL
• PostgreSQL
• Oracle
• SQLServer
• Cloudflare D1
• Literal PDO Connection String

Installation

Just type:

composer require "byjg/anydataset-db"

Running Tests

docker compose up -d
vendor/bin/phpunit

More details, including the database suites, are in Running Unit tests.

---

Open source ByJG