Module 0337: Using mysql-await to access MariaDB

Tak Auyeung, Ph.D.

October 12, 2021

Contents

 1 About this module
 2 Why mysql-await?
 3 Setting up the credential JSON file
 4 Testing the credential
 5 An alternative using synchronous methods
 6 An alternative to an alternative that works well with Express+node
 7 MariaDB access and Express

1 About this module

2 Why mysql-await?

There are many node modules that offer MariaDB/Mysql access. mysql-await stands out due to the async functions offered by this module.

3 Setting up the credential JSON file

The first step to use mysql-await is to set up a JSON (JavaScript Object Notation) file that stores your database access credentials. It is a terrible idea to hard code account names, and especially passwords, in a script.

To do this, create a file of this path ~/.mysqlSecrets.json. Note that this file is not created in ~/express or ~/express.debug. There are several reasons. First, all your scripts that access the database needs this files, including the Express scripts, but also any sample program that you may want to use.

Use your favorite editor to create this file. For example, if you are using nano, then use the following command:

nano ~/.mysqlSecrets.json

In the editor, specify something like the following (replace with information that is specific to you):

{ 
  "connectionLimit": 1, 
  "host": "localhost", 
  "user": "auyeungt", 
  "password": "1234567", 
  "database": "auyeungt" 
}

In your file, keep connectionLimit to 1, and keep the host as localhost. Modify all the other entries. The password is not the Power sign in password, but rather the MariaDB password.

Since this file name starts with a period (.), it is a hidden file that a plain ls command will not list. You will need to use ls -a to list this file. Let us protect this file further by changing the access permissions:

chmod u=rw ~/.mysqlSecrets.json

This effect of this command does not affect the visibility or access of this file to you, the user. However, it makes the file inaccessible to all other users.

4 Testing the credential

Use the following code to test the credentials.

Listing 1:A script to test the credentials
 
"use strict"; 
module.paths.unshift(’/usr/lib/node_modules’) 
const fs = require("fs") 
const mdb = require("mysql-await") 
const os = require("os") // needed for os.homedir() because ~ does not expand 
 
async function main() 
{ 
  // the following creates and configures an object to represent 
  // a connection, but makes no attempt to connect 
  let mdbConnection = mdb.createConnection( 
    JSON.parse( // decode JSON content 
      fs.readFileSync( // from the file 
        os.homedir()+"/.mysqlSecrets.json", // in the home folder 
        { encoding: "utf8" } 
      ) 
    ) 
  ) 
 
  // the following actually communicates with MariaDB to try to 
  // make a connection, this takes time! 
  await mdbConnection.awaitConnect() 
 
  console.log(’connected’) 
 
  // all operations that need the data base should be here 
 
  // the following concludes the connection with MariaDB 
  await mdbConnection.awaitEnd() 
  console.log(’disconnected’) 
} 
 
main()

You can download this script.

If the output of this program is the following, then everything is working out. 

connected
disconnected

As the comments explain, the tilde (~) symbol expands to the home directory when you are in the shell (where you interactively issue CLI commands). However, in a node script, there is no shell (such as bash) to expand the tilde symbol. Fortunately, the os (operating system) module supplies the homedir method that returns the full path of the home directory as a string.

5 An alternative using synchronous methods

mysql-await is an extension to mysql (the oirignal node module for access MariaDB/Mysql). mysq (the module) offers synchronous methods.

The synchronous methods are not suitable in a single thread server when it is serving simultaneous incoming requests. However, because nothing can be done before the database connection is established, using asynchronous operations does not actually offer any advantage.

The transition from asynchronous to synchronous operations is actually very straightforward in this case. The following script makes use of the original mysql module synchronous methods.

Listing 2:Connection initialization using synchronous methods
 
"use strict"; 
module.paths.unshift(’/usr/lib/node_modules’) 
const fs = require("fs") 
const mdb = require("mysql-await") 
const os = require("os") // needed for os.homedir() because ~ does not expand 
 
function main() 
{ 
  // the following creates and configures an object to represent 
  // a connection, but makes no attempt to connect 
  let mdbConnection = mdb.createConnection( 
    JSON.parse( // decode JSON content 
      fs.readFileSync( // from the file 
        os.homedir()+"/.mysqlSecrets.json", // in the home folder 
        { encoding: "utf8" } 
      ) 
    ) 
  ) 
 
  // the following actually communicates with MariaDB to try to 
  // make a connection, this takes time! 
  mdbConnection.connect() 
 
  console.log(’connected’) 
 
  // all operations that need the data base should be here 
 
  // the following concludes the connection with MariaDB 
  mdbConnection.end() 
  console.log(’disconnected’) 
} 
 
main()

You can download this script.

The advantage of using the synchronous methods is that the code can now exist outside of a function. await can only be used inside a function that is declared async, any statement that is not in a function definition is not in an async environment.

6 An alternative to an alternative that works well with Express+node

Most apps require the connection to a database to serve most end-points. As a result, it is not unusual that many developers use variables in the global scope. Any name that is not declared within a block (enclosed by braces {}) is visible everywhere in the script.

The following alternative makes the connection a global variable so all end-point handlers can use it:

Listing 3:Using synchronous methods to initialize a global database connection variable
 
"use strict"; 
module.paths.unshift(’/usr/lib/node_modules’) 
const fs = require("fs") 
const mdb = require("mysql-await") 
const os = require("os") // needed for os.homedir() because ~ does not expand 
 
// the following creates and configures an object to represent 
// a connection, but makes no attempt to connect 
let mdbConnection = mdb.createConnection( 
  JSON.parse( // decode JSON content 
    fs.readFileSync( // from the file 
      os.homedir()+"/.mysqlSecrets.json", // in the home folder 
      { encoding: "utf8" } 
    ) 
  ) 
) 
 
// the following actually communicates with MariaDB to try to 
// make a connection, this takes time! 
mdbConnection.connect() 
 
console.log(’connected’) 
 
// all operations that need the data base should be here 
 
// the following concludes the connection with MariaDB 
mdbConnection.end() 
console.log(’disconnected’)

You can download this script.

Although the use of global variables is generally considered not-the-best method, there are very limited ways to avoid the use of global variables in an Express+node end-point handler. This is because an end-point handler is a call-back function, it is called asynchronously by the event loop of Express+node. As such, an Express+node script cannot explicitly pass parameters, such as a database connection object, to the end-point handler.

As a result, in the end, the code in this section is the most common way to initialize the connection object to a database. The initialization code of Express should be placed after mdbConnection.connect() to ensure that when an end-point is handled, the database connection object mdbConnection is already initialized.

7 MariaDB access and Express

It is best to initialize a connection to MariaDB before Express is initialized. This is because Express can handle sessions and session variables using a database. Furthermore, the handlers of end-points are also likely to require database operations.