Connect MySQL With Node App

MySQL is an open-source traditional database based on a relational database management system. Unlike NoSQL databases, MySQL stores the data with the help of tables, rows, and columns.

There are following steps to connect MySQL with Node JS:

Connecting MySQL Database using Node JS

• Step 1: Creating a Node Project
• Step 2: Installing MySQL Node JS Driver
• Step 3: Setting up MySQL Database Connection using Node JS
• Step 4: Closing MySQL Database Connection

Creating a Node Project

• First, We will open the command line tool and create a folder using the command mkdir mysql-with-node-app. Then, We will open the folder mysql-with-node-app in the Visual Studio Code using the following commands:

cd mysql-with-node-app
code .

• Then, We initiate a node project using the command npm init -y. The command npm init -y will craete a file package.json inside the folder mysql-with-node-app.

Installing MySQL Node JS Driver

• We install the Node JS driver mysql with the following command:

npm install mysql --save

• Now, We will start the MySQL Service. Then, We log in to MySQL database with the following command:

mysql -u root -p
Enter password: **********

• We need a database schema to use the MySQL Database server with Node JS. So, We will create a database schema named library-db using the command:

CREATE DATABASE library_db;

• Now, We will check whether database library_db is created with the command:

mysql> SHOW DATABASES;

• The above command will display an output similar to this:

+--------------------+
| Database           |
+--------------------+
| library_db         |
| sys                |
| world              |
+--------------------+

Setting up MySQL Database Connection using Node JS

• First, We create a file server.js and require the mysql module in the server.js file as show below:

const mysql = require('mysql')

• Then, We initiate the connection to the MySQL database using the createConnection() method. The createConnection() method accepts the database credentials (i.e. host, user, password, etc).

const initMysqlConnection = mysql.createConnection({
	host: 'localhost',
	user: 'root',
	password: '',
	database: 'library_db',
})

• Now, >code We will establish the connection between the Node JS application and MySQL Database using the connect() method.

// Connect myS:QL with node app
> initMysqlConnection.connect((error) => {

	if (error) {
	 console.log(error)
	}

	console.log('Connected to the MySQL database')
})

• Next, We will run the command node server.js and get the following output:

Connected to the MySQL database

Closing MySQL Database Connection

• We can close the connection between the Node JS application and MySQL server using the end() method. The end method allows the execution of queries that are already in the queue before ending the connection.

connectMysql.end((err) => {
	if (err) {
		console.log(error)
	}
	console.log('Connection closed')
})

• We can close the connection forcefully with destroy() method. The destroy method ends the connection immediately as well as all events and callback triggered during the connection.

connectMysql.destroy()

Creating MySQL Tables Using Node JS

• First, We will create a file createTable.js inside the folder mysql-with-node-app. Then, we will write an SQL string to create a table in the MySQL database:

// Creating table in mySQL database
const books = `CREATE TABLE IF NOT EXISTS books
    (id INT PRIMARY KEY AUTO_INCREMENT,
    book_title VARCHAR(255) NOT NULL)`

• Now, We will execute SQL string books using query() method that will create a table named books in the database library_db:

// Execute SQL string `books`
initMysqlConnection.query(books, (error, results, fields) => {
	if (error) {
		console.log(error)
	}
	console.log('Successfully created the table')
})

// Close the connection
initMysqlConnection.end()

• Next, We will run the command node createTable.js and verify whether the table books is created using the SQL commands:

mysql> USE library_db;
mysql> SHOW TABLES;

• The SQL command SHOW TABLES will display the following output:

+----------------------+
| Tables_in_library_db |
+----------------------+
| books                |
+----------------------+

Inserting Data in MySQL Tables using Node JS

There are the following methods to insert a row in MySQL Table:

• Inserting One Row in MySQL Table
• Inserting Multiple Rows in MySQL Table

Inserting One Row in MySQL Table

• We will use the INSERT INTO statement to insert a row in the table books. Let's create a file insertRow.js inside the folder mysql-with-node-app.

• Now, we will add the following code block to the file insertRow.js:

const mysql = require('mysql')

const initMysqlConnection = mysql.createConnection({
	host: 'localhost',
	user: 'root',
	password: '',
	database: 'library_db',
})

// SQL string to insert a row in the table `books`
const insertStatement = `INSERT INTO books
    (book_title)
    VALUES
    ("Learn MySQL")`

// Execute SQL string `insertStatement`
initMysqlConnection.query(insertStatement)

// Close the connection
initMysqlConnection.end()

• Next, we insert a row using the command node insertRow.js.

Inserting Multiple Rows in MySQL Table

• Again, We will use INSERT INTO statement to insert multiple rows the table books. Let's create a file insertMultipleRows.js inside the folder mysql-with-node-app.

• Now, we will add the following code block to the file insertMultipleRows.js:

const mysql = require('mysql')

const initMysqlConnection = mysql.createConnection({
	host: 'localhost',
	user: 'root',
	password: 'Root@12345',
	database: 'library_db',
})

// SQL string to insert rows in the table `books`
const insertStatement = `INSERT INTO books
    (book_title)
    VALUES ? `

const books = [['Learn Node JS'], ['Learn JavaScript']]

// Execute SQL string `insertStatement`
initMysqlConnection.query(
	insertStatement,
	[books],
	(error, results, fields) => {
		if (error) {
			console.log(error)
		}
		console.log(`Successfully inserted ${results.affectedRows} rows`)
	},
)

// Close the connection
initMysqlConnection.end()

• In the above code block, we used unnamed parameter ? as values with INSERT INTO statement. When we execute insertStatement using query() method, an array of arrays named books will replace the unnamed parameter ?.
• Now, we will run the command node insertMultipleRows.js to insert rows.

// Execute SQL string `insertStatement`
initMysqlConnection.query(
	insertStatement,
	[books],
	(error, results, fields) => {
		if (error) {
			console.log(error)
		}
		console.log(`Successfully inserted ${results.affectedRows} rows`)
	},
)

// Close the connection
initMysqlConnection.end()

Conclusion

• We use mysql Node JS driver to connect MySQL with Node JS.
• We can install mysql Node JS driver using the command npm install mysql.
• createConnection() method helps to configure the MySQL database with Node JS.
• We use connect() method to connect MySQL with Node JS
• We use .end() method to close the connection smoothly and .destroy() method to close the connection forcefully.
• .query() method helps to execute MySQL statements and MySQL Queries.