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:

• provide bulk BulkInserter, BulkUpdater, BulkUpserter, BulkDeleter with explicit type per field and DB-native upsert (ON DUPLICATE KEY UPDATE on MySQL/MariaDB, ON CONFLICT ... DO UPDATE on PostgreSQL);
• provide DbalFinder and DbalMutator for typed reads and writes with optional DTO mapping (no ORM hydration);
• provide CursorIteratorInterface and OffsetIteratorInterface for streaming large result sets with bounded memory;
• support multiple Doctrine Connection instances side-by-side (multi-database / sharded consumers);
• expose TransactionService with PostgreSQL advisory locks and row-level FOR UPDATE / FOR NO KEY UPDATE / FOR SHARE / FOR KEY SHARE locks (with MySQL 8+ semantics);
• register seven Doctrine Types on build() so consumers can declare columns via #[Column(type: '...')] without hand-rolled Type registration;
• emit PSR-3 events from every bulk operation (start, end, constraint_violation, connection_error) when a logger is injected;
• emit a JSON caller-trace comment prepended to every executeQuery / executeStatement / prepare call when sql_comment_enabled is on, so MySQL slow log and pg_stat_statements carry the originating controller;
• ship an itests/ integration layer with verified numbers on MySQL 8.4, MariaDB 11, and PostgreSQL 16 (see §Verified Load-Test Performance).

The bundle does not:

• replace Doctrine ORM — it runs alongside it. Single-row reads and transactional writes should stay on ORM unless there is a measured reason to move them;
• ship a query DSL or a query builder beyond the per-vendor SQL helpers in Sql\Builder\SqlBuilderInterface;
• manage schema or migrations — use Doctrine Migrations or any other tool you already have;
• ship an ORM-style unit-of-work or identity map. The bundle operates row-by-row at the DBAL level;
• support Doctrine ORM 3.x + DBAL 3.x as a runtime combo. The bundle targets DBAL 4.2+; using DBAL 3.x will throw at boot.

Compatibility

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:

• processing large volumes of data with minimal memory consumption;
• starting data processing before the entire query completes (lazy loading);
• implementing streaming and data transfer — useful when integrating with queues, APIs, synchronisation logic, and exports.

Core interfaces

Finder / Mutator

• DbalFinderInterface: reading data, with optional mapping of the result to a DTO.
• DbalMutatorInterface: updates, deletes, inserts, plus a plain execute method.

Bulk operations

• BulkInserterInterface: inserts one or several rows into the database.
• BulkUpdaterInterface: updates one or several rows in the database.
• BulkUpserterInterface: a combined operation that updates or inserts rows (upsert) in the database.
• BulkDeleterInterface: deletes rows from the database, including soft-delete support.

Iterators

• CursorIteratorInterface: cursor-based reading, suitable for streaming processing.
• OffsetIteratorInterface: standard paginated iteration.

Helpers

• DtoFieldExtractor: extracts and normalises fields from a DTO.
• DbalTypeGuesser: maps PHP types to SQL types.
• MysqlSqlBuilder: SQL query generator for MySQL.

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:

• the table name;
• the array of rows to insert;
• automatic or manual ID generation;
• explicit type per field.

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:

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:

{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:

• float_array — FLOAT[] array literal (PostgreSQL).
• float4_array — FLOAT4[] array literal (PostgreSQL).
• int_array — INT[] array literal (PostgreSQL).
• text_array — TEXT[] array literal (PostgreSQL).
• numeric_array — NUMERIC[] array literal (PostgreSQL).
• jsonb — Doctrine’s built-in JsonbType; emits JSONB on PostgreSQL 12+ and falls back to JSON on MySQL / MariaDB.
• jsonb_object — Doctrine’s built-in JsonbObjectType; same shape as jsonb but normalises object shapes on read.

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):

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

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:

• --chunk=<int> — chunk size for batch processing.
• --count=<int> — number of records (default 1000).
• --cycle=<int> — number of insert / update / delete cycles (for benchmarking).
• --track — enables logging of the results.

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:

• iteration number;
• execution time;
• memory used;
• memory delta;
• cumulative time.

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

• PostgreSQL 17 / MariaDB 12 / MySQL 9 have not yet been added as dedicated test fixtures. SQL fragments in the bundle are expected to remain compatible because they target stable core features (ON DUPLICATE KEY UPDATE, ON CONFLICT ... DO UPDATE), but please open an issue if you hit a regression on a newer server.

Sources

• github.com/elriseio/dbal-bundle — MIT.
• Packagist: elriseio/dbal-bundle
• Doctrine DBAL documentation
• Symfony documentation