Download the PHP package elriseio/dbal-bundle without Composer

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)

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)

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:

Register the bundle in config/bundles.php:

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:

You can pass custom Connection and DbalBundleConfig:

Creating Individual Components

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

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

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

Example of Using in a Service

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

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:

DbalBulkUpdater

DbalBulkUpdater Allows updating from 1 to multiple rows in the database.

📌 Example

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

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.

DbalFinder

DbalFinder Provides methods for type-safe extraction of data from the database.

Usage Examples

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

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.

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:

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:

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

Wire a logger via services.yaml:

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.

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.

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:

Test Table

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

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

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

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:

Logging Results

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

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.