Hi @Dominik Yesterday I tried to increase the render database plan (Bigger Plan for 20$/month). It did not change anything.
IMHO this is definitely NOT an issue with server resources but with render infrastructure itself. I am experiencing different issues there. The one with broken deployments, then with random connection loses to database (even with the bigger payed plan one) after 10 minutes of idle or something.
I created a setup now on digitalocean. Was a smooth experience and (so far!) I am not experiencing any of those connection losses…
Heroku same issue. Happend after i added a new field to a content type. Any change to the shcema of any content type ends up in knex error. That is a huge issue in general. Adding data to existing schema works all the time. Changing the schema creates the problem. Anyone has any idea of how to fix that?
Hmm that’s looks exactly like the problem I am experiencing! Unfortunately, I am still on v3 so I hope I can port over the fix once it gets finally merged…
Oh wow… I thought this problem only started manifesting with v.4 and v.3 used a different connector than Knex, so not that many reported the issue yet…
So guys here is some update from my deployment setup.
I had huge issues on render.com with my setup. I got the knex issue, after some idle time the server lost connection to the database and needed to reboot, … So I increased database and node ressources to very high specs. Without any improvement.
So I switched to digitalocean app platform to check if this an issue of render. (Setup is as easy as with render)
… Well, yes, it is an issue with render… (At least for me)
I am using the following setup now on digitalocean:
Web service: $20.00/mo – Basic 2 GB RAM | 1 vCPU x 1
Dev Database: 7$/mo 512 MB RAM, Shared CPU, 1 GB Disk
Even with this “low specs” (I bought a lot bigger package on render) I am not facing any of the above mentioned issues.
Changing database models → no knex issues
No idle connection issues
I have no idea what the issue is at render.com.
But I wasted a lot of time trying to fix the render hosting with pool settings and stuff and it only caused frustration.
TLDR: If anyone has the same issues i highly recommend to benefit of my painful experience, skip render and just use digitalocean (or some other hosting provider)
I have had similar issues with Railway and even fly.io. I was really hoping Fly would work because it’s like having Strapi on the edge. Only Digital Ocean seems to work well for me.
It happens when there is no activity for a while. If I make a call to the API from my front end every hour or so, it never happens.
Also, I did try connecting to the Digital Ocean postgres database from my fly deployment and it still had problems.
When Strapi is deployed to Digital Ocean, I can use almost any database service and have no issues.
Maybe I will try using PlanetScale for my database (MySQL).
I really want this to work on Fly. My experience with it has been great so far, besides this.
I was facing the same issue on postgres when I was adding new component or changing any content types. I have created a for @strapi/database package and now everything is working fine for me. This patch is inspired by the PR posted in earlier comment.
diff --git a/node_modules/@strapi/database/lib/dialects/postgresql/schema-inspector.js b/node_modules/@strapi/database/lib/dialects/postgresql/schema-inspector.js
index 7e13a56..e6b778c 100644
--- a/node_modules/@strapi/database/lib/dialects/postgresql/schema-inspector.js
+++ b/node_modules/@strapi/database/lib/dialects/postgresql/schema-inspector.js
@@ -40,28 +40,32 @@ const SQL_QUERIES = {
`,
FOREIGN_KEY_LIST: /* sql */ `
SELECT
- tco."constraint_name" as constraint_name,
+ tco."constraint_name" as constraint_name
+ FROM information_schema.table_constraints tco
+ WHERE
+ tco.constraint_type = 'FOREIGN KEY'
+ AND tco.constraint_schema = ?
+ AND tco.table_name = ?
+ `,
+ FOREIGN_KEY_REFERENCES: /* sql */ `
+ SELECT
+ kcu."constraint_name" as constraint_name,
kcu."column_name" as column_name,
rel_kcu."table_name" as foreign_table,
rel_kcu."column_name" as fk_column_name,
rco.update_rule as on_update,
rco.delete_rule as on_delete
- FROM information_schema.table_constraints tco
- JOIN information_schema.key_column_usage kcu
- ON tco.constraint_schema = kcu.constraint_schema
- AND tco.constraint_name = kcu.constraint_name
+ FROM information_schema.key_column_usage kcu
JOIN information_schema.referential_constraints rco
- ON tco.constraint_schema = rco.constraint_schema
- AND tco.constraint_name = rco.constraint_name
+ ON kcu.constraint_schema = rco.constraint_schema
+ AND kcu.constraint_name = rco.constraint_name
JOIN information_schema.key_column_usage rel_kcu
ON rco.unique_constraint_schema = rel_kcu.constraint_schema
AND rco.unique_constraint_name = rel_kcu.constraint_name
AND kcu.ordinal_position = rel_kcu.ordinal_position
- WHERE
- tco.constraint_type = 'FOREIGN KEY'
- AND tco.constraint_schema = ?
- AND tco.table_name = ?
- ORDER BY kcu.table_schema, kcu.table_name, kcu.ordinal_position, kcu.constraint_name;
+ WHERE kcu.constraint_name=ANY(?)
+ AND kcu.table_schema = ?
+ AND kcu.table_name = ?;
`,
};
@@ -210,19 +214,30 @@ class PostgresqlSchemaInspector {
const ret = {};
for (const fk of rows) {
- if (!ret[fk.constraint_name]) {
- ret[fk.constraint_name] = {
- name: fk.constraint_name,
- columns: [fk.column_name],
- referencedColumns: [fk.fk_column_name],
- referencedTable: fk.foreign_table,
- onUpdate: fk.on_update.toUpperCase(),
- onDelete: fk.on_delete.toUpperCase(),
- };
- } else {
- ret[fk.constraint_name].columns.push(fk.column_name);
- ret[fk.constraint_name].referencedColumns.push(fk.fk_column_name);
+ ret[fk.constraint_name] = {
+ name: fk.constraint_name,
+ columns: [],
+ referencedColumns: [],
+ referencedTable: null,
+ onUpdate: null,
+ onDelete: null,
+ };
+ }
+ const contraintNames = Object.keys(ret);
+ const dbSchema = this.getDatabaseSchema();
+ if (contraintNames.length > 0) {
+ const {rows:fkReferences} = await this.db.connection.raw(SQL_QUERIES.FOREIGN_KEY_REFERENCES,[
+ [contraintNames], dbSchema,tableName]);
+
+ for (const fkReference of fkReferences) {
+ ret[fkReference.constraint_name].referencedTable = fkReference.foreign_table;
+ ret[fkReference.constraint_name].columns.push(fkReference.column_name);
+ ret[fkReference.constraint_name].referencedColumns.push(fkReference.fk_column_name);
+ ret[fkReference.constraint_name].onUpdate = fkReference.on_update.toUpperCase();
+ ret[fkReference.constraint_name].onDelete=fkReference.on_delete.toUpperCase();
+
}
+
}
return Object.values(ret);
