MySQL integration All Versions

Examples

• 3

  Connect to MySQL

  edit move example delete share

  3

  One of the easiest ways to connect to MySQL is by using mysql module. This module handle the connection between Node.JS app and MySQL server. For installing the you have to install it like any other module:

  npm install --save mysql
  

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

  var mysql      = require('mysql');
  var connection = mysql.createConnection({
    host     : 'localhost',
    user     : 'me',
    password : 'secret',
    database : 'database_schema'
  });
  
  connection.connect();
  

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

  edited Jul 21 at 22:21

  

  
• 1

  Query a connection object with parameters

  edit move example delete share

  1

  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 yesterday

  

  
• 1

  Query a connection object without parameters

  edit move example delete share

  1

  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 at 19:29

  
• 0

  Return the query when an error occurs

  edit move example delete share

  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 at 12:43

  
• 0

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

  edit move example delete share

  0

  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 at 12:43

  
• 0

  Using a connection pool

  edit move example delete share

  0

  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.

  created Jul 21 at 19:29