Developing a CRUD Node.js Application with PostgreSQL
Tags: tutorial, node, postgres, webdev
Published: 2021-02-09 22:39:42

---
title: Developing a CRUD Node.js Application with PostgreSQL
published: true
description: Article which teaches how to develop a CRUD application with Node.js & PostgreSQL focusing on the Back-End.
tags: tutorial, nodejs, postgresql, webdev
//cover_image: https://direct_url_to_image.jpg
---

Hey all Coders! How are you doing? Today we are going to start a series of articles where we will be developing a CRUD application with Node.js & PostgreSQL focusing on the Back-End. After that, we will be migrating the project to serverless architecture with **[Azure Functions](https://docs.microsoft.com/azure/javascript/node-howto-write-serverless-code?WT.mc_id=javascript-11915-gllemos)**, migrating the local database to **[Azure PostgreSQL](https://azure.microsoft.com/services/postgresql/?WT.mc_id=javascript-11915-gllemos)**, and finally deploying this application using **[GitHub Actions](https://docs.microsoft.com/azure/app-service/deploy-github-actions?WT.mc_id=javascript-11915-gllemos)**.

I created a repository on GitHub **[HERE](https://github.com/glaucia86/nodejs-postgresql-azure)**, where all the source code of this project and the resources that I will be mentioned during the 5 articles are allocated.

> updated repository **[HERE](https://github.com/glaucia86/employee-crud-api)**.

> *Important information: the focus of the project will be on the Back-End. If, when we reach the end of the project, we want to test the Back-End API created in some application in the Front-End, I can then be creating a generic Front-End just to test the created api!*

Well, I talked a lot, let's go ?!

## Table of Contents

 * [ Resources Used during the Tutorial](#resources-used-during-the-tutorial)  
 * [ Installing PostgreSQL Database](#installing-postgresql-database)  
 * [ Creating Table in PostgreSQL](#creating-table-in-postgresql)  
 * [ Creating the Application Architecture in Node.js](#creating-the-application-architecture-in-nodejs)  
 * [ Developing the Application](#developing-the-application)  
 * [ Understanding a little bit more about the package: 'node-postgres'](#understanding-a-little-bit-more-about-the-package-nodepostgres)  
 * [ Creating an environment variable with 'dotenv'](#creating-an-environment-variable-with-dotenv)  
 * [ Configuring the connection string to the database in the file: 'database.js'](#configuring-the-connection-string-to-the-database-in-the-file-databasejs)  
 * [ Route: (POST) 'Create Product'](#route-post-create-product)  
 * [ Route: (GET) 'List All Products'](#route-get-list-all-products)  
 * [ Route: (GET) 'List Product by Id'](#route-get-list-product-by-id)  
 * [ Route: (PUT) 'Update Product by Id'](#route-put-update-product-by-id)  
 * [ Route: (DELETE) 'Delete Product by Id'](#route-delete-delete-product-by-id)  
 * [ Conclusion](#conclusion) 

## Resources Used during the Tutorial

I will mention here some resources that we will use during the development of this application throughout this article. Here they are:

* **[Visual Studio Code](https://code.visualstudio.com/?WT.mc_id=javascript-11915-gllemos)**
* **[Node.js](https://nodejs.org/en/)**
* **[Postman](https://www.getpostman.com/)**
* **[Package - node-postgres](https://node-postgres.com/)**
* **[PostgreSQL](https://www.postgresql.org/)**


One of the things that attract many developers to use PostgreSQL is that it is completely free, available on any Operating Systems, and best of all: it is completely **[Open Source](https://github.com/postgres)**! Countless large companies like Uber, Netflix, Spotify, Instagram, Reddit, and many others make use of PostgreSQL. That's why PostgreSQL is so popular! 

PostgreSQL was the first database that I learned to work with and it was with it that I learned in Database I and II classes in 2012 at the University. I always liked PostgreSQL because it is very simple and easy to use!

We'll need to install PostgreSQL on our machine. But also feel free to use some Docker image to continue this tutorial.

## Installing PostgreSQL Database

Well, I'll teach you here how to install PostgreSQL for different OS users:

* **Windows**: for Windows users, **[download Postgres for Windows](https://www.postgresql.org/download/windows/)** . Common and simple installation, as if you were installing a program on Windows.

* **macOS**: for Mac users, just download the packages **[HERE](https://www.postgresql.org/download/macosx/)**. Also, you will need to have **[Homebrew](https://brew.sh/)** installed. If you have some issues or difficulty installing, I recommend you to take a look at this video **[HERE](https://www.youtube.com/watch?v=5AOkxqFaYEE)**

* **Linux**: for Linux users, as there are countless different versions of Linux, I recommend you to take a look at this PostgreSQL guide **[HERE](http://postgresguide.com/setup/install.html)**

In my case, I will be using Windows, since it is my main development machine. I will be using PostgreSQL version 12. After installing, just search for pgAdmin. A browser page will open on **[http://127.0.0.1:16450/browser/](http://127.0.0.1:16450/browser/)** and now we can start to use it!

[![Screen-Shot-03-01-20-at-10-01-PM.png](https://i.postimg.cc/fybcCy7H/Screen-Shot-03-01-20-at-10-01-PM.png)](https://postimg.cc/phg5X268)

## Creating Table in PostgreSQL

We will now create the table with the properties that will be used to persist to be used in our Back-End.

The class will be: `Product`

```sql

Class: Product

- productId: integer primary
- product_name_: varchar
- quantity: int
- price: real

```

Now open PgAdmin. It is likely that you will need to include the password to perform some actions within PgAdmin. It is likely that you will need to create a Database. Just create with the name you want. After you've created your database, right-click on **Create Script**, and run the script below on PostgreSQL (as shown in the gif below):

```sql

CREATE TABLE products (
    productId SERIAL PRIMARY KEY,
    productName VARCHAR(255) NOT NULL,
    quantity INTEGER NOT NULL,
    price NUMERIC(5,2)
);

```


[![postgresql-01.gif](https://s5.gifyu.com/images/postgresql-01.gif)](https://gifyu.com/image/IKZa)

Now, all you have to do is access the newly created table!

[![postgresql-02.gif](https://s5.gifyu.com/images/postgresql-02.gif)](https://gifyu.com/image/IKZt)

Great! We have already created our table!

## Creating the Application Architecture in Node.js

Now that our table is created, let's create the project in Node.js. In this project, I will follow the SOLID  & Clean Code principle. If you want to know more about these two topics, I highly recommend you to see these 2 links below:

{% youtube XzdhzyAukMM %}

* 📄 **[SOLID Code in Node.JS](https://medium.com/@nanofaroque/solid-code-in-nodejs-a87685b4cdfe)**

Well, let's start to architect our project. Create a folder called API and run the following command:

```bash
> npm init -y
```

This command creates a standard package.json file. And now we will install the following packages:

```bash

> npm i --save-dev husky nodemon

``` 

And also install the other packages as dependencies:

```bash

> npm i cors dotenv express express-promise-router pg

```

And finally, the `package.json` file will look like this:

```json
{
  "name": "crud-nodejs-psql",
  "version": "1.0.0",
  "description": "Aplicação CRUD com Node.js & PostgreSQL",
  "main": "server.js",
  "scripts": {
    "dev": "nodemon",
    "lint": "eslint --ext .js,.html -f ./node_modules/eslint-friendly-formatter . --fix",
    "prepush": "npm run lint",
    "start": "node server.js"
  },
  "keywords": [
    "node.js",
    "javascript",
    "postgresel",
    "azure",
    "serverless",
    "azure-functions",
    "azure-devops",
    "azure-storage",
    "github-actions",
    "app-service",
    "express"
  ],
  "author": "Glaucia Lemos",
  "license": "MIT",
  "bugs": {
    "url": "https://github.com/glaucia86/nodejs-postgresql-azure/issues"
  },
  "homepage": "https://github.com/glaucia86/nodejs-postgresql-azure#readme",
  "devDependencies": {
    "eslint": "^6.8.0",
    "eslint-config-airbnb-base": "^14.0.0",
    "eslint-plugin-import": "^2.20.1",
    "husky": "^4.2.3",
    "nodemon": "^2.0.2"
  },
  "dependencies": {
    "cors": "^2.8.5",
    "dotenv": "^8.2.0",
    "eslint-friendly-formatter": "^4.0.1",
    "eslint-plugin-html": "^6.0.0",
    "express": "^4.17.1",
    "express-promise-router": "^3.0.3",
    "pg": "^7.18.2"
  }
}
```

Create the structure of the folders and files as shown in the image below:

[![Screen-Shot-03-05-20-at-10-50-PM.png](https://i.postimg.cc/Xv6KhNwR/Screen-Shot-03-05-20-at-10-50-PM.png)](https://postimg.cc/9D1qwhgb)

## Developing the Application

Here after, I will not explain what each file does. Because the main focus of the article is to create a RESTful API, which in the end must be persisted in PostgreSQL.

Open **[Visual Studio Code](https://code.visualstudio.com/?WT.mc_id=devto-blog-gllemos)** and let's start developing the file: server.js and include the following code block:

* **file: server.js**

```javascript
/**
 * File: server.js
 * Description: arquivo responsável por toda a configuração e execução da aplicação.
 * Data: 02/03/2020
 * Author: Glaucia Lemos
 */

const app = require('./src/app');

const port = process.env.PORT || 3000;

app.listen(port, () => {
  console.log('Aplicação executando na porta ', port);
});
```

Now, open the `src -> app.js` file and include the code block below:

* **file: app.js**

```javascript
const express = require('express');
const cors = require('cors');

const app = express();

// ==> Rotas da API:
const index = require('./routes/index');
// const productRoute = require('./routes/product.routes');

app.use(express.urlencoded({ extended: true }));
app.use(express.json());
app.use(express.json({ type: 'application/vnd.api+json' }));
app.use(cors());

app.use(index);
// app.use('/api/', productRoute);

module.exports = app;
```


Note that in this `app.js file`, there are several lines of code commented on, just to be able to initially execute our API and see if we are developing correctly. But, later on we will make some significant changes to this file and then uncomment these lines.

And finally, open the `src -> routes -> index.js` file and include the code block below:

* **file: src -> routes -> index.js**

```javascript
/**
 * File: src/routes/index.js
 * Description: arquivo responsável pela chamada da Api da aplicação.
 * Data: 02/03/2020
 * Author Glaucia Lemos
 */

const express = require('express');

const router = express.Router();

router.get('/api', (req, res) => {
  res.status(200).send({
    success: 'true',
    message: 'Seja bem-vindo(a) a API Node.js + PostgreSQL + Azure!',
    version: '1.0.0',
  });
});

module.exports = router;
```

Now, open the command prompt inside the apifolder and run the command:

```bash

> nodemon

``` 


And then open the postman and include the following URL in (GET): `localhost:3000/api/`:

[![Screen-Shot-03-02-20-at-12-47-AM.png](https://i.postimg.cc/L5rv4Wbk/Screen-Shot-03-02-20-at-12-47-AM.png)](https://postimg.cc/jn4yZMKC)

If the screen is shown just like the one you can see above, this means our API is working correctly! Now, let's go deeper into development. Here we go!

## Understanding a little bit more about the package: 'node-postgres'

You may have noticed that when we installed some packages, we included the **[node-postgres](https://node-postgres.com/)** package. This package will be essential for us to work with the PostgreSQL Client on Node.js.

This package is an **[open source](https://github.com/brianc/node-postgres)** project. And it has simple and straightforward documentation - teaching how we can implement this package in Promises or using Async / Await. It helped me a lot to develop this tutorial!

I recommend reading the package documentation, which can be found here **[HERE](https://node-postgres.com/features/pooling)**.

In this project, I decided to use node-postgres rather than Sequelize, which is a widely used ORM for those who use PostgreSQL, MySQL, MariaDB, SQLite, and Microsoft SQL Server. Just to make this `project-1` easier.

As we already installed the node-postgres package at the beginning, let's move on!

## Creating an environment variable with 'dotenv'

Another point that you may have noticed is that we also installed the dotenv package. This server package is important so that we can store our environment variables that we don't want to make available to the public when you do a `commit`.

And as how we will use the database connection string and as how this connection string has sensitive data we don't want to make it available to everyone. We will address this now in our project. To do this, follow the steps below:

```sql

DATABASE_URL=postgres://{db_username}:{db_password}@{host}:{port}/{db_name}

```

If you don't know what your PostgreSQL db_usernameis, just right click on the PgAdmin Server and go to **Properties -> Connections** and you will find the username. See the gif below:

[![postgresql-03.gif](https://s5.gifyu.com/images/postgresql-03.gif)](https://gifyu.com/image/IuWY)

## Configuring the connection string to the database in the file: 'database.js'

Now that we have included our `connectionstring` in the **.env** file, now is time to start developing and configuring our application's database connection to PostgreSQL.

To do this, open the `database.js` file and include the following code block:

* **config/database.js:**

```javascript
/**
 * Arquivo: config/database.js
 * Descrição: arquivo responsável pelas 'connectionStrings da aplicação: PostgreSQL.
 * Data: 04/03/2020
 * Author: Glaucia Lemos
 */

const { Pool } = require('pg');
const dotenv = require('dotenv');

dotenv.config();

// ==> Conexão com a Base de Dados:
const pool = new Pool({
  connectionString: process.env.DATABASE_URL
});

pool.on('connect', () => {
  console.log('Base de Dados conectado com sucesso!');
});

module.exports = {
  query: (text, params) => pool.query(text, params),
};
```

This block is connecting with our connection string so that we can finally begin to persist data in PostgreSQL using Node.js.

Note that we are using **node-postgres package**. If you want to understand a little more about **pg.Pool**, I recommend reading **[HERE](https://node-postgres.com/api/pool)**, as from now on we will use it a lot, including the properties of this builder!

## Route: (POST) 'Create Product'

We've done the configuration of our application and we are already making the connection to the database! Now the game will start! And for that, we will develop the first route. For this, we will use a lot from now on two files: **product.controller.js** and **product.routes.js**.

Follow these steps:

Include the code block below in the **product.routes.js** file

* **file: product.routes.js**

```javascript
// @ts-nocheck
/**
 * Arquivo: src/routes/product.routes.js
 * Descrição: arquivo responsável pelas rotas da api relacionado a classe 'Product'.
 * Data: 04/03/2020
 * Author Glaucia Lemos
 */

const router = require('express-promise-router')();
const productController = require('../controllers/product.controller');

// ==> Definindo as rotas do CRUD - 'Product':

// ==> Rota responsável por criar um novo 'Product': (POST): localhost:3000/api/products
router.post('/products', productController.createProduct);

module.exports = router;
```

Now we will develop the logic of the method createProductinside the file `product.controller.js`

* **controllers/product.controller.js**

```javascript
const db = require("../config/database");

// ==> Método responsável por criar um novo 'Product':

exports.createProduct = async (req, res) => {
  const { product_name, quantity, price } = req.body;
  const { rows } = await db.query(
    "INSERT INTO products (product_name, quantity, price) VALUES ($1, $2, $3)",
    [product_name, quantity, price]
  );

  res.status(201).send({
    message: "Product added successfully!",
    body: {
      product: { product_name, quantity, price }
    },
  });
};
```

Note that we simply use the insert query in the code, just as we would in an SQL script. Just like that. And of course, to return all the values entered we put a message to confirm the created product and returning all values for that product.

Now we need to update the **app.js** file before testing our application. To do this, uncomment the lines where the **product.routes** were:

* **file: app.js**

```javascript
/**
 * Arquivo: app.js
 * Descrição: arquivo responsável por toda a configuração da aplicação.
 * Data: 02/03/2020
 * Author: Glaucia Lemos
 */

const express = require('express');
const cors = require('cors');

const app = express();

// ==> Rotas da API:
const index = require('./routes/index');
const productRoute = require('./routes/product.routes');

app.use(express.urlencoded({ extended: true }));
app.use(express.json());
app.use(express.json({ type: 'application/vnd.api+json' }));
app.use(cors());

app.use(index);
app.use('/api/', productRoute);

module.exports = app;
```

Open the command prompt and type the following command into the api folder

```bash

> nodemon

```

1. Now we can test our first created route. So now, open Postman at the following endpoint: (POST) **localhost: 3000/api/products**, as shown in the gif below:

[![postgresql-04.gif](https://s5.gifyu.com/images/postgresql-04.gif)](https://gifyu.com/image/IucG)

If the following message appears as below:

```json
{
    "message": "Product added successfully!",
    "body": {
        "product": {
            "product_name": "Logitech MK270 Wireless Keyboard and Mouse Combo",
            "quantity": "2",
            "price": "18.99"
        }
    }
}
```

It is because it persisted perfectly. To be sure of that, open the PgAdmin of the created table and do as shown in the gif below:

[![postgresql-05.gif](https://s5.gifyu.com/images/postgresql-05.gif)](https://gifyu.com/image/IugE)

Perfect! Once we manage to create the first route, the others will be easy!,

Let's keep on!

## Route: (GET) 'List All Products'

Now we are going to create the route that will list all created and persisted products in PostgreSQL. As the POST route is already created, it would be good for you if you included more data to help with future routes!

Return to the **product.routes.js** file again and add the route to list all products:

* **file: product.routes.js**

```javascript

// ==> Rota responsável por listar todos os 'Products': (GET): localhost:3000/api/products
router.get('/products', productController.listAllProducts);
```

Now, go back to the file **product.controller.js** and let's develop the logic of the **listAllProducts** method:

* **file: product.controller.js**

```javascript

// ==> Método responsável por listar todos os 'Products':
exports.listAllProducts = async (req, res) => {
  const response = await db.query('SELECT * FROM products ORDER BY product_name ASC');
  res.status(200).send(response.rows);
};

```

Note that the query I made here was: **SELECT * FROM products ORDER BY product_name ASC**. Here I am asking to return all products persisted in PostegreSQL in alphabetical order! I did it to make it a little different! ;)

Let's test it. Open Postman and see the result:

[![postgresql-06.gif](https://s5.gifyu.com/images/postgresql-06.gif)](https://gifyu.com/image/Iu4g)

It worked perfectly! Note that if we need to do a more elaborate SELECT with subqueries, following the logic, it will work perfectly! :)

## Route: (GET) 'List Product by Id'

Now, it's very easy. Just combine our knowledge of SQL with the other CRUD's that we have already created in other Node.js applications.

Now let's create the route to list a specific product by Id. Again, open the file product.routes.js and include one more route:

* **file: product.routes.js**

```javascript
(...)

// ==> Rota responsável por selecionar 'Product' pelo 'Id': (GET): localhost:3000/api/products/:id
router.get('/products/:id', productController.findProductById);

(...)
```

Now open the file **product.controller.js** and we will develop the logic of this route:

* **file: product.controller.js**

```javascript
(...)

// ==> Método responsável por selecionar 'Product' pelo 'Id':
exports.findProductById = async (req, res) => {
  const productId = parseInt(req.params.id);
  const response = await db.query('SELECT * FROM products WHERE productid = $1', [productId]);
  res.status(200).send(response.rows);
}
```

Let's test this route on Postman now and see what happens:

[![postgresql-07.gif](https://s5.gifyu.com/images/postgresql-07.gif)](https://gifyu.com/image/Iu6x)


## Route: (PUT) 'Update Product by Id'

Now let's go back to the **product.routes.js** file to create the **updateProductById** route that will be responsible for updating the product by the Id:

* **file: product.routes.js**

```javascript
(...)

// ==> Rota responsável por atualizar 'Product' pelo 'Id': (PUT): localhost: 3000/api/products/:id
router.put('/products/:id', productController.updateProductById);
```

Let's return to the **updateProductById** file to develop the logic of the **product.controller.js** method:
 
* **file: product.controller.js**

```javascript
(...)

// ==> Método responsável por atualizar um 'Product' pelo 'Id':
exports.updateProductById = async (req, res) => {
  const productId = parseInt(req.params.id);
  const { product_name, quantity, price } = req.body;

  const response = await db.query(
    "UPDATE products SET product_name = $1, quantity = $2, price = $3 WHERE productId = $4",
    [product_name, quantity, price, productId]
  );

  res.status(200).send({ message: "Product Updated Successfully!" });
};
```

It's updating perfectly! See the gif below:

[![postgresql-08.gif](https://s5.gifyu.com/images/postgresql-08.gif)](https://gifyu.com/image/IWMz)

Now let's go to our last route!

## Route: (DELETE) 'Delete Product by Id'

Finally, we got to the last route of our api! Let's go back to the **product.routes.js** file and create the route for the **deleteProductById** method:

*  **file: product.routes.js**

```javascript
(...)

// ==> Rota responsável por excluir 'Product' pelo 'Id': (DELETE): localhost:3000/api/products/:id
router.delete('/products/:id', productController.deleteProductById);

(...)
```

And finally, developing the logic of this route in the file **product.controller.js**:

* **file: product.controller.js**

```javascript
(...)

// ==> Método responsável por excluir um 'Product' pelo 'Id':
exports.deleteProductById = async (req, res) => {
  const productId = parseInt(req.params.id);
  await db.query('DELETE FROM products WHERE productId = $1', [
    productId
  ]);

  res.status(200).send({ message: 'Product deleted successfully!', productId });
};
```

Everything is working perfectly, if we open PostgreSQL we will see that now we only have 5 registered products!

[![postgresql-10.gif](https://s5.gifyu.com/images/postgresql-10.gif)](https://gifyu.com/image/IWMx)

## Conclusion

Today we have learned how to create a CRUD API RESTFul with Node.js persisting locally in PostgreSQL. In the next article, I'll teach you how to deploy this application in Azure App Service! After performing this deployment we will test on Postman and consequently on Swagger!

Here I would like to let some good resources about Node.js, Azure Database PostgreSQL & GitHub Actions:

- ✅ **[Build JavaScript applications with Node.js](https://docs.microsoft.com/en-us/learn/paths/build-javascript-applications-nodejs/?WT.mc_id=javascript-11915-gllemos)**
- ✅ **[Azure Database for PostgreSQL documentation](https://docs.microsoft.com/en-us/azure/postgresql/?WT.mc_id=javascript-11915-gllemos)**
- ✅ **[Quickstart: Create an Azure Database for PostgreSQL server by using the Azure portal](https://docs.microsoft.com/en-us/azure/postgresql/quickstart-create-server-database-portal?WT.mc_id=javascript-11915-gllemos)**
- ✅ **[Tutorial: Design a real-time analytics dashboard by using Azure Database for PostgreSQL – Hyperscale (Citus)](https://docs.microsoft.com/en-us/azure/postgresql/tutorial-design-database-hyperscale-realtime?WT.mc_id=javascript-11915-gllemos)**
- ✅ **[Azure for JavaScript & Node.js developers](https://docs.microsoft.com/en-us/azure/developer/javascript/?WT.mc_id=javascript-11915-gllemos&view=azure-node-latest)**
- ✅ **[Free E-Book - Azure Serverless Computing Cookbook](https://azure.microsoft.com/en-us/resources/azure-serverless-computing-cookbook/?WT.mc_id=javascript-11915-gllemos)**
- ✅ **[Free Course - Create Serverless Applications](https://docs.microsoft.com/en-us/learn/paths/create-serverless-applications/?WT.mc_id=javascript-11915-gllemos)**

And to stay on top of several other news, be sure to follow me on Twitter!

[![Twitter](https://code4coders.files.wordpress.com/2019/05/image-12.png)](https://twitter.com/glaucia_lemos86)

And subscribe on my **[Youtube Channel - Glaucia Lemos](https://bit.ly/youtube-canal-glaucialemos)** to see weekly new videos about Web Development, Node.js & JavaScript!

[![Screen-Shot-12-31-20-at-01-06-AM.png](https://i.postimg.cc/pr3TL4Pd/Screen-Shot-12-31-20-at-01-06-AM.png)](https://bit.ly/youtube-canal-glaucialemos)

See you! 😍