Node.js MySQL

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:

• Linux: Installing Node.js on Linux
• Windows: Installing Node.js on Windows
   

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

You can also try this code with Online Javascript Compiler

Run Code

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');
});

You can also try this code with Online Javascript Compiler

Run Code

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));
    });
});

You can also try this code with Online Javascript Compiler

Run Code

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 DSA, DBMS, Competitive Programming, Python, Java, JavaScript, etc. 

Also, check out some of the Guided Paths on topics such as Data Structure and Algorithms, Competitive Programming, Operating Systems, Computer Networks, DBMS, and System Design, etc. as well as some Contests, Test Series, Interview Bundles, and some Interview Experiences curated by top Industry Experts.

Happy Learning!