Database

Amarant framework features a hybrid active record object relational mapper (ORM). Data is mapped using data models. Each data model is bound to a single database table.

The supported database type is PostgreSQL. See system requirements.

Database migrations are written using pure SQL for maximum flexibility.

Note

When referring to field or column, we always mean a colum in a database table.

Connections

---

Connections are abstractions that enable access to the database. Internally, each connection uses an adapter to communicate with the database. An adapter then connects to the database.

By default, the application has one default connection configured, using the configuration mentioned here.

The default connection is bound using binding identifier db.connection.default.

Creating a connection

---

If needed, you can create additional connections using dependency injection configurator.
Check the annotations to know more.

<?php

declare(strict_types=1);

namespace Vendor\ModuleName\Configuration;

use Amarant\Framework\Contract\Di\BindInterface;
use Amarant\Framework\Contract\Di\ConfigurableContainerInterface;
use Amarant\Framework\Contract\Di\DiConfiguratorInterface;
use Amarant\Framework\Data\Set;
use Amarant\Framework\Database\Adapter\PostgresqlAdapter;
use Amarant\Framework\Database\Connection\ConnectionPool;
use Amarant\Framework\Database\Connection\DefaultConnection;
use Amarant\Framework\Enum\DiEnum;
use Override;

class Di implements DiConfiguratorInterface
{
    #[Override] public function configure(ConfigurableContainerInterface $container, Set $configurators): void
    {
        $container
            ->bind(
                // (1)
                'db.adapter.custom',
                fn (ConfigurableContainerInterface $container) => $container
                    ->getBindBuilder()
                    // (2)
                    ->type(PostgresqlAdapter::class)
                    // (3)
                    ->argument('database', DiEnum::getEnvInjection('CUSTOM_CONNECTION_DATABASE_NAME'))
                    ->argument('dsn', DiEnum::getEnvInjection('CUSTOM_CONNECTION_DSN'))
                    // (4)
                    ->argument('options', [])
                    ->build()
            )
            ->bind(
                // (5)
                'db.connection.custom',
                fn (ConfigurableContainerInterface $container) => $container
                    ->getBindBuilder()
                    // (6)
                    ->type(DefaultConnection::class)
                    // (7)
                    ->argument('adapter', DiEnum::getIdInjection('db.adapter.custom'))
                    ->build()
            )
            ->extend(
                ConnectionPool::class,
                function (BindInterface $bind, ConfigurableContainerInterface $container) {
                    $bind->addValueToArgument(
                        // (8)
                        'connections',
                        DiEnum::getIdInjection('db.connection.custom'),
                        // (9)
                        'custom'
                    );

                    return $bind;
                }
            );
    }
}

1. Create an adapter.
2. The adapter implementation to use.
3. Inject parameters from the environment.
4. Optionally, add PDO options.
5. Create a connection.
6. The connection implementation to use.
7. Inject the newly created adapter.
8. Add connection to connection pool constructor parameter.
9. The name of the new connection in the pool.

The connection can now be fetched from the pool like this:

$customConnection = $connectionPool->getConnection('custom');

Note

Connections will not establish a connection to the database in advance, but only when the connection is used to perform some query.

Data models

---

When data is loaded from the database, it's hydrated to a data model. Also, the data from a data model can be persisted to the database. Multiple hydrators and persisters can be used in this process.

When data models are loaded, saved or deleted, multiple events are fired, allowing event subscribers to access the data model, check what fields have changed, etc.

A data model can store data which is not a part of its table schema. By default, and when using only the default persister, only the properties that exist in the table schema will be persisted.

The reason for this is to allow multiple hydrators to enrich the data model with additional data. Conversely, to allow multiple persisters to save additional data, for example relational data to other tables.

Create a data model

---

Check the annotations to know more.

<?php

declare(strict_types=1);

namespace Vendor\ModuleName\DataModel;

use Amarant\Framework\Data\Database\DataModel;
use Override;

// (1)
/**
 * @method int|string|null getFieldA()
 * @method $this setFieldA(int|string|null $value)
 * @method int|string|null getFieldB()
 * @method $this setFieldB(int|string|null $value)
 */
// (2)
class Example extends DataModel
{
    // (3)
    public const FIELD_FIELD_A = 'field_a';
    public const FIELD_FIELD_B = 'field_b';

    // (4)
    #[Override] public static function getTable(): string
    {
        return 'example';
    }
}

1. Add method annotations for the IDE.
2. Extend the base data model Amarant\Framework\Data\Database\DataModel.
3. It's recommended that you define table fields as public constants or an enum to be able to use them when building queries.
4. This is the only method that must be implemented when extending the base data model. It should correspond to a table name in the database that this data model will be bound to.

To additionally customize the data model, override methods from the base model.

Note

When getting or setting data on a data model, always use get or set and the name of a field in PascalCase as the method name.

For example, a table field named some_field, its getter method is getSomeField, while the setter is setSomeField.

You can also use the is or has methods. The isSomeField would return the value of the field as boolean, while hasSomeField would return if the data model has a value present for the field, as a boolean.

Primary key

---

To set the primary key field name, override the method getIdField. By default, the name is id. To fetch the value of the primary key, use getIdValue method.

Discriminator

---

Defines a discriminator column (table field) name and a discriminator map which determine the data model class that the data will be hydrated into.

In the following example, when value of table field some_discriminator_field is value a, the table row will be hydrated to SomeDataModelClass::class.

Otherwise, when the value is value b, the table row will be hydrated using SomeOtherDataModelClass::class class.

<?php

/**
 * @inheritDoc
 */
#[Override] public static function getDiscriminatorMap(): array
{
    return [
        'value a' => SomeDataModelClass::class,
        'value b' => SomeOtherDataModelClass::class,
    ];
}

#[Override] public static function getDiscriminatorColumn(): ?string
{
    return 'some_discriminator_field';
}

Default values

To set default data, override the method getDefaultData and return an array of field names mapped to a value. This data will be set on the data model upon instantiation.

<?php

#[Override] protected function getDefaultData(): array
{
    return [
        'field_a' => 'a',
        'field_b' => 'b',
    ];
}

Tracking changes

---

A data model stores a copy of the data which can be used to check which values have changed.

Methods hasOriginalData, getOriginalData can be used to fetch the original data of the model.

Additionally, methods isModified and isDeleted can be used to check if the model was modified or deleted. These flags can be reset using clearModified or changed using setDeleted.

Duplication

---

To make a copy of a data model, use the copy method. This creates a new instance using the same data, but without the primary key field.

Important

This doesn't do a deep copy of any nested data models which might have been added to the model.

Events

---

All the following events are dispatched using
Amarant\Framework\Database\Event\DataModelEvent object
from Amarant\Framework\Database\Repository\AbstractRepository.

Important

When referring to these events, always use Amarant\Framework\Enum\EventEnum. Do not hardcode event names.

EventEnum::DATA_MODEL_EVENT_NAME_HYDRATE_AFTER — dispatched after the data was fetched from the database and all hydrators were executed.

EventEnum::DATA_MODEL_EVENT_NAME_SAVE_BEFORE , EventEnum::DATA_MODEL_EVENT_NAME_SAVE_AFTER — dispatched before and after all persisters are executed.

EventEnum::DATA_MODEL_EVENT_NAME_COMMIT_AFTER — dispatched after the database transaction commits following a save. Use this instead of SAVE_AFTER when your subscriber must not run if the transaction is rolled back.

EventEnum::DATA_MODEL_EVENT_NAME_DELETE_BEFORE , EventEnum::DATA_MODEL_EVENT_NAME_DELETE_AFTER — dispatched before and after the model was deleted.

EventEnum::DATA_MODEL_EVENT_NAME_DELETE_COMMIT_AFTER — dispatched after the transaction commits following a delete.

EventEnum::DATA_MODEL_EVENT_NAME_DELETE_BY_IDS_BEFORE , EventEnum::DATA_MODEL_EVENT_NAME_DELETE_BY_IDS_AFTER — dispatched before and after deleting rows using primary keys.

EventEnum::DATA_MODEL_EVENT_NAME_QUERY_BUILDER_BUILD_BEFORE — dispatched before every query is built. The QueryBuilder for the current query is available under the query_builder key in the event data bag. Subscribers can add filters, joins, or sort conditions to every query issued for a particular data model. This is the mechanism used by data scope limiting.

Note

If a transaction is retried, for example in case of a deadlock, each event won't be fired more than once.

Table-scoped event variants

Every event above is also dispatched a second time with the table name appended as a suffix. Use EventEnum::getSuffixedEventName() to subscribe only to events for a specific data model.

use Amarant\Framework\Enum\EventEnum;
use Vendor\ModuleName\DataModel\Example;

// fires for every data model
$generalEvent = EventEnum::DATA_MODEL_EVENT_NAME_SAVE_AFTER->value;

// fires only when Example is saved
$tableEvent = EventEnum::getSuffixedEventName(
    EventEnum::DATA_MODEL_EVENT_NAME_SAVE_AFTER,
    Example::getTable()
);

Prefer the table-scoped variant whenever your subscriber is only relevant for one data model. The general event is for cross-cutting concerns.

Event object

All data model events deliver a Amarant\Framework\Database\Event\DataModelEvent instance. Key methods:

Method	Available in	Description
getModel(): ?DataModelInterface	All events	The data model instance.
getModelClass(): string	All events	The data model class name.
getConnection(): ConnectionInterface	All events	The database connection used for this operation. Useful for performing related queries or inserts within the same transaction.
isNew(): bool	Save events	true for an INSERT, false for an UPDATE.
wasModified(): bool	Save events	true if any field was changed before save.
getOldData(): array	Save / delete events	Snapshot of the model's data before the operation.
getIds(): ?array	Delete-by-ids events	The array of primary key values being deleted.
getPreviousEvent(): ?DataModelEventInterface	Commit events	Points to the corresponding SAVE_AFTER or DELETE_AFTER event.
areKeysModified(array $keys, array $keyTypes, ?string $path): bool	Save events	Returns true if any of the given field keys differ between old and current data.
getKeysDiff(array $keys, bool $stopOnFirstMatch, array $keyTypes, ?string $path): array	Save events	Returns an associative diff of changed keys: ['key' => ['old' => …, 'new' => …]].
areKeysChangedFromTo(array $values, array $keyTypes, ?string $path): bool	Save events	Returns true if fields changed from specific old values to specific new values.

Pausing events

By implementing Amarant\Framework\Contract\Database\DataModel\EventAwareDataModelInterface
on a data model, events can be paused.

If the method isPaused returns true, no events will be fired.

Repositories

---

The next thing we should do after creating a new data model is to create a repository for it.

Repositories expose a public interface the rest of the application can use to fetch, save and delete data for a particular data model.

Always use a default repository class, configured for a particular data model, and inject it into your repository, unless you have a good reason not to. The default repository takes care of events, hydration, persistence and more.

Creating a repository

---

First, create an interface for the repository. This will ease up testing, as you can swap to a different implementation. Also, you can make the implementation final.

<?php

declare(strict_types=1);

namespace Vendor\ModuleName\Contract\Database\Repository;

use Vendor\ModuleName\DataModel\Example;

interface ExampleRepositoryInterface
{
    public function get(int|string $id): ?Example;

    public function save(Example $example): void;

    public function delete(Example $example): void;
}

Next, create the implementation.
Check the annotations to know more.

<?php

declare(strict_types=1);

namespace Vendor\ModuleName\DataModel\Repository;

use Amarant\Framework\Contract\Database\Repository\RepositoryInterface;
use Override;
use Vendor\ModuleName\Contract\Database\Repository\ExampleRepositoryInterface;
use Vendor\ModuleName\DataModel\Example;

final class ExampleRepository implements ExampleRepositoryInterface
{
    public function __construct(
        // (1)
        private readonly RepositoryInterface $repository
    ) {
    }

    #[Override] public function get(int|string $id): ?Example
    {
        /**
         * @var Example|null $result
         */
        $result = $this->repository->get($id);
        return $result;
    }

    #[Override] public function save(Example $example): void
    {
        $this->repository->save($example);
    }

    #[Override] public function delete(Example $example): void
    {
        $this->repository->delete($example);
    }
}

1. We'll inject this default repository in the next step.

Configure the repository and inject the default repository.

<?php

declare(strict_types=1);

namespace Vendor\ModuleName\Configuration;

use Amarant\Framework\Contract\Database\Repository\RepositoryPoolInterface;
use Amarant\Framework\Contract\Di\ConfigurableContainerInterface;
use Amarant\Framework\Contract\Di\DiConfiguratorInterface;
use Amarant\Framework\Data\Set;
use Amarant\Framework\Enum\DiEnum;
use Override;
use Vendor\ModuleName\Contract\Database\Repository\ExampleRepositoryInterface;
use Vendor\ModuleName\DataModel\Example;
use Vendor\ModuleName\DataModel\Repository\ExampleRepository;

class Di implements DiConfiguratorInterface
{
    #[Override] public function configure(ConfigurableContainerInterface $container, Set $configurators): void
    {
        $container
            ->bind(
                ExampleRepositoryInterface::class,
                fn (ConfigurableContainerInterface $container) => $container
                    ->getBindBuilder()
                    ->type(ExampleRepositoryInterface::class)
                    ->concrete(ExampleRepository::class)
                    ->argument(
                        'repository',
                        // (1)
                        DiEnum::getCallInjection(
                            RepositoryPoolInterface::class,
                            'getRepository',
                            [Example::class]
                        )
                    )
                    ->build()
            );
    }
}

1. Inject a default repository for the Example data model, using call injection on the repository pool.

You can now inject and use ExampleRepositoryInterface.

Query caching

---

Repositories support an optional Redis-backed query result cache. Each query is keyed by a SHA-256 hash of its SQL and bound parameters. Cache entries are invalidated automatically by the repository whenever a model is saved or deleted — there is no manual invalidation needed in normal use.

Opting in

By default the cache is disabled for every data model. To enable it, override isCacheable to return true.

<?php

use Amarant\Framework\Data\Database\DataModel;
use Override;

class Example extends DataModel
{
    #[Override] public static function isCacheable(): bool
    {
        return true;
    }
}

Cache invalidation

The repository clears cached results automatically:

• On save($model) — clears all cached queries that included the saved model's row, identified by primary key.
• On delete($model) — clears all cached queries that included the deleted model's row.
• On deleteByIds($ids), deleteByQuery($qb), and truncate() — clears all cached queries for the data model, because the exact set of affected rows is unknown.

Limitation: joined queries are not cached

Queries that join other tables are never stored in the cache. The cache cannot determine which joined rows affect a cached entry, so it conservatively skips caching whenever joins are present.

If a data model is marked isCacheable and the query has joins, the query runs against the database as normal — there is no error, just no caching.

Instance caching (experimental)

isInstanceCacheable() is an alternative mode that caches fully hydrated data model instances rather than raw result sets. The data model must be serializable. It is currently slower than standard query caching and is not recommended for general use.

Migrations

---

Migrations are used to update the database schema.

They can be executed and reverted. Migrations are executed for each application module, in the order they were added in, and each module is migrated in the order of the module dependency chain.

In the case of revert, same logic applies but in reverse order.

Create a migration

---

Use the Amarant CLI to create a migration. You must specify the module name.

php bin/ama db:migrations:create Vendor_ModuleName --name=example_init

Note

The name parameter is optional. It's used to add a suffix to the migration file name.

We now have a new migration file created.

Creating code/Vendor/ModuleName/Migrations/1729707441_20241023181721_example_init.php

Let's create a table in the up method and drop it in the down method.

<?php

// phpcs:ignorefile
/** Amarant Migration 1729707441_20241023181721_example_init */

use Amarant\Framework\Contract\Database\Connection\ConnectionInterface;
use Amarant\Framework\Contract\Database\Schema\MigrationInterface;

return new class () implements MigrationInterface {
    public function up(ConnectionInterface $connection): void
    {
        $sql = <<<SQL
        CREATE TABLE example (
            id BIGINT NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
            field_a VARCHAR(255) NOT NULL,
            field_b VARCHAR(255) NULL
        );

        CREATE INDEX example_field_a_idx ON example USING btree (field_a);
SQL;
        $connection->execute($sql);
    }

    public function down(ConnectionInterface $connection): void
    {
        $connection->execute('DROP TABLE IF EXISTS example;');
    }
};

Execute and revert migrations

---

To execute pending migrations, use:

php bin/ama db:migrations:migrate -vvv

To revert migrations, use:

php bin/ama db:migrations:rollback --until=1729707441_20241023181721_example_init -vvv

This is a partial revert. It will skip reverting all other migrations after this particular one. To revert all migrations, remove the until parameter.

Note

You can add the --dry-run parameter to skip database queries, allowing you to see which migrations would be executed or reverted and in which order.

Warning

Doing a full revert, without the until and dry-run parameters effectively removes all defined tables from the database and truncates the migrations table.

Hydrators

---

Hydrators are used to normalize data loaded from the database and populate a data model with it. The default hydrator populates only the fields that are defined in a table a data model is bound to.

Multiple hydrators can be used to add or change the data model's data, for example to load some other data and enrich the model with it.

Create a hydrator

---

Check the annotations to know more.

<?php

declare(strict_types=1);

namespace Vendor\ModuleName\DataModel\Hydrator;

use Amarant\Framework\Contract\Database\Connection\ConnectionInterface;
use Amarant\Framework\Contract\Database\DataModel\DataModelInterface;
use Amarant\Framework\Contract\Database\HydratorInterface;
use Amarant\Framework\Data\Database\DataConverter;
use Amarant\Framework\Database\Registry;
use InvalidArgumentException;
use Override;
use Vendor\ModuleName\DataModel\Example;

final class ExampleHydrator implements HydratorInterface
{
    // (1)
    #[Override] public function supports(string|DataModelInterface $className, mixed $source): bool
    {
        if ($className instanceof DataModelInterface) {
            $className = \get_class($className);
        }

        return $className === Example::class && \is_array($source);
    }

    #[Override] public function hydrate(
        DataModelInterface $dataModel,
        ConnectionInterface $connection,
        Registry $registry,
        mixed $source
    ): void {
        // (2)
        if (($source[Example::FIELD_FIELD_A] ?? null) === 'some value') {
            $dataModel->setSomeOtherData('some other value');
        }

        // (3)
        if ($dataModel->getFieldA() === 'some value') {
            $dataModel->setSomeOtherData('some other value');
        }

        // (4)
        $dataModel->clearModified();
    }
}

1. Return true if this hydrator supports the data model and the source data. Returning false will result in hydrate not being called on this class.
2. Use the value from source if the tag priority of this hydrator is before the default hydrator.
3. Use the value from the data model if the tag priority of this hydrator is after the default hydrator.
4. Always clear the modified flag on the data model, because every change on the model results in the modified flag being set to true.

Tag the hydrator using dependency injection configurator.

<?php

declare(strict_types=1);

namespace Vendor\ModuleName\Configuration;

use Amarant\Framework\Contract\Di\ConfigurableContainerInterface;
use Amarant\Framework\Contract\Di\DiConfiguratorInterface;
use Amarant\Framework\Data\Set;
use Amarant\Framework\Enum\DiEnum;
use Override;
use Vendor\ModuleName\DataModel\Hydrator\ExampleHydrator;

class Di implements DiConfiguratorInterface
{
    #[Override] public function configure(ConfigurableContainerInterface $container, Set $configurators): void
    {
        $container
            ->tag(
                [
                    // (1)
                    450 => ExampleHydrator::class,
                ],
                [DiEnum::DB_HYDRATOR]
            );
    }
}

1. Use priority lower than 500 to run this hydrator after the default one or use priority greater than 500 to run before it.

Persisters

---

Persisters are used to save data to the database, converting the values to database suitable format, depending on field types, in the process.

The default persister saves only the fields that are defined in a table a data model is bound to.

As with hydrators, multiple persisters can be used, most commonly to save data to related tables.

Create a persister

---

Check the annotations to know more.

<?php

declare(strict_types=1);

namespace Vendor\ModuleName\DataModel\Persister;

use Amarant\Framework\Contract\Database\Connection\ConnectionInterface;
use Amarant\Framework\Contract\Database\DataModel\DataModelInterface;
use Amarant\Framework\Contract\Database\PersisterInterface;
use Override;
use Vendor\ModuleName\DataModel\Example;

final class ExamplePersister implements PersisterInterface
{
    #[Override] public function supports(DataModelInterface $dataModel): bool
    {
        // (1)
        return $dataModel instanceof Example;
    }

    #[Override] public function persist(
        ConnectionInterface $connection,
        DataModelInterface $dataModel
    ): void {
        // (2)
    }
}

1. Return true if this persister supports the data model. Returning false will result in persist not being called on this class.
2. Persisters are executed within a database transaction. Primary key value is already present on the data model if this persister is executed after the default one. It can be used to save data to some other, related tables using the connection or a repository that can be injected in the constructor.

Tag the persister using dependency injection configurator.

<?php

declare(strict_types=1);

namespace Vendor\ModuleName\Configuration;

use Amarant\Framework\Contract\Di\ConfigurableContainerInterface;
use Amarant\Framework\Contract\Di\DiConfiguratorInterface;
use Amarant\Framework\Data\Set;
use Amarant\Framework\Enum\DiEnum;
use Override;
use Vendor\ModuleName\DataModel\Persister\ExamplePersister;

class Di implements DiConfiguratorInterface
{
    #[Override] public function configure(ConfigurableContainerInterface $container, Set $configurators): void
    {
        $container
            ->tag(
                [
                    // (1)
                    450 => ExamplePersister::class,
                ],
                [DiEnum::DB_PERSISTER]
            );
    }
}

1. Use priority lower than 500 to run this persister after the default one or use priority greater than 500 to run before it.

Querying

---

Queries are built using Amarant\Framework\Database\Connection\QueryBuilder. Obtain an instance via the injected repository:

$queryBuilder = $this->repository->getQueryBuilder();

QueryBuilder is a fluent builder. Call filter and modifier methods on it, then pass it to a repository query method.

Filtering

The where method sets the initial condition. Each subsequent call appends with AND by default.

use Amarant\Framework\Search\Builder\Filter;

$queryBuilder
    ->where([
        Example::FIELD_FIELD_A => 'active',                            // (1)
        Example::FIELD_FIELD_B => [Filter::OPERATOR_GT => 100],        // (2)
    ])
    ->andWhereOr([                                                      // (3)
        [Example::FIELD_FIELD_B => [Filter::OPERATOR_NULL => true]],
        [Example::FIELD_FIELD_B => [Filter::OPERATOR_LTE => 500]],
    ]);

1. A plain value defaults to Filter::OPERATOR_EQ.
2. Operator is specified as a nested array: [Filter::OPERATOR_* => $value].
3. andWhereOr appends an AND (… OR …) group.

Method	SQL equivalent
where(array $fields)	Sets the first condition group (WHERE …).
andWhere(array $fields)	Appends AND (a AND b AND …).
andWhereNot(array $fields)	Appends AND NOT (a AND b AND …).
andWhereOr(array $fields)	Appends AND (a OR b OR …).
andWhereNotOr(array $fields)	Appends AND NOT (a OR b OR …).
whereQuery(string $query)	Sets the first condition as a raw SQL string.
andWhereQuery(string $query)	Appends a raw SQL string with AND.

Filter operators

All operators are constants on Amarant\Framework\Search\Builder\Filter.

Constant	SQL
Filter::OPERATOR_EQ	= value
Filter::OPERATOR_NEQ	!= value
Filter::OPERATOR_IN	IN (…) — uses @> on array columns
Filter::OPERATOR_NIN	NOT IN (…) — uses @> on array columns
Filter::OPERATOR_INA	Array field contains all values (@>)
Filter::OPERATOR_NINA	Array field does not contain all values
Filter::OPERATOR_AINA	Array field overlaps values (&&)
Filter::OPERATOR_ANINA	Array field does not overlap values (&&)
Filter::OPERATOR_INOV	In nested JSON object values
Filter::OPERATOR_GT	> value
Filter::OPERATOR_GTE	>= value
Filter::OPERATOR_LT	< value
Filter::OPERATOR_LTE	<= value
Filter::OPERATOR_NULL	IS NULL (true) / IS NOT NULL (false)
Filter::OPERATOR_RANGE	BETWEEN low AND high — value is [low, high]
Filter::OPERATOR_NRANGE	NOT BETWEEN low AND high
Filter::OPERATOR_LIKE	LIKE '%value%'
Filter::OPERATOR_NLIKE	NOT LIKE '%value%'
Filter::OPERATOR_ILIKE	ILIKE '%value%' (case-insensitive)
Filter::OPERATOR_NILIKE	NOT ILIKE '%value%'
Filter::OPERATOR_TRUE	IS TRUE
Filter::OPERATOR_FALSE	IS FALSE
Filter::OPERATOR_EXISTS	EXISTS (subquery)
Filter::OPERATOR_COALESCE_EMPTY	COALESCE(field, '') = ''
Filter::OPERATOR_COALESCE_EMPTY_NOT	COALESCE(field, '') != ''
Filter::OPERATOR_JSON_INA	JSON array field contains value
Filter::OPERATOR_JSON_NINA	JSON array field does not contain value
Filter::OPERATOR_JSONB_ATTRIBUTE_PATH_EXISTS	jsonb_path_exists(…)

Sorting, grouping, and limiting

$queryBuilder
    ->withOrderBy(Example::FIELD_FIELD_A, QueryBuilder::SORT_ASC)
    ->withOrderBy(Example::FIELD_FIELD_B, QueryBuilder::SORT_DESC)
    ->withGroupBy(Example::FIELD_FIELD_A)
    ->withLimit('10 OFFSET 0');

Field prefixing

QueryBuilder::prefix(string $field) prefixes a field with the main table alias (e.field_a). Use it when building raw join conditions or ON clauses to avoid ambiguous column names.

QueryBuilder::prefix(Example::FIELD_FIELD_A) // → 'e.field_a'

Duplicating a query builder

duplicate() returns a deep copy of the current builder state. Useful for building a base query once and forking it for different result sets.

$base = $this->repository->getQueryBuilder()->where([Example::FIELD_FIELD_A => 'active']);

$all = $base->duplicate()->withOrderBy(Example::FIELD_FIELD_A);
$page = $base->duplicate()->withLimit('10 OFFSET 0');

Repository query methods

---

All methods below are on Amarant\Framework\Contract\Database\Repository\RepositoryInterface.

Method	Description
get(int\|string $id): ?DataModelInterface	Load a single model by primary key.
getByQuery(QueryBuilder $qb): Set	Return all matching models as a Set.
getOneByQuery(QueryBuilder $qb): ?DataModelInterface	Return the first matching model or null.
getCount(QueryBuilder $qb): int	Return the count of matching rows.
getIdValues(QueryBuilder $qb): iterable	Stream primary key values.
getIdValuesAsArray(QueryBuilder $qb): array	Return primary key values as an array.
getColumnValuesByQuery(QueryBuilder $qb, string $column): iterable	Stream a single column's values.
getColumnValueByQuery(QueryBuilder $qb, string $column): mixed	Return a single column value from the first matching row.
scrollByQuery(QueryBuilder $qb): iterable	Cursor-based scroll over a large result set without loading it all into memory.
paginateByQuery(QueryBuilder $qb, int $pageSize, int $page, ?Closure $itemConverter): PaginatorInterface	Return a paginated result set.
batchLoadById(array $ids): array	Load models by an array of primary key values; returns [id => DataModel] map.
batchLoadByField(string $field, array $ids): array	Load models grouped by a field value; returns [fieldValue => [DataModel, …]] map.
save(DataModelInterface $dataModel): ?DataModelInterface	Persist a model (insert or update).
delete(DataModelInterface $dataModel): void	Delete a model.
deleteByIds(array $ids): void	Delete rows by primary key values.
deleteByQuery(QueryBuilder $qb): void	Delete all rows matching the query.
truncate(): void	Truncate the table.
getByRawQuery(string $sql, array $params): Set	Execute a raw SQL query and hydrate results.
getOneByRawQuery(string $sql, array $params): ?DataModelInterface	Execute a raw SQL query and return the first hydrated model.
getColumnByRawQuery(string $sql, array $params, string $column): iterable	Execute a raw SQL query and stream a single column.

Scrolling large result sets

scrollByQuery returns a lazy iterable. Use it when processing tables that are too large to load entirely into memory.

foreach ($this->repository->scrollByQuery($queryBuilder) as $id => $model) {
    $this->process($model);
}

Batch loading

Use batchLoadById and batchLoadByField to avoid N+1 query patterns inside lazy-loading callbacks. Collect all IDs first, then load in a single query.

// Collect IDs, then batch load
$ids = array_map(fn (Order $o) => $o->getCustomerId(), $orders);
$customerMap = $this->customerRepository->batchLoadById($ids); // [id => Customer]

foreach ($orders as $order) {
    $customer = $customerMap[$order->getCustomerId()] ?? null;
}

Transactions

---

Amarant\Framework\Contract\Database\Connection\ConnectionInterface exposes the transaction API. Inject the connection directly (or retrieve it from ConnectionPool) when you need explicit transaction control.

Automatic retry with transactional

The recommended approach for write operations is transactional, which handles begin/commit/rollback and automatically retries on deadlocks.

$connection->transactional(
    function () use ($connection): void {
        $connection->insert('example', ['field_a' => 'value']);
        $this->repository->save($model);
    },
    skipBeginTransaction: false,
    maxRetries: 5           // (1)
);

1. Maximum retry attempts on deadlock. Default is 5.

Manual transaction control

$connection->beginTransaction();
try {
    $this->repository->save($model);
    $connection->commit();
} catch (Throwable $e) {
    $connection->rollback();
    throw $e;
}

Commit events

queueCommitEvents schedules events to be dispatched only after the outermost transaction commits. This prevents sending notifications or invalidating caches when a transaction is rolled back.

$connection->queueCommitEvents([$event]);

Joins

---

Joins are added to a QueryBuilder via withJoin or withJoinOnce. The join type is controlled by constants on Amarant\Framework\Database\Join.

use Amarant\Framework\Database\Join;
use Amarant\Framework\Database\Connection\QueryBuilder;

$queryBuilder->withJoin(
    name: ['ot' => 'other_table'],                              // (1)
    on: sprintf(
        '%s.%s = %s',
        'ot',
        OtherModel::FIELD_EXAMPLE_ID,
        QueryBuilder::prefix(Example::getIdField())             // (2)
    ),
    cols: ['ot.some_field'],                                    // (3)
    type: Join::JOIN_TYPE_LEFT                                  // (4)
);

1. ['alias' => 'table_name'] — exactly one entry.
2. QueryBuilder::prefix adds the main table alias to avoid ambiguity.
3. Columns to include from the joined table in the SELECT.
4. Join::JOIN_TYPE_LEFT, JOIN_TYPE_RIGHT, JOIN_TYPE_INNER, or JOIN_TYPE_FULL.

withJoinOnce is identical but skips adding the join if the same alias was already added. Use it in shared query-builder helpers that may be called multiple times.

Upserts

---

ConnectionInterface::upsert inserts a row or updates it on conflict. Use Amarant\Framework\Database\OnConflict to describe the conflict target and action.

use Amarant\Framework\Database\OnConflict;

$connection->upsert(
    table: 'example',
    data: ['field_a' => 'value', 'field_b' => 42],
    onConflict: new OnConflict(
        target: Example::FIELD_FIELD_A,           // (1)
        action: OnConflict::ACTION_UPDATE,         // (2)
        updateKeys: [Example::FIELD_FIELD_B]       // (3)
    )
);

1. The conflict target column (unique constraint column).
2. OnConflict::ACTION_UPDATE to update, OnConflict::ACTION_NOTHING to silently skip on conflict.
3. Which columns to update when a conflict is detected. Ignored when ACTION_NOTHING is used.

Raw values

---

Amarant\Framework\Database\RawValue wraps a value to prevent it from being quoted or parameterized. Use it when passing a literal SQL expression as a column value.

use Amarant\Framework\Database\RawValue;

$connection->insert('example', [
    'field_a' => 'normal value',   // bound as a parameter
    'field_b' => new RawValue('NOW()'),   // inserted verbatim
]);

RawValue::fromValues(array $values) is a convenience constructor that wraps each element of an array in a RawValue.