Quickstart: Use Node.js to connect and query data in Azure Database for MySQL - Flexible Server

Article
12/23/2024

In this quickstart, you connect to Azure Database for MySQL flexible server by using Node.js. You then use SQL statements to query, insert, update, and delete data in the database from Mac, Linux, and Windows platforms.

This article assumes that you're familiar with developing using Node.js, but you're new to working with Azure Database for MySQL flexible server.

Prerequisites

This quickstart uses the resources created in either of these guides as a starting point:

Important

Ensure the IP address you're connecting from has been added the server's firewall rules using the Manage firewall rules for Azure Database for MySQL - Flexible Server using the Azure portal or Manage firewall rules for Azure Database for MySQL - Flexible Server using Azure CLI

Install Node.js and the MySQL connector

Depending on your platform, follow the instructions in the appropriate section to install Node.js. Use npm to install the mysql2 package and its dependencies into your project folder.

Visit the Node.js downloads page, and then select your desired Windows installer option.
Make a local project folder such as nodejsmysql.
Open the command prompt, and then change directory into the project folder, such as cd c:\nodejsmysql\

Run the NPM tool to install mysql2 library into the project folder.

cd c:\nodejsmysql\
"C:\Program Files\nodejs\npm" install mysql2
"C:\Program Files\nodejs\npm" list

Verify the installation by checking the npm list output text. The version number might vary as new patches are released.

Run the following commands to install Node.js and npm the package manager for Node.js.

 # Using Ubuntu
 sudo curl -sL https://deb.nodesource.com/setup_14.x | sudo -E bash -
 sudo apt-get install -y nodejs

 # Using Debian, as root
 sudo curl -sL https://deb.nodesource.com/setup_14.x | bash -
 sudo apt-get install -y nodejs

Run the following commands to create a project folder mysqlnodejs and install the mysql2 package into that folder.
```
mkdir nodejsmysql
cd nodejsmysql
npm install --save mysql2
npm list
```
Verify the installation by checking npm list output text. The version number may vary as new patches are released.

Run the following commands to install Node.js and npm the package manager for Node.js.

RHEL 7.x
```
sudo yum install -y rh-nodejs8
scl enable rh-nodejs8 bash
```
RHEL 8.x
```
 sudo yum install -y nodejs
```
Run the following commands to create a project folder mysqlnodejs and install the mysql2 package into that folder.
```
mkdir nodejsmysql
cd nodejsmysql
npm install --save mysql2
npm list
```
Verify the installation by checking npm list output text. The version number might vary as new patches are released.

Run the following commands to install Node.js and npm the package manager for Node.js.
```
 sudo zypper install nodejs
```
Run the following commands to create a project folder mysqlnodejs and install the mysql2 package into that folder.
```
mkdir nodejsmysql
cd nodejsmysql
npm install --save mysql2
npm list
```
Verify the installation by checking npm list output text. The version number might vary as new patches are released.

Visit the Node.js downloads page, and then select your macOS installer.
Run the following commands to create a project folder mysqlnodejs and install the mysql2 package into that folder.
```
mkdir nodejsmysql
cd nodejsmysql
npm install --save mysql2
npm list
```
Verify the installation by checking the npm list output text. The version number might vary as new patches are released.

Get connection information

Get the connection information needed to connect to the Azure Database for MySQL flexible server instance. You need the fully qualified server name and sign in credentials.

Sign in to the Azure portal.
From the left-hand menu in Azure portal, select All resources, and then search for the server you have created (such as mydemoserver).
Select the server name.
From the server's Overview panel, make a note of the Server name and Server admin login name. If you forget your password, you can also reset the password from this panel.

Run the code samples

Paste the JavaScript code into new text files, and then save it into a project folder with file extension .js (such as C:\nodejsmysql\createtable.js or /home/username/nodejsmysql/createtable.js).
Replace host, user, password and database config options in the code with the values that you specified when you created the MySQL flexible server and database.
Obtain SSL certificate: To use encrypted connections with your client applications,you'll need to download the public SSL certificate which is also available in Azure portal Networking blade as shown in the screenshot below.

Save the certificate file to your preferred location.

In the ssl config option, replace the ca-cert filename with the path to this local file. This will allow the application to connect securely to the database over SSL.
Open the command prompt or bash shell, and then change directory into your project folder cd nodejsmysql.
To run the application, enter the node command followed by the file name, such as node createtable.js.
On Windows, if the node application isn't in your environment variable path, you might need to use the full path to launch the node application, such as "C:\Program Files\nodejs\node.exe" createtable.js

Connect, create table, and insert data

Use the following code to connect and load the data by using CREATE TABLE and INSERT INTO SQL statements.

The mysql.createConnection() method is used to interface with the Azure Database for MySQL flexible server instance. The connect() function is used to establish the connection to the server. The query() function is used to execute the SQL query against MySQL database.

const mysql = require('mysql2');
const fs = require('fs');

var config =
{
    host: 'your_server_name.mysql.database.chinacloudapi.cn',
    user: 'your_admin_name',
    password: 'your_admin_password',
    database: 'quickstartdb',
    port: 3306,
    ssl: {ca: fs.readFileSync("your_path_to_ca_cert_file_DigiCertGlobalRootCA.crt.pem")}
};

const conn = new mysql.createConnection(config);

conn.connect(
    function (err) {
    if (err) {
        console.log("!!! Cannot connect !!! Error:");
        throw err;
    }
    else
    {
        console.log("Connection established.");
        queryDatabase();
    }
});

function queryDatabase()
{
    conn.query('DROP TABLE IF EXISTS inventory;',
        function (err, results, fields) {
            if (err) throw err;
            console.log('Dropped inventory table if existed.');
        }
    )
    conn.query('CREATE TABLE inventory (id serial PRIMARY KEY, name VARCHAR(50), quantity INTEGER);',
        function (err, results, fields) {
            if (err) throw err;
            console.log('Created inventory table.');
        }
    )
    conn.query('INSERT INTO inventory (name, quantity) VALUES (?, ?);', ['banana', 150],
        function (err, results, fields) {
            if (err) throw err;
            else console.log('Inserted ' + results.affectedRows + ' row(s).');
        }
    )
    conn.query('INSERT INTO inventory (name, quantity) VALUES (?, ?);', ['orange', 250],
        function (err, results, fields) {
            if (err) throw err;
            console.log('Inserted ' + results.affectedRows + ' row(s).');
        }
    )
    conn.query('INSERT INTO inventory (name, quantity) VALUES (?, ?);', ['apple', 100],
        function (err, results, fields) {
            if (err) throw err;
            console.log('Inserted ' + results.affectedRows + ' row(s).');
        }
    )
    conn.end(function (err) {
        if (err) throw err;
        else  console.log('Done.')
    });
};

Read data

Use the following code to connect and read the data by using a SELECT SQL statement.

The mysql.createConnection() method is used to interface with the Azure Database for MySQL flexible server instance. The connect() method is used to establish the connection to the server. The query() method is used to execute the SQL query against MySQL database. The results array is used to hold the results of the query.

const mysql = require('mysql2');
const fs = require('fs');

var config =
{
    host: 'your_server_name.mysql.database.chinacloudapi.cn',
    user: 'your_admin_name',
    password: 'your_admin_password',
    database: 'quickstartdb',
    port: 3306,
    ssl: {ca: fs.readFileSync("your_path_to_ca_cert_file_DigiCertGlobalRootCA.crt.pem")}
};

const conn = new mysql.createConnection(config);

conn.connect(
    function (err) {
        if (err) {
            console.log("!!! Cannot connect !!! Error:");
            throw err;
        }
        else {
            console.log("Connection established.");
            readData();
        }
    });

function readData(){
    conn.query('SELECT * FROM inventory',
        function (err, results, fields) {
            if (err) throw err;
            else console.log('Selected ' + results.length + ' row(s).');
            for (i = 0; i < results.length; i++) {
                console.log('Row: ' + JSON.stringify(results[i]));
            }
            console.log('Done.');
        })
    conn.end(
        function (err) {
            if (err) throw err;
            else  console.log('Closing connection.')
    });
};

Update data

Use the following code to connect and update the data by using an UPDATE SQL statement.

const mysql = require('mysql2');
const fs = require('fs');

var config =
{
    host: 'your_server_name.mysql.database.chinacloudapi.cn',
    user: 'your_admin_name',
    password: 'your_admin_password',
    database: 'quickstartdb',
    port: 3306,
    ssl: {ca: fs.readFileSync("your_path_to_ca_cert_file_DigiCertGlobalRootCA.crt.pem")}
};

const conn = new mysql.createConnection(config);

conn.connect(
    function (err) {
        if (err) {
            console.log("!!! Cannot connect !!! Error:");
            throw err;
        }
        else {
            console.log("Connection established.");
            updateData();
        }
    });

function updateData(){
       conn.query('UPDATE inventory SET quantity = ? WHERE name = ?', [75, 'banana'],
            function (err, results, fields) {
                if (err) throw err;
                else console.log('Updated ' + results.affectedRows + ' row(s).');
           })
       conn.end(
           function (err) {
                if (err) throw err;
                else  console.log('Done.')
        });
};

Delete data

Use the following code to connect and delete data by using a DELETE SQL statement.

const mysql = require('mysql2');
const fs = require('fs');

var config =
{
    host: 'your_server_name.mysql.database.chinacloudapi.cn',
    user: 'your_admin_name',
    password: 'your_admin_password',
    database: 'quickstartdb',
    port: 3306,
    ssl: {ca: fs.readFileSync("your_path_to_ca_cert_file_DigiCertGlobalRootCA.crt.pem")}
};

const conn = new mysql.createConnection(config);

conn.connect(
    function (err) {
        if (err) {
            console.log("!!! Cannot connect !!! Error:");
            throw err;
        }
        else {
            console.log("Connection established.");
            deleteData();
        }
    });

function deleteData(){
       conn.query('DELETE FROM inventory WHERE name = ?', ['orange'],
            function (err, results, fields) {
                if (err) throw err;
                else console.log('Deleted ' + results.affectedRows + ' row(s).');
           })
       conn.end(
           function (err) {
                if (err) throw err;
                else  console.log('Done.')
        });
};

Clean up resources

To clean up all resources used during this quickstart, delete the resource group using the following command:

az group delete \
    --name $AZ_RESOURCE_GROUP \
    --yes