user@elrise.io:~/dbal-bundle$ 
· [active]

dbal-bundle — DBAL manager for Symfony highload projects

→ репозиторий

Overview

Dbal Bundle is a Symfony bundle for high-load systems where the standard capabilities of Doctrine ORM become a bottleneck. The bundle provides abstractions and interfaces for direct, efficient, and scalable database operations at the Doctrine DBAL level — bulk insert/update/upsert, cursor-based streaming iterators, DTO mapping, advisory locks, and verified cross-vendor performance against MySQL 8, MariaDB 10.5+, and PostgreSQL 12+.

The bundle does not aim to replace Doctrine ORM. It lives next to it: use ORM for transactional single-row reads/writes, use the bundle for bulk operations, streaming reads, and read-modify-write sequences that need explicit concurrency control.

The architectural reasoning behind this layer — what tasks ORM does not carry, where its zone of responsibility ends, and why a dedicated DBAL layer pays off in high-load projects — is in Why high-load Symfony needs a dedicated DBAL layer alongside ORM.

Quick start

Minimum scenario: install the bundle, register it, and run a bulk insert through the factory.

composer require elriseio/dbal-bundle

// config/bundles.php
return [
    Elrise\Bundle\DbalBundle\ElriseDbalBundle::class => ['all' => true],
];

class MyService
{
    public function __construct(private DbalManagerFactory $factory) {}

    public function insertUsers(array $emails): void
    {
        $inserter = $this->factory->createBulkInserter();
        $now = date('Y-m-d H:i:s');

        $inserter->insert('users', array_map(
            static fn (string $email) => ['email' => $email, 'created_at' => $now],
            $emails,
        ));
    }
}

Full configuration, all bulk operations, concurrency helpers, and the verified performance tables are below.

What it is and what it is not

The bundle does:

The bundle does not:

Compatibility

Component Version
PHP 8.3+ (strict types)
Symfony 7.2+
Doctrine DBAL 4.2+ (verified on 4.4.x)
MySQL 8.0+ (verified on 8.0, 8.4 LTS)
MariaDB 10.5+ (verified on 10.6, 10.10, 10.11, 11.0.7)
PostgreSQL 12+ (test fixtures target 16; 17 expected compatible)
PSR-3 1.x or 3.x (declared psr/log: ^3.0 in composer.json)

CI gate: scripts/check_interface_namespace_imports.sh rejects legacy Enum\*Interface imports.

Architecture

The bundle is built on interfaces and abstractions that are easy to extend and adapt to any needs.

At the core of select operations are generators (yield), which allows:

Core interfaces

Finder / Mutator

Bulk operations

Iterators

Helpers

Installation

composer require elriseio/dbal-bundle

Register the bundle in config/bundles.php:

return [
    Elrise\Bundle\DbalBundle\ElriseDbalBundle::class => ['all' => true],
];

Working with DbalManagerFactory

The DbalManagerFactory class lets you conveniently create DBAL infrastructure components with the ability to override the database connection (Connection) and the configuration (DbalBundleConfig) per service.

Quick creation of DbalManager

If you want to use all DBAL components at once, just call createManager():

$dbalManager = $factory->createManager();

You can pass custom Connection and DbalBundleConfig:

$dbalManager = $factory->createManager($customConnection, $customConfig);

Creating individual components

If you need one of the components separately, use the matching method:

$finder         = $factory->createFinder(...);
$mutator        = $factory->createMutator(...);
$cursorIterator = $factory->createCursorIterator(...);
$offsetIterator = $factory->createOffsetIterator(...);
$bulkInserter   = $factory->createBulkInserter(...);
$bulkUpdater    = $factory->createBulkUpdater(...);
$bulkUpserter   = $factory->createBulkUpserter(...);
$bulkDeleter    = $factory->createBulkDeleter(...);

For every method you can specify a custom Connection and (optionally) DbalBundleConfig. This is especially useful when working with multiple databases or with different configuration strategies.

Service example

class MyService
{
    public function __construct(private DbalManagerFactory $factory) {}

    public function updateBulkData(array $rows): void
    {
        $bulkUpdater = $this->factory->createBulkUpdater();
        $bulkUpdater->update('my_table', $rows);
    }
}

Bulk Insert

The module supports bulk data insertion with the ability to specify:

Usage example

/** @var BulkInserterInterface $inserter */
$inserter->insert('user_table', [
    [
        'id' => IdStrategy::AUTO_INCREMENT, // ID generated by the DB
        'email' => ['user1@example.com', ParameterType::STRING],
        'created_at' => (new \DateTime())->format('Y-m-d H:i:s'),
    ],
    [
        'id' => IdStrategy::UUID, // ID generated in code
        'email' => ['user2@example.com', ParameterType::STRING],
        'created_at' => (new \DateTime())->format('Y-m-d H:i:s'),
    ],
    [
        // ID generated in code (UUIDv7 default)
        'email' => ['user3@example.com', ParameterType::STRING],
        'created_at' => (new \DateTime())->format('Y-m-d H:i:s'),
    ],
]);

The ['value', ParameterType::TYPE] array lets you set the value type, compatible with Doctrine\DBAL\ParameterType. If the type is not specified, it is determined automatically.

ID generation strategies (IdStrategy)

The ID can be generated automatically or set manually, depending on the strategy:

Strategy Description
IdStrategy::AUTO_INCREMENT The value is not given — it is generated at the database level.
IdStrategy::UUID The value is generated in code (UUIDv7). Default for new code.
IdStrategy::UID Deprecated since 1.0.x for collision-safety; remains in 2.0 (per ADR-0002 § Decision 3). The 18-char id is collision-prone under concurrent writers. Prefer IdStrategy::UUID (UUIDv7) for new code; existing IdStrategy::UID users do not need to migrate to upgrade to 2.0.
IdStrategy::INT The value is generated as a random integer.
IdStrategy::STRING A string is generated (for example, based on uniqid()).
IdStrategy::DEFAULT The value must be used for working with Postgres and generating a DEFAULT ID as part of Insert / Upsert operations.
IdStrategy::migrateFromV1() Static helper. Maps v1 cases (INT, STRING) to their v2 replacements per ADR-0002 § Decision 3. Available in 1.x as a developer-experience aid for consumers planning their 2.0 upgrade. Cases surviving 2.0 (AUTO_INCREMENT, UUID, UID, DEFAULT) pass through unchanged.

DbalBulkUpdater

DbalBulkUpdater lets you update from one to many rows in the database.

Example

$bulkUpdater
    ->updateMany('api_history', [
        ['id' => 1, 'status' => 'success'],
        ['id' => 2, 'status' => 'success'],
    ]);

By default the id field is used as the condition. Updates are issued via CASE WHEN ... THEN ... without multiple queries. The number of affected rows is returned.

DbalBulkUpserter

DbalBulkUpserter lets you insert or update records by key fields. If a record with that id already exists, it is updated; otherwise a new record is inserted.

Example

$bulkUpserter
    ->upsertMany('api_history', [
        [
            'id' => 123,
            'status' => 'success',
            'updated_at' => date('Y-m-d H:i:s'),
            'created_at' => date('Y-m-d H:i:s'),
        ],
        [
            'id' => IdStrategy::AUTO_INCREMENT,
            'status' => 'success',
            'updated_at' => date('Y-m-d H:i:s'),
            'created_at' => date('Y-m-d H:i:s'),
        ],
    ], ['status', 'updated_at']);

Replaceable fields are passed as the third argument (replaceFields). id can be generated automatically via IdStrategy::AUTO_INCREMENT.

PostgreSQL: RETURNING id in one round-trip

On PostgreSQL, upsertManyReturningIds appends RETURNING <column> to the upsert SQL and returns the generated/updated IDs in row order. MySQL / MariaDB do not support RETURNING and will throw LogicException on this call.

$ids = $bulkUpserter->upsertManyReturningIds(
    'api_history',
    [
        ['id' => IdStrategy::AUTO_INCREMENT, 'status' => 'success', 'updated_at' => date('Y-m-d H:i:s')],
        ['id' => IdStrategy::AUTO_INCREMENT, 'status' => 'pending', 'updated_at' => date('Y-m-d H:i:s')],
    ],
    ['status', 'updated_at'],
);
// $ids is [123, 124] in the order of the input rows.

DbalFinder

DbalFinder provides methods for typed retrieval of data from the database.

Usage examples

// Get one row by SQL (LIMIT 1 is appended automatically).
$result = $finder->fetchOneBySql(
    'SELECT * FROM api_history WHERE id = :id',
    ['id' => $id],
    ApiDto::class
);

// Get multiple rows with mapping to a DTO.
$results = $finder->fetchAllBySql(
    'SELECT * FROM api_history ORDER BY id LIMIT 10',
    [],
    ApiDto::class
);

// Find a record by ID.
$result = $finder->findById($id, 'api_history', ApiDto::class);

// Find records by a list of IDs.
$result = $finder->findByIdList($idList, 'api_history', ApiDto::class);

If no DTO class is given, an array is returned.

DbalMutator

DbalMutator is intended for safe insertion and modification of data in database tables.

Usage examples

// Insert one row into a table.
$mutator->insert('api_history', [
    'type' => ['callback', ParameterType::STRING],
    'merchant_id' => '12345',
    'provider' => 'example-provider',
    'trace_id' => 'trace-001',
    'our_id' => 'our-001',
    'ext_id' => 'ext-001',
    'data' => json_encode(['source' => 'test']),
    'status' => 'success',
    'created_at' => date('Y-m-d H:i:s'),
    'updated_at' => date('Y-m-d H:i:s'),
]);

Fields with types (for example, ['value', ParameterType::STRING]) are supported. If no type is specified, it is determined automatically.

⚠️ Important

Before using insert(), updateMany(), upsertMany(), you must always specify the actual service fields via setFieldNames() or the common fieldNames config:

->setFieldNames([
    BundleConfigurationInterface::ID_NAME => 'id',
    BundleConfigurationInterface::CREATED_AT_NAME => 'created_at',
    BundleConfigurationInterface::UPDATED_AT_NAME => 'updated_at',
])

SQL caller-trace comment (opt-in)

The bundle exposes the existing DbalConnection::setAdditionalSqlCommentEnable toggle through the doctrine_dbal bundle configuration. Default: off (backward-compatible).

When enabled, every executeQuery, executeStatement, and prepare call prepends a JSON caller-trace comment to the SQL string. The comment carries applicationCaller (the first non-framework class in the backtrace) and entryPointController (the Symfony controller or console command name, if any). Operators can read the comment in their MySQL slow log, PostgreSQL pg_stat_statements, or the database's general log to see which application code path produced each query.

Enable per environment in config/packages/doctrine_dbal.yaml:

doctrine_dbal:
    sql_comment_enabled: true

The same flag is also exposed on DbalBundleConfig::$sqlCommentEnabled for programmatic control in tests or custom wiring.

PSR-3 Logger integration for bulk operations (opt-in)

AbstractDbalWriteExecutor and its descendants (BulkInserter, BulkUpdater, BulkUpserter, BulkDeleter) accept an optional Psr\Log\LoggerInterface. When no logger is injected, the executor uses a NullLogger and behaves exactly as before. When a logger is injected (e.g. Symfony\Bridge\Monolog\Logger), each bulk operation emits four event types:

Event Level Payload
dbal.bulk.{op}.start INFO operation, table, chunk_size, total_rows
dbal.bulk.{op}.end INFO operation, table, rows_affected, duration_ms, peak_memory_mb
dbal.bulk.constraint_violation WARNING original DBAL message, attempt count
dbal.bulk.connection_error ERROR original DBAL message, attempt count

{op} is one of insert, update, upsert, delete, soft_delete.

Wire a logger via services.yaml:

services:
    Elrise\Bundle\DbalBundle\Manager\Bulk\BulkInserter:
        parent: Elrise\Bundle\DbalBundle\Manager\Bulk\AbstractDbalWriteExecutor
        arguments:
            $logger: '@monolog.logger.dbal'

    monolog.logger.dbal:
        class: Symfony\Bridge\Monolog\Logger
        arguments: ['dbal']

composer.json already declares psr/log: ^3.0; no additional dependency is required.

Concurrency helpers (advisory locks and row-level locks)

TransactionService exposes high-throughput concurrency helpers.

PostgreSQL advisory locks

PostgreSQL advisory locks are application-level mutexes not tied to any table — useful for leader election or single-writer sections across an entire app.

// PG advisory lock — leader election or single-writer section
$txService->transactional(function () use ($txService) {
    $txService->acquireAdvisoryLock(42); // blocks until granted
    try {
        // ... critical section ...
    } finally {
        $txService->releaseAdvisoryLock(42);
    }
});

tryAdvisoryLock($key) is the non-blocking variant; it returns false if another holder owns the lock. All three advisory-lock methods are PostgreSQL-only and throw LogicException on MySQL / MariaDB.

Row-level locks

SELECT ... WHERE ... FOR UPDATE locks specific rows before a read-modify-write sequence so other transactions cannot modify them until the current transaction commits or rolls back.

// Row-level lock before read-modify-write
$txService->transactional(function () use ($txService, $id) {
    $txService->lockRows('orders', ['id' => $id]); // FOR UPDATE
    $order = $finder->findById($id, 'orders');
    // ... modify and save ...
});

The LockMode enum (FOR_UPDATE, FOR_NO_KEY_UPDATE, FOR_SHARE, FOR_KEY_SHARE) covers both PostgreSQL row-lock forms and MySQL 8+ semantics. On MySQL legacy, FOR_SHARE maps to LOCK IN SHARE MODE. FOR_NO_KEY_UPDATE and FOR_KEY_SHARE are PostgreSQL-only and throw LogicException on MySQL / MariaDB.

Registered Doctrine Types

The bundle registers seven Doctrine Types on build() so consumers can declare columns via #[Column(type: '...')] without hand-rolled Type registration:

Verified Load-Test Performance

Measured by the itests/ integration load-testing pipeline (ADR-0004) on real MySQL 8.4, PostgreSQL 16, and MariaDB 11 in Docker. Full report with the per-vendor raw envelopes: itests/reports/WAVE_LT_CROSS_VENDOR_REPORT.md.

Single-vendor metrics (one --rows 1000 --chunk 100 invocation per scenario):

Scenario Vendor duration ops/sec p50 latency p95 / p99 latency errors
bulk_insert MySQL 8.4 0.207s 4,831 16.48 ms 41.45 ms 0
bulk_insert PostgreSQL 16 0.130s 7,692 10.31 ms 20.79 ms 0
bulk_insert MariaDB 11 0.222s 4,505 20.73 ms 37.99 ms 0
bulk_upsert MySQL 8.4 0.027s 37,037 11.29 ms 104.90 ms 0
bulk_upsert PostgreSQL 16 0.108s 9,259 10.17 ms 805.01 ms 0
bulk_upsert MariaDB 11 0.028s 35,714 12.12 ms 47.19 ms 0
bulk_update MySQL 8.4 0.031s 64,516 1.97 ms 19.45 / 31.68 ms 0
bulk_update PostgreSQL 16 0.148s 13,514 9.34 ms 14.45 / 16.23 ms 0
bulk_update MariaDB 11 0.028s 71,429 1.81 ms 9.41 / 13.85 ms 0
cursor_stream MySQL 8.4 0.002s 500,000 0.15 ms 0.21 ms 0
cursor_stream PostgreSQL 16 0.003s 333,333 0.23 ms 0.30 ms 0
cursor_stream MariaDB 11 0.002s 500,000 0.13 ms 0.17 ms 0

Bounded-concurrency parallel runner (--workers 2 --concurrency 2, cursor_stream, --rows 500 per worker):

Vendor total_rows total_duration throughput ops/sec p50 / p95 / p99 latency errors
MySQL 8.4 400 0.002s 200,000 0.31 / 0.34 / 0.42 ms 0
PostgreSQL 16 1,000 0.002s 500,000 0.23 / 0.24 / 0.24 ms 0
MariaDB 11 1,000 0.002s 500,000 0.32 / 0.35 / 0.38 ms 0

PG and MariaDB scale linearly with worker count. MySQL's total_rows=400 on the parallel run is a known unique-email collision between workers, not a correctness regression.

Envelope contract (ADR-0004 Decision §5) is observed end-to-end on all three vendors; 306/306 PHPUnit tests pass; php-cs-fixer reports 0 violations. bulk_insert is bounded at 4-7k rows/sec on all three vendors — a known gap to native COPY / LOAD DATA captured in ADR-0005 and tracked under AR-036; not a correctness regression.

Testing

The bundle ships a BulkTest console-command suite under BulkTestCommands/ that exercises every bulk operation against a real database. The fixtures live in tests/_db/.

Wire the test commands

Add to services.yaml:

services:
    Elrise\Bundle\DbalBundle\Manager\Bulk\BulkUpserter:
        arguments:
            $connection: '@Doctrine\DBAL\Connection'
            $config: '@Elrise\Bundle\DbalBundle\Config\DbalBundleConfig'
            $sqlBuilder: '@Elrise\Bundle\DbalBundle\Sql\Builder\SqlBuilderInterface'

    Elrise\Bundle\DbalBundle\BulkTestCommands\BulkInsertManyCommand:
        arguments:
            $connection: '@Doctrine\DBAL\Connection'
            $bulkInserter: '@Elrise\Bundle\DbalBundle\Manager\Contract\BulkInserterInterface'
        tags: [ 'console.command' ]

    Elrise\Bundle\DbalBundle\BulkTestCommands\BulkUpdateManyCommand:
        arguments:
            $connection: '@Doctrine\DBAL\Connection'
            $bulkInserter: '@Elrise\Bundle\DbalBundle\Manager\Contract\BulkInserterInterface'
            $bulkUpdater: '@Elrise\Bundle\DbalBundle\Manager\Contract\BulkUpdaterInterface'
        tags: [ 'console.command' ]

    Elrise\Bundle\DbalBundle\BulkTestCommands\BulkUpsertManyCommand:
        arguments:
            $connection: '@Doctrine\DBAL\Connection'
            $bulkInserter: '@Elrise\Bundle\DbalBundle\Manager\Contract\BulkInserterInterface'
            $bulkUpserter: '@Elrise\Bundle\DbalBundle\Manager\Contract\BulkUpserterInterface'
        tags: [ 'console.command' ]

    Elrise\Bundle\DbalBundle\BulkTestCommands\BulkDeleteManyCommand:
        arguments:
            $connection: '@Doctrine\DBAL\Connection'
            $bulkDeleter: '@Elrise\Bundle\DbalBundle\Manager\Contract\BulkDeleterInterface'
            $bulkInserter: '@Elrise\Bundle\DbalBundle\Manager\Contract\BulkInserterInterface'
        tags: [ 'console.command' ]

    Elrise\Bundle\DbalBundle\BulkTestCommands\BulkSoftDeleteManyCommand:
        arguments:
            $connection: '@Doctrine\DBAL\Connection'
            $bulkDeleter: '@Elrise\Bundle\DbalBundle\Manager\Contract\BulkDeleterInterface'
            $bulkInserter: '@Elrise\Bundle\DbalBundle\Manager\Contract\BulkInserterInterface'
        tags: [ 'console.command' ]

Test table

Run the prepopulated SQL fixtures against your test database before executing the commands:

// for MySQL
tests/_db/init.sql

// for PostgreSQL
tests/_db/init_postgres.sql

Command usage

bin/console dbal:test:run-all
bin/console dbal:test:bulk-insert-many
bin/console dbal:test:bulk-update-many
bin/console dbal:test:bulk-upsert-many
bin/console dbal:test:bulk-delete-many
bin/console dbal:test:bulk-soft-delete-many
bin/console dbal:test:cursor-iterator
bin/console dbal:test:offset-iterator
bin/console dbal:test:finder
bin/console dbal:test:mutator
bin/console dbal:test:transaction-service
bin/console dbal:test:insert

Every command supports:

Example:

bin/console app:test:bulk-upsert-many --chunk=200 --count=5000 --cycle=5 --track

Result logging

When the --track flag is passed, the command saves performance logs to a CSV file:

var/log/<test_type>_<timestamp>.csv

Each row in the log contains:

Get hands-on

dummy-market-agent is a reference Symfony service with Application Layer, CQRS, API Platform, and DBAL-based persistence. Start it with Docker to inspect API operations, command/query handlers, persistence, container wiring, layer boundaries, and tests.

Known compatibility gaps

Sources