How to connect Sequelize to cloud sql nodejs connector

[samuel_ireke]

Hey community,
Please could anyone help with a suggestion of how I can effectively connect sequelize to cloude-sql for postgres via the google cloud nodejs connector. 

I have read all the google docs I can find on this which uses Knex and others as example but nothing has worked. My team and I have been on this issue for days now as we are preparing for deployment and would like to use CloudSQL as our production db.

So far we've been developinf locally.
Here is what I am trying to do when initialising sequelize:

const Sequelize = require("sequelize");
const config = require("config");
const { Connector } = require("@google-cloud/cloud-sql-connector");

const dbConfig = config.get("database");
const connector = new Connector();

const sequelize = new Sequelize(dbConfig);

sequelize.beforeConnect(async (config) => {
  const clientOpts = await connector.getOptions({
    instanceConnectionName: process.env.CLOUDSQL_INSTANCE_CONNECTION_NAME,
    // authType: "IAM",
    ipType: "PUBLIC",
  });
  if (process.env.NODE_ENV === "development") {
    config = { ...config, ...clientOpts };
  }
});

// Test connection
try {
  sequelize.authenticate();
  console.log("Connection has been established successfully.");
} catch (err) {
  console.error("Unable to connect to the database:", err);
}

module.exports = sequelize;

dbConfig looks like so:

database: {
    host: process.env.CLOUDSQL_DB_HOST,
    dialect: "postgres",
    username: process.env.CLOUDSQL_DB_USERNAME,
    password: process.env.CLOUDSQL_DB_PASSWORD,
    database: process.env.CLOUDSQL_DB_DB,
    logging: false, 
  },

Not sure where I have got it wrong. 
Only works when 

CLOUDSQL_DB_HOST=PUBLIC_IP

and not when 

CLOUDSQL_DB_HOST= INSTANCE_NAME

 as recommended in google docs. As such I can only use 

CLOUDSQL_DB_USERNAME=postgres

but 

CLOUDSQL_DB_USERNAME=CUSTOM_USER 

never works. NOTE cutom_user is properly added to the cloud sql instance as a user. Service account in IAM agrant the cloud-sql-client role as well.

I have also tried cloud proxy, but that has also failed.


Any help is much appreciated.

Thanks

[ms4446]

To connect to a Cloud SQL instance using the Cloud SQL Node.js Connector, you do not need to set the host property in the Sequelize configuration. The connector will handle the connection for you.

You should also make sure that you have granted the cloud-sql-client role to the service account that your application is using. To do this, follow these steps:

1. Go to the IAM & Admin console: https://console.cloud.google.com/iam-admin/serviceaccounts: 
2. Click the name of the service account that your application is using.
3. Click the Permissions tab.
4. Click Add.
5. In the Role field, enter cloud-sql-client.
6. Click Save.

The application should be authenticated with the service account, either by setting the GOOGLE_APPLICATION_CREDENTIALS environment variable to point to the service account JSON key or by using the default application credentials if running on Google Cloud Platform.

You can use the beforeConnect hook to set the connection options for your Cloud SQL instance. This can be useful if you need to connect to a different Cloud SQL instance depending on the environment that your application is running in.

Here is an example of a beforeConnect hook that you can use:

sequelize.beforeConnect(async (config) => {
  const clientOpts = await connector.getOptions({
    instanceConnectionName: process.env.CLOUDSQL_INSTANCE_CONNECTION_NAME,
    // authType: "IAM",
    ipType: "PUBLIC",
  });
  if (process.env.NODE_ENV === "development") {
    config = { ...config, ...clientOpts };
  }
});

Once you have updated your configuration, you should be able to connect to your Cloud SQL instance using Sequelize without any problems.

[samuel_ireke]

Hi @ms4446 ,

Thanks for your response. 
However, we are having the same issue. We now get this Sequelize error, which suggests the an authentication issue but can't figure out why.

C:\Users\...\myApp\node_modules\sequelize\lib\dialects\postgres\connection-manager.js:143
                reject(new sequelizeErrors.ConnectionError(err));
                       ^

ConnectionError [SequelizeConnectionError]: password authentication failed for user "grand-central-test@application-deployment-project.iam"
    at Client._connectionCallback (C:\Users\...\myApp\node_modules\sequelize\lib\dialects\postgres\connection-manager.js:143:24)       
    at Client._handleErrorWhileConnecting (C:\Users\...\myApp\node_modules\pg\lib\client.js:318:19)
    at Client._handleErrorMessage (C:\Users\...\myApp\node_modules\pg\lib\client.js:338:19)
    at Connection.emit (node:events:513:28)
    at C:\Users\...\myApp\node_modules\pg\lib\connection.js:116:12
    at Parser.parse (C:\Users\...\myApp\node_modules\pg-protocol\dist\parser.js:40:17)
    at Socket.<anonymous> (C:\Users\...\myApp\node_modules\pg-protocol\dist\index.js:11:42)
    at Socket.emit (node:events:513:28)
    at addChunk (node:internal/streams/readable:324:12)
    at readableAddChunk (node:internal/streams/readable:297:9) {
  parent: error: password authentication failed for user "grand-central-test@application-deployment-project.iam"
      at Parser.parseErrorMessage (C:\Users\...\myApp\node_modules\pg-protocol\dist\parser.js:287:98)
      at Parser.handlePacket (C:\Users\...\myApp\node_modules\pg-protocol\dist\parser.js:126:29)
      at Parser.parse (C:\Users\...\myApp\node_modules\pg-protocol\dist\parser.js:39:38)
      at Socket.<anonymous> (C:\Users\...\myApp\node_modules\pg-protocol\dist\index.js:11:42)
      at Socket.emit (node:events:513:28)
      at addChunk (node:internal/streams/readable:324:12)
      at readableAddChunk (node:internal/streams/readable:297:9)
      at Readable.push (node:internal/streams/readable:234:10)
      at TCP.onStreamRead (node:internal/stream_base_commons:190:23) {
    length: 149,
    severity: 'FATAL',
    code: '28P01',
    detail: undefined,
    hint: undefined,
    position: undefined,
    internalPosition: undefined,
    internalQuery: undefined,
    where: undefined,
    schema: undefined,
    table: undefined,
    column: undefined,
    dataType: undefined,
    constraint: undefined,
    file: 'auth.c',
    line: '329',
    routine: 'auth_failed'
  },
  original: error: password authentication failed for user "grand-central-test@application-deployment-project.iam"
      at Parser.parseErrorMessage (C:\Users\...\myApp\node_modules\pg-protocol\dist\parser.js:287:98)
      at Parser.handlePacket (C:\Users\...\myApp\node_modules\pg-protocol\dist\parser.js:126:29)
      at Parser.parse (C:\Users\...\myApp\node_modules\pg-protocol\dist\parser.js:39:38)
      at Socket.<anonymous> (C:\Users\...\myApp\node_modules\pg-protocol\dist\index.js:11:42)
      at Socket.emit (node:events:513:28)
      at addChunk (node:internal/streams/readable:324:12)
      at readableAddChunk (node:internal/streams/readable:297:9)
      at Readable.push (node:internal/streams/readable:234:10)
      at TCP.onStreamRead (node:internal/stream_base_commons:190:23) {
    length: 149,
    severity: 'FATAL',
    code: '28P01',
    detail: undefined,
    hint: undefined,
    position: undefined,
    internalPosition: undefined,
    internalQuery: undefined,
    where: undefined,
    schema: undefined,
    table: undefined,
    column: undefined,
    dataType: undefined,
    constraint: undefined,
    file: 'auth.c',
    line: '329',
    routine: 'auth_failed'
  }
}

GOOGLE_APPLICATION_CREDENTIALS has been added to our env varaibles and points to the service account key location. As suggested, we've removed the host from the sequelize config. 
Here is the updated code:

// config.js
database: {
    dialect: "postgres",
    username: process.env.CLOUDSQL_DB_USERNAME,
    password: process.env.CLOUDSQL_DB_PASSWORD,
    database: process.env.CLOUDSQL_DB_DB,
    migrationStorageTableName: "migrations",
    logging: false, //console.log,
  },

//database.js
const Sequelize = require("sequelize");
const config = require("config");
const { Connector } = require("@google-cloud/cloud-sql-connector");
const connector = new Connector();

const dbConfig = config.get("database");

const sequelize = new Sequelize(dbConfig);

sequelize.beforeConnect(async (config) => {
  const clientOpts = await connector.getOptions({
    instanceConnectionName: process.env.CLOUDSQL_INSTANCE_CONNECTION_NAME,
    authType: "IAM",
  });
  if (process.env.NODE_ENV === "development") {
    config = { ...config, ...clientOpts };
  }
  console.log(config);
});

// Test connection
try {
  sequelize.authenticate();
  console.log("Connection has been established successfully.");
} catch (err) {
  console.error("Unable to connect to the database:", err);
}

module.exports = sequelize;

The console.log added in the beforeConnect hook returns the following:

{
  database: 'database-name',
  username: 'service-account-name@project-id.iam',
  password: 'somePassword',
  host: 'localhost',
  port: 5432,
  pool: {
    max: 5,
    min: 0,
    idle: 10000,
    acquire: 60000,
    evict: 1000,
    validate: [Function: bound _validate]
  },
  protocol: 'tcp',
  native: false,
  ssl: undefined,
  replication: false,
  dialectModule: null,
  dialectModulePath: null,
  keepDefaultTimezone: undefined,
  dialectOptions: undefined,
  stream: [Function: stream]
}

The service account was also added as a User to the cloudSQL instance.
In IAM, the service account has the following IAM roles : 

Cloud SQL Client

Cloud SQL Instance User

Logs Writer.

Note: in the beforeConnect hook, Using

ipType: "PUBLIC"

 instead of 

authType: "IAM"

also returned the same error.

We tried removing the password from the config while following similar instructions in this codelabs but this returned the below error:

ConnectionError [SequelizeConnectionError]: SASL: SCRAM-SERVER-FIRST-MESSAGE: client password must be a string

[ms4446]

Troubleshooting Authentication Issues with the Cloud SQL Node.js Connector

Service Account Authentication

When using the Cloud SQL Node.js Connector with IAM authentication (authType: "IAM"), you do not need to provide a password in your Sequelize configuration. The IAM authentication flow manages the authentication process on its behalf.

Password Authentication

If you are using traditional user/password authentication, ensure that the password provided is correct. The error message indicates that password authentication has failed, which suggests either the password is incorrect or there might be an issue with how the authentication is being handled.

Localhost Configuration

The host property set to localhost is typically used when you are connecting through the Cloud SQL Auth Proxy running on the same machine. If you are not using the proxy, the Cloud SQL Node.js Connector should configure the connection to point directly to the Cloud SQL instance.

Roles and Permissions

Verify that the service account has the necessary permissions to access the Cloud SQL instance. The roles of Cloud SQL Client and Cloud SQL Instance User are usually sufficient, but double-check that they are correctly assigned to the service account.

Environment Variables

Check that the environment variables, such as GOOGLE_APPLICATION_CREDENTIALS, are correctly configured and accessible by your application.

Verbose Logging

Enable verbose logging for both Sequelize and the Cloud SQL Node.js Connector to gain more detailed insights into the connection process and any issues that may arise.

Cloud SQL Auth Proxy

Consider using the Cloud SQL Auth Proxy alongside your application. It can simplify authentication and connection management, particularly if IAM authentication is causing issues.

Error Handling in Code

Ensure that you are correctly handling promises in your connection test code. Use .then() and .catch() to handle the resolution and errors of the promise returned by sequelize.authenticate(), like so:

sequelize.authenticate()
  .then(() => console.log("Connection has been established successfully."))
  .catch(err => console.error("Unable to connect to the database:", err));

[K_V_Rao]

I am also facing the same issue, if you get any solution please post it over here 

[Svemirsky]

Hi @samuel_ireke 

Have you managed to connect in the end?

I am facing same issues as you and I am not at this point able to figure it out.

I am not even sure this is possible using Sequelize, all the working examples are using pg Pools.

[frenner]

Hey @samuel_ireke,

I've been running into the same issue with MySQL and automatic IAM login.
My solution was to explicitly define `password: null` and to pass the clientOpts into the dialectOptions.

This should work with sequelize >= 6.36 (See this PR which fixed Postgres compatibility to pass down stream)