NodeJS MySQL Basic

Prepare MySQL in Linux

Install mysql server

apt-get install mysql-server

You can get mysql-client etc…. And you need to set password to access.
Access check

mysql -uroot -ppassword

root is your user, password is your password.

Others, if you want to manipulate data using GUI, you also install GUI tool(MySQL workbench).

To prepare npm, or other nodejs related, please refer this

Package install in Express

Create express project

This is general operation

express myapp

Install mysql package

package is managed by packge.json
Example

"dependencies": {
  "mysql": "*"
}

Add mysql package.

Prepare data

Create database

mysql> create database mynavi_db;

Create table

CREATE TABLE `mynavi_db`.`mydata` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) NULL,
  `mail` VARCHAR(255) NULL,
  `tel` VARCHAR(45) NULL,
  PRIMARY KEY(`id`)
);

Sample Code(only connection)

exports.index = function(req, res) {
  var mysql = require('mysql');
  var connection = mysql.createConnection({
    host : 'localhost',
    user : 'root',
    password : 'kiririn',
    database : 'mynavi_db'
  });
  // Establish connection
  connection.connect(function(err) {
    if (err) {
      connection.err('Error :' + err.stack);
      return;
    }
    // connection.threadId
    connection.query('select * from mydata', function(err, rows) {
        // connected
    });
  });
};

// connection.end();

Sample Code(with Pool)

You can check mysql package

index.js

I implemented operations in get function. The routing is ‘/’.

exports.index = function(req, res) {
  var mysql = require('mysql');
  var pool = mysql.createPool({
    host : 'localhost',
	user : 'root',
	password : 'password',
	database : 'mynavi_db'
  });
  
  pool.getConnection(function(err, connection) {
    if (err) {
      connection.release();
      console.log(err);
      res.redirect('/'); 
    }
    else {
      connection.query("SELECT * FROM mydata", function(err, rows) {
        connection.release();
        res.render('index', {
          title: 'Express',
	  msg : 'My Data List',
          datas : rows
	});
       
      });
    }
  });
};

rows are query result.

Topic for node mysql

node mysql page has good
examples you want.

Connection with connection string

var connection = mysql.createConnection('mysql://user:pass@host/db');

Terminate connection

Escape query

escape connection.escape(), pool.escape()

Transaction

connection.beginTransaction(function(err) {
  if (err) { throw err; }
  connection.query('INSERT INTO book SET title=?', title,function(err, result) {
    if (err) {
      connection.rollback(function() {
        throw err;
      });
    }
	
    // Next
    connection.query('INSERT INTO log SET data=?', log, function(err, result) {
      if (err) {
        connection.rollback(function() {
	  throw err;
        });
      }
    });
    connection.commit(function(err) {
      if (err) {
        connection.rollback(function() {
          throw err;
        });
      }
      console.log('success');
    });
  });
});

Error handling

err is JavaScript Error object
err.code : MySQL server error
err.fatal : fatal or not

Ref

node mysql
qiita