sql: support for Strapi content management system

[dikshant]

This is a meta issue to track support for Strapi. Strapi is an open source headless CMS (content management system). Strapi supports using PostgreSQL as the datastore. Strapi currently relies on:

• sql: move alter column type general support out of experimental #49329
• sql: support multi-dimensional arrays #32552

There has been some effort on Strapi's side to get CockroachDB to work, however it was abandoned due to the lack of those two features. Additional investigation will be needed to determine what else is missing.

Jira issue: CRDB-21161

[derrickmehaffy]

Hello from the Strapi team 👋

A community user of ours is currently working on a crdb implementation, we did understand the need for #49329 and it's something that also have identified as something that is needed for us to properly implement crdb. I'm not entirely sure why #32552 would be needed but was just wondering if you had a timeline for this.

Thanks and looking forward to having crdb in Strapi!

cc @cpaczek

[dikshant]

👋🏽 I believe #32552 is mentioned here because @adamspindler did some testing and found that 2-dimensional arrays were needed.
#49329 is something we are looking into, but we are not quite certain when it will land.
cc: @ajwerner as it relates to the declarative schema changer supporting alter column type (I think based on the board it was moved into)

[cpaczek]

#49329 is something we are looking into, but we are not quite certain when it will land.

What is the recommended way of altering a column type? Maybe we can figure out a workaround.

The current PR (strapi/strapi#12346) has cockroach DB working with the exception of migrating columns that change type.

We are using serial4 for the id fields using SET serial_normalization = "sql_sequence"; I saw that this could have performance impacts. Do you know by how much? Our constraint is we need IDs to be 32-bit so we are able to parse them in JS as JS can only parse up to 53-bit integers until you need to use a special class.

[ethan-gallant]

The Serial type in CRDB has always been best-effort to support standard postgres dialect. In general with multi master databases, serial primary columns will have a negative performance implication as all inserting nodes need to maintain a sync.

In CRDB you'll actually notice serial columns don't always increment one by one. They'll skip numbers at times because of this synchronization behavior to avoid conflicting entries into a table.

See here https://www.cockroachlabs.com/docs/stable/serial.html

Generally it's suggested to use 128bit UUIDv4 for the primary key as it removes any requirement for synchronization between CRDB nodes since it's guaranteed unique.

[derrickmehaffy]

In CRDB you'll actually notice serial columns don't always increment one by one. They'll skip numbers at times because of this synchronization behavior to avoid conflicting entries into a table.

Indeed this is exactly what MariaDB does as well with a key offset. I've never seen any performance benchmarks around this 🤔

While we (Strapi) do want to implement UUIDs, it's not feasible for us right now as it would be considered a massive breaking change but it is something we are considering for Strapi v5