Developing a CRUD Node.js Application with PostgreSQL

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:

Installing PostgreSQL Database

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

  • Windows: for Windows users, just download it HERE. Common and simple installation, as if you were installing a program on Windows.
  • macOS: for Mac users, just download the packages HERE. Also, you will need to have Homebrew installed. If you have some issues or difficulty installing, I recommend you to take a look at this video HERE
  • Linux: for Linux users, as there are countless different versions of Linux, I recommend you to take a look at this PostgreSQL guide HERE

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

Class: Product

- productId: integer primary
- product_name_: varchar
- quantity: int
- price: real
CREATE TABLE products (
productId SERIAL PRIMARY KEY,
productName VARCHAR(255) NOT NULL,
quantity INTEGER NOT NULL,
price NUMERIC(5,2)
);

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:

> npm init -y
> npm i --save-dev husky nodemon
> npm i cors dotenv express express-promise-router pg
{
"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"
}
}

Developing the Application

Hereafter, 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.

  • file: server.js
/**
* 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);
});
  • file: app.js
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;
  • file: src -> routes -> index.js
/**
* 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;
> nodemon

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 package. This package will be essential for us to work with the PostgreSQL Client on Node.js.
This package is an open source 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!

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.

DATABASE_URL=postgres://{db_username}:{db_password}@{host}:{port}/{db_name}
DATABASE_URL=postgres://postgres:glau123@localhost:5432/crud-nodejs-psql

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.

  • config/database.js
/**
* 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),
};

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.

  • file: product.routes.js
// @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;
  • controllers/product.controller.js
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 }
},
});
};
  • file: app.js
/**
* 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;
> 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:
{
"message": "Product added successfully!",
"body": {
"product": {
"product_name": "Logitech MK270 Wireless Keyboard and Mouse Combo",
"quantity": "2",
"price": "18.99"
}
}
}

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!

  • file: product.routes.js
// ==> Rota responsável por listar todos os 'Products': (GET): localhost:3000/api/products
router.get('/products', productController.listAllProducts);
  • file: product.controller.js
// ==> 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);
};

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.

  • file: product.routes.js
(...)

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

(...)
  • file: product.controller.js
(...)

// ==> 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);
}

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
(...)

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

// ==> 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!" });
};

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
(...)

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

(...)
  • file: product.controller.js
(...)

// ==> 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 });
};

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!

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Glaucia Lemos

Glaucia Lemos

Cloud Advocate 🥑 @Microsoft | Contributor & Member NodeJs Foundation & React Community | Open Source | Geek | GirlOpen Source 💻