Database Store

Purpose and Scope

The Database Store provides SQL-backed persistent caching using a relational database as the storage backend. This implementation stores cache entries in database tables with automatic expiration management, supports atomic operations via database transactions, and integrates with the distributed locking system through a separate lock table.

For information about the base Store interface contract, see Store Contracts. For distributed locking mechanisms, see Distributed Locking. For cache management commands that work with database stores, see Console Commands.

Sources: src/DatabaseStore.php1-336

---

Architecture Overview

Class Structure

DatabaseStore Class Hierarchy

The DatabaseStore class implements both the Store interface for cache operations and the LockProvider interface for distributed locking. It uses a connection resolver to obtain fresh database connections from the pool and manages two separate tables: one for cache entries and one for distributed locks.

Sources: src/DatabaseStore.php17-75

---

Database Schema

Cache Table Structure

The cache table requires three columns to store cache entries:

Column	Type	Purpose
key	string	Prefixed cache key (primary key or unique index)
value	text/blob	Serialized cache value
expiration	integer	Unix timestamp for expiration

Lock Table Structure

The lock table is managed by DatabaseLock but configured through DatabaseStore:

Column	Type	Purpose
key	string	Lock name (primary key or unique index)
owner	string	Lock owner identifier
expiration	integer	Unix timestamp for lock expiration

Sources: src/DatabaseStore.php59-75 publish/cache.php74-82

---

Configuration

The database store is configured through the cache configuration file:

Configuration Parameters

Parameter	Type	Default	Description
connection	string	'default'	Database connection name for cache operations
table	string	'cache'	Table name for cache entries
lock_connection	string	(same as connection)	Separate connection for lock operations
lock_table	string	'cache_locks'	Table name for distributed locks
lock_lottery	array	[2, 100]	Probabilistic cleanup odds [wins, attempts]
lock_timeout	integer	86400	Default lock timeout in seconds (24 hours)

Sources: publish/cache.php74-82 src/DatabaseStore.php59-75

---

Connection Management

Connection Resolution

Connection Resolution Flow

The connection() method retrieves a fresh connection from the pool for each operation. This ensures proper connection lifecycle management in pooled environments like Swoole/Hyperf where connections are shared across coroutines.

Sources: src/DatabaseStore.php80-83

---

Cache Operations

Retrieval Operations

Get Operation Flow

Get Operation with Automatic Cleanup

The get() method delegates to many() which retrieves values and automatically removes expired entries. The operation uses time-based partitioning to identify expired records and deletes them in a single query with an additional time check to prevent race conditions.

Sources: src/DatabaseStore.php88-137

Many Operation Implementation

The many() method src/DatabaseStore.php98-137 performs batch retrieval with several optimizations:

1. Batch Query: Uses whereIn() to retrieve all keys in a single query
2. Expiration Check: Partitions results into valid and expired entries
3. Automatic Cleanup: Deletes expired entries with double-check on expiration time
4. Null Handling: Returns null for missing or expired keys

Storage Operations

Put Operation Flow

Put Operation with Upsert

The put() method uses database upsert operations to either insert new entries or update existing ones atomically. This eliminates race conditions between check-and-insert operations.

Sources: src/DatabaseStore.php142-165

Add Operation (Conditional Insert)

Conditional Insert Operation

The add() method src/DatabaseStore.php170-185 implements conditional insertion with a two-step approach:

1. Check if key exists using get()
2. Attempt raw insert, catching duplicate key exceptions

This approach handles race conditions gracefully - if another process inserts the key between the check and insert, the database constraint will cause an exception that's caught and returns false.

Sources: src/DatabaseStore.php170-185

Atomic Numeric Operations

Increment/Decrement with Transactions

Transaction-Based Atomic Operations

The increment() and decrement() methods src/DatabaseStore.php190-311 use database transactions with lockForUpdate() to ensure atomicity:

1. Begin Transaction: Wrap entire operation in transaction
2. Lock Row: Use SELECT ... FOR UPDATE to obtain exclusive lock
3. Validate: Check if value exists and is numeric
4. Update: Compute new value and update in same transaction
5. Commit: Release lock and commit changes

This prevents lost updates when multiple processes attempt concurrent increments.

Sources: src/DatabaseStore.php281-311

---

Serialization

Serialization Methods

The DatabaseStore uses PHP's native serialization functions:

Serialization Layer

The serialize() method src/DatabaseStore.php324-327 converts PHP values to strings using serialize(), while unserialize() src/DatabaseStore.php332-335 reconstructs the original values. This supports storing complex data types including arrays, objects, and nested structures.

Sources: src/DatabaseStore.php324-335

---

Expiration Management

Expiration Strategy

Lazy Expiration on Reads

The DatabaseStore implements lazy expiration: expired entries are automatically removed during get() and many() operations src/DatabaseStore.php119-128 This ensures that expired data is never returned while distributing cleanup costs across normal operations.

Manual Pruning

The pruneExpired() method src/DatabaseStore.php263-268 provides manual cleanup of all expired entries:

This method is typically invoked by the cache:prune-db-expired console command for scheduled maintenance.

Sources: src/DatabaseStore.php263-268 src/DatabaseStore.php119-128

---

Distributed Locking Integration

Lock Creation

Lock Provider Implementation

The DatabaseStore implements the LockProvider interface by creating DatabaseLock instances:

• lock() src/DatabaseStore.php218-230 - Creates a new lock with specified timeout
• restoreLock() src/DatabaseStore.php235-238 - Restores an existing lock using owner identifier

Both methods pass through the store's configuration including:

• Connection resolver for database access
• Connection name for the lock table
• Lock table name
• Lock lottery odds for probabilistic cleanup
• Default lock timeout

Sources: src/DatabaseStore.php218-238

---

Deletion Operations

Single Key Deletion

The forget() method src/DatabaseStore.php243-248 removes a single cache entry:

This operation always returns true regardless of whether the key existed, maintaining consistency with the Store interface contract.

Bulk Deletion

The flush() method src/DatabaseStore.php253-258 removes all cache entries from the table:

This is a destructive operation that clears the entire cache table without conditions.

Sources: src/DatabaseStore.php243-258

---

Key Prefixing

Prefix Strategy

Key Namespacing

The prefix property src/DatabaseStore.php39 is prepended to all cache keys before storage. This allows multiple applications or environments to share the same cache table without key collisions. The prefix is configured via the cache configuration and typically includes the application name.

All operations that interact with keys automatically apply the prefix:

• get() / many() - Prefix applied in query src/DatabaseStore.php109-111
• put() / putMany() - Prefix applied to data src/DatabaseStore.php158
• add() - Prefix applied before insert src/DatabaseStore.php176
• forget() - Prefix applied in where clause src/DatabaseStore.php245
• lock() - Prefix applied to lock name src/DatabaseStore.php223

Sources: src/DatabaseStore.php39 src/DatabaseStore.php109-111 src/DatabaseStore.php158 src/DatabaseStore.php176 src/DatabaseStore.php245 src/DatabaseStore.php223

---

Performance Considerations

Query Optimization

The DatabaseStore includes several optimizations:

Optimization	Implementation	Benefit
Batch Retrieval	many() with whereIn()	Single query for multiple keys
Upsert	upsert() in putMany()	Atomic insert-or-update
Conditional Expiration	Only delete if expired entries exist	Reduces unnecessary DELETE queries
Transaction-based Atomics	lockForUpdate() in increment/decrement	Prevents lost updates

Index Requirements

For optimal performance, the cache table should have:

1. Primary Key or Unique Index on key column - Required for upsert operations
2. Index on expiration column - Optimizes pruning queries
3. Composite Index on (key, expiration) - Optional, for get operations with expiration checks

Connection Pooling

The DatabaseStore obtains fresh connections for each operation via connection() src/DatabaseStore.php80-83 which is essential in coroutine environments:

• Prevents Connection Sharing: Each coroutine gets its own connection
• Automatic Cleanup: Connections returned to pool after operation
• Transaction Safety: Ensures transaction boundaries are respected

Sources: src/DatabaseStore.php98-137 src/DatabaseStore.php150-165 src/DatabaseStore.php283-311 src/DatabaseStore.php80-83

---

Forever Storage

The forever() method src/DatabaseStore.php210-213 implements indefinite storage:

This sets an expiration of 315,360,000 seconds (approximately 10 years), effectively making the entry permanent until explicitly deleted. This avoids special-casing NULL expiration values throughout the codebase.

Sources: src/DatabaseStore.php210-213

---

Integration with Repository Layer

Repository Integration

The Repository layer see Repository Pattern wraps the DatabaseStore and adds:

• Event dispatching for observability
• Additional serialization layer (on top of DatabaseStore's serialization)
• Remember pattern with closure execution
• Type-safe getter methods

The DatabaseStore handles the low-level database operations, expiration logic, and atomic guarantees, while the Repository provides the high-level API.

Sources: src/DatabaseStore.php1-336