JSON Columns and Doctrine DBAL 3 Upgrade

Version 3 of the popular Doctrine DBAL library has been released at the end of 2020, almost 10 years after the initial release of version 2. In September 2021, Doctrine ORM 2.10 added support for DBAL 3, while still supporting DBAL 2 at the same time. Versions prior to 2.10 aren’t compatible with DBAL 3.

The upgrade process from DBAL 2 to DBAL 3 is usually straightforward. Most breaking changes impact low-level classes, that aren’t commonly directly manipulated when using the ORM. However, things become more complex if you rely on JSON columns, and you may know that I’m a big user of them!

Doctrine DBAL has native support for JSON columns. DBAL provides a native type allowing storing PHP arrays as JSON arrays in JSON columns (if supported by the DBMS, otherwise a text column is used) and retrieving them later as PHP arrays. In DBAL 2.6 this type has been renamed from json_array to json. In DBAL 3, the deprecated json_array type has been entirely removed.

A Quick Workaround

If doctrine/dbal isn’t explicitly listed in the require section of the composer.json file of your project, upgrading to Doctrine ORM 2.10 causes errors about the missing json_array type.

It’s intended. This type has been removed from DBAL 3, and this transient dependency installed because of Doctrine ORM has been transparently upgraded. Fixing the problem is simple as explicitly fobidding to Composer to install DBAL 3+: composer require 'doctrine/dbal:^2'

But upgrading from json_array to json should be straightforward right? find src/ -type f | xargs sed -i 's/type: "json_array"/type: "json"/g' (or something similar) and done!

Unfortunately, this time that’s not that simple…

The Subtle Difference Between json and json_array Types

As its name indicate, the historical json_array type supports only arrays of PHP scalar values. Even if the new json type was documented as being an alias of json_array, that’s not what the code does! The new type doesn’t only support arrays, it supports any JSON document that can be emited by the json_encode() PHP function. This means that JSON database columns can now contain any JSON data types: objects, strings, numbers, and booleans. This also means that the related property of the object can now contain any PHP type when using the json type, but only arrays when using the old json_array type:

<?php

use Doctrine\ORM\Mapping\{Column, Entity};

#[Entity]
class Example
{
    #[Column(type: 'json_array')
    public /* array */ $old;

    #[Column(type: 'json')
    public /* mixed */ $new;
}

This PHP type change is an issue when using the Symfony PropertyInfo Component, which is a dependency of various popular tools including API Platform and the Symfony Serializer Component. PropertyInfo uses Doctrine’s metadata to guess the type of a PHP property… When upgrading DBAL, the guessed type changes. For instance, when using API Platform, the guessed type is used to generate the OpenAPI and Hydra documentations, consequently, when upgrading DBAL, the API documentation changes… which is unexpected and most likely not what you want.

I updated DBAL’s documentation to reflect this change. So, you need to update your code to make it clear that the property can only contain an array. Adding an explicit PHP type does the trick, and is a good practice anyway:

<?php

use Doctrine\ORM\Mapping\{Column, Entity};

#[Entity]
class Example
{
    #[Column(type: 'json')
    public array $new1; // Explicit type

    /** @var array */ // PHPDoc is also supported
    #[Column(type: 'json')
    public $new2;
}

If the property contains user generated data, be sure to validate the data properly before trying assigning it!

The Column Definition Must Be Updated Too

If you generate a new database schema, the changes above work as expected, but if you try to connect to an existing schema, you get this annoying error: Unknown column type "json_array" requested. Any Doctrine type that you use has to be registered with \Doctrine\DBAL\Types\Type::addType().

That’s because the Doctrine type to use is hardcoded in a column comment. For your code using the json type to work, you also must remove this old comment from the column definition. The easiest way to do that is using Doctrine Migrations.

First, be sure to use a recent version of Doctrine DBAL 2, to have both the new json and the deprecated json_array types. Then upgrade your code. Then generate and execute the database migration:

$ bin/console doctrine:migrations:diff
$ bin/console doctrine:migrations:migrate

Alternatively, remove the comment associated with your JSON column manually. Example with Postgres:

COMMENT ON COLUMN my_table.my_json_column IS NULL;

You can finally update to DBAL 3: composer require 'doctrine/dbal:^3'

Summary

To upgrade your JSON columns from Doctrine DBAL 2 to DBAL 3:

1. Downgrade to DBAL 2
2. Change the DBAL types in your column definitions from json_array to json
3. Add the array PHP type hint to the properties associated with the JSON columns
4. Remove the comment associated with the JSON columns, if possible using a migration
5. Upgrade to DBAL 3

If you want to learn Doctrine in a practical way, you may be interested in my book Persistence in PHP with the Doctrine ORM. If you like this kind of contributions, consider sponsoring me on GitHub!