Database

phpnomad/database provides concrete database implementations of the datastore pattern. It's designed to let you define table schemas, execute queries, and persist models in SQL databases while maintaining the storage-agnostic abstractions from phpnomad/datastore.

At its core:

By implementing the datastore interfaces with database-backed handlers, you get full CRUD operations, complex querying, caching, and event notifications—all while keeping your domain logic portable.

Key ideas at a glance

The database persistence lifecycle

When your application performs a data operation through a database-backed datastore, the request flows through these layers:

Application → Datastore → DatabaseHandler → QueryBuilder → Database → ModelAdapter → Model
                                    ↓                ↑
                              Cache Check      Cache Store
                                    ↓
                              Event Broadcast

Application layer

Your application calls methods on the Datastore interface:

$post = $postDatastore->find(123);
$posts = $postDatastore->where([
    ['column' => 'status', 'operator' => '=', 'value' => 'published']
]);

Datastore layer

The Datastore delegates to its database handler:

class PostDatastore implements PostDatastoreInterface
{
    use WithDatastorePrimaryKeyDecorator;

    protected Datastore $datastoreHandler;

    public function __construct(PostDatabaseDatastoreHandler $datastoreHandler)
    {
        $this->datastoreHandler = $datastoreHandler;
    }
}

Database handler layer

The Database Handler extends IdentifiableDatabaseDatastoreHandler and uses the WithDatastoreHandlerMethods trait to implement all standard operations:

class PostDatabaseDatastoreHandler extends IdentifiableDatabaseDatastoreHandler 
    implements PostDatastoreHandler
{
    use WithDatastoreHandlerMethods;

    public function __construct(
        DatabaseServiceProvider $serviceProvider,
        PostsTable $table,
        PostAdapter $adapter,
        TableSchemaService $tableSchemaService
    ) {
        $this->serviceProvider = $serviceProvider;
        $this->table = $table;
        $this->modelAdapter = $adapter;
        $this->tableSchemaService = $tableSchemaService;
        $this->model = Post::class;
    }
}

Cache check

Before querying the database, the handler checks the cache:

$cacheKey = ['identities' => ['id' => 123], 'type' => Post::class];
if ($cached = $this->serviceProvider->cacheableService->get($cacheKey)) {
    return $cached; // Cache hit, skip database
}

Query building

The handler uses QueryBuilder to construct SQL:

$query = $this->serviceProvider->queryBuilder
    ->select()
    ->from($this->table)
    ->where('id', '=', 123)
    ->build();

The QueryBuilder generates parameterized SQL with placeholders to prevent injection.

Database execution

The query executes against the database and returns raw rows:

$row = $this->serviceProvider->queryStrategy->execute($query);

Model conversion

The ModelAdapter converts the raw row to a model:

$post = $this->modelAdapter->toModel($row);

Cache storage

The model is stored in cache for future requests:

$this->serviceProvider->cacheableService->set($cacheKey, $post);

Event broadcasting

Events are broadcast after successful operations:

// After create
$this->serviceProvider->eventStrategy->dispatch(new RecordCreated($post));

// After update
$this->serviceProvider->eventStrategy->dispatch(new RecordUpdated($post));

// After delete
$this->serviceProvider->eventStrategy->dispatch(new RecordDeleted($post));

Why use the database package

Automatic caching

Every find operation checks cache first. Subsequent requests for the same record return instantly without database queries. Cache invalidates automatically on updates and deletes.

Event-driven architecture

Database operations broadcast events that other systems can listen to. Create audit logs, send notifications, update search indices, or trigger workflows—all decoupled from the handler.

Query abstraction

No raw SQL in your handlers. Build queries with arrays and let QueryBuilder handle SQL generation, parameterization, and escaping.

Schema versioning

Table definitions include version numbers. When schemas change, migrations can detect version differences and update tables accordingly.

Standardized patterns

All database handlers follow the same pattern: extend the base, inject dependencies, implement interfaces. This consistency makes codebases predictable and maintainable.

Core components

Database handlers

Handlers extend IdentifiableDatabaseDatastoreHandler and use WithDatastoreHandlerMethods to implement CRUD operations. They connect table schemas to datastore interfaces.

See Database Handlers for complete documentation.

Table schemas

Table classes extend Table and define columns, indices, and versioning. They specify how entities are stored in the database without writing DDL.

class PostsTable extends Table
{
    public function getUnprefixedName(): string
    {
        return 'posts';
    }

    public function getColumns(): array
    {
        return [
            (new PrimaryKeyFactory())->toColumn(),
            new Column('title', 'VARCHAR', [255], 'NOT NULL'),
            new Column('content', 'TEXT', null, 'NOT NULL'),
            (new DateCreatedFactory())->toColumn(),
        ];
    }

    public function getIndices(): array
    {
        return [
            new Index(['title'], 'idx_posts_title'),
        ];
    }
}

See Table Schema Definition and Tables for complete documentation.

Query building

The QueryBuilder converts condition arrays and parameters into SQL queries. Conditions use a structured format that supports AND/OR logic, operators, and nested groups.

$posts = $handler->where([
    [
        'type' => 'AND',
        'clauses' => [
            ['column' => 'status', 'operator' => '=', 'value' => 'published'],
            ['column' => 'views', 'operator' => '>', 'value' => 1000]
        ]
    ]
], limit: 10);

See Query Building for complete documentation.

Caching and events

The database package includes automatic caching and event broadcasting. Models are cached by identity and invalidated on mutations. Events broadcast after successful operations.

See Caching and Events for complete documentation.

Database service provider

The DatabaseServiceProvider is injected into handlers and provides access to:

See DatabaseServiceProvider for complete documentation.

Column and index factories

The database package provides factories for common column patterns:

public function getColumns(): array
{
    return [
        (new PrimaryKeyFactory())->toColumn(),
        new Column('authorId', 'BIGINT', null, 'NOT NULL'),
        (new ForeignKeyFactory('author', 'authors', 'id'))->toColumn(),
        (new DateCreatedFactory())->toColumn(),
        (new DateModifiedFactory())->toColumn(),
    ];
}

See Column and Index Factories for complete documentation.

Junction tables

Many-to-many relationships use junction tables. The JunctionTable class automatically creates compound primary keys, foreign keys, and standard indices from two related tables.

class PostsTagsTable extends JunctionTable
{
    public function __construct(
        // Base dependencies...
        PostsTable $leftTable,
        TagsTable $rightTable
    ) {
        parent::__construct(...func_get_args());
    }
}

See Junction Tables for complete documentation.

Supported databases

The database package works with:

The query builder generates standard SQL that should work across these systems. Platform-specific features (stored procedures, triggers, full-text search) are not abstracted.

When to use this package

Use phpnomad/database when:

If your data comes from REST APIs, GraphQL, or other non-database sources, you don't need this package. Use phpnomad/datastore and implement custom handlers.

Package components

Required reading

Deep dives

Reference

Relationship to other packages

Next steps