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!
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',
},
});