Notes about a NodeJS program I wrote at work - 2nd March, 2023

The app was about an ExpressJS web server accessing MySQL database. The client is written using the EJS - a templating language that lets generate HTML using JavaScript. I will briefly touch upon some aspects of the code for the server as well as the client.

MySQL Database Queries

The database CRUD (Create, Read, Update and Delete) queries were written as stored procedures (or procedures). The procedures were called in the NodeJS program using the mysql library calls. An advantage of using the procedures is that the stored procedure is defined and stored in the database along with table, index, and other database object definitions. A call to the procedure executes it on the database server and returns the result. This also allows your application code to be clean and simple (avoiding the SQL query syntax details).

A sample procedure code looks like the following. This code updates a table row based upon a provided items table id and quantity values and returns the updated row count as a result.

DELIMITER $$
DROP PROCEDURE IF EXISTS update_item;
CREATE PROCEDURE update_item(IN id INT, IN quantity_in INT)
BEGIN
    UPDATE items
    SET quantity = quantity + quantity_in 
    WHERE  item_id = id;

    SELECT ROW_COUNT() AS updated_count;
END$$
DELIMITER ;

The NodeJS code calling the procedure:

const sql = "CALL update_item(?, ?)";
connection.query(sql, [ id, quantity ], function (error, result) {
    // work with the query result or the error object ...
})

Sometimes, I also had to use the async-await syntax for the database query code:

// this in an async function ...
try {
    const query = util.promisify(connection.query).bind(connection);
    const result = await query(sql, [ id, quantity ]);
    // ...
}
catch(error) { 
// ...

Note that the util in the above code is the NodeJS util module.

The App's Client

This is a sample EJS client code used in the app:

<%- include("header", { title: title }); %>

<div>Browse items and perform edits on them here.
  <form action="item_edit" method="post">
    <input type="hidden" name="_method" value="add" >
    <button type="submit"><i class="fa fa-plus"></i> Add Item</button>
  </form>
</div>

<table>
  <thead>
    <tr>
      <th>Id</th>
      <th>Name</th>
      <th>Quantity</th>
      <th>Action</th>
    </tr>
  </thead>
  <tbody>
    <% items.forEach(function(row) { %>
      <tr>
        <td><%= row.item_id %></td>
        <td><%= row.name %></td>
        <td><%= row.quantity %></td>
        <td>
          <form action="item" method="post">
            <input type="hidden" name="id" value=<%= row.item_id %> >
            <input type="hidden" name="_method" value="delete" >
            <button type="submit"><i class="fa fa-trash"></i></button>
          </form>
          <form action="item_edit" method="post">
            <input type="hidden" name="_method" value="edit" >
            <input type="hidden" name="id" value=<%= row.item_id %> >
            <button type="submit"><i class="fa fa-pencil"></i></button>
          </form>
        </td>
      </tr>
    <% }); %>
  </tbody>
</table>

<%- include("footer"); %>

The screenshot of the client as seen in the browser window (only the relevant rendered HTML is shown):

Comments

A typical web app has aspects of database, a web server and a browser client. The web server provides the services to access the database and send data to the client. All these together are typically referred as a full-stack application.

Writing such an app is the full-stack development. Full stack development requires skills in programming specific languages (in this case NodeJS, JavaScript), databases and the client side scripting. In addition, the knowledge of how the components of the stack connect, interact and provide specific functionality.