Notes about a NodeJS program I wrote at work - 2nd March, 2023
Tags: node
Published: 2023-03-09 11:52:24

The app was about an ExpressJS web server accessing MySQL database. The client is written using the [EJS](https://ejs.co/) - 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](https://www.npmjs.com/package/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](https://nodejs.org/docs/latest-v14.x/api/util.html).

---

## 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):

![Screenshot of the partial client user interface](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/5zaq78mv6kqmkysuonhu.png)

---

## 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. 

---