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