node-api-with-postgres-DB

A simple node API with express connected to a postgreSQL database

Cloning Repository

git clone https://github.com/manaraph/node-api-with-postgres-DB.git

Setting up PostgreSQL database using the command line

# Use the default user `postgres` Then enter your password for postgres
> psql -U postgres                                  
# View your connection information
> \conninfo                                         
# Create a new user `me` with with password `password`
> CREATE ROLE me WITH LOGIN PASSWORD 'password';    
# Give user `me` access to create databases
> ALTER ROLE me CREATEDB;                           
# Lists all roles/users
> \du                                               
# Exit from the default session
> \q                                                
# Connect as user me, and login using the password: `password`
> psql -d postgres -U me                            
# Create a database `api`
> CREATE DATABASE api;                              
# Lists all avaialble database
> \list                                             
# connect to the database `api`
> \c api                                            
# Connected to `api=>` Create table users with two field and auto-incrementing primary id
> CREATE TABLE users (           
    ID SERIAL PRIMARY KEY,
    name VARCHAR(30),
    email VARCHAR(30)
    );                                              
# Insert 2 entries to users
> INSERT INTO users (name, email)
    VALUES ('Jerry', 'jerry@example.com'), 
    ('George', 'george@example.com');                 
# Create table data 
> CREATE TABLE data(ID VARCHAR(100), NAME VARCHAR(50), ADDRESS VARCHAR(100), 
    PHONE_NUMBER VARCHAR(100), EMAIL VARCHAR(100), COMPANY VARCHAR(100), 
    DATE_REGISTERED VARCHAR(50), LAST_UPDATED VARCHAR(50))
# Import data from `.\src\data\data.csv` to our data table
> \COPY data(ID,NAME,ADDRESS,PHONE_NUMBER,EMAIL,COMPANY,DATE_REGISTERED,LAST_UPDATED) 
    from '.\src\data\data.csv' delimiter ',' csv header;

Setting up the noSQL Database

# Create json data table `jsondt`
> CREATE TABLE jsondt( data JSON);
##Inserts the json data from data table as arrays
# > INSERT INTO jsondt(data) 
#     select json_build_array(
#     ID,NAME,ADDRESS,PHONE_NUMBER,EMAIL,
#     COMPANY,DATE_REGISTERED,LAST_UPDATED) 
#     FROM data;                                
#Inserts the json data from data table as arrays
> INSERT INTO jsondt(data) 
SELECT json_build_object( 'id',ID,'name',NAME,'address',ADDRESS,
'phone',PHONE_NUMBER, 'email',EMAIL,'company',COMPANY,
'registered',DATE_REGISTERED,'updated',LAST_UPDATED) from data;     
# View all data on jsondt table
> SELECT * FROM jsondt

Installation and running app with npm

cd node-api-with-postgres-DB
# install dependencies
npm install 
# serve app at localhost:3000
npm start

Installation and running app with yarn

cd node-api-with-postgres-DB
# install dependencies
yarn 
# serve app at localhost:3000
yarn start

API Endpoints

You can use postman or even curl to reach out to the following api endpoints:

Testing API with curl

• If you are running this command from powershell and you encounter the error A positional parameter cannot be found that accepts argument run the command below to fix it.

  Remove-item alias:curl

• See details of the fix above on this link.

• Ensure that your server is running and connection has been established with the postgres database before running these request.

# Display home with api info 
# This can also view on your browser by visiting the link  => http://localhost:3000/
> curl -v http://localhost:3000/
# GET => Retrieve all users
# This can also view on your browser by visiting the link  => http://localhost:3000/users
> curl -v http://localhost:3000/users
# GET => Retrieve a users with id 1
# This can also view on your browser by visiting the link  => http://localhost:3000/users/1
> curl -v http://localhost:3000/users/1
# POST => Create a new user
> curl --data "name=m4&email=m4@example.com" http://localhost:3000/users
# PUT => Update user with ID 1
> curl -X PUT -d "name=Kramer" -d "email=kramer@example.com" http://localhost:3000/users/1
# DELETE => Delete user with ID 1
> curl -X "DELETE" http://localhost:3000/users/1

Credits

Adapted from:

• Setting up a RESTful API with Node.js and PostgreSQL by Tania Rascia
• Importing csv into PostgreSQL table
• PostgreSQL as NoSQL with Data Validation

Resources:

• Generation of random data