MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Archive for the ‘ExpressJS’ Category

MySQL JSON Server

with one comment

A student question: Does JavaScript make context switching for web-based applications obsolete? Wow! I asked what that meant. He said, it means JavaScript replaces all other server-side programming languages, like PHP, C#, or Python. I asked the student why he believed that. His answer was that’s what two interviewing managers told him.

I thought it would be interesting to put the idea to a test. Below is a Node.js script that acts as a utility that queries the MySQL database with substitution variables in query. It also returns a standard out (stdout) stream of the MySQL query’s results. It also supports three flag and value pairs as arguments, and optionally writes the results of the MySQL query to a log file while still returning result as the stdout value. All errors are written to the standard error (stderr) stream.

The Node.js solution is completely portable between Windows and Linux. You can deploy it to either platform without any edits for Windows case insensitive Command-Line Interface (CLI). Clearly, Node.js offers a replacement for direct interaction with the .NET components in PowerShell. This appears to mean basic Linux shell or PowerShell knowledge is all that’s required to write and deploy JavaScript programs as server-side programming solutions. It means anything that you would have done with the .NET you can do with JavaScript. Likewise, you can replace PHP, C#, Python, or Ruby server-side scripts with JavaScript programs.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109

// Declare constants. 
const fs = require('fs') 
const util = require('util') 
const express = require('express') 
const mysql = require('mysql') 
const connection = mysql.createConnection({ 
  host: 'localhost', 
  user: 'student', 
  password: 'student', 
  database: 'studentdb' 
}) 
 
// Declare local variables for case insensitive use. 
var data = '' 
var buffer = Buffer.alloc(0) 
var path = '' 
 
// Declare default query variables dates. 
var startDate = new Date('1980-01-01') 
var endDate = new Date() 
 
// Set default endDate value as tomorrow. 
endDate.setDate(new Date().getDate() + 1) 
 
// Define a regular expression for valid file names. 
var regexp = /^([0-9a-zA-Z]+|[0-9a-zA-Z]+\.+[0-9a-zA-Z]{3})$/ 
 
// Assign dynamic variables from arguments. 
var argv = process.argv.slice(2) 
 
// Check for paired values, evaluate and assign them to local variables. 
if ((argv.length % 2) == 0) { 
 for (let i = 0; i < argv.length; i += 2) { 
   // Assign a file name to write to the output path. 
   if ((argv[i].toLowerCase() == '-f') && (regexp.test(argv[i+1]))) { 
     // Assign present working for Windows or Linux. 
     if (process.platform == 'win32') 
       path = '.\\' + argv[1] 
     else 
       path = './' + argv[1] 
   } 
   // Assign a start date from the input string. 
   else if (argv[i].toLowerCase() == '-b') { 
     startDate = new Date(argv[i+1]) 
   } 
   // Assign a end date from the input string. 
   else if (argv[i].toLowerCase() == '-e') { 
     endDate = new Date(argv[i+1]) 
   } 
 } 
} 
else { 
 console.error('Arguments must be in pairs: flag and value.') 
} 
 
// Define and run MySQL query. 
connection.query("SELECT   i.item_title " + 
                 ",        date_format(i.release_date,'%d-%M-%Y') AS release_date " + 
                 "FROM     item i JOIN common_lookup cl " + 
                 "ON       i.item_type = cl.common_lookup_id " + 
                 "WHERE    cl.common_lookup_type = 'BLU-RAY' " + 
                 "AND      i.release_date BETWEEN ? AND ? " + 
                 "ORDER BY i.release_date" 
                ,[startDate, endDate], function (err, result) { 
 if (err) { 
   console.error('Query contains error ...') 
   console.error('-> ' + err) 
 } 
 else { 
   // Prints the index value in the RowDataPacket. 
   for(let element in result) { 
     data += result[element].item_title + ', ' + result[element].release_date + '\n' 
   } 
   // Write file when data string is not empty. 
   if (data.length > 0 ) { 
     buffer = Buffer.alloc(data.length,data) 
 
     // Check for a defined path before writing a file. 
     if (path.length > 0) { 
       // Open the file. 
       fs.open(path, 'w', function(err, fd) { 
         if (err) { 
           console.error('Could not open [' + path + '] file [' + err + ']') 
         } 
         else { 
           // Write the file. 
           fs.write(fd, buffer, 0, buffer.length, null, function(err) { 
             if (err) 
               console.error('Error writing [' + path + '] file [' + err + ']') 
             fs.close(fd, function() { 
               if (fs.existsSync(path)) { 
                 process.exit(0) 
               } 
             }) 
           }) 
         }   
       }) 
     } 
     // Set standard out (stdout). 
     console.log(data) 
   } 
   else { 
     console.error('Query returned no rows.') 
   } 
 } 
}) 
 
// Close MySQL connection. 
connection.end()

You can call this code with the default values, like

node app.js

You can call this code with a user defined file name, and a custom start and end date values, like

node app.js -f output.csv -b '2001-01-01' -e '2004-12-31'

The latter command returns the following by querying my MySQL studentdb video store:

Star Wars II, 16-May-2002 
Harry Potter and the Chamber of Secrets, 28-May-2002 
Harry Potter and the Sorcerer's Stone, 28-May-2002 
Die Another Day, 03-June-2003 
Harry Potter and the Prisoner of Azkaban, 23-October-2004

As always, I hope this helps somebody trying to sort it out.

Written by maclochlainn

August 1st, 2020 at 1:05 am

Posted in bash,ExpressJS,Fedora,JavaScript,Linux,MySQL,MySQL 8,NodeJS

Tagged with , , ,

Express.js & MySQL

without comments

Sometimes, you just half to chuckle. A couple folks felt that I didn’t give enough information in my post showing how to configure a small Node.js application that could access a MySQL database. Specifically, they wanted me to explain the following:

  1. Configure your Express.js and MySQL development in a single Node.js application.
  2. How to convert the list of RowDataPacket objects as elements of data, which is really just simple JavaScript knowledge.
  3. How to bind variables into the query.

Like the other blog post, this one 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. A global install of Node.js means you need to create a node_modules symbolic link to the /usr/local/lib/node_modules directory in the db directory (in Linux). You can use the following Linux command from within the db directory to create the appropriate symbolic link:

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

or, assuming you have a /home/some_user/db directory

ln -s /usr/local/lib/node_modules /home/some_user/node_modules

After creating the node_modules symbolic link, you need to run the following two npm commands. Please note that second command holds the secret-sauce for generating a package.json file that supports Express.js and the MySQL driver:

npm init --y
sudo npm install --save express mysql

Then, you need to replace the package.json file with the contents of the package-lock.json file from your last npm command.

Here’s a small sample program that uses Express.js, converts the RowDataPackets collection, and binds local variables into the query:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43

// Require libraries.
const express = require('express') 
const mysql = require('mysql')
 
// Create a mysql connection. 
const connection = mysql.createConnection({ 
  host: 'localhost', 
  user: 'student', 
  password: 'student', 
  database: 'studentdb' 
}) 
 
// Declare two local variables.
const start_date = '2001-01-01' 
const end_date = '2003-12-31' 
 
// Connect and display results in the console log.
connection.connect((err) => { 
if (err) 
  throw err 
else { 
  console.log('Connected to MySQL Server!\n') 
  connection.query("SELECT   i.item_title " + 
                   ",        date_format(i.release_date,'%d-%M-%Y') AS release_date " + 
                   "FROM     item i JOIN common_lookup cl " + 
                   "ON       i.item_type = cl.common_lookup_id " + 
                   "WHERE    cl.common_lookup_type = 'BLU-RAY' " + 
                   "AND      i.release_date BETWEEN ? AND ? " + 
                   "ORDER BY i.release_date" 
                  ,[start_date, end_date], function (err, result) { 
    if (err) 
      throw err 
    else { 
      // Prints the index value in the RowDataPacket. 
      for(let element in result) { 
        console.log(result[element].item_title + ', ' + result[element].release_date) 
      } 
      console.log('') 
      console.log('Press Ctrl-C to terminate ...') 
    } 
  }) 
} 
})

Line 28 shows two question marks. They act as placeholders for binding variables. Then, on line 30 you see a collection of the start_date and end_date local variables, which is the second argument to the query() function.

Rather than define individual variables, you can pass them as a collection directly. For example, you replace lines 14 and 15 with this single line:

14

const dates = ['2001-01-01','2003-12-31']

Then, you can pass dates as the second argument to the query() function, like this:

30

                  ,dates, function (err, result) {

Lines 35 and 36 show you how to convert a collection of RowDataPacket objects into elements of data. The for loop assigns the index value to the element variable, which lets you address a single RowDataPacket object instance. The dot (“.“) notation lets you use the name in a name-value pair to reference its value.

It displays the following:

Connected to MySQL Server! 
 
Star Wars II, 16-May-2002 
Harry Potter and the Chamber of Secrets, 28-May-2002 
Harry Potter and the Sorcerer's Stone, 28-May-2002 
Die Another Day, 03-June-2003 
 
Press Ctrl-C to terminate ...

Naturally, I’ll get around to writing something up that shows how to leverage MySQL data into a dynamic form with Handlebars at some point in the near future.

As always, I hope this helps those looking for a solution.

Written by maclochlainn

July 28th, 2020 at 1:48 pm

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

Tagged with , ,