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 ORMs:
  • MySQL 8

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 alexk136/dbal-bundle

Register the bundle in config/bundles.php:

Elrise\Bundle\DbalBundle\ElriseDbalBundle::class::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' => ['user1@example.com', 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' => ['user2@example.com', ParameterType::STRING],
        'created_at' => (new \DateTime())->format('Y-m-d H:i:s'),
    ],
    [
        // The ID will be generated in the code.
        'email' => ['user3@example.com', 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	The value is generated in the code (18 characters)
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

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.

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',
])

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.2+
• Symfony 7.0+
• Doctrine DBAL 3.6+
• MySQL 5.7 / 8 / PostgreSQL 16