title

description

author

ms.author

ms.reviewer

ms.date

ms.service

ms.subservice

ms.topic

ms.custom

ms.devlang

Quickstart: Connect using Node.js

This quickstart provides several Node.js code samples you can use to connect and query data from Azure Database for MySQL - Flexible Server.

shreyaaithal

shaithal

maghan

06/18/2024

mysql

flexible-server

quickstart

mvc

devx-track-js

mode-api

linux-related-content

javascript

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

[!INCLUDEapplies-to-mysql-flexible-server]

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:

Create an Azure Database for MySQL flexible server instance using Azure portal
Create an Azure Database for MySQL flexible server instance 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.

Windows

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 may vary as new patches are released.

Linux (Ubuntu/Debian)

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.

Linux (RHEL)

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 may vary as new patches are released.

Linux (SUSE)

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.

macOS

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 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. :::image type="content" source="./media/how-to-connect-tls-ssl/download-ssl.png" alt-text="Screenshot showing how to download public SSL certificate from Azure portal." lightbox="./media/how-to-connect-tls-ssl/download-ssl.png":::

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 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 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.azure.com',
    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.azure.com',
    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.

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.

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

var config =
{
    host: 'your_server_name.mysql.database.azure.com',
    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.

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.

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

var config =
{
    host: 'your_server_name.mysql.database.azure.com',
    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

Next steps

Encrypted connectivity using Transport Layer Security (TLS 1.2) in Azure Database for MySQL flexible server.
Learn more about Networking in Azure Database for MySQL flexible server.
Create and manage Azure Database for MySQL flexible server firewall rules using the Azure portal.
Create and manage an Azure Database for MySQL flexible server virtual network using Azure portal.

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

connect-nodejs.md

connect-nodejs.md

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

Prerequisites

Install Node.js and the MySQL connector

Windows

Linux (Ubuntu/Debian)

Linux (RHEL)

Linux (SUSE)

macOS

Get connection information

Run the code samples

Connect, create table, and insert data

Read data

Update data

Delete data

Clean up resources

Next steps

Files

connect-nodejs.md

Latest commit

History

connect-nodejs.md

File metadata and controls

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

Prerequisites

Install Node.js and the MySQL connector

Windows

Linux (Ubuntu/Debian)

Linux (RHEL)

Linux (SUSE)

macOS

Get connection information

Run the code samples

Connect, create table, and insert data

Read data

Update data

Delete data

Clean up resources

Next steps