user@elrise.io:~/projects/$ ← elrise.io
· [archived] tags: phpsymfonydoctrinedbalbundle

dbal-manager — DBAL manager for Symfony highload projects

A Symfony bundle for highload systems where Doctrine ORM is a bottleneck: bulk insert/update/upsert, cursor-based iterators, direct DTO mapping, full SQL control at the DBAL level.

→ repository

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

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

$dbalManager = $factory->createManager();

Custom Connection and DbalBundleConfig are supported:

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

Creating Individual Components

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

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, // generated by the database
        'email' => ['user1@example.com', ParameterType::STRING],
        'created_at' => (new \DateTime())->format('Y-m-d H:i:s'),
    ],
    [
        'id' => IdStrategy::UUID, // generated in code
        'email' => ['user2@example.com', ParameterType::STRING],
        'created_at' => (new \DateTime())->format('Y-m-d H:i:s'),
    ],
    [
        // id will be generated in 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)

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.

$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.

$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.

// 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);
$result = $finder->findByIdList($idList, 'api_history', ApiDto::class);

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

DbalMutator

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

$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.

⚠️ Important

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.

Using 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/<test_type>_<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

Get it

github.com/alexk136/dbal-manager — open source, MIT.