Unable to connect to Cloud SQL (PostgreSQL) instance using Sequelize

[falk-stefan]

I've been trying to run migration scripts and therefore connect to the Cloud SQL instance. However, already sitting here for almost two days trying to make this work I'm running out of ideas on how to diagnose the problem.

For starters, I am able to connect to my instance from my local machine without problems:

$ gcloud sql connect my-project-postgres-dev --user=postgres

Sequelize Config

Inspired by this example, this is how I am preparing the configuration for Sequelize in the development-environment:

// File: config.js
const dotenv = require('dotenv');
const { Connector, IpAddressTypes, AuthTypes } = require('@google-cloud/cloud-sql-connector');
dotenv.config();

let dbConfig;

if (ENVIRONMENT === 'development') {
  const connector = new Connector();
  const clientOpts = connector.getOptions({
    instanceConnectionName: process.env.CLOUD_SQL_INSTANCE,
    ipType: IpAddressTypes.PUBLIC,
    authType: process.env.DB_PASSWORD ? AuthTypes.PASSWORD : AuthTypes.IAM,
  });
  dbConfig = {
    username: process.env.DB_USERNAME,
    password: process.env.DB_PASSWORD,
    host: process.env.DB_HOST,
    database: process.env.DB_NAME,
    dialect: process.env.DB_DIALECT,
    dialectOptions: clientOpts,
  };
} else { /* brevity */ }

console.log(`[config.js] config`, dbConfig);
module.exports = dbConfig;

For the Connector to work, I already need the credentials from the Service Account. As such, I am setting GOOGLE_APPLICATION_CREDENTIALS to the path and run sequelize-cli db:migrate

$ GOOGLE_APPLICATION_CREDENTIALS=sa-cred.json sequelize-cli db:migrate

The console.log() from the config.js shows me the following:

[config.js] config {
  username: 'postgres',
  password: 'password',
  host: '12.34.56.78',
  database: 'projectdb-dev',
  dialect: 'postgres',
  dialectOptions: Promise { <pending> }
}

However, nothing will happen as I'll just get a timout error:

ERROR: connect ETIMEDOUT 12.34.56.78:5432

I've also tried this using gcloud builds, and as such, I have a cloudbuild.yaml which essentially does the same thing as above:

# Step to install dependencies
- name: 'gcr.io/cloud-builders/npm'
  args: [ 'install' ]
# Step to apply Sequelize migrations
- name: 'gcr.io/cloud-builders/npm'
  entrypoint: 'bash'
  args:
    - '-c'
    - GOOGLE_APPLICATION_CREDENTIALS=$_GOOGLE_APPLICATION_CREDENTIALS npm run db:migrate

The result is the same. I'd just get a timeout.

It's not clear to me whether this is an issue with Sequelize or whether I am doing something wrong here.

Can somebody configm that Cloud SQL should work? If so: What am I missing here?

[ephys]

Your call to connector.getOptions returns a promise, you need to await it

[falk-stefan]

I couldn't find a way to provide a configuration to Sequelize in an async fashion. I kinda assumed that this would happen internally. If this is not the case, how can I resolve the promise correctly?

[ephys]

The constructor is fully sync, you need to await the promise before giving it to us, which means that you either need to use top-level-await (requires using ESM), or use dependency injection, like the one nest provides

[falk-stefan]

Is there a simple example that you can point me to? I'm not using Nest.js.

Not sure if this is relevant, but the entire application is written in TypeScript with a few exceptions like the config.js because I wasn't able to make the sequelize-cli work with it.

[ephys]

Then your best bet will be to use ESM, otherwise you would have to set up a messy setup to achieve async initialization.

This gist explains how to configure a TS project to use ESM

Once you've done that, you can simply use await, as top-level-await will be available

[falk-stefan]

Thank you. I was giving it a try but it still won't let me write the config in TS (sequelize/cli#1099 👀) :

$ sequelize-cli db:migrate

Sequelize CLI [Node: 18.17.0, CLI: 6.6.1, ORM: 6.36.0]


ERROR: Error reading "src/db/config.ts". Error: TypeError [ERR_UNKNOWN_FILE_EXTENSION]: Unknown file extension ".ts" for /home/sfalk/workspaces/project/backend/src/db/config.ts

Alternatively, after downgrading to with npm i -D sequelize-cli@6.3.0:

$ sequelize-cli db:migrate

Sequelize CLI [Node: 18.17.0, CLI: 6.3.0, ORM: 6.36.0]


ERROR: Error reading "src/db/config.ts". Error: SyntaxError: Cannot use import statement outside a module

[falk-stefan]

@ephys part of me was doubtful I can make this work after trying to make this work for two days.. Now I can use TS throughout the project and my migrations finally work... Thank you so much! 🙏