MySQL - Node.js Syntax

---

---

Node.js is a JavaScript runtime environment that allows developers to run JavaScript code outside of a web browser, enabling server-side scripting.

When we talk about a Node.js MySQL connector, we are referring to a specific library that facilitates communication between a Node.js application and a MySQL database. This connector enables developers to interact with a MySQL database by providing methods and functionalities that simplify tasks like querying, updating, and managing data within the database using JavaScript code. Essentially, it acts as a bridge, allowing Node.js applications to seamlessly connect with and manipulate data stored in a MySQL database.

Installation "mysql" package

To use MySQL with Node.js, you can use the "mysql" package, which is a popular MySQL driver for Node.js. Here are the steps to install Node.js and the MySQL package −

Step 1: Install Node.js

Visit the official Node.js website (https://nodejs.org/) and download the latest version of Node.js for your operating system. Follow the installation instructions provided on the website.

Step 2: Create a Node.js Project

Create a new directory for your Node.js project and navigate to it using your terminal or command prompt.

mkdir mynodeproject
cd mynodeproject

Step 3: Initialize a Node.js Project

Run the following command to initialize a new Node.js project. This will create a 'package.json' file.

npm init -y

Step 4: Install the MySQL Package

Install the "mysql" package using the following command:

npm install mysql

Step 5: Create a JavaScript File

Create a JavaScript file (e.g., app.js) in your project directory.

Step 6: Run the Node.js Script

Run your Node.js script using the following command:

node app.js

Now, you have successfully installed the MySQL Node.js connector (mysql package) for your Node.js project.

NodeJS Functions to Access MySQL

In Node.js, the "mysql" package provides a set of functions to interact with MySQL databases. Here are some of the major functions you can use−

S.No	Function & Description
1	createConnection(config) Creates a new MySQL connection.
2	connect(callback) Establishes a connection to the MySQL server.
3	query(sql, values, callback) Executes a SQL query on the connected MySQL database. You can provide placeholders in the SQL query and pass values as an array to replace the placeholders.
4	execute(sql, values, callback) Similar to the query function, but specifically designed for executing non-select queries (e.g., INSERT, UPDATE, DELETE).
5	beginTransaction(callback) Starts a new transaction.
6	commit(callback) Commits the current transaction.
7	rollback(callback) Rolls back the current transaction.
8	end() Closes the MySQL connection.

Basic Example

Following are the steps to connect and communicate with a MySQL database using Node.js −

• Download and install Node.js
• Create a new directory, navigate to it, and run 'npm init -y'.
• Run 'npm install mysql'.
• Create a JavaScript file (e.g., app.js) and use the "mysql" package to connect to the MySQL database.
• Use the query or execute functions to perform SQL queries on the database.
• Implement error handling for database operations. Close the database connection when finished.
• Execute your Node.js script with node app.js.

The following example shows a generic syntax of NodeJS to call any MySQL query.

const mysql = require("mysql2");

// Create a connection to the MySQL database
const connection = mysql.createConnection({
  host: 'your-mysql-hostname',
  user: 'your-mysql-username',
  password: 'your-mysql-password',
  database: 'your-mysql-database',
});

// Connect to the database
connection.connect((err) => {
  if (err) {
    console.error('Error connecting to MySQL:', err);
    return;
  }
  console.log('Connected to MySQL database');
  
  // Perform MySQL operations here
  connection.query("SELECT * FROM your_table", (err, results) => {
    if (err) throw err;
    console.log('Query result:', results);
  });

  // Close the connection when done
  connection.end((err) => {
    if (err) console.error('Error closing MySQL connection:', err);
    else console.log('Connection closed');
  });
});