Using Ecotone for Database Mapping, Queries, and Parameter Conversion in PHP

When handling data we often need to map classes to database tables and vice‑versa, turning simple scalar types into higher‑level objects. Mapping, retrieving, and storing data are low‑level operations unrelated to business logic, so they are usually hidden behind interfaces such as DAO or repository patterns.

PHP Way with Business Interfaces

The Ecotone business interface aims to reduce low‑level and boilerplate code, allowing developers to focus on important business logic.

Ecotone also provides special database‑access interfaces that eliminate the need for conversion logic, parameter binding, and manual SQL execution.

Modifying Database Data

To define methods that insert, update, or delete records we can use the Ecotone DbalWrite attribute.

<code>&lt;?php

interface PersonService
{
    #[DbalWrite('INSERT INTO persons VALUES (:name, :surname)')]
    public function register(string $name, string $surname): void;
}
</code>

The attribute tells Ecotone to execute the given SQL when the method is called. The implementation is supplied by Ecotone and registered in the dependency container.

Parameters of the method are automatically bound to the corresponding SQL placeholders ( :name , :surname ).

Returning the Number of Affected Rows

When updating or deleting records we can return an int to indicate how many rows were modified.

<code>&lt;?php

interface PersonService
{
    #[DbalWrite('UPDATE activities SET active = false WHERE last_activity < :activityOlderThan')]
    public function markAsInactive(\DateTimeImmutable $activityOlderThan): int;
}
</code>

Ecotone will convert the DateTimeImmutable argument to a string before executing the query.

Parameter Conversion

Domain models often use richer types than the scalar types understood by the database. Ecotone allows conversion mechanisms to keep interfaces business‑oriented.

Built‑in Class Conversion

Ecotone provides default conversion for date‑time objects and any class that implements __toString() .

<code>&lt;?php

final readonly class PersonId
{
    public function __construct(public string $id) {}
    public function __toString(): string
    {
        return $this->id;
    }
}
</code>

This class can be used directly in an interface method without manual conversion.

<code>&lt;?php

interface PersonService
{
    #[DbalWrite('INSERT INTO activities VALUES (:personId, :activity, :time)')]
    public function store(PersonId $personId, string $activity, \DateTimeImmutable $time): void;
}
</code>

Custom Parameter Conversion

We can write our own converters, for example to store an enum DayOfWeek as an integer.

<code>&lt;?php

enum DayOfWeek: string
{
    case MONDAY = 'monday';
    case TUESDAY = 'tuesday';
    // ... other days ...
    public function toNumber(): int
    {
        return match ($this) {
            self::MONDAY => 1,
            self::TUESDAY => 2,
            // ...
        };
    }
}
</code>

<code>&lt;?php

final readonly class DayOfWeekConverter
{
    #[Converter]
    public function dayToNumber(DayOfWeek $day): int
    {
        return $day->toNumber();
    }
}
</code>

Using the converter, a method can accept DayOfWeek and have it stored as an integer.

<code>&lt;?php

interface Scheduler
{
    #[DbalWrite('INSERT INTO schedule(day, task) VALUES (:day, :task)')]
    public function scheduleForDayOfWeek(DayOfWeek $day, string $task): void;
}
</code>

Expression Language

Expressions can be used to transform parameters before persistence. For instance, converting a PersonName object to lower case.

<code>&lt;?php

final readonly class PersonName
{
    public function __construct(public string $name) {}
    public function toLowerCase(): string
    {
        return strtolower($this->name);
    }
}
</code>

<code>&lt;?php

interface PersonService
{
    #[DbalWrite('INSERT INTO persons VALUES (:personId, :name)')]
    public function register(int $personId, #[DbalParameter(expression: 'payload.toLowerCase()')] PersonName $name): void;
}
</code>

Non‑Parameter Methods

Attributes can also define parameters that are not passed by the caller, such as a timestamp generated via an expression.

<code>&lt;?php

interface PersonService
{
    #[DbalWrite('INSERT INTO persons VALUES (:personId, :name, :now)')]
    #[DbalParameter(name: 'now', expression: "reference('clock').now()")]
    public function register(int $personId, PersonName $name): void;
}
</code>

JSON‑Based Database Parameters

When a column stores JSON, we can convert complex objects to JSON before persisting.

<code>&lt;?php

final readonly class PersonRole
{
    public function __construct(public string $role) {}
    public function getRole(): string
    {
        return $this->role;
    }
}
</code>

<code>&lt;?php

interface PersonService
{
    /**
     * @param PersonRole[] $roles
     */
    #[DbalWrite('INSERT INTO persons VALUES (:personId, :roles)')]
    public function addRoles(int $personId, #[DbalParameter(convertToMediaType: MediaType::APPLICATION_JSON)] array $roles): void;
}
</code>

Querying Database Data

Fetching Multiple Records

Use the DbalQuery attribute to define a SELECT statement. Parameters can be simple scalars or a Pagination value object.

<code>&lt;?php

interface PersonService
{
    #[DbalQuery('SELECT person_id, name FROM persons LIMIT :limit OFFSET :offset')]
    public function getPersons(int $limit, int $offset): array;
}
</code>

<code>&lt;?php

final readonly class Pagination
{
    public function __construct(public int $limit, public int $offset) {}
}
</code>

<code>&lt;?php

interface PersonService
{
    #[DbalQuery('SELECT person_id, name FROM persons LIMIT :(pagination.limit) OFFSET :(pagination.offset)')]
    public function getNameListWithIgnoredParameters(Pagination $pagination): array;
}
</code>

Converting Result Sets

Ecotone can map rows to DTOs using converters defined in the interface return type.

<code>&lt;?php

interface PersonService
{
    #[DbalQuery('SELECT person_id, name FROM persons WHERE person_id = :personId', fetchMode: FetchMode::FIRST_ROW)]
    public function get(int $personId): PersonDTO;
}
</code>

<code>&lt;?php

class PersonDTOConverter
{
    #[Converter]
    public function to(array $personDTO): PersonDTO
    {
        return new PersonDTO($personDTO['person_id'], $personDTO['name']);
    }
}
</code>

Returning Null or Single Values

Methods can return PersonDTO|null when a row may be missing, or use FetchMode::FIRST_COLUMN_OF_FIRST_ROW to obtain aggregate values such as COUNT(*) .

<code>&lt;?php

interface PersonService
{
    #[DbalQuery('SELECT COUNT(*) FROM persons', fetchMode: FetchMode::FIRST_COLUMN_OF_FIRST_ROW)]
    public function countPersons(): int;
}
</code>

Converting Multiple Records

Docblock annotations can specify the desired return type (e.g., @return PersonDTO[] ) so Ecotone knows how to convert each row.

<code>&lt;?php

interface PersonService
{
    /**
     * @return PersonDTO[]
     */
    #[DbalQuery('SELECT person_id, name FROM persons LIMIT :limit OFFSET :offset')]
    public function get(int $limit, int $offset): array;
}
</code>

Fetching Large Result Sets

Use FetchMode::ITERATE to stream rows one by one, keeping memory usage low.

<code>&lt;?php

interface PersonService
{
    /**
     * @return iterable<PersonDTO>
     */
    #[DbalQuery('SELECT person_id, name FROM persons', fetchMode: FetchMode::ITERATE)]
    public function getAll(): iterable;
}
</code>

Doctrine ORM Support

By annotating an interface with #[Repository] , Ecotone treats it as a Doctrine repository, allowing get and save operations on entities.

<code>&lt;?php

interface PersonRepository
{
    #[Repository]
    public function get(int $personId): ?Person;

    #[Repository]
    public function save(Person $person): void;
}
</code>

Eloquent Model Support

The same #[Repository] annotation works with Laravel's Eloquent models.

<code>&lt;?php

interface PersonRepository
{
    #[Repository]
    public function get(int $personId): ?Person;

    #[Repository]
    public function save(Person $person): void;
}
</code>

Ecotone’s database abstraction lets developers concentrate on business logic while handling low‑level persistence details automatically.

For more details, refer to the Ecotone documentation: https://docs.ecotone.tech/