Has anyone used planetscale.com as MySQL database?
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.
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
@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?
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.
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.
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?
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.
error: alter table admin_permissions
foreign key (created_by_id
) references admin_users
) 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
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.