phpnomad/db

phpnomad/db implements the datastore pattern for SQL databases. It gives you table schemas, query building, automatic caching, and event broadcasting on top of the storage-agnostic abstractions in phpnomad/datastore, so your handlers stay free of raw SQL and your domain logic stays portable.

You get full CRUD operations, condition-array querying, cache lookups on reads, cache invalidation on writes, and RecordCreated, RecordUpdated, and RecordDeleted events dispatched from every mutation. The package powers the data layer in Siren and has been in production for years.

Installation

composer require phpnomad/db

This package is the abstraction layer. To actually execute queries you also need a concrete integration like phpnomad/mysql-db-integration, which provides the QueryStrategy and table-management strategies that phpnomad/db delegates to.

Quick Start

Define a table schema by extending Table. Columns and indices come from value objects and factories, and a table version string lets migrations detect schema changes.

<?phpusePHPNomad\Database\Abstracts\Table;
usePHPNomad\Database\Factories\Column;
usePHPNomad\Database\Factories\Columns\DateCreatedFactory;
usePHPNomad\Database\Factories\Columns\DateModifiedFactory;
usePHPNomad\Database\Factories\Columns\PrimaryKeyFactory;
usePHPNomad\Database\Factories\Index;
class PostsTable extends Table
{
publicfunctiongetUnprefixedName(): string
{
return'posts';
}
publicfunctiongetSingularUnprefixedName(): string
{
return'post';
}
publicfunctiongetAlias(): string
{
return'p';
}
publicfunctiongetTableVersion(): string
{
return'1';
}
publicfunctiongetColumns(): array
{
return [
(newPrimaryKeyFactory())->toColumn(),
newColumn('title', 'VARCHAR', [255], 'NOT NULL'),
newColumn('content', 'TEXT', null, 'NOT NULL'),
newColumn('status', 'VARCHAR', [20], "NOT NULL DEFAULT 'draft'"),
(newDateCreatedFactory())->toColumn(),
(newDateModifiedFactory())->toColumn(),
];
}
publicfunctiongetIndices(): array
{
return [
newIndex(['status'], 'idx_posts_status'),
];
}
}

Create a handler by extending IdentifiableDatabaseDatastoreHandler and pulling in WithDatastoreHandlerMethods. The base class implements find, findMultiple, update, and delete against the id column, and the trait supplies the rest of the CRUD surface along with cache and event wiring.

<?phpusePHPNomad\Database\Abstracts\IdentifiableDatabaseDatastoreHandler;
usePHPNomad\Database\Providers\DatabaseServiceProvider;
usePHPNomad\Database\Services\TableSchemaService;
usePHPNomad\Database\Traits\WithDatastoreHandlerMethods;
class PostDatabaseDatastoreHandler extends IdentifiableDatabaseDatastoreHandler
{
use WithDatastoreHandlerMethods;
publicfunction__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;
}
}

Once the handler is wired into your container, reads hit the cache first and writes invalidate it automatically. Complex reads use condition arrays instead of raw SQL.

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

The QueryBuilder turns that array into parameterized SQL and runs it through whichever QueryStrategy your integration package provides.

Key Concepts

• Extend Table to define a schema with columns, indices, and a version string for migrations
• Extend IdentifiableDatabaseDatastoreHandler as the base for any handler keyed by a single id column
• Use the WithDatastoreHandlerMethods trait for CRUD, cache reads, cache invalidation, and event dispatch
• Build reads and writes through QueryBuilder and ClauseBuilder with condition arrays instead of raw SQL
• Inject DatabaseServiceProvider into every handler to access QueryBuilder, QueryStrategy, ClauseBuilder, CacheableService, EventStrategy, and LoggerStrategy
• Use column factories like PrimaryKeyFactory, DateCreatedFactory, DateModifiedFactory, and ForeignKeyFactory for common column patterns
• Model many-to-many relationships with JunctionTable, which handles compound primary keys and foreign key constraints

Documentation

Full documentation lives at phpnomad.com, including detailed guides on table schema definition, database handlers, query building, caching and event broadcasting, junction tables, and the column and index factories.

License

MIT License. See LICENSE.txt.