Setup & CRUD PostgreSQL, Nodejs as backend

What is PostgreSQL
PostgreSQL, or Postgres, is a free, open-source relational database system that competes with MySQL, SQL Server, and MariaDB. Since 1997, it has been known for its reliability, flexibility, and compliance with standards, making it a popular choice for developers and companies.

What is node-postgres?
Node-postgres (pg) is a non-blocking PostgreSQL client for Node.js, providing a set of modules to interact with PostgreSQL databases. It supports features like callbacks, promises, async/await, connection pooling, prepared statements, cursors, advanced type parsing, and C/C++ bindings.

Install PostgreSQL Installer

Windows Installer 

For Mac: 

brew install postgresql

To start services

brew services start postgresql
==> Successfully started `postgresql@14` (label: homebrew.mxcl.postgresql@14)

To stop services

brew services stop postgresql
==> Successfully stopped `postgresql@14` (label: homebrew.mxcl.postgresql@14)

Connect to Postgres’s 

psql postgres
// connecting default postgres database with the default login information and no option flags

OPTIONS:
-h or --host=HOSTNAME: The database server host or socket directory; the default is local socket
-p or --port=PORT: The database server port; the default is 5432
-U or --username=USERNAME: The database username; the default is your_username
-w or --no-password: Never prompt for password
-W or --password: Force password prompt, which should happen automatically

Check what database, user, and port we’ve connected to using the \conninfo command

postgres=# \conninfo
You are connected to database "postgres" as user "croma" via socket in "/tmp" at port "5432".

Some common commands

\q: Exit psql connection
\c: Connect to a new database
\dt: List all tables
\du: List all roles
\list: List databases

Creating a role in Postgress

First, we’ll create a role named me with the password password. This role will act as a user in this scenario.

postgres=# CREATE ROLE me WITH LOGIN PASSWORD 'password';

Provide create permission "me" to be able to create a database:

postgres=# ALTER ROLE me CREATEDB;

Connect postgres with me role

Instead of postgres=#, our prompt now shows postgres=> , meaning we’re no longer logged in as a superuser.

psql -d postgres -U me

Create a database with the SQL command

postgres=> CREATE DATABASE api;

Connect to the new api database with me using the \c connect command:

postgres=> \c api

Creating a table in Postgres

api=>
CREATE TABLE users (
  ID SERIAL PRIMARY KEY,
  name VARCHAR(30),
  email VARCHAR(30)
);

Let’s add some data to work with by adding two entries to users:

INSERT INTO users (name, email)
  VALUES ('Jerry', 'jerry@example.com'), ('George', 'george@example.com');

Getting all entries in users:

api=> SELECT * FROM users;

id |  name  |       email        
----+--------+--------------------
  1 | Jerry  | jerry@example.com
  2 | George | george@example.com

Using PSQL in nodejs

Install node-postgres to connect to PostgreSQL

npm i pg

Perform CRUD Operation using postgres

GET all users

pool.query('SELECT * FROM users ORDER BY id ASC', (error, results) => {
    if (error) {
      throw error
    }
    response.status(200).json(results.rows)
  })

GET a single user by ID

pool.query('SELECT * FROM users WHERE id = $1', [id], (error, results) => {
    if (error) {
      throw error
    }
    response.status(200).json(results.rows)
  })

POST a new user

pool.query('INSERT INTO users (name, email) VALUES ($1, $2) RETURNING *', [name, email], (error, results) => {
    if (error) {
      throw error
    }
    response.status(201).send(`User added with ID: ${results.rows[0].id}`)
  })

PUT updated data in an existing user

pool.query(
    'UPDATE users SET name = $1, email = $2 WHERE id = $3',
    [name, email, id],
    (error, results) => {
      if (error) {
        throw error
      }
      response.status(200).send(`User modified with ID: ${id}`)
    }
  )

DELETE a user

pool.query('DELETE FROM users WHERE id = $1', [id], (error, results) => {
    if (error) {
      throw error
    }
    response.status(200).send(`User deleted with ID: ${id}`)
  })