# Postgres 🐘

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)

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1709718208175/f04f079c-2464-42c2-b97b-2d587e3024fc.png align="center")

#### \*\*

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.

```plaintext
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.

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

* Using neondb
    

[https://neon.tech/ is a decent service that](https://neon.tech/) lets you create a server.

![notion image](https://www.notion.so/image/https%3A%2F%2Fprod-files-secure.s3.us-west-2.amazonaws.com%2F085e8ad8-528e-47d7-8922-a23dc4016453%2Fb7ee7eea-328e-4b37-8481-0afb51676e7b%2FScreenshot_2024-02-02_at_9.45.53_PM.png?table=block&id=f089c2e4-7756-4532-a7df-ca4a482138ac&cache=v2 align="left")

![notion image](https://www.notion.so/image/https%3A%2F%2Fprod-files-secure.s3.us-west-2.amazonaws.com%2F085e8ad8-528e-47d7-8922-a23dc4016453%2F18fc29c5-b324-4e91-bdd0-549d4977561b%2FScreenshot_2024-02-02_at_9.46.01_PM.png?table=block&id=2d1bd758-fd33-4bef-95ae-47f826076991&cache=v2 align="left")

#### **Connection String**

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

```plaintext
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

```plaintext
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’s**`schema`.

### **Tables in SQL**

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

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1709727244515/745dd71e-b973-44b6-aed9-9ba68433db93.png align="center")

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 -

```plaintext
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

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1709729246503/43857d26-fd35-4f36-add0-e4930d2da81f.png align="center")

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

```plaintext
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*

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

after running this command you see something like this:

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1709729571647/e73f5db8-9f9e-4126-b83b-1004ba9dfeff.png align="center")

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

```basic
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

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1709729868283/2877546a-678e-4fcc-8de2-8c48e8bae519.png align="center")

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

```basic
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

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1709730105487/56be3f58-1142-4c58-831b-c77e6e6ba2d3.png align="center")

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:

```basic
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

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1709730284598/e1282f4f-32d3-407e-9213-31380d1a0fdb.png align="center")

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**
    

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

fill them according to your name email and password,

* **UPDATE**
    

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

* **DELETE**
    

```basic
DELETE FROM users
WHERE id = 1;
```

* **SELECT**
    

```basic
SELECT * FROM users
WHERE id = 1;
```

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

```basic
SELECT * FROM users;
```

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1709730691256/e473bc47-914b-4768-8753-98691fa1cea6.png align="center")

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 :

```basic
npm i pg
```

Connecting: -

```basic
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 -

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

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

```basic
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!***