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

 

 

 

 

References

See Also :