access database from Javascript , query database with Javascript , connect database in Javascript , connect to database with Javascript , accessing database with Javascript

Javascript is one of the most popular programming languages , as all other languages like PHP , C# , Python they all provides driver to connect with Mysql Server . And unlike the MongoDB database Mysql also provides it’s module for Nodejs to connect with , as Mysql is also a popular relational database so there’s need to be support for Nodejs .

Mysql’s module mysql i.e., driver for Nodejs helps Nodejs developer to connect with Mysql server , it’s a very easy library to use and implement .

In this article you will learn to connect with Mysql database using Javascript Nodejs in 3 simple steps .

Requirements

We need the below two requirements in order to make connection with Mysql database using Javascript Nodejs .

1 . Mysql Server (we are using XAMPP )

2 . Nodejs installed

Step 1 : Install Mysql Nodejs library

To install the mysql library you need to have Nodejs installed in your system . Run the following command to install mysql library .

npm install mysql

After installation you need to require this module in your main file before making connection to database .

Note : Don’t forgot to start Mysql server before making a connection unless you will face an error.

Step 2 : Make connection to database and execute queries

After successful installation of library you can make connection to database . You can make connection to database in 2 ways

1 . Simple connection

2 . Pooled connection

Simple connection

In simple connection you will use the basic components to make a connection to Mysql database . First of all you need to require the mysql library in file and then with createConnection() pass all the required parameters i.e., host , user , password , database as shown in below example .

var mysql = require('mysql'); // requiring nodejs mysql library

var connection = mysql.createConnection({ // creating database connection
    host     : 'localhost',
    user     : 'root',
    password : '',
    database : 'users_db'
})

connection.connect();

connection.query("SELECT * FROM users",(err,results,fields) => {
    if(err) throw err; // if any error occurs then it will stop here 

    if(results){
        console.log(results);  //showing all the result after successful execution of query
        connection.destroy(); //closing the connection 
    }
})

After passing all parameters in createConnection() method , call the connect() method to connect with database , and for executing queries on database query() method will be used .

query() method has an inbuilt function which accepts 3 arguments error , result and fields . If any error occurs then it will be stored in error argument . After successful execution of query result will be store in result variable and fields variable is storing all the information of table , permission and users.

After successful execution of queries you need to close the connection by using destroy() method .

Pooled connection

Pooled connection works same as Simple connection but it has the capability to manage multiple connections at a time .

var mysql = require('mysql'); // requiring nodejs mysql library

var pool  = mysql.createPool({
    connectionLimit : 10,
    host            : 'localhost',
    user            : 'root',
    password        : '',
    database        : 'users_db'
  });

// use getConnection to handle errors and connection at the same time 

pool.getConnection((err,connection) => {

    if(err) console.log(err.stack); //handle errors that may occur while making connection 

    connection.query("SELECT * FROM users",(err,results,fields) => {
        console.log(results);
        connection.destroy(); //destroying a single connection 
    })
})


// you can also use the direct query() method as shown below on pool to execute query

pool.query("SELECT * FROM users",(err,result,fields) => {
    if(err){
        console.log(err); //handle errors that may occur while executing queries
    }else{
        console.log(result);
        console.log(fields);
        pool.end(); // destroying the pool 
    }
});

We’ve used the end() method on pool to destroy all connection of pool , perhaps we can also destroy a single connection by using destroy() method on connection property of getConnection() method .

Step 3 : Executing main file

After writing all the code in a separate file you need to execute that file using below command . 

node your_file_name 

// example : node index.js

Additional

You can use wild cards to generate maintainable code .

connection.query('SELECT * FROM users WHERE id = ?', ['2'], function (error, results, fields) {

    // handle errors with error variable 
    // handle results stored in results variable
    // check information about return result with fields variable

});

You can use url to make direct connection with mysql database , you can check this page for more info .

var connection = mysql.createConnection('mysql://user:[email protected]/db?debug=true&charset=BIG5_CHINESE_CI&timezone=-0700');

Now in order to prevent execution of malicious queries , you may use connection.escape() , pool.escape() or mysql.escape() method .

var query = 'SELECT * FROM users WHERE id = ' + connection.escape(userId);

So that’s all on how to connect to database with javascript .

You can Follow me on Linkedin : Anand Raj

Join our page on Linkedin Be Practical

Join Our Telegram community of developers over here

Interested in Firebase ? Checkout this super easy course Firebase Chat application Development with Javascript and Jquery by clicking here

Next Article : Learn to implement Google Maps in your site using javascript

Thanks for reading ✌