Defining Sequelize on google cloud sql nodejs

Question

Hi i having an issue connecting to Google Cloud SQL from GAE.

My app is running inside docker container here is the docker file

FROM node:8.10.0-alpine

ARG NODE_ENV=production
ENV NODE_ENV=$NODE_ENV

# env like sql user db instance connection name
# Set a working directory
WORKDIR /usr/src/app
COPY ./build/package.json .
COPY ./build/yarn.lock .

# Install Node.js dependencies
RUN yarn install --production --no-progress
# Copy application files
COPY ./build .
COPY ./src/db/seeders ./seeds
COPY ./src/db/migrations ./migrations
COPY ./scripts ./scripts

RUN yarn run db:seed # -> failed to run this line
RUN yarn run db:migrate
# Run the container under "node" user by default
USER node

CMD [ "node", "server.js" ]

to connect to the db i'm using Sequealize this is my connection config

module.exports = {
  production: {
    dialect: 'postgres',
    seederStorage: 'sequelize',
    seederStorageTableName: 'sequelize_seeder',
    username: process.env.SQL_USER,
    password: process.env.SQL_PASSWORD,
    database: process.env.SQL_DATABASE,
    host: `/cloudsql/${process.env.INSTANCE_CONNECTION_NAME}`,
    logging: true,
    dialectOptions: {
      socketPath: `/cloudsql/${process.env.INSTANCE_CONNECTION_NAME}`,
      supportBigNumbers: true,
      bigNumberStrings: true,
      ssl: false,
    },
    pool: {
      max: 5,
      idle: 30000,
      acquire: 60000,
    },
    operatorsAliases: false,
    define: {
      freezeTableName: true,
    },
  },
};

I tried almost everything from setting the host to localhost/127.0.0.1 While doing so i'm getting SequelizeConnectionRefusedError: connect ECONNREFUSED 127.0.0.1:5432

If i'm setting host: host:/cloudsql/${process.env.INSTANCE_CONNECTION_NAME}``

I'm getting a different error SequelizeConnectionError: connect ENOENT {MY_INSTANCE_CONNECTION_NAME}.s.PGSQL.5432

my app.yaml file

env: flex
runtime: custom
env_variables:
   #env db user etc..
beta_settings:
  cloud_sql_instances: MY_INSTANCE_CONNECTION_NAME

I tried to log in with knex and I managed to connect so i assuming something wrong with my configuration

Answer 1

Spent whole day today trying to connect from Google App Engine app to Google Cloud SQL (PostreSQL) when deploying through Bitbucket pipelines.

Here is the configs that worked for me (may be they will save someone few hours of life):

const sequelize = new Sequelize(DB_NAME, USERNAME, PASSWORD, {
    dialect: 'postgres',
    // e.g. host: '/cloudsql/my-awesome-project:us-central1:my-cloud-sql-instance'
    host: '/cloudsql/${INSTANCE_CONNECTION_NAME}',
    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: '/cloudsql/${INSTANCE_CONNECTION_NAME}'
    },
    logging: false,
    operatorsAliases: false
});

app.yaml file:

runtime: nodejs
env: flex

# make sure to include code below
beta_settings:
    cloud_sql_instances: my-awesome-project:us-central1:my-cloud-sql-instance

In my case when I did not provide host it failed as well all other variants with connections string when connecting to the Google Cloud SQL.

Cheers!