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 with

'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,
],

Recently some of our tests started failing for MySQL and Postgres on CI, but passed in SQLite and MariaDB, which is what most of us use locally. 

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. 

How does that translate to different engines?

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

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

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:

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.

That's exactly what our problem was.

For MariaDB, the JSON type is just an alias. See 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.

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.

How did we fix this?

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

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

Translating Drupal Canvas

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!