New Release: Doctrine DBAL 3.2.0

Posted on November 26, 2021 by Sergei Morozov

We are happy to announce the immediate availability of Doctrine DBAL 3.2.0. As most of the minor releases, this one focuses on new features, improvements and deprecations of the old APIs. Here are some details on the most significant features and improvements:

Platform-aware schema comparison (#4746)

Up until this release, the logic of comparing database schemas had a major design flaw: it took into account only the abstract schema definitions without taking the target platform into account.

This flaw would lead to multiple issues which shared the same root cause: the two definitions could be considered different by the DBAL, but they would produce the same DDL.

For instance, consider the two column definitions:

// old schema
$column1 = new Column('contents', Type::getType('text'));

// new schema
$column2 = new Column('contents', Type::getType('text'), ['default' => 'Hello, world!']);

If we compared them with the comparator, we'd get a diff:

$comparator = new Comparator();
$comparator->diffColumn($column1, $column2);
// array(1) {
//   [0] =>
//   string(7) "default"
// }

This might be valid for the platforms that support the DEFAULT constraint on TEXT columns but isn't valid for those that don't support it (e.g. MySQL). Regardless of the diff, both definitions would produce the same DDL on MySQL:


An attempt to migrate the old schema to the new one would produce a false-positive diff but applying it wouldn't result in any schema changes.

A false-negative diff was also possible. Consider these following example:

// old schema
$column1 = new Column('settings', Type::getType('json'));

// new schema
$column2 = new Column('settings', Type::getType('json'), ['length' => 16777215]);

Comparison of the above column definitions should have triggered a diff on MySQL and migrate the underlying column from TEXT to MEDIUMTEXT but it didn't, because the DBAL would ignore the length of the TEXT columns.

Apart from that, the DBAL would compare only a subset of the definitions, so some column options as the character set and collation weren't taken into account during comparison at all.

The new approach

Instead of comparing abstract definitions on a per-property basis, the new implementation compares the DDL that is generated from both definitions for the target database platform. If the definitions produce the same DDL, they are considered equal. According to the tests and the number of resolved issues, this approach should be more accurate and less error-prone.

Implementing this approach was impossible without introducing a new API which rendered the existing API obsolete.

Prior to DBAL 3.2.0, the schema comparator could be only instantiated directly via the new keyword:

$comparator = new Comparator();

Instantiated like this, the comparator doesn't have a notion of the target database platform and cannot perform the comparison properly. That is why, this way of instantiation is deprecated in favor of instantiating the comparator by the schema manager:

$schemaManager = $connection->createSchemaManager();
$comparator = $schemaManager->createComparator();

This way, the schema manager can instantiate a platform-specific comparator and provide it with the necessary context (e.g. the default collation used by the database).

While the old API is still available, it is recommended to use the new API for more accurate comparison.

Support for psr/cache (#4620)

Since the Doctrine Cache library is being sunset, the new DBAL release introduced the ability to use a PSR-6 compatible implementation for result caching.

While both the doctrine/cache and psr/cache APIs will be supported until the next major DBAL release, we recommend users to switch to a PSR-6 compatible implementation in their projects.

Support for psr/log (#4967)

The SQLLogger interface was designed long ago and has certain limitations: there is no way to log messages at different logger levels and it is really challenging to extend the logger functionality without introducing breaking API changes.

The new DBAL release introduces a new middleware that can delegate logging to a PSR-3 compatible implementation.

Note that the new logger won't produce the messages identical to the ones produced by the old one. If you have any processes built around analysing log messages, you may need to make some changes before adopting the new API.

Always cache the full result (#5003)

The implementation of the result cache prior to DBAL 3.2.0 would store the result set in the cache only once it was fetched completely. It led to the following issues:

  1. If the result isn't yet cached and its consumer didn't fetch it completely, the query would be executed again.
  2. In case of a cache miss, the DBAL would get() the cache entry twice: once to fetch the data and once to merge the just fetched result with other results that may be stored in the cache.

The new implementation stores the results in the cache right after they were fetched. It simplifies the caching layer significantly and makes its behavior more straightforward.

Add events for Transaction begin/commit/rollback (#4622)

The new DBAL version introduces three more transaction-related events:

  • onTransactionBegin,
  • onTransactionCommit,
  • onTransactionRollBack.

Subscribing to those might be helpful if the application logic integrates the database transaction flow with the business logic implemented outside the database. For instance, in the filesystem.

Basic exception handling in IBM DB2 and SQL Server drivers (#4929, #4928)

The DBAL provides a mechanism that converts driver-specific error codes to portable error-specific exceptions. For instance an attempt to insert NULL into a column that has a NOT NULL constraint applied will result in error with the code 1566 on MySQL and in ORA-01400 on Oracle. The DBAL will convert these two errors to a portable NotNullConstraintViolationException.

Historically, the DBAL drivers based on the ibm_db2, sqlsrv and pdo_sqlsrv extensions did not support this feature and would thow a generic DriverException.

As of DBAL 3.2.0, this feature is supported by all bundled drivers.

Improved AbstractPlatform::getLengthExpression() (#4855)

Although the LENGTH expression was implemented for all supported database platforms, the different implementations didn't have consistent semantics:

  1. Most implementations would return the length in characters (Unicode code points), which is the most expected behavior.
  2. The implementations for MySQL and IBM DB2 would return the number of bytes. It worked fine for the strings that consisted only of the ANSI characters, but an attempt to use it with a wider range of characters would produce an unexpected result. For instance, the length of the string 'Привет, мир!' might be reported as 19 instead of 12.

As of DBAL 3.2.0, all platforms return the length in Unicode points according to the character set used by the database connection. Note, SQL Server supports UTF-8 only as of SQL Server 2019.

You can find more details in the release notes.