MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Archive for the ‘Node.js Developer’ tag

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 , , ,

Node.js Routing

without comments

I liked the example used to explore basic routing in Chapter 2 of the Web Development with Node & Express book. I embellished the Node.js router example and static pages just a bit. The routing example requires you create a public subdirectory where you deploy the code and put the about.html, home.html, and 404.html files in the public subdirectory. Then, you must put a logo.png file in a tmp subdirectory inside of the public directory. The book assumes you know how to build these pages, which seems reasonable but just in case, here are the files I used to test the book’s example.

The about.html page:

<html>
<head>
<title>About Page</title>
</head>
<body>
<p>A small sample Node.js routing example.</p>
<p><img width="25" src="/img/logo.png" />&nbsp;<sup><i>MEAN Stack Development</i></sup></p>
</body>
</html>

The home.html page:

<html>
<head>
<title>Home Page</title>
</head>
<body>
<p style="font-size:110%">Star Trek: The Original Series - Season 1</p>
<p><img width="300" src="/img/StarTrekOriginal1.png" /></p>
<p><img width="25" src="/img/logo.png" />&nbsp;<sup><i>MEAN Stack Development</i></sup></p>
</body>
</html>

The 404.html page:

<html>
<head>
<title>404 Error Message</title>
</head>
<body>
<p>A 404 Error Message Page.</p>
<p><img width="25" src="/img/logo.png" />&nbsp;<sup><i>MEAN Stack Development</i></sup></p>
</body>
</html>

The routing example sets the path to lowercase, which is important when you type the URL to verify physical files. For example, you need to use the routing startrekoriginal1.png string value in the URL. It will fail if you use the mixed case actual file name of the StarTrekOriginal1.png file. That’s because the routing maps the lowercase string to the physical file.

While the /public directory is the home directory of the virtual map as a rule, you can’t use it in the URL (as explained in next Chapter 3). Also, the router uses /img as a virtual directory which maps to the physical /tmp subdirectory. When you want to validate a physical image file you need to know these two rules. They explain why the following URL lets you verify a physical image file found in the /public/tmp directory.

localhost:3000/img/StarTrekOriginal1.png

you will trigger the 404 Error page. Here’s the Node.js routing code:

/* Construct a web server. */
const http = require('http')
const fs = require('fs')
const port = process.env.PORT || 3000
 
/* Function uses the fs package to read files. */
function serveStaticFile(res, path, contentType, responseCode = 200) {
  fs.readFile(__dirname + path, (err, data) => {
    if (err) {
      res.writeHead(500, { 'Content-Type': 'text/plain' })
      return res.end('500 - Internal Error')
    }
    res.writeHead(responseCode, { 'Content-Type': contentType })
    res.end(data)
  })
}
 
/* Create the Node.js server. */
const server = http.createServer((req, res) => {
  // Normalize URL by removing query string, optional
  // trailing slash, and making it lowercase.
  const path= req.url.replace(/\/?(?:\?.*)?$/,'').toLowerCase()
 
  switch(path) {
    case '':
      serveStaticFile(res, '/public/home.html', 'text/html' )
      break
    case '/about':
      serveStaticFile(res, '/public/about.html', 'text/html' )
      break
    case '/img/startrekoriginal1.png':
      serveStaticFile(res, '/public/tmp/StarTrekOriginal1.png', 'image/png' )
      break
    case '/img/logo.png':
      serveStaticFile(res, '/public/tmp/logo.png', 'image/png' )
      break
    default:
      serveStaticFile(res, '/public/404.html', 'text/html', 404 )
      break
  }
})
 
server.listen(port, () => console.log(`server started on port ${port}; ` +
                                      'press Ctrl-C to terminate...'))

Assuming you name the Node.js routing example helloworld3.js, you would start the router with the following command:

node helloworld3.js

It should start the router. Enter the following URL:

http://localhost:3000

You should see a page rendered like the following:

As always, I hope this helps those trying to use this technology.

Written by maclochlainn

July 17th, 2020 at 10:22 pm

Posted in JavaScript,Linux,NodeJS

Tagged with