How to connect Sequelize to Google Cloud SQL database when I have deployed my Node.JS backend api?

Question

I have a Node.js + Express backend API, and I use Sequelize ORM to query my database. My database runs on google cloud SQL and I use the MySQL dialect.

So here is my index.js now where I initialize sequelize:

import { Sequelize } from "sequelize";


const sequelize = new Sequelize({
    dialect: "mysql",
    database: "databasename",
    username: "databaseusername",
    password: "userpassword",
    port: process.env.PORT || 3306,
    socketPath: "/cloudsql/SQL_CONNECTION_NAME",
    timestamps: false,
});


export default sequelize;

where 'SQL_CONNECTION_NAME' is obviously of the form PROJECT_ID:LOCATION:SQL_INSTANCE_NAME.

I have seen so many variants for how to define sequelize that I just don't know what I should put in some fields, or if I should leave them out completely, etc... (for example, some sources used "host" field, some didn't, some used both "host" field and "socketPath" field etc..)

Here is my server.js, basically the entry point for my API:

import express from "express"
import router from "./routes.js";
import cors from "cors";
import sequelize from "./sequelize_models/index.js";  // this is the sequelize instance defined above
import { createServer } from "http";
import { Server } from "socket.io";
import path from "path";

const app = express();

app.use(cors());
app.use(express.json());
app.use(express.urlencoded({ extended: true }));

// this endpoint is just for testing
app.get("/", (req, res) =>
{
    res.sendFile(path.join(__dirname, "index.html"));
});

app.use("/api", router);

const server = createServer(app);
export const socketIoServer = new Server(server, {
    cors: {
        origin: "http://localhost:3000",
        methods: ["GET", "POST", "PUT", "DELETE"],
        allowedHeaders: ["Access-Control-Allow-Origin"]
    }
});

sequelize.sync()
         .then(() =>
         {
             console.log("Models synchronized with database.");
         })
         .catch(error =>
         {
             console.error("Error synchronizing models:", error);
         });

socketIoServer.on('connect', socket =>
{
    console.log("A user connected.");

    socket.on('message', data =>
        {
            console.log("Message from user: " + data);
        }
    )
});

server.listen(process.env.PORT || 3306, () =>
{
    console.log(`Server is running on port ${process.env.PORT || 3306}.`);
})

So actually, when I am running this backend locally (after changing the port and how I define sequelize a bit) it works just fine, and I see all the items from the database on my frontend as it should be. But when I run it on my machine, I use this https://cloud.google.com/sql/docs/mysql/connect-auth-proxy as well, to be able to actually connect to the database, I suppose, but this doesn't seem to work for when my backend is deployed...

I deployed this backend using the gcloud command-line tool, with the command gcloud app deploy, and this is the app.yaml for it:

runtime: nodejs20
service_account: {serviceName}@{PROJECT_ID}.iam.gserviceaccount.com
beta_settings:
  cloud_sql_instances: {PROJECT_ID}:{LOCATION}:{CLOUD_SQL_INSTANCE_NAME}

After deploying my backend, it has a url like 'https://{PROJECT_ID}.ew.r.appspot.com', and on any endpoint (that I have defined in my application) I go to I just get

{
  "error": "Internal Server Error"
}

with a status code 500.

I have tried searching the google docs for connecting to the database in other ways but none of them mentioned something like sequelize and I have not been able to make it work otherwise.

I would appreciate a lot any help!

Answer 1

It looks as though you have misconfigured your Sequelize() client initialization in your index.js.

As per the Sequelize MySQL docs all custom options such as socketPath should be passed via dialectOptions. (also if you are connecting via unix socket you should not specify port).

Your Sequelize contruction in index.js should look like the following:

const sequelize = new Sequelize('database', 'username', 'password', {
  dialect: 'mysql',
  dialectOptions: {
    // Your mysql2 options here
    socketPath: '/cloudsql/SQL_CONNECTION_NAME',
  },
});