Developing a CRUD Node.js Application with PostgreSQL

Resources Used during the Tutorial

Installing PostgreSQL Database

  • 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

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

> 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

  • 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’

Creating an environment variable with ‘dotenv’

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’

  • 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’

  • 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’

  • 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’

  • 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’

  • 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’

  • 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

--

--

--

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

Love podcasts or audiobooks? Learn on the go with our new app.

Recommended from Medium

Building Scalable Systems using Queues

Instruments for data scientist toolbox

GSoC coding week 7 with SCoRe Lab

Random Numbers in Python and JavaScript

How to unload/detach an R package without restarting R?

Using Waterfall Charts in Python to analyze Iowa Liquor change

Program #1: Setting Up and Programming “Blinking LED” on an ESP32 Board Using Arduino IDE

Content Discovery

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 💻

More from Medium

Making Readable Code With Dependency Injection and Jakarta CDI

Git Feature — A new methodology to manage your development work

Resolving “Invalid file coverage object, missing keys, found:data” for NYC

Let’s Code A Simple Subscription Service — Authentication