Here we will setup the node.js server to connect to a MySQL database.

Using a Putty / SSH terminal window on the Linux node.js server first go to your working directory for your node-js application then install the mysql module using npm as shown below. Details of the mysql package we are using are found at https://www.npmjs.com/package/mysql

It is important to note npm install just installs the package to a directory called /node_modules/ within your current working directory. Since we don’t want a package that may be modified or removed to adversely affect our application we will install the package directly in the /node application working directory.

cd /node
npm install mysql

Then using Sublime or text editor create a test-mysql.js file with the below code. Next using Filezilla or a SSH FTP client upload the file to the /vnode directory.

const readline = require('readline');
const rl = readline.createInterface({
input: process.stdin,
output: process.stdout
});
rl.question('mysql host name? ', (hostanswer) => {
rl.question('mysql user name? ', (useranswer) => {
rl.question('mysql password? ', (passwordanswer) => {
rl.question('mysql database? ', (dbanswer) => {
rl.close();


var mysql = require('mysql');
var connection = mysql.createConnection({
host : hostanswer,
user : useranswer,
password : passwordanswer,
database : dbanswer,
multipleStatements: true
});

connection.connect();

var sql="DROP TABLE IF EXISTS `test-mysql-Customers`; CREATE TABLE `test-mysql-Customers` (`CompanyName` varchar(45) NOT NULL,`City` varchar(45) DEFAULT NULL, `Country` varchar(45) DEFAULT NULL,PRIMARY KEY (`CompanyName`)); INSERT INTO `test-mysql-Customers` VALUES ('Company1','City1','Country1'),('Company2','City2','Country2'); SELECT * from `test-mysql-Customers`";

connection.query(sql, function(err, rows, fields) {
if (!err) {
console.log('The mysql return data: ', rows);
console.log('The mysql test completed successfully. test-mysql-Customers table was created.');
} else
console.log('Error while performing Query.');
});

connection.end();


});
});
});
});

Next go to the terminal window still in the /node working directory and run the test-mysql.js server application. When prompted enter a valid mysql server. In a previous post I created a mysql database on an Amazon RDS server. An RDS or any valid mysql server will work. The test simply creates a database table on the server called test-mysql-Customer with 3 fields and 2 records.

node test-mysql.js

The results will be the returned SQL string data and the final line will have:

The mysql test completed successfully. test-mysql-Customers table was created.

That’s all folks!

Next we will explore creating an asynchronous real word server.

Advertisements