I know that you know there are a few types of databases and the most famous ones are SQL and NoSQL.

Most students prefer to learn SQL databases after they learn the NoSQL like MongoDB which is good to learn NoSQL database which is a very good approach but if it's your first time learning database it's alright I am here to explain you each and everything you have to know about.

but before let's see what is the difference between the SQL and NoSQL databases.

• NoSQL databases

1. Store data in a schema-less fashion. Extremely lean and fast way to store data.

2. Examples - MongoDB,

SQL databases

1. Stores data in the form of rows

2. Most full-stack applications will use this

3. Examples - MySQL and, Postgres

but you see what is wrong with the NoSQL database and why most of the full stack app uses the SQL database

Why not NoSQL?

its schemaless property makes it ideal for bootstrapping the project fast,
But as your app grows, this property makes it very easy for data to get curropted

What is Schemaless?

Different rows can have different schema (keys/types)

**

Problems?**

1. This can lead to inconsistent database

2. Can cause runtime errors

3. Is too flexible for an app that needs strictness

Upsides?

1. Can move very fast

2. Can change schema very easily

There is a point above Is too flexible for an app that needs strictness this principle is also applicable in real life and in the coding for that we are using typescript instead of javascript. My blog on the typescript is coming on 10 March 2024 on my blog channel. let's come back to the topic.

till now we have discussed how many types of databases and what is the problem with NoSQL, let me discuss solid reasons for :

Why SQL?

SQL databases have a strict schema. They require you to

1. Define your schema

2. Put in data that follows that schema

3. Update the schema as your app changes and perform migrations

if you already know the NoSQL database then you can easily understand the points that are given below:

So there are 4 parts when using an SQL database (not connecting it to Node.js, just running it and putting data in it)

1. Running the database.

2. Using a library that lets you connect and put data in it.

3. Creating a table and defining it’s schema.

4. Run queries on the database to interact with the data (Insert/Update/Delete)

if you don't know about them then don't worry we just do in the second point we said that

we are gonna use a library to perform the CRUD operation in the SQL database if you don't know what is CRUD then it simply means Create Read Update and Delete. from the database.

Okay I think this is enough to explain the SQL so let's move to

Creating a database

You can go and easily generate the Postgres string for the database from neon db for now I am going to do it locally by using docker no need to install Postgres locally because it takes time and data for this practice session and learning session I am going to generate the Postgres string using Docker so let's get start:

• Using docker locally

if you also have docker locally then follow this command in your terminal for generating the container of postgres locally.

docker run --name my-postgres -e POSTGRES_PASSWORD=mysecretpassword -d -p 5432:5432 postgres

in this command POSTGRES_PASSWORD=mysecretpassword you can write any password you want to secure your database. it depends upon you.

Connection String

Now modify the connecting string as per your password and your string is ready which helps you to connect the database and your backend.

postgresql://postgres:mysecretpassword@localhost:5432/postgres?sslmode=disable

• Using neondb

https://neon.tech/ is a decent service that lets you create a server.

Connection String

Your connecting string will look like something this but the password and username will change as per your command

postgresql://username:password@ep-broken-frost-69135494.us-east-2.aws.neon.tech/calm-gobbler-41_db_2253874

Using a library that lets you connect and put data in it.

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 SQL, you can type in queries interactively, issue them to PostgreSQL, and see the query results.

How to connect to your database?

psql Comes bundled with Postgresql. You don’t need it for this tutorial. We will directly be communicating with the database from Node.js

psql -h p-broken-frost-69135494.us-east-2.aws.neon.tech -d database1 -U 100xdevs

2. pg

pg is 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.

for now, just read them both if you are getting confused then don't worry we will discover them deeply in further blog for now lets:

Creating a table and defining it’sschema.

Tables in SQL

A single database can have multiple tables inside. Think of them as collections in a MongoDB database.

Until now we just have just database that we can interact with. The next step in the case of postgres is to define schema of your tables. To create a table, the command to run is -

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
);

1. CREATE TABLE USER: this command initiates the creation of a new table in the database named user.

2. id SERIAL PRIMARY KEY:

   • id: The name of the first column in the users table.

   • SERIAL: A PostgreSQL-specific data type for creating an auto-incrementing integer.

   • PRIMARY KEY: This constraint specifies that the id column is the primary key for the table, meaning it uniquely identifies each row. Values in this column must be unique and not null.

3. email VARCHAR(255) UNIQUE NOT NULL:

   • email: The name of the second column, intended to store the user's username.

   • VARCHAR(50): A variable character string data type that can store up to 50 characters.

   • UNIQUE: No two users can have the same username.

   • NOT NULL: every row must have a username.

4. reated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP

   • created_at: The name of the fifth column.

   • TIMESTAMP WITH TIME ZONE: This data type stores both a timestamp and a time zone.

Don't lose hope of learning Postgres this is just for the reading I know this is tough but you have to just read this and that's it. you don't have to learn this. I have a better and easier version for you. Which is PRISMA we will discuss this in PART-2 of this blog for now let's move ahead

💡

If you have access to a database right now, try running this command to create a simple table in there

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

if you still don't understand let me explain to you briefly okay so:

Step 1: Install Docker

You have to install the docker locally by watching YouTube because this is the better way to use Postgres instead of using it online ok let's start after installing the docker locally your docker will look like this

after downloading you do not have to signin or signup you have to just start the docker using the terminal open the terminal and type

sudo systemctl start docker

after that, your docker gets started and you have to just open the terminal and run the command given below but before that, you can use whatever the password you want for your database in place of the mypassword POSTGRES_PASSWORD=mysecretpassword

docker run --name my-postgres -e POSTGRES_PASSWORD=mysecretpassword -d -p 5432:5432 postgres

after running this command you see something like this:

you can see after running the command the instance of the postgres created in the docker now running. Now your connecting string is ready to use which is

postgresql://postgres:mysecretpassword@localhost:5432/postgres?sslmode=disable

use your password instead of mypassword;

Step 2: Creating the user table:

open the new Terminal and run the docker ps command it will show you how many containers are there in the docker, now copy the containerID from it

now we are going to open the terminal of the container for that we have to follow the command

docker exec -it CONTAINER ID psql -U postgres

write the container ID correctly now welcome to the Postgres terminal in the container which looks like

now we have nothing in this so first we have to create the user table To create the user table we will follow these commands:

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
);

I already explained this above so I have to proceed after running this command in your terminal the user table gets created like this

this is how you can create the user table in postgres. Now it is time to insert the user in it for that we have another command:

• INSERT

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

fill them according to your name email and password,

• UPDATE

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

• DELETE

DELETE FROM users
WHERE id = 1;

• SELECT

SELECT * FROM users
WHERE id = 1;

To see the inserted user you have to use the command:

SELECT * FROM users;

this is how easy it is.

What about the Nodejs app how do Query from that?

psql is one such library that takes commands from your terminal and sends them over to the database.

To do the same in Node.js, you can use one of many Postgres clients

but before that :

npm i pg

Connecting: -

import { Client } from 'pg'

const client = new Client({
  host: 'my.database-server.com',
  port: 5334,
  database: 'database-name',
  user: 'database-user',
  password: 'secretpassword!!',
})

client.connect()

Querying -

const result = await client.query('SELECT * FROM USERS;')
console.log(result)

this is a function to create a user table in your database.

import { Client } from 'pg'

const client = new Client({
  connectionString: "postgresql://postgres:mysecretpassword@localhost/postgres"
})

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();

Sure! Looking forward to Part 2 of the Database Blog series with Prisma. If you have any more questions or need assistance in the future, feel free to reach out. Goodbye for now!