Data Access & CQRS

Melodic uses a CQRS (Command Query Responsibility Segregation) pattern for data access. Queries read data, Commands write data. Both are simple PHP classes that own their SQL and execute through DbContext. There is no mediator bus — services instantiate queries and commands directly.

Service → new Query/Command → execute(DbContext) → Result

DbContext

Melodic\Data\DbContext is a thin PDO wrapper that provides parameterized queries, model hydration, and transaction support. It implements DbContextInterface for easy mocking and DI binding.

Constructor

public function __construct(
    PDO|string $dsn,
    ?string $username = null,
    ?string $password = null,
    array $options = [],
)

Accepts either a PDO instance you have already configured, or a DSN connection string. When you pass a DSN string, DbContext creates the PDO connection for you and automatically enables exception error mode and FETCH_ASSOC as the default fetch mode.

// Pass an existing PDO instance
$pdo = new PDO('sqlite:database.db');
$context = new DbContext($pdo);

// Or pass a DSN string — PDO is created automatically
$context = new DbContext('mysql:host=localhost;dbname=myapp', 'root', 'secret');

Methods

Method	Returns	Purpose
query(string $class, string $sql, array $params = [])	array	Execute a SELECT and return an array of hydrated model objects
queryFirst(string $class, string $sql, array $params = [])	?object	Execute a SELECT and return the first result as a hydrated model, or null
command(string $sql, array $params = [])	int	Execute INSERT, UPDATE, or DELETE and return the affected row count
scalar(string $sql, array $params = [])	mixed	Execute a SELECT and return the first column of the first row
transaction(callable $callback)	mixed	Wrap a callback in beginTransaction/commit with auto-rollback on exception
lastInsertId()	int	Get the last auto-increment ID after an INSERT

Usage Examples

// Fetch all users as UserModel objects
$users = $context->query(UserModel::class, 'SELECT id, username, email FROM users');

// Fetch a single user by ID
$user = $context->queryFirst(
    UserModel::class,
    'SELECT id, username, email FROM users WHERE id = :id',
    ['id' => 42]
);

// Insert a new row
$affected = $context->command(
    'INSERT INTO users (username, email, created_at) VALUES (:username, :email, :createdAt)',
    ['username' => 'alice', 'email' => 'alice@example.com', 'createdAt' => date('Y-m-d H:i:s')]
);

// Get a scalar count
$count = $context->scalar('SELECT COUNT(*) FROM users');

// Get last inserted ID
$id = $context->lastInsertId();

Model Hydration

When you call query() or queryFirst(), DbContext creates model instances without calling the constructor and maps each column in the result set to a property of the same name on the target class.

• Uses ReflectionClass::newInstanceWithoutConstructor() to create the instance
• Maps column names to property names directly — the column name must match the property name
• Works with public, protected, and private properties via reflection
• Columns with no matching property are silently ignored

QueryInterface

Melodic\Data\QueryInterface defines the contract for read operations — SELECT queries that return data.

interface QueryInterface
{
    public function getSql(): string;

    public function execute(DbContextInterface $context): mixed;
}

• getSql() — returns the SQL statement (useful for logging and debugging)
• execute() — runs the query against a DbContext and returns the result
• The return type of execute() is mixed — your implementations can return a model, an array of models, a scalar value, or null

CommandInterface

Melodic\Data\CommandInterface defines the contract for write operations — INSERT, UPDATE, and DELETE statements.

interface CommandInterface
{
    public function getSql(): string;

    public function execute(DbContextInterface $context): int;
}

• getSql() — returns the SQL statement
• execute() — runs the command against a DbContext and returns the number of affected rows

Creating a Query

A query class takes its filtering parameters in the constructor, builds the SQL, and executes through the DbContext. Here are several common patterns.

Get All Records

<?php

declare(strict_types=1);

namespace App\Queries;

use App\Models\UserModel;
use Melodic\Data\DbContextInterface;
use Melodic\Data\QueryInterface;

class GetAllUsersQuery implements QueryInterface
{
    private readonly string $sql;

    public function __construct()
    {
        $this->sql = "SELECT id, username, email, created_at AS createdAt
                       FROM users ORDER BY id";
    }

    public function getSql(): string
    {
        return $this->sql;
    }

    /**
     * @return UserModel[]
     */
    public function execute(DbContextInterface $context): array
    {
        return $context->query(UserModel::class, $this->sql);
    }
}

Get by ID

<?php

declare(strict_types=1);

namespace App\Queries;

use App\Models\UserModel;
use Melodic\Data\DbContextInterface;
use Melodic\Data\QueryInterface;

class GetUserByIdQuery implements QueryInterface
{
    private readonly string $sql;

    public function __construct(
        private readonly int $id,
    ) {
        $this->sql = "SELECT id, username, email, created_at AS createdAt
                       FROM users WHERE id = :id";
    }

    public function getSql(): string
    {
        return $this->sql;
    }

    public function execute(DbContextInterface $context): ?UserModel
    {
        return $context->queryFirst(UserModel::class, $this->sql, ['id' => $this->id]);
    }
}

Get with Filters

<?php

declare(strict_types=1);

namespace App\Queries;

use App\Models\UserModel;
use Melodic\Data\DbContextInterface;
use Melodic\Data\QueryInterface;

class SearchUsersQuery implements QueryInterface
{
    private readonly string $sql;

    public function __construct(
        private readonly string $search,
        private readonly int $limit = 25,
        private readonly int $offset = 0,
    ) {
        $this->sql = "SELECT id, username, email, created_at AS createdAt
                       FROM users
                       WHERE username LIKE :search OR email LIKE :search
                       ORDER BY username
                       LIMIT :limit OFFSET :offset";
    }

    public function getSql(): string
    {
        return $this->sql;
    }

    /**
     * @return UserModel[]
     */
    public function execute(DbContextInterface $context): array
    {
        return $context->query(UserModel::class, $this->sql, [
            'search' => '%' . $this->search . '%',
            'limit'  => $this->limit,
            'offset' => $this->offset,
        ]);
    }
}

Scalar Query

<?php

declare(strict_types=1);

namespace App\Queries;

use Melodic\Data\DbContextInterface;
use Melodic\Data\QueryInterface;

class GetUserCountQuery implements QueryInterface
{
    private readonly string $sql;

    public function __construct()
    {
        $this->sql = "SELECT COUNT(*) FROM users";
    }

    public function getSql(): string
    {
        return $this->sql;
    }

    public function execute(DbContextInterface $context): int
    {
        return (int) $context->scalar($this->sql);
    }
}

Creating a Command

A command class takes the data it needs as constructor parameters, builds the SQL, and executes through the DbContext. Commands always return the number of affected rows.

Insert

<?php

declare(strict_types=1);

namespace App\Commands;

use Melodic\Data\CommandInterface;
use Melodic\Data\DbContextInterface;

class CreateUserCommand implements CommandInterface
{
    private readonly string $sql;

    public function __construct(
        private readonly string $username,
        private readonly string $email,
    ) {
        $this->sql = "INSERT INTO users (username, email, created_at)
                       VALUES (:username, :email, :createdAt)";
    }

    public function getSql(): string
    {
        return $this->sql;
    }

    public function execute(DbContextInterface $context): int
    {
        return $context->command($this->sql, [
            'username'  => $this->username,
            'email'     => $this->email,
            'createdAt' => date('Y-m-d H:i:s'),
        ]);
    }
}

Update

<?php

declare(strict_types=1);

namespace App\Commands;

use Melodic\Data\CommandInterface;
use Melodic\Data\DbContextInterface;

class UpdateUserEmailCommand implements CommandInterface
{
    private readonly string $sql;

    public function __construct(
        private readonly int $id,
        private readonly string $email,
    ) {
        $this->sql = "UPDATE users SET email = :email WHERE id = :id";
    }

    public function getSql(): string
    {
        return $this->sql;
    }

    public function execute(DbContextInterface $context): int
    {
        return $context->command($this->sql, [
            'id'    => $this->id,
            'email' => $this->email,
        ]);
    }
}

Delete

<?php

declare(strict_types=1);

namespace App\Commands;

use Melodic\Data\CommandInterface;
use Melodic\Data\DbContextInterface;

class DeleteUserCommand implements CommandInterface
{
    private readonly string $sql;

    public function __construct(
        private readonly int $id,
    ) {
        $this->sql = "DELETE FROM users WHERE id = :id";
    }

    public function getSql(): string
    {
        return $this->sql;
    }

    public function execute(DbContextInterface $context): int
    {
        return $context->command($this->sql, ['id' => $this->id]);
    }
}

Model

Melodic\Data\Model is a lightweight base class for your data transfer objects. It provides convenience methods for creating instances from arrays and converting them back.

<?php

declare(strict_types=1);

namespace App\Models;

use Melodic\Data\Model;

class UserModel extends Model
{
    public int $id;
    public string $username;
    public string $email;
    public string $createdAt;
}

Model implements JsonSerializable, so you can pass model instances directly to json_encode().

Methods

Method	Returns	Purpose
static fromArray(array $data)	static	Create an instance from an associative array — accepts both PascalCase (DB) and camelCase (frontend) keys
toArray()	array	Convert all initialized public properties to an associative array with camelCase keys
toPascalArray()	array	Convert all initialized public properties to an array with original PascalCase keys — booleans are converted to integers for PDO compatibility
toUpdateArray()	array	Like toPascalArray() but excludes null values — ideal for partial UPDATE statements where null means “not provided”
jsonSerialize()	mixed	Implements JsonSerializable — delegates to toArray()

// Create from an array (e.g., from request body)
$user = UserModel::fromArray([
    'id'        => 1,
    'username'  => 'alice',
    'email'     => 'alice@example.com',
    'createdAt' => '2025-01-01 00:00:00',
]);

// Convert to camelCase array (e.g., for JSON response)
$data = $user->toArray();
// ['id' => 1, 'username' => 'alice', 'email' => 'alice@example.com', 'createdAt' => '2025-01-01 00:00:00']

// Works directly with json_encode() via JsonSerializable
$json = json_encode($user);
// {"id":1,"username":"alice","email":"alice@example.com","createdAt":"2025-01-01 00:00:00"}

PascalCase Arrays for SQL Parameters

When building SQL parameter arrays for INSERT or UPDATE statements, use toPascalArray() and toUpdateArray(). These return the original PascalCase property names (matching your database column names) and convert booleans to integers for PDO compatibility.

// toPascalArray() — all initialized properties with PascalCase keys
$params = $user->toPascalArray();
// ['Id' => 1, 'Username' => 'alice', 'Email' => 'alice@example.com', 'CreatedAt' => '2025-01-01 00:00:00']

// toUpdateArray() — only non-null properties (for partial updates)
$partialUpdate = UserModel::fromArray(['email' => 'new@example.com']);
$params = $partialUpdate->toUpdateArray();
// ['Email' => 'new@example.com'] — null/uninitialized properties excluded

Transactions

Use transaction() to wrap multiple commands in a single atomic operation. If the callback throws an exception, the transaction is automatically rolled back. Otherwise, it is committed.

$result = $context->transaction(function (DbContextInterface $ctx) {
    // Create the user
    $ctx->command(
        'INSERT INTO users (username, email, created_at) VALUES (:username, :email, :createdAt)',
        ['username' => 'alice', 'email' => 'alice@example.com', 'createdAt' => date('Y-m-d H:i:s')]
    );

    $userId = $ctx->lastInsertId();

    // Assign the default role
    $ctx->command(
        'INSERT INTO user_roles (user_id, role_id) VALUES (:userId, :roleId)',
        ['userId' => $userId, 'roleId' => 1]
    );

    return $userId;
});
// $result is the user ID — both inserts succeeded or neither did

You can also use transactions within a service method by calling $this->context->transaction():

class UserService extends Service
{
    public function createWithRole(string $username, string $email, int $roleId): int
    {
        return $this->context->transaction(function (DbContextInterface $ctx) use ($username, $email, $roleId) {
            (new CreateUserCommand($username, $email))->execute($ctx);
            $userId = $ctx->lastInsertId();
            (new AssignRoleCommand($userId, $roleId))->execute($ctx);

            return $userId;
        });
    }
}

Registering DbContext

Register DbContextInterface as a singleton in the DI container so the same instance is shared across all services within a request.

$app->services(function ($container) {
    $container->singleton(DbContextInterface::class, function () {
        $pdo = new PDO('sqlite:database.db');
        return new DbContext($pdo);
    });
});

For a MySQL or PostgreSQL connection:

$container->singleton(DbContextInterface::class, function () use ($app) {
    return new DbContext(
        $app->config('database.dsn'),      // 'mysql:host=localhost;dbname=myapp'
        $app->config('database.username'),  // 'root'
        $app->config('database.password'),  // 'secret'
    );
});