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

Article
04/12/2024

Caution

This article references CentOS, a Linux distribution that is nearing End Of Life (EOL) status. Please consider your use and planning accordingly. For more information, see the CentOS End Of Life guidance.

APPLIES TO: Azure Database for MySQL - Single Server

Important

Azure Database for MySQL single server is on the retirement path. We strongly recommend that you upgrade to Azure Database for MySQL flexible server. For more information about migrating to Azure Database for MySQL flexible server, see What's happening to Azure Database for MySQL Single Server?

In this quickstart, you connect to an Azure Database for MySQL 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.

Prerequisites

An Azure account with an active subscription. Create a trial account.
An Azure Database for MySQL server. Create an Azure Database for MySQL server using Azure portal or Create an Azure Database for MySQL server using Azure CLI.

Important

Ensure the IP address you're connecting from has been added the server's firewall rules using the Azure portal or 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 the 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 may 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
 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.

CentOS 7.x
```
sudo yum install -y rh-nodejs8
scl enable rh-nodejs8 bash
```
CentOS 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 may 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 may 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 may vary as new patches are released.

Get connection information

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

Log 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.

Running 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 server and database.
Obtain SSL certificate: Download the certificate needed to communicate over SSL with your Azure Database for MySQL server from https://dl.cacerts.digicert.com/DigiCertGlobalRootCA.crt.pem and save the certificate file to your local drive.
In the ssl config option, replace the ca-cert filename with the path to this local file.
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 may 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 MySQL server. 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: 'mydemoserver.mysql.database.chinacloudapi.cn',
    user: 'myadmin@mydemoserver',
    password: 'your_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', 154],
            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 MySQL server. 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: 'mydemoserver.mysql.database.chinacloudapi.cn',
    user: 'myadmin@mydemoserver',
    password: 'your_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 data by using an UPDATE SQL statement.

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

var config =
{
    host: 'mydemoserver.mysql.database.chinacloudapi.cn',
    user: 'myadmin@mydemoserver',
    password: 'your_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 = ?', [200, '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: 'mydemoserver.mysql.database.chinacloudapi.cn',
    user: 'myadmin@mydemoserver',
    password: 'your_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

Next steps

Migrate your database using Export and Import