I have tried at least 100 different things but can’t seem to connect my Forest Admin server running on Google App Engine to the Google Cloud SQL database in the same project. I have followed the instruction in here to a tee.
I just wanted to put it out there in case someone else also ran into this issue.
Happy to hear any ideas tbh
Expected behavior
It should connect
Actual behavior
It doesn’t connect
Failure Logs
A 2020-08-05T01:35:38.784280Z e[31m[forest] Unexpected error: connect ECONNREFUSED 127.0.0.1:5432e[39m
A 2020-08-05T01:35:38.784305Z e[31mSequelizeConnectionRefusedError: connect ECONNREFUSED 127.0.0.1:5432e[39m
A 2020-08-05T01:35:38.784317Z e[31m at connection.connect.err (/srv/node_modules/sequelize/lib/dialects/postgres/connection-manager.js:170:24)e[39m
A 2020-08-05T01:35:38.784326Z e[31m at Connection.connectingErrorHandler (/srv/node_modules/pg/lib/client.js:213:14)e[39m
A 2020-08-05T01:35:38.784335Z e[31m at Connection.emit (events.js:198:13)e[39m
A 2020-08-05T01:35:38.784342Z e[31m at Socket.reportStreamError (/srv/node_modules/pg/lib/connection.js:57:10)e[39m
A 2020-08-05T01:35:38.784350Z e[31m at Socket.emit (events.js:198:13)e[39m
A 2020-08-05T01:35:38.784358Z e[31m at emitErrorNT (internal/streams/destroy.js:91:8)e[39m
A 2020-08-05T01:35:38.784367Z e[31m at emitErrorAndCloseNT (internal/streams/destroy.js:59:3)e[39m
A 2020-08-05T01:35:38.784375Z e[31m at process._tickCallback (internal/process/next_tick.js:63:19)e[39m
Context
Please provide any relevant information about your setup.
Package Version:
Express Version:
Sequelize Version:
Database Dialect:
Database Version:
Project Name:
UPDATE: It seems like an issue with how sequelize library connects via unix socket, given it successfully connects if used knex library.
The following temporary patch in the Forest Admin server code fixed it for me:
Split the database_url into multiple params so that App Engine sequelize can connect to Cloud SQL via Unix sockets. Theres a bug in sequalize database URI parsing where it applies localhost if the host is empty or starts with / in the URL, which is the case for Unix sockets. I landed on this solution after massive experimental iterations on top of the direction I got from the following two issues:
1. https://stackoverflow.com/questions/49766669/sequelize-cli-migration-with-non-standard-unix-sockets
2. https://github.com/sequelize/sequelize/issues/1247
diff --git a/models/index.js b/models/index.js
index 7d10cad..1c50421 100644
--- a/models/index.js
+++ b/models/index.js
@@ -2,22 +2,23 @@ const fs = require('fs');
const path = require('path');
const Sequelize = require('sequelize');
-if (!process.env.DATABASE_URL) {
- console.error('Cannot connect to the database. Please declare the DATABASE_URL environment variable with the correct database connection string.');
- process.exit();
+let ssl = false;
+if (process.env.DATABASE_SSL && JSON.parse(process.env.DATABASE_SSL.toLowerCase())) {
+ ssl = true;
}
let databaseOptions = {
- logging: process.env.NODE_ENV === 'development' ? console.log : false,
+ dialect: process.env.DATABASE_DIALECT,
+ host: process.env.DATABASE_HOST,
pool: { maxConnections: 10, minConnections: 1 },
- dialectOptions: {}
+ logging: process.env.NODE_ENV === 'development' ? console.log : false,
+ ssl,
+ dialectOptions: {
+ ssl,
+ }
};
-if (process.env.DATABASE_SSL && JSON.parse(process.env.DATABASE_SSL.toLowerCase())) {
- databaseOptions.dialectOptions.ssl = true;
-}
-
-let sequelize = new Sequelize(process.env.DATABASE_URL, databaseOptions);
+let sequelize = new Sequelize(process.env.DATABASE_NAME, process.env.DATABASE_USER, process.env.DATABASE_PASSWORD, databaseOptions);
let db = {};
fs
(END)
However, the right fix might involve digging down why URL with socket doesn’t get parsed in Sequelize. An alternative acceptable fix might also be to use some other library for URL parsing and forward those variables to Sequelize. I’ll let forest admin team take it from here.
The DATABASE_NAME is the name of my database which is mydb. The DATABASE_HOST is the one where I wanted to put a non-standard entry: /cloudsql/my-project-id:us-west1:my-psql-instance
Thanks for sharing your issue and your solution!
You’re right, we don’t support cloudsql natively, but the workaround you found seems fine
FYI another possible solution is explained here.
And I’m going to add a Product Board request so that we can support this natively.
