Hello Everyone in this tutorial i will show you how to use MySQL database with NodeJS. Here We will be using “mysql” module you can download or install it using this command.

 npm install mysql 

MySQL is free and open source database management software which uses SQL(Structured Query Language) which is widely used and quite popular in Websites to store information and make robust dynamic websites.

NodeJS with MySQL is very useful as it can be used in various NodeJs applications like chat or games etc.

So let’s get started. First include the mysql module and create a connection and provide the user and password and database, leave the password empty if you do not have one. We will be using simple connections. This modules also supports connection pooling in order to manage multiple connections.


var mysql = require("mysql");
var conn = mysql.createConnection({
"host":"localhost",
"user":"root",
"password":"",
"database":"test"
});
conn.connect();

So now we have connected to our MySQL server, so now we can execute queries.

var query = "select * from users"
conn.query(query,function(err,rows,fields){
if(err){
console.log(err)};
console.log(rows); // will print all the rows in form of associative array
conn.end(); // to finally terminate the connection.
});

In case of Prepared Statements we pass the values in the form of array and query with “?” and later we provide the values.

var query = "select * from users where username=? and password=?";
conn.query(query,["username","password"],function(err,rows,fields){
if(err){
console.log(err)};
console.log(rows); // will print all the rows in form of associative array
conn.end(); // to finally terminate the connection.
});

Connection Pooling:

<pre><span class="pl-k">var</span> mysql <span class="pl-k">=</span> <span class="pl-c1">require</span>(<span class="pl-s"><span class="pl-pds">'</span>mysql<span class="pl-pds">'</span></span>);
<span class="pl-k">var</span> pool  <span class="pl-k">=</span> <span class="pl-smi">mysql</span>.<span class="pl-en">createPool</span>({
  connectionLimit <span class="pl-k">:</span> <span class="pl-c1">10</span>,
  host            <span class="pl-k">:</span> <span class="pl-s"><span class="pl-pds">'localhost</span><span class="pl-pds">'</span></span>,
  user            <span class="pl-k">:</span> <span class="pl-s"><span class="pl-pds">'root</span><span class="pl-pds">'</span></span>,
  password        <span class="pl-k">:</span> <span class="pl-s"><span class="pl-pds">'</span><span class="pl-pds">'</span></span>,
  database        <span class="pl-k">:</span> <span class="pl-s"><span class="pl-pds">'test</span><span class="pl-pds">'</span></span>
});

<span class="pl-smi">pool</span>.<span class="pl-en">query</span>(<span class="pl-s"><span class="pl-pds">'</span>SELECT 1 + 1 AS solution<span class="pl-pds">'</span></span>, <span class="pl-k">function</span>(<span class="pl-smi">err</span>, <span class="pl-smi">rows</span>, <span class="pl-smi">fields</span>) {
  <span class="pl-k">if</span> (err) <span class="pl-k">throw</span> err;

  <span class="pl-en">console</span>.<span class="pl-c1">log</span>(<span class="pl-s"><span class="pl-pds">'</span>The solution is: <span class="pl-pds">'</span></span>, rows[<span class="pl-c1">0</span>].<span class="pl-smi">solution</span>);
});</pre>

In order to avoid SQL Injection attacks, you should always escape any user provided data before using it inside a SQL query. You can do so using the mysql.escape(), connection.escape() or pool.escape() methods:

var userId = 'some user provided value';
var sql    = 'SELECT * FROM users WHERE id = ' + connection.escape(userId);
connection.query(sql, function(err, results) {
  // ...
});

Bonus:

  1. Use Object.keys(rows).lengthto find the number of rows.
  2. Use
    rows.insertId

    in order to get the id of data last inserted.

  3. Use
    rows.affectedRows/code]</pre>
    in order to get rows affected by the query.</li>
    	<li>Use
    <pre>rows.changedRows

    in order to get the number of rows changed due to update query.

  4. For connection ID use
    connection.connect(function(err) {
      if (err) throw err;
      console.log('connected as id ' + connection.threadId);
    });

 

Advertisements