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

Actomyosin

Hi - I am facing the same issue - how did you resolved this issue? can you please help. I am using NodeJS+Postgres+Sequelize+GoogleCloudRUN+Typesript. I tried almost all option but unable to get through. – Shipping

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!

Lynnett

It looks like Sequalize connects via localhost if you provide a username/password, even when socketPath is specified. According to this [gist][1] the options are to either have a null user and pass, or specify host (looks like socketPath is potentially unused?). I can't find any clarification within the Sequalize docs. [1]: gist.github.com/myndzi/03eb5c42e8385470403c – Methodist

yeah, checked the sequelize's source code [1] (line 56, 153), localhost is indeed a default value for host. Also found more on connection via UNIX sockets here [2]. [1]: github.com/sequelize/sequelize/blob/master/lib/sequelize.js, [2]: github.com/sequelize/sequelize/blob/… – Lynnett

Answer 2

It looks like something is wrong with the params passed to sequlize. Try using a simple connection string var conString = "postgres://UserName:Password@Host:5432/YourDatabase";

Makepeace