Support for jsonb indexable columns

[borutjures]

Problem to Solve

It would be great if data for a complex type, such as another model, a List, or a Map, would be indexable at the properties level.

Currently these types are stored as a json object in the database:

• https://docs.serverpod.dev/concepts/database/models#data-representation

• serverpod/packages/serverpod/lib/src/database/adapters/postgres/database_connection.dart

  Line 740 in a638ce4

  if (column is ColumnSerializable) return 'json';

Proposal

There is a jsonb type in PostgreSQL. jsonb supports indexing, which can be a significant advantage (https://www.postgresql.org/docs/current/datatype-json.html).

It would be great if jsonb was used instead of json, but it probably cannot be changed at this stage (it might be a breaking change for a small percentage of the existing data).

Is it possible to add another ColumnSerializableX column type that would use jsonb in the database?

Or could the used database type for ColumnSerializable be configurable per project (json or jsonb)?

Use Case

The Address class has state and city properties and we would like to index these in the database.

class: Company
table: company
fields:
  address: Address # This would be possible if stored as a `jsonb` instead of `json` column

Alternatives

Only ugly ones like extracting state and city properties as separate fields from the Address class.

class: Company
table: company
fields:
  address: Address # Stored as a json column
  address_state: String  # Duplicate of `Address.state`
  address_city: String  # Duplicate of `Address.city`

Additional context

No response

How experienced are you with this library?

Expert - Experienced and comfortable with using this library in complex projects

Are you interested in working on a PR for this?

• I want to work on this

[SandPod]

Hello @borutjures 👋

Thanks for your feature suggestion.

We would like to improve this. As you mention, this would be a breaking change for existing projects, but we could make it configurable.

Ideally, this configuration would be good to apply to:

• Project level - Switching the type for all JSON objects in the project.
• Model level - Switching the type for all JSON objects in a model.
• Field level - Switching the type for a specific field in a model.

By making this configurable on these three levels, we would enable migration for users to JSONB (which is the preferred way to store JSON).

[borutjures]

I have a first working version with model level configuration. I used customSerialized as a keyword:

class: Greeting
table: greeting
customSerialized: jsonb       <= use jsonb instead of json as default for this table
fields:
  message: String
  tags: List<String>          <= jsonb will be used instead of json
  timestamp: DateTime

Next is field level configuration. I'm thinking of using a serialize keyword to distinguish it from the serialized keyword used for enums:

class: Greeting
table: greeting
fields:
  message: String
  author: Author, serialize=jsonb        <= use jsonb instead of json for this field only
  tags: List<String>                     <= use default json for this field

Please advise if different keywords should be used.

[SandPod]

Are you actively working on this @borutjures?

If so, should I assign it to you?

Implementation plan

We would recommend that you implement this in three PRs. That will improve the review speed of the change as well.

Focus first on the Project level; this will allow you to resolve the database layer fixes in isolation.
Then implement the model-level configuration, and lastly implement the field-level configuration.

This is a candidate for a new default value for Serverpod 3.0.

[borutjures]

@SandPod Yes, I'm actively working on this. You can assign it to me.

I already have a working solution for all three levels. I'm currently writing the tests.

Project-level configuration would probably be a good candidate for a separate PR.
However, model-level and field-level configurations should be kept in one PR to make sure that the model-level configuration works well if one model field doesn’t specify the serialize option while another field in the same model does specify it. Example:

class: Greeting
table: greeting
serialize: jsonb
fields:
  author: Author
  tags: List<String>, serialize=json

I’ll push a single PR first and break it into separate PRs in case a quick review shows that separate PRs would be better.

[SandPod]

Awesome! 🚀

However, model-level and field-level configurations should be kept in one PR to make sure that the model-level configuration works well if one model field doesn’t specify the serialize option while another field in the same model does specify it.

I still believe this could all be solved in the last field level configuration PR, since these are all field level concerns.

I’ll push a single PR first and break it into separate PRs in case a quick review shows that separate PRs would be better.

OK, breaking it apart will allow us to review and merge parts of the solution faster without getting stuck in unrelated discussions. But I'm fine with a first bigger PR attempt! 👍

[SandPod]

Additional note: Remember that the migration system needs to handle this as well, either by dropping the table or column when the value is switched. This will give the user a warning if they accidentally change this.

[borutjures]

Thank you for the heads-up about the migrations. While adding the tests I noticed a possible discrepancy in the tests for migrating integer to bigint: #3933