Has anyone used planetscale.com?

mbrain-io · August 17, 2021, 4:27pm

Has anyone used planetscale.com as MySQL database?

aveprik · November 22, 2021, 8:16am

Just tried.
If you have anything that uses components in your models, you will not be able to use PlanetScale for your DB because they don’t support FOREIGN KEY constraint (and will not). But strapi generates sql schema that uses foreign key constraints with ON DELETE CASCADE.
So the error will be:
error Error: ER_QUERY_INTERRUPTED: foreign key constraints are not allowed, see Vitess | A database clustering system for horizontal scaling of MySQL

So yeah, PlanetScale sounds like a good managed DB option but no it doesn’t work with Strapi. Unless, of course, Strapi drop the use of foreign key constraints.

bflor · December 3, 2021, 8:07am

We’ve been using PlanetScale and Strapi for a few months without issue. Just to power an 11ty static site generation, so nothing heavy, but it’s been quite nice. Our dev environment is a Github codespace, so PlanetScale as the db made things really easy

We haven’t noticed any issues using components in the models, fwiw. I suppose if we run into issues with ER_QUERY_INTERRUPTED we’ll know what the issue is now, though

tbarn · December 7, 2021, 7:36pm

@bflor Interesting! We are giving Strapi a spin for the first time at PlanetScale too. Did you do a custom setup with your own schemas to avoid the foreign keys?

bflor · December 7, 2021, 8:42pm

We didn’t do anything special except use a non-main branch of the db (because Planetscale has branches and you can’t do schema modifications on the main branch). And we’ve just continued to use that. It’s not for a production site yet, but seems fine at this point. And probably will remain so for our use case (as backing an 11ty-generated site).

The only issue we’ve seen, which we thought was “normal” but maybe is related to Planetscale, is if certain fields are renamed (in particular, to the same name but different capitalization), Strapi can’t make the schema change it needs to be done manually in the db. Not sure if that’s related to Planetscale, but it’s not been a big problem.

tbarn · December 7, 2021, 9:52pm

The Strapi CLI way of creating a new project very quickly hits the error because it tried to make foreign keys in PlanetScale and couldn’t. So I am guessing you started out some other way? It did create some of the models though.

My teammate and I at PlanetScale were already talking about the same issue with branching since Strapi was likely going to try to do DDL statements on a production branch, which is not allowed by PlanetScale.

aveprik · December 23, 2021, 2:37am

This is interesting topic here

I wanted to give a PlanetScale a try but I am not willing to switch schema creation to a manual mode. And Strapi closed my request for dropping foreign key constraints saying that many devs asked for this feature so it won’t be dropped.
So yeah, on one side there is this great product that is PlanetScale which I would like to use and on the other hand Strapi, which I have already invested in heavily, that doesn’t work with the PlanetScale.
A bit of a bummer.

tbarn do you think there will be any solution any time soon that will allow PlanetScale💘 Strapi without too much of a maintenance/manual handling of schema?

forestcall · January 12, 2022, 8:51pm

I am curious why this is something enforced by Strapi development team?
I understand the primary ‘concern’. Vitess | A database clustering system for horizontal scaling of MySQL

But simply allowing for a --flag setting in install to circumvent this restriction would be more fitting. It really rubs me the wrong way. Our project is married to strapi but I wont ever use strapi again.

santyas · March 24, 2022, 10:12pm

Impossible

error: alter table admin_permissionsadd constraintadmin_permissions_created_by_id_fk foreign key (created_by_id) references admin_users (id) on delete SET NULL - ER_QUERY_INTERRUPTED: foreign key constraints are not allowed, see https://vitess.io/blog/2021-06-15-online-ddl-why-no-fk/

using develop branch credentials

danielehrhardt · July 31, 2022, 8:01pm

github.com/strapi/strapi

Remove foreign key constraints from generated DB schema to allow using PlanetScale DB with Strapi

opened 12:53AM - 03 Dec 21 UTC

closed 02:39PM - 03 Dec 21 UTC

benderillo

## Feature request ### Please describe your feature request - [x] **I have… created my request on the Product Board before I submitted this issue** - [x] **I have looked at all the other requests on the Product Board before I submitted this issue** ### Summary [PlanetScale](https://planetscale.com/) is this new amazing database-as-a-service offering from creators of [Vitess](https://vitess.io/) . Unfortunately, Strapi users cannot use this service because it does not allow foreign key constraints in the DB schema but Strapi creates the FOREIGN KEY constraints when generates the schema. They explain why foreign keys are not supported in this article: https://docs.planetscale.com/tutorials/operating-without-foreign-keys Strapi creates foreign key constraints when creates the db schema from the models, specifically, when creating components but there could be other cases too. This feature request is to update the code that generates the DB schema to remove the foreign key constraints from it. ### Why is it needed? To allow Strapi users to use PlanetScale and leverage solid, battle-tested, horizontally scaled MySQL clusters conveniently offered as a managed service. ### Suggested solution(s) Update schema generating code in Stripe to not to produce foreign key constraints. ### Related issue(s)/PR(s) None.

DMehaffy · August 1, 2022, 2:20am

Hey all,

Yes I did close and lock the issue as it’s not something we plan to support but I wanted to pop in here and give a little more reason why we can’t just add a flag or configuration option to disable this.

I’m sure as many of you understand, every configuration option we add we need to be able to support it, test it, and when things break we are inevitably responsible to fix it. By disallowing foreign key constraints it means we are saying that we support users running their databases in a slightly dangerous state (I’m sure that’s widely open to interpretation, but not something I wish to debate here). And while for most of the free and open source users much like yourselves this is no problem, there are many things that we state “do at your own risk” when it comes to Strapi itself but all of these options require customization.

The issue happens when you look at our enterprise customers and eventually our cloud, we build Strapi in such a way that many huge companies are using it for mission critical applications and we need to ensure that Strapi it safe, stable, and secure and this requires us to make hard choices on what we can and cannot implement. We as a team made the decision to properly implement foreign key constraints as part of Strapi v4 and this was an engineering decision that we are not currently willing to rollback to support a small niche (there are many other hybrid/horizontally scaling database options such as cockroachDB that a community user is currently working on implementing: https://github.com/strapi/strapi/pull/12346) or much better clustering options such as MariaDB’s Galera clusters (which support multi-masters as well!).

At the end of the day your project is yours, the code is yours, and the benefit to Strapi is you can customize it all the way down to the core level using patch-package so if you wish to remove FQs on your own project you have that freedom, if you want to fork Strapi’s database package and resolve it differently you have that freedom, or if you wanted to fork Strapi in it’s entirety you have that freedom as well.

We have to make decisions for the bulk of our users and there is some cases where we simply have to say “no, we can’t do this” for various reasons. In this specific case it’s pretty much 90% maintenance overhead that we are simply not willing to take on. Exactly the same reason we don’t have Microsoft’s SQL Server or OracleDB as database options in Strapi even though Knex supports them.

manhhoach · October 16, 2022, 9:05am

I can not create foreign key
planetscale damn

FrederikNordahl · January 3, 2024, 2:30pm

People who are finding this thread - planetscale has added foreign key contraints as a beta feature:Announcing foreign key constraints support