Connecting Sequelize to Google Cloud SQL

Question

does anyone know how to connect to Google Cloud SQL from Sequelize?

  sequelize = new Sequelize(process.env.TEST_DB || 'postgres', 'blah', null, {
    dialect: 'postgres',
    operatorsAliases: Sequelize.Op,
    host: process.env.DB_HOST || 'localhost',
    define: {
      underscored: true
    },
  });
  connected = true;

Hanni

Where is your code running? App Engine Standard? Flexible? Your local machine? – Petrick

I'm just trying to use Google Cloud Proxy on my machine. But afterwards I'd like to deploy the app to Google Cloud Compute Engine. – Hanni

Ok, you are using proxy. I updated my answer to be more proxy specific. Please make sure you have followed the proxy setup. – Cauline

Answer 1

You can connect from Sequelize like from any other client tool or ORM. Getting the access correctly depends on where you are running your code. If you code runs outside GCP, you can follow the external app instructions on this page: https://cloud.google.com/sql/docs/postgres/connect-external-app.

If you are using proxy in your local computer, you set process.env.DB_HOST to 127.0.0.1. You can find troubleshooting tips at https://cloud.google.com/sql/docs/postgres/sql-proxy#troubleshooting.

Cauline

Thanks Veikko. I got it working on my local computer. But do you know how I could set this up in Sequelize for production? Sequelize doc seems to suggest to use a uri, in this format: postgres://user:[email protected]:5432/dbname docs.sequelizejs.com/manual/installation/… Do you I just add that into the host? And also do you know what should be replacing example.com? – Hanni

You can use the proxy also from compute engine instance, or you can whitelist your compute engine instances static ip in cloud sql. Both work well. You can find specific instructions and recommendations for compute engine at cloud.google.com/sql/docs/postgres/connect-compute-engine. – Cauline

@Hanni can you share how you succeeded connecting it ? I receive the following error: original: error: pg_hba.conf rejects connection for host "ip addr", user "postgres", database "db-name", no encryption – Shutt

Answer 2

index.js

const sequelize = new Sequelize('{db_name}', '{db_user}', '{db_password}', {
  dialect: 'mysql',
  host: '/cloudsql/{instance}',
  timestamps: false,
  dialectOptions: {
    socketPath: '/cloudsql/{instance}'
},
});

add this in serverless.yml

beta_settings:
  cloud_sql_instances: {xxxxxxx-xxxxxx:us-central1:xxxxxxxxxxx}

Thaddeus

This solution (using the cloudsql instance name) worked for me when my app ran inside GCP (I'd previously been trying to use external SQL host IP address, which only worked from external networks and did not work inside GCP) – Paulin

I learned that the dialectOptions part is required inside of GCP (CloudRun for example), but caused me errors when connecting to the proxy on my local machine (cloud.google.com/sql/docs/mysql/…) , Just a heads up. I updated my code like so ...(host === '127.0.0.1' ? {} : { dialectOptions: { socketPath: host, }, }) – Verbenaceous

What does the /cloudsql/{instance} mean ? does it means the instance identifier ? – Margoriemargot