Intro to PostgreSQL

PostgreSQL, or Postgres, is a leading open-source relational database system renowned for its robustness, extensibility, and SQL standards compliance.

Use Cases:

• Web Applications: PostgreSQL is commonly used as the backend database for web applications, powering dynamic websites and web services.
• Data Analysis and Reporting: PostgreSQL integrates seamlessly with analytical tools and reporting platforms, enabling organizations to analyze and visualize data efficiently.

Installation:

Creating a database:-

Connecting to the database using a library:-

Option 1. psql

psql is a terminal-based front-end to PostgreSQL. It provides an interactive command-line interface to the PostgreSQL (or TimescaleDB) database. With psql, you can type in queries interactively, issue them to PostgreSQL, and see the query results.

Option 2. pg

pgis a Node.js library that you can use in your backend app to store data in the Postgres DB (similar to mongoose). We will be installing this eventually in our app.

Creating a table and defining it’s schema.

Tables in SQL

A single database can have multiple tables inside. Think of them as collections in a MongoDB database. More on Tables. The next step in postgres is to define the schema of your tables. SQL stands for Structured query language. It is a language in which you can describe what/how you want to put data in the database.
Lets create a table users with data fields like id, username, email, password, created_at
To create a table, the command to run is - in the SQL shell

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

Output:-

CREATE TABLE

Then try running \dt to see if the table has been created or not.

\dt

There are 4 things you’d like to do with a database

1. INSERT

INSERT INTO users (username, email, password)
VALUES ('username_here', 'user@example.com', 'user_password');

2.UPDATE

UPDATE users
SET password = 'new_password'
WHERE email = 'user@example.com';

3. DELETE

DELETE FROM users
WHERE id = 1;

4.SELECT

SELECT * FROM users
WHERE id = 1;

How to do queries from a Node.js app?

In the end, postgres exposes a protocol that someone needs to talk to be able to send these commands (update, delete) to the database. psql is one such library that takes commands from your terminal and sends it over to the database. To do the same in a Node.js , you can use one of many Postgres clients Start by deleting the created table.

DROP TABLE users;

Move to a empty folder and open terminal and execute the following commands-

npm init

npm install pg

npm i

Create a new file eg. db.js and add the following code.

const { Client } = require('pg')

const client = new Client({
  host: 'localhost',
  port: 5432,
  database: 'postgres',
  user: 'postgres',
  password: 'urpassword',
})
client.connect();

This connects you to the local database you have created. Now as we have successfully connected to the local database now we will create a new table users as we deleted the table.

const { Client } = require('pg')

const client = new Client({
  host: 'localhost',
  port: 5432,
  database: 'postgres',
  user: 'postgres',
  password: '1234',
})


async function createUsersTable() {
    await client.connect()
    const result = await client.query(`
        CREATE TABLE users (
            id SERIAL PRIMARY KEY,
            username VARCHAR(50) UNIQUE NOT NULL,
            email VARCHAR(255) UNIQUE NOT NULL,
            password VARCHAR(255) NOT NULL,
            created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
        );
    `)
    console.log(result)
}

createUsersTable();

now run db.js by

node db.js

Output:- To insert a row in the table:-

async function insertData() {

   try {
      await client.connect(); // Ensure client connection is established
      const insertQuery = "INSERT INTO users (username, email, password) VALUES ('username2', 'user3@example.com', 'user_password');";
      const res = await client.query(insertQuery);
      console.log('Insertion success:', res); // Output insertion result
    } catch (err) {
      console.error('Error during the insertion:', err);
    } finally {
      await client.end(); // Close the client connection
    }
  }

  insertData();

Now you'll have something like this- So, To delete all rows or users in this context:-

async function deleteAllUsers() {
    try {
        const result = await client.query('DELETE FROM users');
        console.log('All users deleted successfully.');
        return result.rowCount;
    } catch (error) {
        console.error('Error deleting users:', error);
        throw error;
    }
}

deleteAllUsers()
    .then(() => client.end())
    .catch(() => client.end());