MySQL integration All Versions

Examples

• 9

  Connect to MySQL
  9

  One of the easiest ways to connect to MySQL is by using mysql module. This module handles the connection between Node.js app and MySQL server. You can install it like any other module:

  npm install --save mysql
  

  Now you have to create a mysql connection, which you can later query.

  var mysql      = require('mysql');
  var connection = mysql.createConnection({
    host     : 'localhost',
    user     : 'me',
    password : 'secret',
    database : 'database_schema'
  });
  
  connection.connect();
  
  // Execute some query statements
  // I.e. SELECT * FROM FOO
  
  connection.end();
  

  In the next example you will learn how to query the connection object.

  edited Dec 14 '16 at 15:08

  

  

  

  
  edit share view discussion request improvement

  edit move example delete example
• 6

  Query a connection object with parameters
  6

  When you want to use user generated content in the SQL, it with done with parameters. For example for searching user with the name aminadav you should do:

  var username = 'aminadav';
  var querystring = 'SELECT name, email from users where name = ?'; 
  connection.query(querystring, [username], function(err, rows, fields) {
    if (err) throw err;
    if (rows.length) {
      rows.forEach(function(row) {
        console.log(row.name, 'email address is', row.email);
      });
    } else {
      console.log('There were no results.');
    }
  });
  

  edited Jul 26 '16 at 17:32

  

  
  edit share view discussion request improvement

  edit move example delete example
• 5

  Using a connection pool
  5

  a. Running multiple queries at same time

  All queries in MySQL connection, are done one after another. It mean that if you want to do 10 queries, and each query takes 2 seconds, it will take 20 seconds to complete. The solution is to create 10 connection and run each query in a different connection. This can be done automatically using connection pool

  var pool  = mysql.createPool({
    connectionLimit : 10,
    host            : 'example.org',
    user            : 'bobby',
    password        : 'pass',
    database        : 'schema'
  });
  
  for(var i=0;i<10;i++){
    pool.query('SELECT ` as example', function(err, rows, fields) {
      if (err) throw err;
      console.log(rows[0].example); //Show 1
    });
   }
  

  It will run all the 10 queries in parallel.

  When you use pool you don't need the connection any more. You can query directly the pool. MySQL module will search for the next free connection to execute your query.

  b. Achieving multitenancy on database server with different databases hosted on it.

  Multitenancy is common requirement of enterprise application nowadays and creating connection pool for each database in database server is not recommended. so, what we can do instead is create connection pool with database server and than switch between databases hosted on database server on demand.

  Suppose our application has different databases for each firm hosted on database server. We will connect to respective firm database when user hits the application. here is the example on how to do that:-

  var pool  = mysql.createPool({
        connectionLimit : 10,
        host            : 'example.org',
        user            : 'bobby',
        password        : 'pass'
      });
      
  pool.getConnection(function(err, connection){
      if(err){
          return cb(err);
      }
      connection.changeUser({database : "firm1"});
      connection.query("SELECT * from history", function(err, data){
          connection.release();
          cb(err, data);
      });
  });
  

  Let me break down the example:-

  When defining pool configuration i did not gave the database name but only gave database server i.e

  {
    connectionLimit : 10,
    host            : 'example.org',
    user            : 'bobby',
    password        : 'pass'
  }
  

  so when we want to use the specific database on database server, we ask the connection to hit database by using:-

      connection.changeUser({database : "firm1"});
  

  you can refer the official documentation here

  edited Sep 11 '16 at 3:30

  

  
  edit share view discussion request improvement

  edit move example delete example
• 4

  Query a connection object without parameters
  4

  You send the query as string, and get a callback with the answer. The callback give your error, array of rows and fields. Each rows, contain all the column of the returned table.

  connection.query('SELECT name,email from users', function(err, rows, fields) {
    if (err) throw err;
  
    console.log('There are:', rows.length,' users);
    console.log('First user name is:',rows[0].name)
  });
  

  created Jul 21 '16 at 19:29

  
  edit share view discussion request improvement

  edit move example delete example
• 1

  Run a number of queries with a single connection from a pool
  1

  There may be situations where you have setup a pool of MySQL connections, but you have a number of queries you would like to run in sequence:

  SELECT 1;
  SELECT 2;
  

  You could just run then using pool.query as seen elsewhere, however if you only have one free connection in the pool you must wait until a connection becomes available before you can run the second query.

  You can, however, retain an active connection from the pool and run as many queries as you would like using a single connection using pool.getConnection:

  pool.getConnection(function (err, conn) {
    if (err) return callback(err);
  
    conn.query('SELECT 1 AS seq', function (err, rows) {
      if (err) throw err;
  
      conn.query('SELECT 2 AS seq', function (err, rows) {
        if (err) throw err;
  
        conn.release();
        callback();
      });
    });
  });
  

  Note: You must remember to release the connection, otherwise there is one less MySQL connection available to the rest of the pool!

  For more information on pooling MySQL connections check out the MySQL docs.

  created Jul 22 '16 at 12:43

  
  edit share view discussion request improvement

  edit move example delete example
• 0

  Return the query when an error occurs
  0

  You can attach the query executed to your err object when an error occurs:

  var q = mysql.query('SELECT `name` FROM `pokedex` WHERE `id` = ?', [ 25 ], function (err, result) {
    if (err) {
      // Table 'test.pokedex' doesn't exist
      err.query = q.sql; // SELECT `name` FROM `pokedex` WHERE `id` = 25
      callback(err);
    }
    else {
      callback(null, result);
    }
  });
  

  created Jul 22 '16 at 12:43

  
  edit share view discussion request improvement

  edit move example delete example