Creating a simple inventory management backend app using PostgreSQL and Express

7 min readApr 21, 2019

Introduction

The goal of this post is to create a basic inventory management backend app from scratch using PostreSQL as our database management system and Node.js/Express as our backend.

What you will need:

A text editor of some kind (I will be using Visual Studio Code)
PostgreSQL (https://www.postgresql.org/)
Node.js / Express
Postman to simulate POST requests
Working knowledge of JavaScript and HTML

Setup

At the very start, create a new repository, calling it whatever you like and cd into it. Inside the new repository, initialize npmjs, which will give us access to a variety of useful tools that will make our job easier. The terminal command for this is:

npm init -y

The -y says yes to all the defaults.

Now we can start installing the necessary helper tools.

npm install pg-promise
npm install express
npm install --save-dev nodemon
npm install express-es6-template-engine

Make special note of the ‘ — save-dev’ before nodemon. This allows the dependency to run only in a testing/developer environment, but not when the app is made available for public use. Briefly, here is a explanation of what each tool does:

pg-promise: gives us an easy to use interface for PostgreSQL in JavaScript
express: web application framework for Node.js. Makes building the backend easier
nodemon: automatic server restarts when we make code changes (rather than having to do it manually)

Without writing any code, you should now have 3 things in your repository: a node_modules folder, package-lock.json, and package.json.

We will now add some files of our own to setup the basic structure of our inventory management app. Please add the following to the root of your repository:

index.js (can also call this app.js)
schema.sql
seed.sql
a folder called routes
a folder called controllers
a folder called models

Create the database

Now we can start writing some code. In your text editor, open the schema.sql file. This is where we will create the structure of our database. To try to keep this simple, we will have only three tables: a customers table, an items table, and a purchases table. Here’s the code:

The “id serial primary key” is a unique key that is auto-generated each time new data is created in the table.

Inside of our seed.sql file that we created earlier, we can fill the tables that we just created with some customers, items, and purchases to start us off.

We don’t need to reference the id primary key when creating these because this will be auto-generated each time.

We are almost done creating the database. We need to enter some commands into the terminal to initialize the database and get it running.

createdb inventory-apppsql -f schema.sql inventory-app && psql -f seed.sql inventory-app

The first line creates an empty database. The second line fills the database with our tables and seed info.

Establish connection

We now need to configure our database connector. This will allow us to interact with our database in JS. In your models folder created earlier, create a file called “conn.js”.

Within conn.js, we will put the following:

This pulls in pg-promise (that helpful tool for working with SQL) and establishes a connection to our newly created database. We then export this database connection on the last line, so that we can communicate with the database in other JS files. The key-value pairs in the options object would normally be put in a .env file.

Start server

This part is equally important and will follow essentially the same steps each time. In your index.js file, put the following:

Line 1: We bring in Express, our Node.js framework to make our lives easier
Line 2: We call express and save it in a variable called ‘app’
Line 6: This will be important later when we look for form submissions
Line 10: We use the built-in .listen on port 4000

Remember when we installed nodemon? Go to your package.json file (which was auto-created earlier) and add this to your scripts:

“dev”: “nodemon index.js”

This creates an alias that we can now have npm run to start our server. In the terminal type:

npm run dev

Our server should now be running on port 4000 (or whatever number you gave your port). In your browser, if you navigate to http://localhost:4000/ you should see ‘cannot get /’.

Define functionalities

We want our inventory app to be able to simulate a customer walking into the store and buying something. When this happens, two things will happen:

A record of the purchase will be added to the purchases table created earlier
The inventory of the item purchased will decrease by the quantity purchased by the customer

We will use the model-view-controller (MVC) architectural pattern to make this happen. Inside the routes and controllers folder, add a file named dashboard.js.

The dashboard router will look for external POST requests, and pass those requests along to the dashboard controller. The dashboard controller will then receive those requests, work with the appropriate models (which we are about to create) and then send back a response.

Create models

We are going to work backwards, starting with the models, then the controller, then the router.

Inside the models folder created earlier, add two files: items.js and purchases.js. These models will communicate with our database. Following the functionalities defined above, our items model will update an item’s inventory in the database when a purchase in made. Our purchases model will create a record of that purchase in the purchases table.

Items model

Here is the code for the items.js file:

We import the connection to the database that we created earlier in conn.js, allowing the functions to talk to the database
The inventoryChange function inside the Item class takes two arguments: the itemid and the purchase quantity. Using SQL, the function updates the item’s inventory inside the table that matches the user entered “itemid”. This is how we will decrease the item’s inventory when a purchase has been made
The last line exports the Item class so it can be used in our controller

Purchases model

Here is the code for the purchases.js file:

The Purchase class also needs to be able to talk to the database, so in line 1 we import the database connection
The newPurchase function takes the same two arguments as our function in the Item class: itemid and qty. The function adds a new purchase record into the purchases table, using the user entered item_id and purchase quantity. It then returns the id of that new purchase.

Dashboard Controller

Zooming out for a moment, remember that the dashboard router looks for external POST requests and passes those requests to the dashboard controller, which then utilizes the appropriate models to work with the database and send a response. Now that the models are ready, we can create a function in the controller that talks to the models and simulates a purchase. Here is the code:

Dashboard controller

We import the Item class and the Purchase class using require
The async function simulatePurchase takes two arguments: the req (request), which is the external POST request to purchase an item, and the res (response), which will be what we send back once the purchase has been completed.
The request needs to be able to save form input so that we know what the user submitted. It does this in the magical req.body object, which will look for each form input and store it. Although we haven’t tried to simulate a form input in Postman yet, we know that the two bits of information we’re interested in are the itemid of the item and qty that is being purchased. These would match the name attribute of our form inputs, or the keys in Postman when we simulate a form.
simulatePurchase fulfills the two functionalities that we defined initially. It creates a record of the purchase using the Purchase class newPurchase function. It also decrease’s the item’s inventory, using the Item class inventoryChange function.
Finally, the function uses res.send to send the response back with the new purchase record’s ID. This is what is shown to the user after the form has been submitted and processed.

Dashboard Router

We almost have a functioning inventory app. The last step is to wire up a router that will listen for HTTP POST requests. A form is created with a method attribute, which says how to send the form data. In our case, this is will be POST. A form also has an action attribute, which tells the form where to send the data upon submission.

We are going to be looking for an HTTP POST request being sent to /buy. The full URL is localhost:4000/buy (or whatever you set your port to).

We import Express and create a router using the built in express.Router() method.
We import the simulatePurchase function created earlier in the controller.
Line 9 uses the router to look for a post request being sent to /buy, and then passes the request along to the simulatePurchase controller function.

One final step is to add a starting route in our index.js file, which will send every request, regardless of its type, to the dashboard router. In more complex applications, there would be multiple routers handling different kinds of requests. Put this code somewhere above the app.listen :

const dashboardRouter = require('./routes/dashboard');

app.use('/', dashboardRouter);

Send a request

In Postman, we can now simulate a post request to localhost:4000/buy. For example, let’s say a customer is purchasing a table (which in my database has an itemid of 3).