Problems integrating ForestAdmin with Google Cloud (appEngine and Postgres SQL)

Hello,

I installed a Postgres SQL database on Google Cloud SQL and I intend to access it from ForestAdmin.

Following the instructions from the ForestAdmin guide, I could see the database content on ForestAdmin in development environment.

The ForestAdmin backend was running locally.

My next step was to install the ForestAdmin backend on Google AppEngine, to have an automate solution. I continued following the guide, including the adjustments provided by the environment section of ForestAdmin documentation.

During the setup of the new enviroment on ForestAdmin system I could get the information to be updated in the backend.

I replaced the environmental information and ran again gcloud app deploy. And continued the operations on ForestAdmin system.

On the new environment, I cannot see the data. Here is the screen that I have after a page reload:

And after some time, I have this image:

Below we have the content of my configuration file in admin backend deployed in Google Cloud:

runtime: nodejs12
env_variables:
   FOREST_ENV_SECRET: 'xxxx'
   FOREST_AUTH_SECRET: 'yyyy'
   NODE_ENV: 'production'
   DATABASE_URL: 'postgres://xxxxxx'
   APPLICATION_URL: 'xxxxxx'

Here the configuration screen in ForestAdmin project:

And, finally, the log messages from Google Cloud for the app:

2021-07-30 11:50:11 default[20210729t185736]  "GET /forest/installationInstallation?timezone=America%2FSao_Paulo&fields%5BinstallationInstallation%5D=id%2CinstallationDate%2Cmanufacturer%2CqcResult%2CserialNumber&fields%5Bmanufacturer%5D=name&page%5Bnumber%5D=1&page%5Bsize%5D=15&searchExtended=0&sort=-id HTTP/1.1" 500
2021-07-30 11:50:12 default[20210729t185736]  "GET /forest/installationInstallation/count?fields%5BinstallationInstallation%5D=id%2CinstallationDate%2Cmanufacturer%2CqcResult%2CserialNumber&fields%5Bmanufacturer%5D=name&searchExtended=0&timezone=America%2FSao_Paulo HTTP/1.1" 500
2021-07-30 11:52:19 default[20210729t185736]  [forest] 🌳🌳🌳  Unexpected error: connect ETIMEDOUT 34.72.218.162:5432
2021-07-30 11:52:19 default[20210729t185736]  {
2021-07-30 11:52:19 default[20210729t185736]    "name": "SequelizeConnectionError",
2021-07-30 11:52:19 default[20210729t185736]    "parent": {
2021-07-30 11:52:19 default[20210729t185736]      "errno": "ETIMEDOUT",
2021-07-30 11:52:19 default[20210729t185736]      "code": "ETIMEDOUT",
2021-07-30 11:52:19 default[20210729t185736]      "syscall": "connect",
2021-07-30 11:52:19 default[20210729t185736]      "address": "34.72.218.162",
2021-07-30 11:52:19 default[20210729t185736]      "port": 5432
2021-07-30 11:52:19 default[20210729t185736]    },
2021-07-30 11:52:19 default[20210729t185736]    "original": {
2021-07-30 11:52:19 default[20210729t185736]      "errno": "ETIMEDOUT",
2021-07-30 11:52:19 default[20210729t185736]      "code": "ETIMEDOUT",
2021-07-30 11:52:19 default[20210729t185736]      "syscall": "connect",
2021-07-30 11:52:19 default[20210729t185736]      "address": "34.72.218.162",
2021-07-30 11:52:19 default[20210729t185736]      "port": 5432
2021-07-30 11:52:19 default[20210729t185736]    },
2021-07-30 11:52:19 default[20210729t185736]    "stack": "SequelizeConnectionError: connect ETIMEDOUT 34.72.218.162:5432\n    at /workspace/node_modules/sequelize/lib/dialects/postgres/connection-manager.js:182:24\n    at Connection.connectingErrorHandler (/workspace/node_modules/pg/lib/client.js:213:14)\n    at Connection.emit (events.js:314:20)\n    at Socket.reportStreamError (/workspace/node_modules/pg/lib/connection.js:57:10)\n    at Socket.emit (events.js:314:20)\n    at emitErrorNT (internal/streams/destroy.js:92:8)\n    at emitErrorAndCloseNT (internal/streams/destroy.js:60:3)\n    at processTicksAndRejections (internal/process/task_queues.js:84:21)"
2021-07-30 11:52:19 default[20210729t185736]  }
2021-07-30 11:52:19 default[20210729t185736]  [forest] 🌳🌳🌳  Unexpected error: connect ETIMEDOUT 34.72.218.162:5432
2021-07-30 11:52:19 default[20210729t185736]  {
2021-07-30 11:52:19 default[20210729t185736]    "name": "SequelizeConnectionError",
2021-07-30 11:52:19 default[20210729t185736]    "parent": {
2021-07-30 11:52:19 default[20210729t185736]      "errno": "ETIMEDOUT",
2021-07-30 11:52:19 default[20210729t185736]      "code": "ETIMEDOUT",
2021-07-30 11:52:19 default[20210729t185736]      "syscall": "connect",
2021-07-30 11:52:19 default[20210729t185736]      "address": "34.72.218.162",
2021-07-30 11:52:19 default[20210729t185736]      "port": 5432
2021-07-30 11:52:19 default[20210729t185736]    },
2021-07-30 11:52:19 default[20210729t185736]    "original": {
2021-07-30 11:52:19 default[20210729t185736]      "errno": "ETIMEDOUT",
2021-07-30 11:52:19 default[20210729t185736]      "code": "ETIMEDOUT",
2021-07-30 11:52:19 default[20210729t185736]      "syscall": "connect",
2021-07-30 11:52:19 default[20210729t185736]      "address": "34.72.218.162",
2021-07-30 11:52:19 default[20210729t185736]      "port": 5432
2021-07-30 11:52:19 default[20210729t185736]    },
2021-07-30 11:52:19 default[20210729t185736]    "stack": "SequelizeConnectionError: connect ETIMEDOUT 34.72.218.162:5432\n    at /workspace/node_modules/sequelize/lib/dialects/postgres/connection-manager.js:182:24\n    at Connection.connectingErrorHandler (/workspace/node_modules/pg/lib/client.js:213:14)\n    at Connection.emit (events.js:314:20)\n    at Socket.reportStreamError (/workspace/node_modules/pg/lib/connection.js:57:10)\n    at Socket.emit (events.js:314:20)\n    at emitErrorNT (internal/streams/destroy.js:92:8)\n    at emitErrorAndCloseNT (internal/streams/destroy.js:60:3)\n    at processTicksAndRejections (internal/process/task_queues.js:84:21)"
2021-07-30 11:52:19 default[20210729t185736]  }
2021-07-30 11:52:19 default[20210729t185736]  GET /forest/installationInstallation/count?fields%5BinstallationInstallation%5D=id%2CinstallationDate%2Cmanufacturer%2CqcResult%2CserialNumber&fields%5Bmanufacturer%5D=name&searchExtended=0&timezone=America%2FSao_Paulo 500 109 - 126782.337 ms
2021-07-30 11:52:19 default[20210729t185736]  GET /forest/installationInstallation?timezone=America%2FSao_Paulo&fields%5BinstallationInstallation%5D=id%2CinstallationDate%2Cmanufacturer%2CqcResult%2CserialNumber&fields%5Bmanufacturer%5D=name&page%5Bnumber%5D=1&page%5Bsize%5D=15&searchExtended=0&sort=-id 500 109 - 127775.669 ms
2021-07-30 11:53:28 default[20210729t185736]  "POST /forest/authentication HTTP/1.1" 200
2021-07-30 11:53:28 default[20210729t185736]  POST /forest/authentication 200 1094 - 2.231 ms
2021-07-30 11:53:28 default[20210729t185736]  "OPTIONS /forest/authentication/callback?code=hHGqDLtMWJ1C5AQ1HmgPmwrZtxZ-E3RKCqssRu8e2V2eU2dwUzljxMYzSKXyDxp7&state=%7B%22renderingId%22%3A97429%7D HTTP/1.1" 204
2021-07-30 11:53:28 default[20210729t185736]  OPTIONS /forest/authentication/callback?code=hHGqDLtMWJ1C5AQ1HmgPmwrZtxZ-E3RKCqssRu8e2V2eU2dwUzljxMYzSKXyDxp7&state=%7B%22renderingId%22%3A97429%7D 204 0 - 0.211 ms
2021-07-30 11:53:29 default[20210729t185736]  "GET /forest/authentication/callback?code=hHGqDLtMWJ1C5AQ1HmgPmwrZtxZ-E3RKCqssRu8e2V2eU2dwUzljxMYzSKXyDxp7&state=%7B%22renderingId%22%3A97429%7D HTTP/1.1" 200
2021-07-30 11:53:29 default[20210729t185736]  GET /forest/authentication/callback?code=hHGqDLtMWJ1C5AQ1HmgPmwrZtxZ-E3RKCqssRu8e2V2eU2dwUzljxMYzSKXyDxp7&state=%7B%22renderingId%22%3A97429%7D 200 519 - 717.265 ms
2021-07-30 11:53:40 default[20210729t185736]  "POST /forest/authentication HTTP/1.1" 200
2021-07-30 11:53:40 default[20210729t185736]  POST /forest/authentication 200 1094 - 1.597 ms
2021-07-30 11:53:40 default[20210729t185736]  "OPTIONS /forest/authentication/callback?code=j-HwyO0WsPGwD9Cs7SvvUWBdvxKV43ZXuVLRPCT9kMN_U0EUw3o9Rw-moHhcRq-w&state=%7B%22renderingId%22%3A97429%7D HTTP/1.1" 204
2021-07-30 11:53:40 default[20210729t185736]  OPTIONS /forest/authentication/callback?code=j-HwyO0WsPGwD9Cs7SvvUWBdvxKV43ZXuVLRPCT9kMN_U0EUw3o9Rw-moHhcRq-w&state=%7B%22renderingId%22%3A97429%7D 204 0 - 0.218 ms
2021-07-30 11:53:40 default[20210729t185736]  "GET /forest/authentication/callback?code=j-HwyO0WsPGwD9Cs7SvvUWBdvxKV43ZXuVLRPCT9kMN_U0EUw3o9Rw-moHhcRq-w&state=%7B%22renderingId%22%3A97429%7D HTTP/1.1" 200
2021-07-30 11:53:41 default[20210729t185736]  GET /forest/authentication/callback?code=j-HwyO0WsPGwD9Cs7SvvUWBdvxKV43ZXuVLRPCT9kMN_U0EUw3o9Rw-moHhcRq-w&state=%7B%22renderingId%22%3A97429%7D 200 519 - 714.294 ms
2021-07-30 11:53:41 default[20210729t185736]  "GET /forest/authGroup?timezone=America%2FSao_Paulo&fields%5BauthGroup%5D=id%2Cname&page%5Bnumber%5D=1&page%5Bsize%5D=15&searchExtended=0&sort=-id HTTP/1.1" 500
2021-07-30 11:53:41 default[20210729t185736]  "GET /forest/authGroup/count?fields%5BauthGroup%5D=id%2Cname&searchExtended=0&timezone=America%2FSao_Paulo HTTP/1.1" 500
2021-07-30 11:55:37 default[20210729t185736]  [forest] 🌳🌳🌳  Unexpected error: connect ETIMEDOUT 34.72.218.162:5432
2021-07-30 11:55:37 default[20210729t185736]  {
2021-07-30 11:55:37 default[20210729t185736]    "name": "SequelizeConnectionError",
2021-07-30 11:55:37 default[20210729t185736]    "parent": {
2021-07-30 11:55:37 default[20210729t185736]      "errno": "ETIMEDOUT",
2021-07-30 11:55:37 default[20210729t185736]      "code": "ETIMEDOUT",
2021-07-30 11:55:37 default[20210729t185736]      "syscall": "connect",
2021-07-30 11:55:37 default[20210729t185736]      "address": "34.72.218.162",
2021-07-30 11:55:37 default[20210729t185736]      "port": 5432
2021-07-30 11:55:37 default[20210729t185736]    },
2021-07-30 11:55:37 default[20210729t185736]    "original": {
2021-07-30 11:55:37 default[20210729t185736]      "errno": "ETIMEDOUT",
2021-07-30 11:55:37 default[20210729t185736]      "code": "ETIMEDOUT",
2021-07-30 11:55:37 default[20210729t185736]      "syscall": "connect",
2021-07-30 11:55:37 default[20210729t185736]      "address": "34.72.218.162",
2021-07-30 11:55:37 default[20210729t185736]      "port": 5432
2021-07-30 11:55:37 default[20210729t185736]    },
2021-07-30 11:55:37 default[20210729t185736]    "stack": "SequelizeConnectionError: connect ETIMEDOUT 34.72.218.162:5432\n    at /workspace/node_modules/sequelize/lib/dialects/postgres/connection-manager.js:182:24\n    at Connection.connectingErrorHandler (/workspace/node_modules/pg/lib/client.js:213:14)\n    at Connection.emit (events.js:314:20)\n    at Socket.reportStreamError (/workspace/node_modules/pg/lib/connection.js:57:10)\n    at Socket.emit (events.js:314:20)\n    at emitErrorNT (internal/streams/destroy.js:92:8)\n    at emitErrorAndCloseNT (internal/streams/destroy.js:60:3)\n    at processTicksAndRejections (internal/process/task_queues.js:84:21)"
2021-07-30 11:55:37 default[20210729t185736]  }
2021-07-30 11:55:37 default[20210729t185736]  [forest] 🌳🌳🌳  Unexpected error: connect ETIMEDOUT 34.72.218.162:5432
2021-07-30 11:55:37 default[20210729t185736]  {
2021-07-30 11:55:37 default[20210729t185736]    "name": "SequelizeConnectionError",
2021-07-30 11:55:37 default[20210729t185736]    "parent": {
2021-07-30 11:55:37 default[20210729t185736]      "errno": "ETIMEDOUT",
2021-07-30 11:55:37 default[20210729t185736]      "code": "ETIMEDOUT",
2021-07-30 11:55:37 default[20210729t185736]      "syscall": "connect",
2021-07-30 11:55:37 default[20210729t185736]      "address": "34.72.218.162",
2021-07-30 11:55:37 default[20210729t185736]      "port": 5432
2021-07-30 11:55:37 default[20210729t185736]    },
2021-07-30 11:55:37 default[20210729t185736]    "original": {
2021-07-30 11:55:37 default[20210729t185736]      "errno": "ETIMEDOUT",
2021-07-30 11:55:37 default[20210729t185736]      "code": "ETIMEDOUT",
2021-07-30 11:55:37 default[20210729t185736]      "syscall": "connect",
2021-07-30 11:55:37 default[20210729t185736]      "address": "34.72.218.162",
2021-07-30 11:55:37 default[20210729t185736]      "port": 5432
2021-07-30 11:55:37 default[20210729t185736]    },
2021-07-30 11:55:37 default[20210729t185736]    "stack": "SequelizeConnectionError: connect ETIMEDOUT 34.72.218.162:5432\n    at /workspace/node_modules/sequelize/lib/dialects/postgres/connection-manager.js:182:24\n    at Connection.connectingErrorHandler (/workspace/node_modules/pg/lib/client.js:213:14)\n    at Connection.emit (events.js:314:20)\n    at Socket.reportStreamError (/workspace/node_modules/pg/lib/connection.js:57:10)\n    at Socket.emit (events.js:314:20)\n    at emitErrorNT (internal/streams/destroy.js:92:8)\n    at emitErrorAndCloseNT (internal/streams/destroy.js:60:3)\n    at processTicksAndRejections (internal/process/task_queues.js:84:21)"
2021-07-30 11:55:37 default[20210729t185736]  }
2021-07-30 11:55:37 default[20210729t185736]  [forest] 🌳🌳🌳  Unexpected error: connect ETIMEDOUT 34.72.218.162:5432
2021-07-30 11:55:37 default[20210729t185736]  {
2021-07-30 11:55:37 default[20210729t185736]    "name": "SequelizeConnectionError",
2021-07-30 11:55:37 default[20210729t185736]    "parent": {
2021-07-30 11:55:37 default[20210729t185736]      "errno": "ETIMEDOUT",
2021-07-30 11:55:37 default[20210729t185736]      "code": "ETIMEDOUT",
2021-07-30 11:55:37 default[20210729t185736]      "syscall": "connect",
2021-07-30 11:55:37 default[20210729t185736]      "address": "34.72.218.162",
2021-07-30 11:55:37 default[20210729t185736]      "port": 5432
2021-07-30 11:55:37 default[20210729t185736]    },
2021-07-30 11:55:37 default[20210729t185736]    "original": {
2021-07-30 11:55:37 default[20210729t185736]      "errno": "ETIMEDOUT",
2021-07-30 11:55:37 default[20210729t185736]      "code": "ETIMEDOUT",
2021-07-30 11:55:37 default[20210729t185736]      "syscall": "connect",
2021-07-30 11:55:37 default[20210729t185736]      "address": "34.72.218.162",
2021-07-30 11:55:37 default[20210729t185736]      "port": 5432
2021-07-30 11:55:37 default[20210729t185736]    },
2021-07-30 11:55:37 default[20210729t185736]    "stack": "SequelizeConnectionError: connect ETIMEDOUT 34.72.218.162:5432\n    at /workspace/node_modules/sequelize/lib/dialects/postgres/connection-manager.js:182:24\n    at Connection.connectingErrorHandler (/workspace/node_modules/pg/lib/client.js:213:14)\n    at Connection.emit (events.js:314:20)\n    at Socket.reportStreamError (/workspace/node_modules/pg/lib/connection.js:57:10)\n    at Socket.emit (events.js:314:20)\n    at emitErrorNT (internal/streams/destroy.js:92:8)\n    at emitErrorAndCloseNT (internal/streams/destroy.js:60:3)\n    at processTicksAndRejections (internal/process/task_queues.js:84:21)"
2021-07-30 11:55:37 default[20210729t185736]  }
2021-07-30 11:55:37 default[20210729t185736]  [forest] 🌳🌳🌳  Unexpected error: connect ETIMEDOUT 34.72.218.162:5432
2021-07-30 11:55:37 default[20210729t185736]  {
2021-07-30 11:55:37 default[20210729t185736]    "name": "SequelizeConnectionError",
2021-07-30 11:55:37 default[20210729t185736]    "parent": {
2021-07-30 11:55:37 default[20210729t185736]      "errno": "ETIMEDOUT",
2021-07-30 11:55:37 default[20210729t185736]      "code": "ETIMEDOUT",
2021-07-30 11:55:37 default[20210729t185736]      "syscall": "connect",
2021-07-30 11:55:37 default[20210729t185736]      "address": "34.72.218.162",
2021-07-30 11:55:37 default[20210729t185736]      "port": 5432
2021-07-30 11:55:37 default[20210729t185736]    },
2021-07-30 11:55:37 default[20210729t185736]    "original": {
2021-07-30 11:55:37 default[20210729t185736]      "errno": "ETIMEDOUT",
2021-07-30 11:55:37 default[20210729t185736]      "code": "ETIMEDOUT",
2021-07-30 11:55:37 default[20210729t185736]      "syscall": "connect",
2021-07-30 11:55:37 default[20210729t185736]      "address": "34.72.218.162",
2021-07-30 11:55:37 default[20210729t185736]      "port": 5432
2021-07-30 11:55:37 default[20210729t185736]    },
2021-07-30 11:55:37 default[20210729t185736]    "stack": "SequelizeConnectionError: connect ETIMEDOUT 34.72.218.162:5432\n    at /workspace/node_modules/sequelize/lib/dialects/postgres/connection-manager.js:182:24\n    at Connection.connectingErrorHandler (/workspace/node_modules/pg/lib/client.js:213:14)\n    at Connection.emit (events.js:314:20)\n    at Socket.reportStreamError (/workspace/node_modules/pg/lib/connection.js:57:10)\n    at Socket.emit (events.js:314:20)\n    at emitErrorNT (internal/streams/destroy.js:92:8)\n    at emitErrorAndCloseNT (internal/streams/destroy.js:60:3)\n    at processTicksAndRejections (internal/process/task_queues.js:84:21)"
2021-07-30 11:55:37 default[20210729t185736]  }
2021-07-30 11:55:37 default[20210729t185736]  GET /forest/authGroup/count?fields%5BauthGroup%5D=id%2Cname&searchExtended=0&timezone=America%2FSao_Paulo 500 109 - 126570.994 ms
2021-07-30 11:55:37 default[20210729t185736]  GET /forest/authGroup/count?fields%5BauthGroup%5D=id%2Cname&searchExtended=0&timezone=America%2FSao_Paulo 500 109 - 115142.805 ms
2021-07-30 11:55:37 default[20210729t185736]  GET /forest/authGroup?timezone=America%2FSao_Paulo&fields%5BauthGroup%5D=id%2Cname&page%5Bnumber%5D=1&page%5Bsize%5D=15&searchExtended=0&sort=-id 500 109 - 127025.037 ms
2021-07-30 11:55:37 default[20210729t185736]  GET /forest/authGroup?timezone=America%2FSao_Paulo&fields%5BauthGroup%5D=id%2Cname&page%5Bnumber%5D=1&page%5Bsize%5D=15&searchExtended=0&sort=-id 500 109 - 115289.621 ms

Could you please help me indicating what I am doing wrong.

Best Regards,

Flavio

Hello @Flavio_Alves,

It looks like this is a very annoying issue regarding the connection to your database.
Can you confirm you are able to reach it from your local machine?

We cannot very help except telling you to retry correctly bind your sql database :slight_smile:

I hope you will be able to find what is making trouble connecting to your database.

Hello @Guillaume_Cisco ,

Thank you for your fast response.

In my local configuration, the operation of the smooth. It works without this kind of issues.

I am starting to work with Google appEngine. Looking at the GCP console, I can see that there are no connection restrictions for apps from the same project. But I don’t know if accessing from the appEngine to the database through the public IP is the correct way to do that.

If it is not the case, I don’t know how to setup the DATABASE_URL on the ForestAdmin backend.

Looking forward to your remarks.

Best regards,

Flavio

I understand @Flavio_Alves,

This is very frustrating.
Have you already look at the documentation on how to deploy your admin backend to Google Cloud Platform

I hope it helps,

Let me know :wink:

Hello @Guillaume_Cisco,

Yes I did. That’s why I decided to create this topic.

I solved the issue here. I studied the source code of the ForestAdmin backend and I realized that it uses the Sequelize, while GCP documentation uses another component as example.

Taking that into account, I found there two links:

And I changed the database initialization in the ForestAdmin backend:

File index.js

databasesConfiguration.forEach((databaseInfo) => {
  // const connection = new Sequelize(databaseInfo.connection.url, databaseInfo.connection.options);
  const connection = new Sequelize(databaseInfo.connection.dbName, databaseInfo.connection.username, databaseInfo.connection.password, {
    dialect: 'postgres',

    host: process.env.DATABASE_GCLOUD,
    pool: {
        max: 5,
        min: 0,
        acquire: 30000,
        idle: 10000
    },
    dialectOptions: {
        // e.g. socketPath: '/cloudsql/my-awesome-project:us-central1:my-cloud-sql-instance'
        // same as host string above
        socketPath: process.env.DATABASE_GCLOUD
    },
    logging: false,
    operatorsAliases: false
  });

I added new environment variables with database connection information:

File database.js:

module.exports = [{
  name: 'default',
  modelsDir: path.resolve(__dirname, '../models'),
  connection: {
    dbName: process.env.DATABASE_NAME,
    username: process.env.DATABASE_USER,
    password: process.env.DATABASE_PASSWORD,
    url: process.env.DATABASE_URL,
    options: { ...databaseOptions },
  },
}];

File app.yaml:

runtime: nodejs12
env_variables:
   FOREST_ENV_SECRET: 'xxxx'
   FOREST_AUTH_SECRET: 'yyyyy'
   NODE_ENV: 'production'
   DATABASE_URL: '<generated from forestadmin>'
   DATABASE_USER: '<database username>'
   DATABASE_PASSWORD: '<database password>'
   DATABASE_NAME: '<database name>'
   DATABASE_GCLOUD: '<gcp database id>'
   APPLICATION_URL: '<app url>'

Finally, on Google Cloud console, I set the app engine project to be a SQL Cloud Admin, on IAM configuration. I believe that it will also work in case of SQL client configuration too.

I don’t know if it is a generic solution, to be added on application main source code. At least, for GCP SQL, it works nice.

Hope it helps,

Best regards,

Flavio

Thank you very much @Flavio_Alves for this deep work on the GCP part!
I’m glad it now works for you! :tada:

I will look in details about what you have done to see if we need to update things from our side.

Have a great day, :sunny: