In software design, architectural choices have a significant influence on the maintainability of your code base. Choosing the proper toolkit, while important, is not enough to guarantee a maintainable solution. Frameworks attempt to solve this problem by imposing an architecture on your code base that (hopefully) uses best practice patterns in software design to help you build a maintainable code base. I will return to the issue of frameworks (and their strengths and weaknesses) another day. 

Meanwhile, let's explore some architectural design choices we can make with the NodeJS mysql package when building CRUD applications. You can refer to my post on Creating a NodeJS REST API with Express and MySQL as a base design blueprint for building a CRUD application using the MySQL package. The application in the post makes several design choices that are not explained in any detail. Let's explore them now.

Connection Pooling

Making a request to MySQL from a NodeJS application (or any other program) requires a connection. Database platforms like MySQL support multiple simultaneous connections. Large MySQL instances may handle hundreds or even thousands of connections at a time. So why does connection pooling matter? Each connection requires both time and memory to create and release. Maintaining and re-using a pool of connections is therefore far more efficient than creating a new connection for each query. Let's look at the starting bit of code from taskdao.js in the sample app:

var mysql = require( 'mysql' );

module.exports = init;

const pool = mysql.createPool({
  connectionLimit : 50,
  host            : 'localhost',
  user            : '<user>',
  password        : '<password>',
  database        : '<db>'
});

function init(){
  return dao;
}

If you refer to taskgateway.js, you will see that this bit of code is virtually identical in the taskgateway, except that it is returning the gateway object instead of the dao object. I could have given these objects a generic name like myObject and then the starting bit of each could would have been identical, as both packages would return myObject in the init() function.

While there is nothing wrong with this solution, it isn't ideal. I made an architectural choice to create a connection pool in both the dao and gateway objects, but these are separate connection pools, each consuming memory and available connections from the MySQL server. The solution doesn't need separate connection pools, and I probably don't want separate connection pools, at least not in a small scale application. At very large  scale, I might choose to isolate the gateway in its own connection pool, but the vast majority of NodeJS solutions will never approach the kind of scale that makes such architectural decisions necessary. Better instead to focus on a generalized solution.

Instead of including the mysql package in each package that needs to interact with the MySQL database, let's create a central place to hold database configuration information and the connection pool. Create a folder called config and inside the folder a file called dbconfig.js:

config/dbconfig.js

var mysql = require( 'mysql' );

const pool = mysql.createPool({
  connectionLimit : 50,
  host            : 'localhost',
  user            : '<user>',
  password        : '<password>',
  database        : '<database>'
});

module.exports = {
  pool: pool
};

Now let's replace the opening bit of the taskdao and taskgateway packages with code that references our dbconfig packages and its connection pool.

const dbConfig = require( "../config/dbconfig.js" );

module.exports = init;

const pool = dbConfig.pool;

What did we do? By moving the call to create the connection pool to the dbconfig package, we have merged the two separate connection pools into one pool that handles connections for both the dao and gateway packages. We have also made our code more maintainable by centralizing the location of database configuration information across the application. In the example application, we only have two places where this information is used, but in a real-world application, we might have dozens of dao and gateway files that use the connection pool. Centralizing the pool in such real-world cases isn't just a matter of architectural preference - it is necessary to both the operation and maintainability of our application.

From a design standpoint, the dbconfig package is the only part of the code that needs to know about the mysql package and the configuration of the connection pool. The dao and gateway objects need access to the connection pool, which this solution provides. We adhere to the DRY principle by not repeating the connection pooling code, and we adhere to the principle of separation of concerns by centralizing the configuration.

CRUD and Return Values

In a CRUD application, much of the code base is concerned with implementing CRUD operations, enabling a user to operate against the database using a well-defined API. Looking at our example application, I made some design choices that deserve exploring. In the case of both the create and update functions, I chose to return an updated copy of the task being created or updated. From a design standpoint, returning the updated task is a manner of fulfilling the REST architectural style by returning a representation of the created/updated task. Detailing how closely the example app adheres to and varies from the REST RFC is outside the scope of this post, but the general idea with create is that the operation should return a representation of what was created. Update requires only a response that the operation was successful and a date/time of execution. I chose to include a representation of the updated object with the update operation, mostly as a practical matter for my client implementation, which I will share soon.

What matters about this architectural choice is how it affects our code. Let's examine the create function in controlllers/tasks.js:

  create: function( request, response){
    taskservice.create( request.body.taskName, request.body.dateDue )
      .then( function( results ){
        //we are reading back the inserted row
        taskservice.read( results )
          .then( function( task ){
            response.send( JSON.stringify( task ) );
          })
          .catch( function( error ){
            console.log( error );
            response.send( JSON.stringify( error ) );
          });
      })
      .catch( function( error ){
        console.log( error );
        response.send( JSON.stringify( error ) );
      });
  }

The create function calls taskservice.create(), which returns a Promise object from the taskdao.create() function. We handle the resolution of the promise in the first .then() block, where we call the taskservice.read() function with the results of the create() call. If you look at the create() function in taskdao.js, you will see that the return value from the function is the taskID of the inserted record, held in results.insertId.  Since the read() function also returns a Promise object, we have another .then() block to handle the return value from the read() call. I could potentially make this read() call in another location, but keeping in mind the separation of concerns principle, I chose to keep all the code that handles Promises returned from the dao in a central location.

Conclusion

Good software design requires making architectural choices that enhance both the operation and maintainability of your code base. In my example app using the mysql package with NodeJS to create a CRUD application, the original application did not centralize the configuration of the connection pool for the application, potentially leading the problems with the long-term operation and maintenance of the application. By centralizing the database information in the dbconfig package, we solve a number of architectural problems and improve the operation of our application, particularly if we expand the scope of our application beyond just operating against the tasks table in the database.

In the design of the API in the example app, I made an architectural choice to return object representations for both create and update functions. That choice requires implementing a call to the read() function in order to return the created/updated value. While there are other possible places I could have made this call, I chose to make it in the same location as the create call so that I could have all of the code that handles the Promises returned from the dao in the same location.

 

That's all for now. Next time, I will share my client code for the Tasks application that operates against the NodeJS app.