Archive for the ‘NodeJS’ Category
Is SQL Programming
Is SQL, or Structured Query Language, a programming language? That’s a great question! A question that many answer with emphasis: “No, SQL is not a programming language!” There are some who answer yes; and they usually qualify that answer with something like: “SQL is a programming language designed to communicate with relational databases.”
It strikes me that those saying “yes” are saying that SQL is only a collection of interface methods to read from and write to a database engine. Those saying SQL is not a programming language often qualify that a programming language must have conditional logic and iterative structures, which don’t exist in SQL.
There’s a third group that are fence sitters. They decline to say whether SQL is a programming language, but they also say individuals who only write SQL aren’t programmers. That’s a bit harsh from my perspective.
Before determining whether SQL is a programming language let’s define a programming language. Let’s define a programming language as a collection of lexical units, or building blocks, that build program units. Lexical units are typically organized as delimiters, identifiers, literals, and comments:
- Delimiters include single or double quotes to identify strings and operators that let you assign and compare values.
- Identifiers are reserved words, keywords, predefined identifiers (like data type names), user-defined variables, subroutines, or types.
- Literals are typically numbers and strings, where some strings qualify as dates because they implement a default format mask to convert strings to dates or date-times.
- Comments are simply delimited text that the program ignores but the programmer uses.
That means a programming language must let you define a variable, assign a value to a variable, iterate across a set of values, and make conditional statements. SQL meets these four conditions, but it does, as a set-programming language, qualify all variables as lists of tuples. Though it is possible to have variables with zero to many elements and one to many members in any given tuple. That means you can assign a literal value to to a one-element list with a single-member tuple, like you would a string or integer to a variable of that type.
As Kris Köhntopp commented, computer science defines a programming language as Turing Complete. As his comment qualifies and the Wikipedia page explains: “Turing completeness in declarative SQL is implemented through recursive common table expressions. Unsurprisingly, procedural extensions to SQL (PLSQL, etc.) are also Turing-complete.” While PostgreSQL introduces recursive query syntax through CTEs, it recently added the search and cycle feature in PostgreSQL 14. The recursive query feature has existed in the Oracle database since Oracle 8, but their documentation calls them hierarchical queries. I wrote a quick a tutorial on hierarchical queries in 2008.
For clarity, define and declare are two words that give grief to some newbies. Let’s qualify what they mean. Declare means to give a variable a name and data type. Define means to declare a variable and assign it a value. Another word for assigning a variable is initializing it. Unassigned variables are automatically assigned a default value or a null dependent on the programming language.
Let’s first declare a local variable, assign it to variable, and display the variable. The following example uses Node.js to define the input variable, assign the input variable to the display variable, and then print the display variable to console. Node.js requires that you assign an empty string to the display variable to define it as a string otherwise its type would be undefined, which is common behavior in dynamically typed languages.
/* Declare the display variable as a string. */ var display = "" /* Define the input variable. */ var input = "Hello World!" /* Assign the input variable contents to the display variable. */ display = input /* Print the display variable contents to console. */ console.log(display) |
It prints:
Hello World! |
Let’s write the same type of program in MySQL. Like the Node.js, there are implementation differences. The biggest difference in MySQL or other relational databases occurs because SQL is a declarative set-based language. That means every variable is a collection of a record structure . You can only mimic a scalar or primitive data type variable by creating a record structure with a single member.
In the case below, there are four processing steps:
- The ‘Hello World!’ literal value is assigned to an input variable.
- The SELECT-list (or comma-delimited set of values in the SELECT clause) is assigned like a tuple to the struct collection variable by treating the query of the literal value as an expression.
- The FROM clause returns the struct collection as the data set or as a derived table.
- The topmost SELECT clause evaluates the struct collection row-by-row, like a loop, and assigns the input member to a display variable.
The query is:
SELECT struct.input AS display FROM (SELECT 'Hello World!' AS input) struct; |
Since the struct collection contains only one element, it displays the original literal value one time, like
+--------------+ | display | +--------------+ | Hello World! | +--------------+ 1 row in set (0.00 sec) |
Let’s update the SQL syntax to the more readable, ANSI 1999 and forward, syntax with a Common Table Expression (CTE). CTEs are implemented by the WITH clause.
WITH struct AS (SELECT 'Hello World!' AS input) SELECT struct.input AS display FROM struct; |
The best thing about CTE values they run one-time and are subsequently available anywhere in your query, subqueries, or correlated subqueries. In short, there’s never an excuse to write a subquery twice in the same query.
Let’s look at loops and if-statements. Having established that we can assign a literal to a variable, re-assign the value from one variable to another, and then display the new variable, let’s assign a set of literal values to an array variable. As before, let’s use Node.js to structure the initial problem.
The program now assigns an array of strings to the input variable, uses a for-loop to read the values from the input array, and uses an if-statement with a regular expression evaluation. The if-statement determines which of the array value meets the condition by using a negating logical expression. That’s because the search() function returns a 0 or greater value when the needle value is found in the string and a -1 when not found. After validating that the needle variable value is found in an input string, the input value is assigned to the display variable.
/* Declare the display variable as a string. */ var display = "" /* Declare a lookup variable. */ var needle = "Goodbye" /* Define the input variable as an array of strings. */ var input = ["Hello World!" ,"Goodbye, Cruel World!" ,"Good morning, too early ..."] /* Read through an array and assign the value that meets * the condition to the display variable. */ for (i = 0; i < input.length; i++) if (!(input[i].search(needle) < 0)) display = input[i] /* Print the display variable contents to console. */ console.log(display) |
Then, it prints the display value:
Goodbye, Cruel World! |
To replicate the coding approach in a query, there must be two CTEs. The needle CTE assigns a literal value of ‘goodbye’ to a one-element collection of a single-member tuple variable. The struct CTE creates a collection of strings by using the UNION ALL operator to append three unique tuples instead of one tuple as found in the early example.
The needle CTE returns a one-element collection of a single-member tuple variable. The struct CTE returns a three-element collection of a single-member tuple, which mimics an array of strings. The needle and struct CTEs return distinct variables with different data types. A cross join operation between the two CTEs puts their results together into the same context. It returns a Cartesian product that:
- Adds a single-row tuples to each row of the query’s result set or derived table.
- Adds a multiple-tuples to each row of the query’s result set or derived table by creating copies of each row (following the Cartesian set theory which multiplies rows and adds columns).
In this case, the Cartesian join adds a one-element needle CTE value to each element, or row, returned by the multiple-element struct CTE and produces the following derived table:
+-----------------------------+---------+ | display | lookup | +-----------------------------+---------+ | Hello World! | goodbye | | Goodbye, cruel world! | goodbye | | Good morning, too early ... | goodbye | +-----------------------------+---------+ 3 rows in set (0.00 sec) |
The following query reads through the CTE collection like a loop and filters out any invalid input values. It uses the MySQL regular expression like function in the WHERE clause, which acts as a conditional or if-statement.
WITH needle AS (SELECT 'goodbye' AS lookup) , struct AS (SELECT 'Hello World!' AS input UNION ALL SELECT 'Goodbye, cruel world!' AS input UNION ALL SELECT 'Good morning, too early ...' AS input) SELECT struct.input AS display FROM struct CROSS JOIN needle WHERE REGEXP_LIKE(struct.input, CONCAT('^.*',needle.lookup,'.*$'),'i'); |
It returns the one display value that meets the criteria:
+-----------------------+ | display | +-----------------------+ | Goodbye, cruel world! | +-----------------------+ 1 row in set (0.00 sec) |
The comparisons of the imperative programming approach in Node.js and declarative programming approach should have established that SQL has all the elements of a programming language. That is, SQL has variable declaration and assignment and both iterative and conditional statements. SQL also has different styles for implementing variable declaration and the examples covered subqueries and CTEs with cross joins placing variables in common scope.
Comparative Approaches:
Next, let’s examine a problem that a programmer might encounter when they think SQL only queries or inserts, updates, or deletes single rows. With that perspective of SQL there’s often a limited perspective on how to write queries. Developers with this skill set level typically write only basic queries, which may include inner and outer joins and some aggregation statements.
Let’s assume the following for this programming assignment:
- A sale table as your data source, and
- A requirement to display the type, number, pre-tax sale amount, and percentage by type.
The sale table definition:
+------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------+--------------+------+-----+---------+----------------+ | sale_id | int unsigned | NO | PRI | NULL | auto_increment | | item_desc | varchar(20) | YES | | NULL | | | unit_price | decimal(8,2) | YES | | NULL | | | serial_no | varchar(10) | YES | | NULL | | +------------+--------------+------+-----+---------+----------------+ |
A basic Node.js program may contain a SQL query that returns the item_desc and unit_price columns while counting the number of serial_no rows and summing the unit_price amounts (that assumes no discount sales, after all its Apple). That type of query leaves calculating the total amount of sales and percentage by type to the Node.js program.
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 s.item_desc " + ", s.unit_price " + ", COUNT(s.serial_no) AS quantity_sold " + ", SUM(s.unit_price) AS sales " + "FROM sale s " + "GROUP BY s.item_desc " + ", s.unit_price", function (err, result) { if (err) throw err else { // Prints the index value in the RowDataPacket. console.log(result) connection.end() }})} }) |
This program would return a JSON structure, like:
[ RowDataPacket { item_desc: 'MacBook Pro 16', unit_price: 2499, quantity_sold: 16, sales: 39984 }, ... RowDataPacket { item_desc: 'MacBook Air M1', unit_price: 999, quantity_sold: 22, sales: 21978 } ] |
While the remaining JavaScript code isn’t difficult to write, it’s unnecessary effort if the developer knew SQL well enough to program in it. The developer could simply re-write the query like the following and return the percentage by type value in the base JSON structure.
WITH sales AS (SELECT SUM(unit_price) AS total FROM sale) SELECT s.item_desc , s.unit_price , COUNT(s.serial_no) AS quantity_sold , SUM(s.unit_price) AS sales , CONCAT(FORMAT((s.unit_price * COUNT(s.serial_no))/sales.total * 100,2),'%') AS percentage FROM sale s CROSS JOIN sales GROUP BY s.item_desc , s.unit_price , sales.total; |
The query uses the sales CTE to calculate and define a tuple with the total sales and adds a derived column calculating the percentage by type of device. It’s probably important to note that aggregation rules require you add the sales.total CTE tuple to the group by clause.
The new query returns this JSON list:
[ RowDataPacket { item_desc: 'MacBook Pro 16', unit_price: 2499, quantity_sold: 16, sales: 39984, percentage: '17.70%' }, ... RowDataPacket { item_desc: 'MacBook Air M1', unit_price: 999, quantity_sold: 22, sales: 21978, percentage: '9.73%' } ] |
The developer would get a complete JSON list when the new query replaces the old. It also would eliminate the need to write additional JavaScript to calculate the percentage by type of device.
Conclusions:
Leveraging the programming power of SQL is frequently possible in many frontend and backend programming solutions. However, the programming power of SQL is infrequently found in programming solutions. That leaves me to ask: “Is it possible that the almost systemic failure to leverage the programming capabilities of SQL is a result of biases by instructors and mentors to their own limited skill sets?” That likely might be true if their instructors and mentors held the belief that: “No, SQL is not a programming language!”
Candidly, folks that write SQL at the programming level almost always have concurrent mastery in two or more imperative programming languages. They’re probably the ones who say, “SQL is a programming language designed to communicate with relational databases.”
Who are those pesky fence sitters? You remember those, don’t you. They’re the ones who declined to take a position on whether SQL is a programming language. Are they the developers who are still learning, and those without an entrenched, preconceived, or learned bias? Or, do they wonder if SQL is Turing complete?
Node.js MySQL Error
While I blogged about how to setup Node.js and MySQL almost two years ago, it was interesting when a student ran into a problem. The student said they’d configured the environment but were unable to use Node.js to access MySQL.
The error is caused by this import statement:
const mysql = require('mysql') |
The student got the following error, which simply says that they hadn’t installed the Node.js package for MySQL driver.
internal/modules/cjs/loader.js:638 throw err; ^ Error: Cannot find module 'mysql' at Function.Module._resolveFilename (internal/modules/cjs/loader.js:636:15) at Function.Module._load (internal/modules/cjs/loader.js:562:25) at Module.require (internal/modules/cjs/loader.js:692:17) at require (internal/modules/cjs/helpers.js:25:18) at Object.<anonymous> (/home/student/Data/cit325/oracle-s/lib/Oracle12cPLSQLCode/Introduction/query.js:4:15) at Module._compile (internal/modules/cjs/loader.js:778:30) at Object.Module._extensions..js (internal/modules/cjs/loader.js:789:10) at Module.load (internal/modules/cjs/loader.js:653:32) at tryModuleLoad (internal/modules/cjs/loader.js:593:12) at Function.Module._load (internal/modules/cjs/loader.js:585:3) |
I explained they could fix the problem with the following two Node.js Package Manager (NPM) commands:
npm init --y npm install --save mysql |
The student was able to retest the code with success. The issue was simply that the Node.js couldn’t find the NPM MySQL module.
Fun JavaScript Q?
I got sent a fun JavaScript question from a student. It was fun for two reasons. They were clearly engaged and trying to figure stuff out on their own and they were just beyond basic syntax issues.
The student wanted to know why this code worked:
// Populate an object with one property. var notempty = {test:"me"} // Test for and enumerate object properties. for (var property in notempty) { if (!notempty.hasOwnProperty(property)) { print("No properties") } else { print("[" + property + "]") } } print("done") |
and returned:
[test] done |
but the behavior changed when the initialized object didn’t have any properties. It failed to print the No properties message:
// Populate an object with one property. var empty = {} // Test for and enumerate object properties. for (var property in empty) { if (!empty.hasOwnProperty(property)) { print("No properties") } else { print("[" + property + "]") } } print("done") |
it printed:
done |
The answer was easy, there were no properties to enumerate, which meant the loop never executes. However, that’s only part of the answer. The complete answer is that the empty object and the default Object.prototype have no properties to enumerate. You should perform a different test before the loop, like:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | // Populate an object without properties. var empty = {} // Test for and enumerate object properties. if (Object.keys(empty).length !== 0) { for (var property in empty) { if (empty.hasOwnProperty(property)) { print("[" + property + "]") } } } else { print("No properties") } // Print complete message. print("done") |
If you run this from the mongo shell, like this
mongo --nodb --norc < object_properties.js |
It prints:
MongoDB shell version v4.0.19 No properties done bye |
If you run this inside the mongo shell with the load function, like
load("object_properties.js") |
It prints:
No properties done true |
However, you can generate a parsing error when you rewrite line 11 in the if-else statement, like so
if (Object.keys(empty).length !== 0) { ... } else { ... } |
The reason for the error is that JavaScript appends a semicolon (;) at the end of each line. Therefore, the if-else-statement appears as an if-only-statement. which raises the syntax error.
It prints:
MongoDB shell version v4.0.19 2021-08-02T13:38:34.672-0600 E QUERY [js] SyntaxError: expected expression, got keyword 'else' @(shell):1:0 done bye |
It appears the parsing error is a warning message because the code completes. Personally, I would prefer that they not raise a warning message because it actually implies a style preference.
There’s a next level problem you may run into while trying to implement reflection, or the discovery of existing properties and their value types, of JavaScript objects. Here’s that code, which seems to be missing from many tutorials and articles:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | // Create an object with functions. // Populate an object without properties. var notEmpty = { first_name: "Clark" , last_name: "Kent" , getName: function() { return this.first_name + " " + this.last_name } } // Test for and enumerate object properties. if (Object.keys(notEmpty).length !== 0) { for (var property in notEmpty) { if (notEmpty.hasOwnProperty(property)) { print("[" + property + "] value is a [" + typeof(notEmpty[property]) + "]") } } } else { print("No properties") } print("done") |
This code on line 12 lets you view a property’s value type:
1 | typeof(notEmpty[property]) |
Only the object[property] subscript notation method works when the property’s value is a variable. The other dot notation syntax to access a property’s value in JavaScript, object.property, doesn’t work without a literal value. Actually, it would work if you embed it inside an eval() function call, but the eval() function is bad juju (black magic) in JavaScript coding.
It prints the following:
MongoDB shell version v4.0.19 [first_name] value is a [string] [last_name] value is a [string] [getName] value is a [function] done bye |
While this shows the basic functionality, it would be more effective to create a function and extend it while adding some output formatting. The next example wouldn’t be found in a JSON (JavaScript Object Notation) collection element but would be found in a NodeJS application managing JSON structures. You would define the constructor to match the data properties of the JSON structure.
The nice thing about a JavaScript function is that “It can specify a list of parameters that will act as variables initialized by the invocation arguments. The body of the function includes variable definitions and statement. (JavaScript: The Good Parts, 2008)” Newer approaches allow you to pass an object as a parameter with named notation inside the object.
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 | // Create an object with functions. function Person(first_name, last_name) { this.first_name = first_name , this.last_name = last_name , this.setFirstName = function(first_name) { this.first_name = first_name } , this.getName = function() { return this.first_name + " " + this.last_name } } // Create an instance of a variable and print results. var myPerson = new Person("Clark","Kent") print("Initial Name: " + myPerson.getName()) // Call the added function and print the new value. myPerson.setFirstName("Lois") print("Reset Name: " + myPerson.getName()) print() // Test for and enumerate object properties. print("Variable Property Names & Values") print("====================================") if (Object.keys(myPerson).length !== 0) { for (var property in myPerson) { if (myPerson.hasOwnProperty(property)) { print("[" + property + "] value is a [" + typeof(myPerson[property]) + "]") } } } else { print("No properties") } print("====================================") print("done") |
It prints the following:
MongoDB shell version v4.0.19 Initial Name: Clark Kent Reset Name: Lois Kent Variable Property Names & Values ==================================== [first_name] value is a [string] [last_name] value is a [string] [setFirstName] value is a [function] [getName] value is a [function] ==================================== done bye |
While the prior example shows object construction, this one introduces one variable inheriting the Person function’s properties and another inheriting the same properties before adding a new one to only the variable. The inherited properties, which are functions, are methods of the variable by delegation.
Below is the modified code. It changes the variable from myPerson to myPerson1 and lines 22 through 28 add the new myPerson2 variable and setLastName function. Lines 47 to 58 enumerate across the new second variable, myPerson2 to show you that it has a new setLastName function.
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 | // Create an object with functions. function Person(first_name, last_name) { this.first_name = first_name , this.last_name = last_name , this.setFirstName = function(first_name) { this.first_name = first_name } , this.getName = function() { return this.first_name + " " + this.last_name } } // Create an instance of a variable and print results. var myPerson1 = new Person("Clark","Kent") print("Initial Name: " + myPerson1.getName()) // Call the added function and print the new value. myPerson1.setFirstName("Lois") print("Reset Name: " + myPerson1.getName()) print() // Create an instance of a variable and print results. var myPerson2 = new Person("Bruce","Wayne") print("Initial Name: " + myPerson2.getName()) // Add a function to the local variable. function setLastName(last_name) { this.last_name = last_name } myPerson2.setLastName = setLastName print() // Test for and enumerate object properties. print("Variable Property Names & Values") print("====================================") if (Object.keys(myPerson1).length !== 0) { for (var property in myPerson1) { if (myPerson1.hasOwnProperty(property)) { print("[" + property + "] value is a [" + typeof(myPerson1[property]) + "]") } } } else { print("No properties") } print("====================================") print() // Test for and enumerate object properties. print("Variable Property Names & Values") print("====================================") if (Object.keys(myPerson2).length !== 0) { for (var property in myPerson2) { if (myPerson2.hasOwnProperty(property)) { print("[" + property + "] value is a [" + typeof(myPerson2[property]) + "]") } } } else { print("No properties") } print("====================================") print("done") |
The program shows you the following output:
MongoDB shell version v4.0.19 Initial Name: Clark Kent Reset Name: Lois Kent Initial Name: Bruce Wayne Variable Property Names & Values ==================================== [first_name] value is a [string] [last_name] value is a [string] [setFirstName] value is a [function] [getName] value is a [function] ==================================== Variable Property Names & Values ==================================== [first_name] value is a [string] [last_name] value is a [string] [setFirstName] value is a [function] [getName] value is a [function] [setLastName] value is a [function] ==================================== done bye |
As always, I hope this helps those working through a similar issue.
JavaScript Streams?
A quick followup to my post on how to write a server-side shell component in JavaScript. Naturally, it’s based on a question posed to me about the original article. It asked, “Why didn’t I use JavaScript’s streams instead of synchronized files?”
Moreover, the question asks why I wrote logic (lines 69 thru 105) that wrote to local files rather than a stream. While they didn’t provide an example, here’s a rewritten solution that uses a stream.
69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 | else { // Returns RowDataPacket from query. 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 data from database query and write file. if (path.length > 0) { let writeStream = fs.createWriteStream(path) writeStream.write(buffer) writeStream.on(buffer) } // Set standard out (stdout) and exit program. console.log(data) process.exit(0) } else { console.error('Query returned no rows.') } } |
The Node.js stream replacement code has a significant problem when the target file can’t be read and written to by a Node.js application. This could be an ownership- or permission-driven problem coupled with the lazy file opening behavior of a stream in JavaScript. The lazy open is a hidden behavior of the createWriteStream() method, which actually calls the fs.open() method. It may raise the following type of error:
events.js:174
throw er; // Unhandled 'error' event
^
Error: EACCES: permission denied, open 'output.csv'
Emitted 'error' event at:
at lazyFs.open (internal/fs/streams.js:277:12)
at FSReqWrap.args [as oncomplete] (fs.js:140:20) |
You can prevent this type of unhandled exception by putting this type of block right after you verify the target file name in the script. It ensures that your program raises a handled exception before your code tries to access a target file as a stream.
69 70 71 72 73 74 75 76 | // Verify access to the file. try { fs.accessSync(path, (fs.constants.R_OK && fs.constants.W_OK)) access = true } catch { console.error("Error accessing [%s] file.", path) } |
Naturally, you also need to define the access variable at the top of your script. The preceding block lets you set the access variable to true on line 72 when you have permissions to the file used by the stream. It also lets you replace line 76 (from the prior example code) with the following statement that effectively blocks any attempt to use a stream that will fail because of the lazy file opening process:
76 | if (access && (path.length > 0)) { |
Adding the extra block does lengthen the program, and change line numbers. I hope I’ve adjusted in a way that makes sense by referencing the old numbers for the change of the decision making if-statement.
As always, I hope this helps those looking for a related solution.
MySQL JSON Server
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.
Express.js & MySQL
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:
- Configure your Express.js and MySQL development in a single Node.js application.
- How to convert the list of
RowDataPacketobjects as elements of data, which is really just simple JavaScript knowledge. - 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.
Node.js & MySQL
These are my notes for creating a small Node.js application that queries a MySQL database. The post will show you how to:
- Configure your Node.js development directory.
- Build a small application to test a MySQL connection.
- 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
Node.js Routing
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" /> <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" /> <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" /> <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.
Node.js Fedora Install
I want to add the MEAN (MongoDB, Express.js, Angular.js, and Node.js) stack to my backend server development course. This post documents the installation and configuration of components on Fedora 30.
The first step requires installing the Node package. The Node package also contains the Node package manager (npm). You install the Node packages as the root user or as a sudoer user with the following command.
yum install -y npm |
It should produce the following installation log:
Last metadata expiration check: 1:10:42 ago on Wed 08 Jul 2020 06:57:52 PM MDT. Dependencies resolved. ================================================================================================================================ Package Architecture Version Repository Size ================================================================================================================================ Installing: npm x86_64 1:6.13.4-1.10.19.0.1.fc30 updates 3.8 M Installing dependencies: nodejs x86_64 1:10.19.0-1.fc30 updates 88 k nodejs-libs x86_64 1:10.19.0-1.fc30 updates 9.1 M Installing weak dependencies: nodejs-full-i18n x86_64 1:10.19.0-1.fc30 updates 7.3 M Transaction Summary ================================================================================================================================ Install 4 Packages Total download size: 20 M Installed size: 91 M Downloading Packages: (1/4): nodejs-10.19.0-1.fc30.x86_64.rpm 173 kB/s | 88 kB 00:00 (2/4): nodejs-full-i18n-10.19.0-1.fc30.x86_64.rpm 2.8 MB/s | 7.3 MB 00:02 (3/4): nodejs-libs-10.19.0-1.fc30.x86_64.rpm 2.7 MB/s | 9.1 MB 00:03 (4/4): npm-6.13.4-1.10.19.0.1.fc30.x86_64.rpm 1.3 MB/s | 3.8 MB 00:02 -------------------------------------------------------------------------------------------------------------------------------- Total 4.9 MB/s | 20 MB 00:04 Running transaction check Transaction check succeeded. Running transaction test Transaction test succeeded. Running transaction Running scriptlet: npm-1:6.13.4-1.10.19.0.1.fc30.x86_64 1/1 Preparing : 1/1 Installing : nodejs-libs-1:10.19.0-1.fc30.x86_64 1/4 Installing : nodejs-full-i18n-1:10.19.0-1.fc30.x86_64 2/4 Installing : npm-1:6.13.4-1.10.19.0.1.fc30.x86_64 3/4 Installing : nodejs-1:10.19.0-1.fc30.x86_64 4/4 Running scriptlet: nodejs-1:10.19.0-1.fc30.x86_64 4/4 Verifying : nodejs-1:10.19.0-1.fc30.x86_64 1/4 Verifying : nodejs-full-i18n-1:10.19.0-1.fc30.x86_64 2/4 Verifying : nodejs-libs-1:10.19.0-1.fc30.x86_64 3/4 Verifying : npm-1:6.13.4-1.10.19.0.1.fc30.x86_64 4/4 Installed: nodejs-1:10.19.0-1.fc30.x86_64 nodejs-full-i18n-1:10.19.0-1.fc30.x86_64 nodejs-libs-1:10.19.0-1.fc30.x86_64 npm-1:6.13.4-1.10.19.0.1.fc30.x86_64 Complete! |
After installing the Node package, you should use the Node package manager (npm) to install the Node Monitor nodemon. nodemon is a popular utility that automatically lets you restart Node programs when you make changes to the source code.
While npm is installed as part of the Node package, you must use npm to install the Node Monitor. The following command installs the nodemon globally on your Fedora system. The -g flag lets you install it globally, which is important when you manage package.json files.
npm install -g nodemon |
You install nodemon globally but most of your web app or project files will be installed locally. Node is a different paradigm than building an Apache or IIS web application because Node provides a framework for you to build a web server.
Here’s a quick Hello World! example that I borrowed a JavaScript helloworld.js file from an excellent Web Development with Node & Express: Leveraging the JavaScript Stack by Ethan Brown. For those who haven’t worked with JavaScript in years, semicolons are optional now.
/* Construct a web server. */ const http = require('http') const port = process.env.PORT || 3000 const server = http.createServer((req, res) => { res.writeHead(200, { 'Content-Type': 'text/plain' }) res.end('Hello world!') }) server.listen(port, () => console.log(`server started on port ${port}); ` + 'press Ctrl-C to terminate...')) |
I put this in /var/www/html/node directory, which is owned by the superuser, root. You need to start the server before accessing it from a browser. You can start the program with the following syntax as a privileged user:
node /var/www/html/node/helloworld.js |
Then, you can use the localhost to access it with the following URL:
http://localhost:3000 |
It will display the following:
Next, you need to use the Node Package Manager (npm) to install the Express.js packages. You do that with the following syntax:
npm install -g express express-generator |
It should produce a console out put like the following:
npm WARN deprecated mkdirp@0.5.1: Legacy versions of mkdirp are no longer supported. Please update to mkdirp 1.x. (Note that the API surface has changed to use Promises in 1.x.) /usr/local/bin/express -> /usr/local/lib/node_modules/express-generator/bin/express-cli.js + express@4.17.1 + express-generator@4.16.1 added 60 packages from 42 contributors in 4.798s |
After you install all the packages, you can inspect them with the following command. The packages are found in the /usr/local/lib/node_modules/express directory. The listing is generated from the package.json file on Fedora and Ubuntu Linux.
npm list -g |
It should display something like this:
/usr/local/lib ├─┬ express@4.17.1 │ ├─┬ accepts@1.3.7 │ │ ├─┬ mime-types@2.1.27 │ │ │ └── mime-db@1.44.0 │ │ └── negotiator@0.6.2 │ ├── array-flatten@1.1.1 │ ├─┬ body-parser@1.19.0 │ │ ├── bytes@3.1.0 │ │ ├── content-type@1.0.4 deduped │ │ ├── debug@2.6.9 deduped │ │ ├── depd@1.1.2 deduped │ │ ├─┬ http-errors@1.7.2 │ │ │ ├── depd@1.1.2 deduped │ │ │ ├── inherits@2.0.3 │ │ │ ├── setprototypeof@1.1.1 deduped │ │ │ ├── statuses@1.5.0 deduped │ │ │ └── toidentifier@1.0.0 │ │ ├─┬ iconv-lite@0.4.24 │ │ │ └── safer-buffer@2.1.2 │ │ ├── on-finished@2.3.0 deduped │ │ ├── qs@6.7.0 deduped │ │ ├─┬ raw-body@2.4.0 │ │ │ ├── bytes@3.1.0 deduped │ │ │ ├── http-errors@1.7.2 deduped │ │ │ ├── iconv-lite@0.4.24 deduped │ │ │ └── unpipe@1.0.0 deduped │ │ └── type-is@1.6.18 deduped │ ├─┬ content-disposition@0.5.3 │ │ └── safe-buffer@5.1.2 deduped │ ├── content-type@1.0.4 │ ├── cookie@0.4.0 │ ├── cookie-signature@1.0.6 │ ├─┬ debug@2.6.9 │ │ └── ms@2.0.0 │ ├── depd@1.1.2 │ ├── encodeurl@1.0.2 │ ├── escape-html@1.0.3 │ ├── etag@1.8.1 │ ├─┬ finalhandler@1.1.2 │ │ ├── debug@2.6.9 deduped │ │ ├── encodeurl@1.0.2 deduped │ │ ├── escape-html@1.0.3 deduped │ │ ├── on-finished@2.3.0 deduped │ │ ├── parseurl@1.3.3 deduped │ │ ├── statuses@1.5.0 deduped │ │ └── unpipe@1.0.0 │ ├── fresh@0.5.2 │ ├── merge-descriptors@1.0.1 │ ├── methods@1.1.2 │ ├─┬ on-finished@2.3.0 │ │ └── ee-first@1.1.1 │ ├── parseurl@1.3.3 │ ├── path-to-regexp@0.1.7 │ ├─┬ proxy-addr@2.0.6 │ │ ├── forwarded@0.1.2 │ │ └── ipaddr.js@1.9.1 │ ├── qs@6.7.0 │ ├── range-parser@1.2.1 │ ├── safe-buffer@5.1.2 │ ├─┬ send@0.17.1 │ │ ├── debug@2.6.9 deduped │ │ ├── depd@1.1.2 deduped │ │ ├── destroy@1.0.4 │ │ ├── encodeurl@1.0.2 deduped │ │ ├── escape-html@1.0.3 deduped │ │ ├── etag@1.8.1 deduped │ │ ├── fresh@0.5.2 deduped │ │ ├── http-errors@1.7.2 deduped │ │ ├── mime@1.6.0 │ │ ├── ms@2.1.1 │ │ ├── on-finished@2.3.0 deduped │ │ ├── range-parser@1.2.1 deduped │ │ └── statuses@1.5.0 deduped │ ├─┬ serve-static@1.14.1 │ │ ├── encodeurl@1.0.2 deduped │ │ ├── escape-html@1.0.3 deduped │ │ ├── parseurl@1.3.3 deduped │ │ └── send@0.17.1 deduped │ ├── setprototypeof@1.1.1 │ ├── statuses@1.5.0 │ ├─┬ type-is@1.6.18 │ │ ├── media-typer@0.3.0 │ │ └── mime-types@2.1.27 deduped │ ├── utils-merge@1.0.1 │ └── vary@1.1.2 └─┬ express-generator@4.16.1 ├── commander@2.15.1 ├── ejs@2.6.1 ├─┬ minimatch@3.0.4 │ └─┬ brace-expansion@1.1.11 │ ├── balanced-match@1.0.0 │ └── concat-map@0.0.1 ├─┬ mkdirp@0.5.1 │ └── minimist@0.0.8 └── sorted-object@2.0.1 |
You can also create a secure node site (HTTPS) with the following additional steps. They include creating a self-signed secure public and private key. This creates the public key:
openssl genrsa -out key.pem |
The openssl command will generate a private key key.pem file. It generates something like the following text message to console:
Generating RSA private key, 2048 bit long modulus (2 primes) ...........+++++ .............................................+++++ e is 65537 (0x010001) |
Next, you need to generate a self-signed certificate. You do this in two steps.
- Create a Distinguished Name (
DN) file. Thecsr.pemfile is theDNfile. You need it to create a self-signed certificate:openssl req -new -key key.pem -out csr.pem
It will prompt you for values, like the following:
You are about to be asked to enter information that will be incorporated into your certificate request. What you are about to enter is what is called a Distinguished Name or a DN. There are quite a few fields but you can leave some blank For some fields there will be a default value, If you enter '.', the field will be left blank. ----- Country Name (2 letter code) [XX]: State or Province Name (full name) []:MiddleEarth Locality Name (eg, city) [Default City]:Rivendell Organization Name (eg, company) [Default Company Ltd]:Fellowship Organizational Unit Name (eg, section) []:Self Common Name (eg, your name or your server's hostname) []:Localhost Email Address []:bilbo@loth.org Please enter the following 'extra' attributes to be sent with your certificate request A challenge password []:friend An optional company name []:Bilbo
- Use the DN file to create your secure certificate. The following
opensslcommand creates the certificate file by using your private keykey.pemandDNcsr.pemfiles.openssl x509 -req -days 9999 -in csr.pem -signkey key.pem -out cert.pem
It should generate a Secure certificate cert.pem file and return something like the following to the console.
Signature ok subject=C = XX, ST = MiddleEarth, L = Rivendell, O = Fellowship, OU = Self, CN = Localhost, emailAddress = bilbo@loth.org Getting Private key
You can put these private key (key.pem) and certificate (cert.pem) files in an ssl subdirectory of the directory where you put the JavaScript program. The following creates a secure server page with the following code.
/* Construct a secure web server. */ const https = require('https') const fs = require('fs') const port = process.env.PORT || 3000 const options = { key: fs.readFileSync('ssl/key.pem'), cert: fs.readFileSync('ssl/cert.pem') } const server = https.createServer((options, res) => { res.writeHead(200, { 'Content-Type': 'text/plain' }) res.end('Hello world!') }) server.listen(port, () => console.log(`server started on port ${port}); ` + 'press Ctrl-C to terminate...')) |
If you try launch your browser using the localhost instead of a DNS or file resolved network name on the designated port, it will raise the following security error:
This site can't be reached https's server IP address could not be found. DNS_PROBE_FINISHED_NXDOMAIN |
An alternate approach to writing a secure server includes using Express.js library. The syntax changes somewhat and you include two new libraries, as shown below:
/* Construct a secure web server. */ const https = require('https') const express = require('express') const fs = require('fs') const app = express() const port = process.env.PORT || 3001 const options = { key: fs.readFileSync('ssl/key.pem'), cert: fs.readFileSync('ssl/cert.pem') } https.createServer(options, app).listen(port, () => { console.log(`Express started in ${app.get('env')} mode ` + `on port + ${port}.`) }) |
This will fail with the following error message if you’re running it with a global installation unless you set the $NODE_PATH environment variable correctly. Without setting the variable you may get the following error message:
internal/modules/cjs/loader.js:638 throw err; ^ Error: Cannot find module 'express' at Function.Module._resolveFilename (internal/modules/cjs/loader.js:636:15) at Function.Module._load (internal/modules/cjs/loader.js:562:25) at Module.require (internal/modules/cjs/loader.js:692:17) at require (internal/modules/cjs/helpers.js:25:18) at Object.<anonymous> (/var/www/html/node/helloworldsecure.js:3:17) at Module._compile (internal/modules/cjs/loader.js:778:30) at Object.Module._extensions..js (internal/modules/cjs/loader.js:789:10) at Module.load (internal/modules/cjs/loader.js:653:32) at tryModuleLoad (internal/modules/cjs/loader.js:593:12) at Function.Module._load (internal/modules/cjs/loader.js:585:3) |
Sometimes they’ll advise you to do an individual user installation of Express.js to get past this error but that’s not necessary. You just need to set the $NODE_PATH environment variable as follows:
export NODE_PATH=/usr/local/lib/node_modules |
This will enable the JavaScript to work without error and without a specific user installation. Assuming you name either of these programs as helloworldsecure.js, you run them with the following command:
node helloworldsecure.js |
You can terminate the program with a Ctrl+c or if use the kill -15 pid command if you started it as a background process. You can find the process ID (pid) with the jobs command.
As always, I hope this is helpful to those starting out with this cool technology stack.
Subscribe via RSS