How to create a basic PostgreSQL and Node JS Application

1. Create the project folder
--------------------------------
[malex@thermalite node-pgsql]$ mkdir node-pgsql

[malex@thermalite node-pgsql]$ cd node-pgsql

[malex@thermalite node-pgsql]$ npm init -y

2. create the database and table on PostgreSQL
--------------------------------------------------------
[malex@thermalite node-pgsql]$ cat database.sql
-- 1. create database
CREATE DATABASE todo_database;

-- 2. connect to database
\c todo_database

-- 3. create table
CREATE TABLE todo(
  todo_id SERIAL PRIMARY KEY,
  description VARCHAR(255)
);


3. Add npm libraries
-------------------------
[malex@thermalite node-pgsql]$ npm i express -s

[malex@thermalite node-pgsql]$ npm i pg -s

[malex@thermalite node-pgsql]$ npm i nodemon -s

4. create connection configuration
------------------------------------------
[malex@thermalite node-pgsql]$ cat dbpgsql.js
const Pool = require("pg").Pool;

const pool = new Pool({
    user: "postgres",
    password: "pfa_user",
    database: "todo_database",
    host: "192.168.1.100",
    port: 5432
});

module.exports = pool;

5. create the index.js
--------------------------
[malex@thermalite node-pgsql]$ cat index.js
const express = require("express");
const app = express();

const pool = require("./dbpgsql");

const port = 5000;

app.use(express.json()) // -> req.body

// ROUTES//

// 0. default root
app.get("/",async (req,res) => {
try{
  res.send("Welcome to PostgreSQL Node Application");
} catch(error){
   console.error(error.message);
}

});

// 1. get all todos
app.get("/todos",async (req,res) => {
    try {
        const allTodos = await pool.query("SELECT * FROM todo");
        res.json(allTodos.rows);
    } catch (error) {
        console.error(error.message);
    }
});

// 2. get a todo
app.get("/todos/:id",async (req,res) => {
    const { id } = req.params;
    try {
        const todo = await pool.query("SELECT * FROM todo WHERE todo_id = $1", [id]);
        res.json(todo.rows[0]);
    } catch (error) {
        console.error(error.message);
    }
});

// 3. create a todo
app.post("/todos",async (req,res) => {
    try {
        const { description } = req.body;
        const newTodo = await pool.query("INSERT INTO todo (description) VALUES ($1) RETURNING *",
        [description]
        );
        res.json(newTodo);
    } catch (error) {
        console.error(error.message);
    }
});

// 4. update a todo
app.put("/todos/:id",async (req,res) => {
    try {
        const { id } = req.params; // WHERE
        const { description } = req.body; //SET

        const updateTodo = await pool.query("UPDATE todo SET description=$1 WHERE todo_id=$2",
        [description, id]
        );
        res.json("Todo was successfully updated.");
    } catch (error) {
        console.error(error.message);
    }
});

// 5. delete a todo
app.delete("/todos/:id",async (req,res) => {
    try {
        const { id } = req.params; // WHERE

        const deleteTodo = await pool.query("DELETE FROM todo WHERE todo_id=$1",
        [id]
        );
        res.json("Todo was successfully deleted.");
    } catch (error) {
        console.error(error.message);
    }
});

app.listen(port,()=> {
    console.log("Server is listening on port: ", port);
});

6. run the application
[malex@thermalite node-pgsql]$ nodemon index


7. open browser and got to http://192.168.1.100:5000/todos