PostgreSQL and REST API

Preconditions:
=> Download NodeJS from https://nodejs.org/en/download
=> Download and install PostgreSQL from https://www.postgresql.org/download/windows/
=> Good to have Postman installed as well from https://www.postman.com/downloads/
=> Be familiar with JavaScript from https://www.hemelix.com/scada-hmi/twincat-hmi/twincat-hmi-async-function/
We can check if those are installed already:
=> Checking if postgres installed
postgres --version
=> Check if the nodeJS installed
node -v
Once we have installed the PostgreSQL then we can create and build REST API.
=> Create a folder name Hemelix_RestAPI (File | Open folder)
=> Fire Visual Studio Code and create a folder called REST API
=> Open a terminal (Terminal | New Terminal) and run the following command
npm init -y
=> Hit enter and it will create a JSON file called package.json file under the REST API folder
=> Install express framework by executing the following command
npm i --save express
=> Install PostgreSQl connector by executing the following command
npm i pg
Now create the structure of the folder of your project as you need.
=> Create file called server.js with the following content
const express = require("express");
const app= express();
const port = 3000;
app.get("/", (req, res) => {
res.send("Hello World, so far so good!");
});
app.listen(port, () => console.log(`app listening on port ${port}`));
=> Console can be cleaned by typing
clear
Register the PGServer
If we want to connect to a database we need to register the pgServer
Postman
Postman is an API platform for building and using APIs. Postman simplifies each step of the API lifecycle and streamlines collaboration. It is a development tool where we can test our REST API without using a browser.
Tips
01: How to Reset Forgotten Password For postgres User
=> Go to C:\Program Files\PostgreSQL\16\data and open pg_hba.conf file
=> Change the content below METHOD to trust
=> Open the service and start postgresssql-x64-16
=> Type in windows search SQL and select (SQL Shell (psql) )
=> Configure by using SQL Shell (psql) just by pressing enter
=> Finally execute the following command
postgres=# ALTER USER postgres WITH PASSWORD 'my_secret_pass';
02: List a user table in PG database
postgres=# \l
03: Connection Status PG database
postgres=# \conninfo => You are connected to database “postgres” as user “postgres” on host “localhost” (address “::1”) at port “5432”.
04: Create PG database
postgres=# CREATE DATABASE students;
=> show list of tables
\l
05: Connect to Database
postgres=# \c students
06: Create Table
CREATE TABLE students (ID SERIAL PRIMARY KEY, name VARCHAR(255), email VARCHAR(255),age INT, dob DATE);
07: Display Table
postgres=# \dt
08: Insert data to Table
INSERT INTO students(name, email, age, dob) VALUES (‘Joe’, ‘joe@gmail.com’, 48, ‘1973-04-04’), (‘Anna’, ‘anna@gmail.com’, 23, ‘2000-01-01’);
09: Display Table Data
postgres=# SELECT * FROM students;
10: Clear the screen
postgres=# \! cls