Dbal Bundle for Symfony

Dbal Bundle is a module for Symfony applications designed 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.

Key Features

• High-performance database operations at the DBAL level.
• Direct work with DTOs and data arrays, without the ORM layer.
• Advanced bulk operations: insert, update, upsert, delete.
• Interfaces for cursor-based and offset-based iterators.
• Basic Finder/Mutator interfaces for reading and modifying data.
• Support for multiple database connections.
• Full control over SQL queries.
• Support for the following databases:
  • MySQL 8 (including 8.4 LTS)
  • MariaDB 10.5+ (10.6, 10.10, 10.11, 11.0.7)
  • PostgreSQL 12+ (verified through 16; 17 compatible but no fixture yet)

Verified Load-Test Performance (Wave LT, --rows 1000 --chunk 100)

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

Notes on the table:

• bulk_upsert workload: 500 rows on the insert path + 500 on the update path (round-robin email against the seeded dataset).
• bulk_update workload: 500 rows on the narrow shape (SET status, WHERE id = ?) + 500 rows on the wide shape (SET quantity / amount_cents / placed_at, WHERE id = ? AND status = ?).
• The p99 jump on PostgreSQL bulk_upsert (805 ms vs 47-105 ms on MySQL/MariaDB) reflects PG's per-statement ON CONFLICT index scan; p50 stays at ~10 ms on all three vendors.
• cursor_stream peak RSS is ~4 MiB on all three, well below the 64 MiB contract ceiling from AR-024.

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 (2× workers ≈ 2× throughput). MySQL's total_rows=400 (instead of 1000) is a known unique-email collision between parallel workers; not a correctness regression — the read path itself is correct.

What the numbers mean

• cursor_stream and bulk_update are excellent on every vendor; the bundle's bulk and streaming paths deliver sub-millisecond p99 latency for reads and narrow updates.
• bulk_upsert is best on MySQL/MariaDB (35-37k ops/sec via ON DUPLICATE KEY UPDATE); PG's ON CONFLICT ... DO UPDATE is ~4× slower on the cold chunk (805 ms p95 vs 105 ms on MySQL) but reaches a similar steady-state on warm chunks.
• bulk_insert runs through the PDO prepared-statement path with multi-row VALUES. It is bounded at 4-7k rows/sec on all three vendors — 1-2 orders of magnitude below the underlying DBs' native bulk-load capability (PG COPY, MySQL/MariaDB LOAD DATA LOCAL INFILE). The per-vendor fast path is captured in design-spike ADR-0005 and tracked under Issues/open/architect/AR-036. No production consumer is currently affected; the gap is a trend-detection signal, not a correctness regression.

All scenarios report errors: 0 on every vendor; the canonical envelope contract (ADR-0004 Decision §5) is observed end-to-end; the bounded-concurrency parallel runner scales linearly on PG and MariaDB. 306/306 PHPUnit tests pass; php-cs-fixer reports 0 new violations.

Architecture

The Dbal 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, synchronization logic, and exports

Key Interfaces:

Finder/Mutator

• DbalFinderInterface: Data reading, supports mapping results to DTO.
• DbalMutatorInterface: Update, delete, insert, with a raw execute method.

Bulk Operations

• BulkInserterInterface: Insert one or more rows into the database.
• BulkUpdaterInterface: Update one or multiple rows in the database.
• BulkUpserterInterface: Combined operation for updating or inserting rows (upsert) into the database.
• BulkDeleterInterface: Delete rows from the database, including support for soft deletes.

Iterators

• CursorIteratorInterface: Supports cursor-based reading, suitable for streaming data processing.
• OffsetIteratorInterface: Standard pagination iteration.

Helper Classes

• DtoFieldExtractor: Extracts and normalizes fields from DTOs.
• DbalTypeGuesser: Maps PHP types to SQL types.
• MysqlSqlBuilder: SQL query generator for MySQL.

Installation

Run the following command to install the bundle:

composer require elriseio/dbal-bundle

Register the bundle in config/bundles.php:

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

Working with DbalManagerFactory

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

Quick Creation of DbalManager

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

$dbalManager = $factory->createManager();

You can pass custom Connection and DbalBundleConfig:

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

Creating Individual Components

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

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

For each of these methods, you can specify your custom Connection and (optionally) DbalBundleConfig:

$bulkUpdater = $factory->createBulkUpdater($customConnection, $customConfig);

This is especially useful if you're working with multiple databases or want to use different configuration strategies.

Example of Using in a Service

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:

• Table name
• Array of rows to insert
• Automatic or manual ID generation
• Explicitly specifying the value type for each field

Usage Example

/** @var BulkInserterInterface $inserter */
$inserter->insert('user_table', [
    [
        'id' => IdStrategy::AUTO_INCREMENT, // The ID will be generated by the database.
        'email' => ['[email protected]', ParameterType::STRING],
        'created_at' => (new \DateTime())->format('Y-m-d H:i:s'),
    ],
    [
        'id' => IdStrategy::UUID, // The ID will be generated in the code.
        'email' => ['[email protected]', ParameterType::STRING],
        'created_at' => (new \DateTime())->format('Y-m-d H:i:s'),
    ],
    [
        // The ID will be generated in the code.
        'email' => ['[email protected]', ParameterType::STRING],
        'created_at' => (new \DateTime())->format('Y-m-d H:i:s'),
    ],
]);

The array ['value', ParameterType::TYPE] allows specifying the value type compatible with Doctrine\DBAL\ParameterType. If the type is not specified, it will be 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 specified — it is generated at the database level
IdStrategy::UUID	The value is generated in the code (UUID v7)
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 (UUID v7) for new code; existing IdStrategy::UID users do not need to migrate to upgrade to 2.0. Use IdStrategy::migrateFromV1() to plan an INT/STRING → 2.0 replacement.
IdStrategy::INT	The value is generated as a random integer
IdStrategy::STRING	A string is generated (e.g., based on uniqid())
IdStrategy::DEFAULT	The value should be used for working with Postgres and generating a DEFAULT ID within 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 Allows updating from 1 to multiple 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. The update is performed using CASE WHEN ... THEN ... without multiple queries. The number of affected rows is returned.

DbalBulkUpserter

DbalBulkUpserter Allows inserting or updating records based on key fields. If a record with the given id already exists, it will be updated; if not, a new record will be 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']);

The fields to be updated are passed as the third argument (replaceFields). The id can be generated automatically using 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 type-safe extraction of data from the database.

Usage Examples

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

// Get multiple rows with mapping to 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 ID.
$result = $finder->findByIdList($idList, 'api_history', ApiDto::class);

If no DTO class is specified, an array will be returned.

DbalMutator

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

Usage Examples

// Inserting a single 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 are supported (e.g., ['value', ParameterType::STRING]). If the type is not specified, it will be determined automatically.

⚠️ Важно

Before using the methods insert(), updateMany(), upsertMany(), it is essential to specify the current service fields either through the setFieldNames() method or a general configuration in the fieldNames field.

->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\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 four high-throughput concurrency helpers.

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) lock 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.

BulkTest Console Commands Setup

To use the test console commands related to bulk DBAL operations (insertMany, updateMany, upsertMany, deleteMany, softDeleteMany), add the following configuration to your 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

To run the commands, you can use a pre-prepared table from an SQL file:

// for MySQL
tests/_db/init.sql

// for PostgreSQL
tests/_db/init_postgres.sql

Manually run this SQL file in your test database before executing the commands.

---

Использование команд

bin/console dbal:test:run-all # Runs all the commands.
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

Each command supports:

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

Example:

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

---

Logging Results

If the --track flag is provided, the command will save performance logs to a CSV file:

var/log/<тип_теста>_<timestamp>.csv

Each line in the log contains:

• Iteration number
• Execution time
• Memory usage
• Memory change
• Cumulative time

Compatibility

• PHP 8.3+
• Symfony 7.2+
• Doctrine DBAL 4.2+ (verified on 4.4.x)
• MySQL 8.0+ (including 8.4 LTS)
• MariaDB 10.5+ (10.6, 10.10, 10.11, 11.0.7)
• PostgreSQL 12+ (test fixtures target 16; 17 not yet covered)
• CI: scripts/check_interface_namespace_imports.sh rejects legacy Enum\*Interface imports.

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.

Registered Doctrine Types

The bundle registers seven Doctrine Types on build() so consumers can declare columns via #[Column(type: '...')] without hand-rolling 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.