Code360 powered by Coding Ninjas X Naukri.com. Code360 powered by Coding Ninjas X Naukri.com
Table of contents
1.
Introduction
2.
Why Use MySQL in Node.js?
3.
How to Use MySQL in Node.js?
3.1.
Installation
3.2.
Connect with MySQL
3.3.
Running Queries from Node.js
4.
Frequently Asked Questions
4.1.
What is MySQL?
4.2.
How can we use MySQL in Node.js?
4.3.
What makes MySQL a good choice to be used with Node.js?
4.4.
How can we run queries from the MySQL module in npm?
5.
Conclusion
Last Updated: Mar 27, 2024
Easy

Node.js MySQL

Author Gazal Arora
0 upvote
Master Python: Predicting weather forecasts
Speaker
Ashwin Goyal
Product Manager @

Introduction

Node.js is a powerful and popular back-end runtime environment that executes JavaScript code. It is used for running JavaScript code outside of the client’s browser. MySQL is a free and open-source Relational Database that is currently developed by Oracle. 

In this article, we will discuss how to use MySQL in Node.js and what are the steps to install, connect and run MySQL queries in Node.js.

Node.js MySQL

Read about Database Integration in Express

Why Use MySQL in Node.js?

  • MySQL is written in C and C++ so that makes it a very powerful Database
     
  • MySQL is open source so you don’t have to pay anything for using it
     
  • Node.js has a module for handling MySQL queries directly
Get the tech career you deserve, faster!
Connect with our expert counsellors to understand how to hack your way to success
User rating 4.7/5
1:1 doubt support
95% placement record
Akash Pal
Senior Software Engineer
326% Hike After Job Bootcamp
Himanshu Gusain
Programmer Analyst
32 LPA After Job Bootcamp
After Job
Bootcamp

How to Use MySQL in Node.js?

Let's learn how to use MySQL in Node.js:

Installation

The first step to use MySQL in Node.js is to install both Node.js and MySQL on your computer. You can refer to these links to install Node.js:

To install MySQL on your computer, refer to this article here.

The next step is to download the MySQL module available through the npm registry. To do so run the following command

npm install mysql

Connect with MySQL

To use MySQL from node.js the first thing we would need to do is to create a connection to MySQL. Create a .js file on your system and run the below code after changing the MySQL credentials according to your setup:

var mysql      = require('mysql');
var connection = mysql.createConnection({
  host     : 'localhost',
  user     : 'admin',
  password : 'password',
  database : 'test'
});
connection.connect(function(error) {
    if (error) throw error;
    console.log('MySQL connection successfully established');
});

Running Queries from Node.js

The skeleton code to run queries on MySQL database from Node.js is as follows:

connection.connect(function(error) {
    if (error) throw error;
    console.log('MySQL connection successfully established');
    connection.query(sql, function(err,result) {
        if(err) throw err;
        console.log(“Result = “ + result);
    }
});

The Below Sample Code depicts how to create a table, perform some DML operations (Insert, update and delete) on the table created
var mysql      = require('mysql');
var connection = mysql.createConnection({
  host     : 'localhost',
  user     : 'admin',
  password : 'password'
});

let create_database = "create database if not exists test;"
let create_table = "create table if not exists test.t1 (c1 int);"
let show_create_table = "show create table test.t1;"
let insert_rows = "insert into test.t1(c1) values (1),(2),(3);"
let update_row = "update test.t1 set c1 = 5 where c1 = 2;"
let delete_row = "delete from test.t1 where c1 = 3;"
let select = "select * from test.t1;"

connection.connect(function(error) {
    if (error) throw error;
    console.log('MySQL connection successfully established');
    connection.query(create_database, function(err,result) {
        if(err) throw err;
        console.log("Database creation Result = " + JSON.stringify(result));
    });
    
    connection.query(create_table, function(err,result) {
        if(err) throw err;
        console.log("Table Creation Result = " + JSON.stringify(result));
    });
    
    connection.query(show_create_table, function(error,res) {
        if(error) throw error;
        console.log("Show Create Table output: " + JSON.stringify(res));

    });

    connection.query(insert_rows, function(err,result) {
        if(err) throw err;
        console.log("Result = " + JSON.stringify(result));
    });

    connection.query(update_row, function(err,result) {
        if(err) throw err;
        console.log("Result = " + JSON.stringify(result));
    });

    connection.query(delete_row, function(err,result) {
        if(err) throw err;
        console.log("Result = " + JSON.stringify(result));
    });

    connection.query(select, function(err,result) {
        if(err) throw err;
        console.log("Result = " + JSON.stringify(result));
    });
});


Output

output

Explanation

We first created a MySQL connection through which we will run all the queries. We did this with the help of the createConnection call in the MySQL npm module. After that using some predefined strings for creating a database, creating a table, and a few DML operations we used the query function in the MySQL module to first create a database and a table in it and then ran insert, update, delete and select on the table that we created.

Frequently Asked Questions

What is MySQL?

MySQL is a free and open-source relational database written in C/C++. It is currently the most popular open-source SQL database Management system which is maintained and developed by Oracle.

How can we use MySQL in Node.js?

To use MySQL in Node.js, we can install the already available module in the npm registry which is called by the name ‘mysql’. It has vast features and is easy to install directly through the npm install command.

What makes MySQL a good choice to be used with Node.js?

MySQL is a good database choice as It is easy to use, fast, and reliable. It is open source and thus free to use. MySQL is a secure database due to its solid data security layer that encrypts data and prevents data breaches. It has a large community and is widely adapted in the industry.

How can we run queries from the MySQL module in npm?

To run queries from the MySQL module in npm, we have to use the function query which takes the argument of the SQL command. It returns an error if there was any issue in the execution otherwise it returns the result of the query which can be used to analyze the data.

Refer to know about :  Update Query in MySQL

Conclusion

In this article, we learned how to use MySQL in Node.js. We also learnt how to create a database, create a table and run DML operations like insert, update, delete, and select on the create table.

Refer here to learn more about MySQL.

You can refer to our guided paths on the Coding Ninjas. You can check our course to learn more about DSADBMSCompetitive ProgrammingPythonJavaJavaScript, etc. 

Also, check out some of the Guided Paths on topics such as Data Structure and AlgorithmsCompetitive ProgrammingOperating SystemsComputer Networks, DBMS, and System Design, etc. as well as some Contests, Test SeriesInterview Bundles, and some Interview Experiences curated by top Industry Experts.

 

Happy Learning!

Previous article
Introduction to Node.js
Next article
Node.js vs Ruby on Rails
Live masterclass