Implementing Cloud SQL Triggers Using Node.js

April 19, 2023

A relational database service for MySQL, PostgreSQL, and SQL Server is called Cloud SQL.

The vocabulary and fundamental ideas underlying Cloud SQL, which offers Google Cloud SQL data storage, are covered on this page. Cloud SQL feature supported by database engine for details on how different Cloud SQL databases stack up against one another.

The Node-API (formerly known as N-API) is an API for creating native Addons. It is maintained as a component of Node.js itself and is independent of the underlying JavaScript runtime (for instance, V8). This API will maintain its Application Binary Interface (ABI) stability across Node.js versions. It is designed to shield add-ons from modifications made to the JavaScript engine that powers the website and enable modules created for one major version of Node.js to function without the need for recompilation of major versions. An in-depth explanation is given in the ABI Stability Handbook.

To make sure that the app effectively connects to the Cloud SQL instance over a Unix socket made available to Cloud Functions, Cloud Run, and App Engine apps, we need to connect Cloud SQL with Node.js.

We are going to explore using the Node.js API to connect to the Cloud SQL database. We need the following for this:

Cloud SQL Database
Node API

Cloud SQL Database

In this section we will set up a database in Cloud SQL.

To begin, pick the Google project in the Google Console after it has opened.
Select SQL, followed by Create Instance, from the navigation menu in the top left. Hamburger Menu > SQL > Create Instance
Click on the MySQL database engine, enter the instance ID and password, and leave the rest of the information as it is.
After filling all the details click Create Instance.

Next we will connect to the instance using MySQL client in Cloud shell.

By selecting the Cloud shell icon in the upper left corner and selecting Continue, an open Cloud shell will appear.
Run the following command in the Cloud Shell prompt to establish a connection to your Cloud SQL instance:

gcloud sql connect –user=root

The instance connection name must then be obtained in order to access it in the Node API to connect to the SQL instance.
The Instance Connection Name can be found on the overview page of the SQL instance; copy it.
Click Databases on the overview page to create one by giving the database a name.
The instance ID can be choose from the overview page.
To create a User Account, select the Users tab. Right now, let any host in.
To add Network, select the Authorization tab. duplicate-paste IPV4.

Now we have a database, a user with a username and password, an instance, and we have whitelisted the IP address, we need to construct a table.

Node API

In this section, we’ll use the mysql module to connect to Cloud SQL.

Download and Install Node.js before installing mysql module.
Install mysql using the following command:

$ npm install mysql

Since we can build and handle connections one at a time, we’ll use MySQL connection pooling in this case .createPool(config)
Now we can create a pool and use directly:

We need to add a DB_INSTANCE_NAME with the instance name in the Overview tab until we can deploy the API to Google App Engine (GAE).