MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Node.js & MySQL

with one comment

These are my notes for creating a small Node.js application that queries a MySQL database. The post will show you how to:

  1. Configure your Node.js development directory.
  2. Build a small application to test a MySQL connection.
  3. Build a small application that connects to the MySQL database and queries data.

This blog post assumes you’ve performed a global install of Node.js on a Linux server. If you’re unfamiliar with how to perform a global Node.js installation, I cover how to do it in this earlier blog post.

Before you write the Node.js applicaiton, you need to setup a db developer directory. Then, create a node_modules symbolic link to the /usr/local/lib/node_modules directory in the db directory. You can use the following command from the db directory:

ln -s /usr/local/lib/node_modules `pwd`/node_modules

After creating the node_modules symbolic link, you need to run the following two npm commands:

npm init --y
npm install --save mysql

The first command sets up a generic package.json file, and the second adds the mysql package and supporting packages to the package.json file. These two steps configure the Node.js side of these examples.

They both require that you create the student user with a native password, like so:

CREATE USER 'student'@'localhost' IDENTIFIED WITH 'mysql_native_password' BY 'student';
GRANT ALL PRIVILEGES ON studentdb.* TO 'student'@'localhost';
FLUSH PRIVILEGES;

The following example shows you how to check a connection to the MySQL database:

const mysql = require('mysql') 
const connection = mysql.createConnection({ 
   host: 'localhost', 
   user: 'student', 
   password: 'student', 
   database: 'studentdb' 
}) 
 
connection.connect((err) => { 
 if (err) 
   throw err 
 else 
   console.log('Connected to MySQL Server!\n') 
   console.log('User configured wiht mysql_native_password.\n'); 
   console.log('Press Ctrl-C to terminate ...') 
})

You extend the previous example by adding a query component and returning the query result value to the console’s log (leveraging the w3school’s Node.js and MySQL tutorial example):

const mysql = require('mysql') 
const connection = mysql.createConnection({ 
   host: 'localhost', 
   user: 'student', 
   password: 'student', 
   database: 'studentdb' 
}) 
 
connection.connect((err) => { 
 if (err) 
   throw err 
 else { 
   console.log('Connected to MySQL Server!\n') 
   connection.query('SELECT DISTINCT item_title FROM item', function (err, result) { 
     if (err) 
       throw err 
     else 
       console.log(result) 
       console.log('Press Ctrl-C to terminate ...') 
   }) 
 } 
})

It should display the following:

Connected to MySQL Server! 
 
[ RowDataPacket { item_title: 'The Hunt for Red October' }, 
 RowDataPacket { item_title: 'Star Wars I' }, 
 RowDataPacket { item_title: 'Star Wars II' }, 
 RowDataPacket { item_title: 'Star Wars III' }, 
 RowDataPacket { item_title: 'The Chronicles of Narnia' }, 
 RowDataPacket { item_title: 'RoboCop' }, 
 RowDataPacket { item_title: 'Pirates of the Caribbean' }, 
 RowDataPacket { item_title: 'MarioKart' }, 
 RowDataPacket { item_title: 'Splinter Cell' }, 
 RowDataPacket { item_title: 'Need for Speed' }, 
 RowDataPacket { item_title: 'The DaVinci Code' }, 
 RowDataPacket { item_title: 'Cars' }, 
 RowDataPacket { item_title: 'Beau Geste' }, 
 RowDataPacket { item_title: 'I Remember Mama' }, 
 RowDataPacket { item_title: 'Tora! Tora! Tora!' }, 
 RowDataPacket { item_title: 'A Man for All Seasons' }, 
 RowDataPacket { item_title: 'Hook' }, 
 RowDataPacket { item_title: 'Around the World in 80 Days' }, 
 RowDataPacket { item_title: 'Harry Potter and the Sorcerer\'s Stone' }, 
 RowDataPacket { item_title: 'Camelot' }, 
 RowDataPacket { item_title: 'Casino Royale' }, 
 RowDataPacket { item_title: 'Die Another Day' }, 
 RowDataPacket { item_title: 'Golden Eye' }, 
 RowDataPacket { item_title: 'Tomorrow Never Dies' }, 
 RowDataPacket { item_title: 'The World Is Not Enough' }, 
 RowDataPacket { item_title: 'Brave Heart' }, 
 RowDataPacket { item_title: 'Christmas Carol' }, 
 RowDataPacket { item_title: 'Scrooge' }, 
 RowDataPacket { item_title: 'Clear and Present Danger' }, 
 RowDataPacket { item_title: 'Harry Potter and the Chamber of Secrets' }, 
 RowDataPacket { item_title: 'Harry Potter and the Prisoner of Azkaban' }, 
 RowDataPacket { item_title: 'Harry Potter and the Goblet of Fire' }, 
 RowDataPacket { item_title: 'Harry Potter and the Order of the Phoenix' }, 
 RowDataPacket { item_title: 'Harry Potter and the Half Blood Prince' }, 
 RowDataPacket { item_title: 'Harry Potter and the Deathly Hallows, Part 1' }, 
 RowDataPacket { item_title: 'Harry Potter and the Deathly Hallows, Part 2' }, 
 RowDataPacket { item_title: 'Tron' }, 
 RowDataPacket { item_title: 'The Avengers' }, 
 RowDataPacket { item_title: 'Thor: The Dark World' } ] 
Press Ctrl-C to terminate ...

As always, I hope this helps those looking to learn

Written by maclochlainn

July 21st, 2020 at 11:51 pm

Posted in JavaScript,Linux,MySQL,MySQL 8,NodeJS,Unix

Tagged with , , ,

«
»