How Ecotone Simplifies PHP Database Mapping, Queries, and Conversions

When handling data, we often need to map classes to database tables and vice versa, turning simple scalar types from the database 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 a DAO or repository pattern.

To stay fully focused on business concerns we try to minimise low‑level code and treat the whole application as a business‑oriented whole rather than a collection of layers.

PHP approach and business interface

Ecotone

's business interface aims to reduce low‑level and boilerplate code, allowing developers to concentrate on the important business logic. Ecotone also provides specialised business interfaces for database access. These interfaces eliminate the need for conversion logic, parameter binding and SQL execution, letting developers hide low‑level code behind an abstraction.

Modifying database data

To define methods that insert, update or delete records we can use the

Ecotone

DbalWrite

attribute.

<?php

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

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

In the example we created a PersonService interface that contains a register() method. The method inserts a new record into the persons table.

Using the DbalWrite attribute binds the SQL INSERT INTO persons VALUES (:name, :surname) to the register() method, informing Ecotone to run this SQL when the method is invoked.

The parameters name and surname are automatically bound to the :name and :surname placeholders.

Return number of modified records

When updating or deleting records we may need to know how many rows were affected. For that we can give the method an int return type.

<?php

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

As you can see we used DateTimeImmutable as a parameter. Ecotone will convert the date‑time to a string before executing the SQL.

Parameter conversion

Domain models usually use higher‑level classes instead of the scalar types understood by the database. In most cases we want the interface to work with business types rather than database types, which can be achieved with a conversion mechanism.

Built‑in class conversion

Ecotone

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

For example, the following class is automatically converted via its __toString() method:

<?php

final readonly class PersonId
{
    public function __construct(public string $id) {}

    public function __toString(): string
    {
        return $this->id;
    }
}

Now we can use it as part of an interface without worrying about conversion:

<?php

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

Both PersonId and DateTimeImmutable will be automatically converted. PersonId is converted to a string because it defines __toString().

Custom parameter conversion

We can write our own converters to customise how a given class is transformed. Suppose we have a DayOfWeek enum that we want to store as an integer in the database.

<?php

enum DayOfWeek: string
{
    case MONDAY = 'monday';
    case TUESDAY = 'tuesday';
    case WEDNESDAY = 'wednesday';
    case THURSDAY = 'thursday';
    case FRIDAY = 'friday';
    case SATURDAY = 'saturday';
    case SUNDAY = 'sunday';

    public function toNumber(): int
    {
        return match ($this) {
            self::MONDAY => 1,
            self::TUESDAY => 2,
            self::WEDNESDAY => 3,
            self::THURSDAY => 4,
            self::FRIDAY => 5,
            self::SATURDAY => 6,
            self::SUNDAY => 7,
        };
    }
}

We can create a DayOfWeekConverter class:

<?php

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

Converter

is a class registered in the dependency container. Ecotone will locate all converters marked with the attribute and call them when conversion is required. In our example the converter is invoked when a DayOfWeek needs to be stored as an integer.

After defining the converter we can use the DayOfWeek class in an interface and be sure it will be stored as an integer:

<?php

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

The converter is reused across all interfaces, so we only need to write it once.

Using expression language

The expression language can be used to customise parameters for a given scenario, allowing us to alter the behaviour of a specific operation.

Assume we have a PersonName class that we want to store in lower‑case before persisting.

<?php

final readonly class PersonName
{
    public function __construct(public string $name) {}

    public function toLowerCase(): string
    {
        return strtolower($this->name);
    }
}

We can call this method before saving by using the DbalParameter attribute with an expression:

<?php

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

The expression invokes toLowerCase() on the PersonName instance before it is stored in the database.

By providing a DbalParameter attribute we can define the expression that should be evaluated prior to persisting the given parameter. payload is a special variable that references the parameter inside the expression.

Non‑parameter methods

Sometimes we need a method that does not receive explicit parameters because the value can be evaluated dynamically. In that case we can use DbalParameter as a method‑level attribute.

<?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;
}

Here the now parameter is predefined by the attribute and its value is obtained from the clock service via the reference() function.

JSON‑based database parameters

Database columns may contain JSON rather than simple scalar values. In domain code JSON is usually represented by complex classes or arrays of objects, which therefore need conversion.

Suppose we want to store an array of person roles. In domain code the roles are represented by a PersonRole class:

<?php

final readonly class PersonRole
{
    public function __construct(public string $role) {}

    public function getRole(): string
    {
        return $this->role;
    }
}

At the interface level we declare the role array:

<?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;
}

The ConvertToMediaType definition on DbalParameter tells Ecotone to convert the parameter to the JSON media type before persisting.

We can implement a custom converter:

<?php

final class PersonRoleConverter
{
    #[Converter]
    public function from(PersonRole $personRole): string
    {
        return $personRole->getRole();
    }
}

This is enough to convert a collection of PersonRole objects directly into JSON.

Querying database data

Query multiple records

To retrieve many rows we can use the DbalQuery attribute, which specifies the SQL query that Ecotone will execute.

<?php

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

The method returns an array of arrays containing person_id and name columns.

We can also pass a Pagination object to make the signature clearer:

<?php

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

<?php

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

In the SQL we use the :(...) operator to access object properties.

Convert result set

When working with domain code we usually want to receive objects instead of the associative arrays returned by Dbal. Ecotone can convert the result according to the return type declared in the interface.

For a single row we can return a PersonDTO:

<?php

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

The fetchMode attribute tells Ecotone to fetch the first row only. The PersonDTOConverter then builds a PersonDTO from the row:

<?php

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

Return null

If no row is found we can use a union return type:

<?php

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

Ecotone

returns a PersonDTO when a record exists, otherwise it returns null.

Return single value

For aggregate functions such as COUNT() we can ask Ecotone to return the first column of the first row:

<?php

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

Convert multiple records

When fetching many rows we can indicate the desired element type via a docblock. Ecotone reads the docblock and converts each row to a PersonDTO:

<?php

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

Get large result set

Loading all rows into memory can cause out‑of‑memory errors for large result sets. Using the fetchMode: FetchMode::ITERATE mode loads and converts one row at a time.

<?php

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

Doctrine ORM support

If we use Ecotone together with Doctrine ORM we can define repository interfaces. The Repository annotation tells Ecotone that the interface is a repository and it will obtain the appropriate Doctrine entity manager.

<?php

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

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

Eloquent model support

Ecotone also works with Laravel's Eloquent models. A repository interface annotated with Repository will be bound to the corresponding Eloquent model.

<?php

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

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

Ecotone's database abstraction helps increase productivity and maintainability by allowing us to work with simple interfaces instead of raw SQL, keeping the focus on business logic.