README

Lightweight and flexible PHP database wrapper with support for multiple database types, query building, and optional logging.

Installation

composer require solophp/database

Features

Support for MySQL, PostgreSQL, SQLite, SQL Server, and other PDO-compatible databases
Safe query building with type-specific placeholders
Flexible null value support, including for date parameters
Configurable fetch modes (arrays or objects)
Query preparation without execution
Optional table prefixing
Integration with PSR-3 compatible Solo Logger
Transaction support
Clean and flexible API with method chaining

Requirements

PHP 8.2+
PDO extension
Solo Logger ^1.0

API Reference

Method	Arguments	Description	Return Type
`query()`	`string $sql, mixed ...$params`	Execute a query with placeholders	`self`
`prepare()`	`string $sql, mixed ...$params`	Prepare SQL string without execution	`string`
`fetchAll()`	`?int $fetchMode = null`	Fetch all rows	`array<int
`fetch()`	`?int $fetchMode = null`	Fetch single row	`array
`fetchColumn()`	`int $columnIndex = 0`	Fetch single column from next row	`mixed`
`lastInsertId()`	—	Get last inserted ID	`string
`rowCount()`	—	Get number of affected rows	`int`
`beginTransaction()`	—	Begin transaction	`void`
`commit()`	—	Commit transaction	`void`
`rollBack()`	—	Roll back transaction	`void`
`inTransaction()`	—	Check if in transaction	`bool`
`withTransaction()`	`callable $callback`	Run logic inside a safe transaction (auto rollback on exception)	`mixed`

Query Placeholders

Placeholder	Description
`?s`	String (safely quoted)
`?i`	Integer
`?f`	Float
`?a`	Array (for IN statements)
`?A`	Associative Array (for SET statements)
`?t`	Table name (with prefix)
`?c`	Column name (safely quoted)
`?d`	Date (DateTimeImmutable or null)
`?l`	LIKE condition with wildcards
`?M`	Multi-row INSERT (array of arrays)
`?r`	Raw parameter (unescaped)

Usage

Basic Configuration

use Solo\Database\{Config, Connection};
use Solo\Logger;
use PDO;

$config = new Config(
    hostname: 'localhost',
    username: 'user',
    password: 'pass',
    dbname: 'mydb',
    prefix: 'prefix_',
    fetchMode: PDO::FETCH_OBJ
);

$logger = new Logger('/path/to/logs/db.log');
$connection = new Connection($config, $logger);
$db = new Database($connection);

Query Examples

// Basic SELECT
$users = $db->query("SELECT * FROM ?t", 'users')->fetchAll();

// INSERT with associative array
$userData = [
    'name' => 'John Doe',
    'email' => 'john@example.com',
    'age' => 25,
    'created_at' => new DateTimeImmutable()
];
$db->query("INSERT INTO ?t SET ?A", 'users', $userData);

// INSERT multiple rows
$data = [['John', 30], ['Alice', 25]];
$db->query("INSERT INTO ?t (name, age) VALUES ?M", 'users', $data);

// Handling null values
$userData = [
    'name' => 'Jane Doe',
    'created_at' => new DateTimeImmutable(),
    'updated_at' => null,
];
$db->query("INSERT INTO ?t SET ?A", 'users', $userData);

// Fetch single row
$user = $db->query("SELECT * FROM ?t WHERE id = ?i", 'users', 1)->fetch();

// Override fetch mode
$userArray = $db->query("SELECT * FROM ?t WHERE id = ?i", 'users', 1)->fetch(PDO::FETCH_ASSOC);

// IN clause
$ids = [1, 2, 3];
$result = $db->query("SELECT * FROM ?t WHERE id IN ?a", 'users', $ids)->fetchAll();

// Dynamic column
$column = 'email';
$userEmail = $db->query("SELECT ?c FROM ?t WHERE id = ?i", $column, 'users', 1)->fetchColumn();

// Transaction (classic)
try {
    $db->beginTransaction();
    
    $db->query("INSERT INTO ?t SET ?A", 'orders', ['product' => 'Laptop']);
    $db->query("UPDATE ?t SET balance = balance - ?f WHERE id = ?i", 'accounts', 799.99, 1);
    
    $db->commit();
} catch (Exception $e) {
    if ($db->inTransaction()) {
    $db->rollBack();
    }
    throw $e;
}

// Transaction (preferred)
$db->withTransaction(function () use ($db) {
    $db->query("INSERT INTO ?t SET ?A", 'orders', ['product' => 'Laptop']);
    $db->query("UPDATE ?t SET balance = balance - ?f WHERE id = ?i", 'accounts', 799.99, 1);
});

// Prepare only
$sql = $db->prepare("SELECT * FROM ?t WHERE user_id = ?i AND status = ?s", 'orders', 15, 'pending');

// Fetch column
$email = $db->query("SELECT email FROM ?t WHERE id = ?i", 'users', 1)->fetchColumn();

// Raw expressions
use Solo\Database\Expressions\RawExpression;
$db->query("UPDATE ?t SET ?A WHERE id = ?i", 'orders', [
    'number' => new RawExpression("CONCAT(RIGHT(phone, 4), '-', id)")
], 42);

Database Support

Date formatting is handled automatically per driver:

PostgreSQL: Y-m-d H:i:s.u P
MySQL: Y-m-d H:i:s
SQLite: Y-m-d H:i:s
SQL Server: Y-m-d H:i:s.u
DBLIB: Y-m-d H:i:s
CUBRID: Y-m-d H:i:s

Return Types

Method	Description	Return Type
`fetchAll(?int $fetchMode = null)`	Get all rows	`array<int
`fetch(?int $fetchMode = null)`	Get one row	`array
`fetchColumn(int $columnIndex = 0)`	Get column from next row	`mixed`
`rowCount()`	Affected rows from last query	`int`
`lastInsertId()`	Last inserted auto-increment ID	`string

Error Handling

All database operations are wrapped in try-catch blocks
Exceptions are thrown with meaningful messages
Logging is automatic when PSR-3 logger is configured
Transactions via withTransaction() auto-rollback on failure

License

MIT License. See LICENSE file for details.

solophp / database

Maintainers

Details