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);
