Internet News

Penyaskito: Canvas Internals – JSON data types in differentes databases: It works on my machine!

Drupal has been working to add a JSON data type since 2023, but that has not landed yet. Drupal Canvas jumps ahead of that in its inputs for a component tree item withTo make lookups more efficient, MySQL also sorts the keys of a JSON object. You should be aware that the result of this ordering is subject to change and not guaranteed to be consistent across releases.
This is just one of the many show-stoppers that we faced while working on the much-anticipated symmetric translation support for Drupal Canvas. If you want to test this experimental feature, check the release notes in Canvas 1.7.0, but please only on test sites for now!

How does that translate to different engines?

The actual sorting of the inputs in the database is, as of today, irrelevant to us. So we ended up with:

For MySQL, there’s a native data type. Quoting their docs:

JSON is an alias for LONGTEXT COLLATE utf8mb4_bin introduced for compatibility reasons with MySQL’s JSON data type. MariaDB implements this as a LONGTEXT rather, as the JSON data type contradicts the SQL:2016 standard, and MariaDB’s benchmarks indicate that performance is at least equivalent.

The problem was that the sorting of the keys of that field was not deterministic, and we used assertSame in our tests to see if operations added/removed the inputs as expected when components evolved.

For MariaDB, the JSON type is just an alias. See their docs:
And the last one, SQLite, has support for a jsonb format since 3.45, but the work in progress for introducing this in Core uses json, which, like MariaDB, is ordinary text and sorting of the keys is respected.

In general, most applications should prefer to store JSON data as jsonb, unless there are quite specialized needs, such as legacy assumptions about ordering of object keys.

'inputs' => [ 'description' => 'The input for this component instance in the component tree.', 'type' => 'json', 'pgsql_type' => 'jsonb', 'mysql_type' => 'json', 'sqlite_type' => 'json', 'not null' => FALSE, ],

For PostgreSQL, the engine offers two different data types: json and jsonb, with the second being the option we (and core) opted for because of its efficiency. But that’s key, as the docs explain:

Our own assertSameInputs, which sorts the keys before comparison. assertEqualsCanonicalizing is not an option, as that sorts by value.
Our own PHPStan rule, which is not 100% accurate but detects most usages of assertSame with these inputs, and suggests using assertSameInputs instead.