Database Operations

Purpose and Scope

This document covers database operations in the hypervel/support package through the DB facade. The DB facade provides a convenient static interface to Hyperf's database connection and query builder systems, including query building, raw SQL execution, transaction management, and connection pooling.

For cache operations, see Cache Management. For Redis-specific operations, see Redis Operations. For general facade architecture, see Facade System.

Sources: src/Facades/DB.php1-40

---

DB Facade Architecture

The DB facade serves as a static proxy to Hyperf's DbConnection\Db class, which itself manages database connections and query execution. The facade resolves to the underlying Hyperf database layer through the service container.

System Architecture Diagram

Facade Resolution Flow

Sources: src/Facades/DB.php33-39

---

Query Building

The DB::table() method returns a query builder instance that provides a fluent interface for constructing SQL queries. The query builder supports all standard SQL operations through chainable methods.

Query Builder Methods

Method	Purpose	Returns
table(string $table)	Initialize query for table	Query\Builder
raw(mixed $value)	Create raw SQL expression	Query\Expression
select()	Select specific columns	Query\Builder
where()	Add WHERE clause	Query\Builder
join()	Add JOIN clause	Query\Builder
orderBy()	Add ORDER BY clause	Query\Builder
groupBy()	Add GROUP BY clause	Query\Builder
get()	Execute and fetch results	Collection
first()	Fetch first result	object|null
find($id)	Find by primary key	object|null

Basic Query Building Example

Sources: src/Facades/DB.php11-12

---

Raw SQL Operations

The DB facade provides methods for executing raw SQL statements directly when the query builder is insufficient or when you need fine-grained control over SQL execution.

Raw SQL Methods

Method	Purpose	Return Type	Use Case
select(string $query, array $bindings, bool $useReadPdo)	Execute SELECT query	array	Retrieve multiple rows
selectOne(string $query, array $bindings, bool $useReadPdo)	Execute SELECT for single row	mixed	Retrieve single row
cursor(string $query, array $bindings, bool $useReadPdo)	Execute SELECT with cursor	Generator	Large result sets
insert(string $query, array $bindings)	Execute INSERT statement	bool	Insert data
update(string $query, array $bindings)	Execute UPDATE statement	int	Update rows, returns affected count
delete(string $query, array $bindings)	Execute DELETE statement	int	Delete rows, returns affected count
statement(string $query, array $bindings)	Execute arbitrary statement	bool	DDL operations
affectingStatement(string $query, array $bindings)	Execute statement, return affected rows	int	Statements that affect rows
unprepared(string $query)	Execute raw SQL without binding	bool	Multi-statement scripts

Parameter Binding

All methods that accept $bindings use PDO prepared statements to prevent SQL injection. The prepareBindings(array $bindings) method processes bindings before execution.

Read/Write Connection Splitting

The $useReadPdo parameter (default: true) on SELECT operations allows directing queries to read replicas for load balancing.

Sources: src/Facades/DB.php13-22

---

Transaction Management

The DB facade provides comprehensive transaction support with automatic rollback on exceptions and nested transaction handling through savepoints.

Transaction Flow Diagram

Transaction Methods

Method	Description
transaction(Closure $callback, int $attempts)	Execute callback within transaction, auto-commit/rollback
beginTransaction()	Manually start transaction
commit()	Commit active transaction
rollBack()	Rollback active transaction
transactionLevel()	Get current nesting level (0 = no transaction)

Transaction Usage Patterns

Sources: src/Facades/DB.php23-28

---

Connection Management

The DB facade supports multiple database connections through connection pooling. Each connection can target different databases or use different drivers.

Connection Resolution

Using Multiple Connections

Connection Pool Configuration

Connection pools are configured in Hyperf's database configuration. Each pool specifies driver, host, credentials, and pooling parameters. The DB::connection(?string $pool) method resolves the named pool from configuration.

Sources: src/Facades/DB.php29

---

Advanced Features

Execution Hooks

The beforeExecuting(Closure $callback) method registers callbacks that execute before every query, useful for logging, monitoring, or query modification.

Pretend Mode

The pretend(Closure $callback) method allows testing query generation without actually executing statements against the database. It returns an array of queries that would have been executed.

Method Reference Table

This table maps all DB facade methods to their underlying functionality:

Category	Methods	Purpose
Query Building	table(), raw()	Build fluent queries
Read Operations	select(), selectOne(), cursor()	Execute SELECT statements
Write Operations	insert(), update(), delete()	Modify data
Statements	statement(), affectingStatement(), unprepared()	Execute arbitrary SQL
Transactions	transaction(), beginTransaction(), commit(), rollBack(), transactionLevel()	Manage transactions
Connections	connection()	Access connection pools
Utilities	prepareBindings(), beforeExecuting(), pretend()	Helper functions

Sources: src/Facades/DB.php10-29

---

Integration with Hyperf Database Layer

The DB facade delegates all operations to Hyperf\DbConnection\Db, which provides the actual implementation. This means:

1. Full Hyperf Compatibility: All Hyperf database features are available
2. Connection Pooling: Leverages Hyperf's coroutine-safe connection pooling
3. Query Builder: Uses Hyperf's query builder implementation
4. Event System: Database events integrate with Hyperf's event dispatcher

Underlying Implementation Chain

Sources: src/Facades/DB.php7-39