Using the Postgres.js library
Tags: node, typescript, postgres, sql
Published: 2023-06-15 21:49:46

For a project of mine, I wanted to branch  out from making use of  MongoDB to using a SQL Database like Postgres. The only experience I have using this SQL Database was on the AWS Cloud Project Bootcamp where we integrated the database Python-Flask backend, so I wanted to test it out but this time with a NodeJS backend. 

I know some people might be asking why not just use Prisma ORM since it's well-known and popular, my reason was to know how to work with Postgres using a low-level library like Postgres.JS to gain some basic knowledge in using SQL databases.

In this article, I will describe how to set up a local PostgreSQL database and how to write queries to a database from NodeJS routes. We will be creating a backend system that simply Signs Up and Sign In users using NodeJS, expressJS , and Typescript.

**Tools needed**

- Docker. You can download Docker [here](https://www.docker.com/products/docker-desktop/)
- Already set up NodeJS Backend Sever with Express.JS and Typescript.
- If needed clone the repo, Project Repo - [GITHUB](https://github.com/OpeOginni/postgresjs_and_node)

## **Setting Up the Local Postgres Database**

We will be making use of Docker to setup our Postgres Database.

In your Project Root Directory you will need to create a `docker-compose.yaml` file. In this file we will need to create a DataBase service using the official Postgres Container Image. 

```yaml
version: "3.8"
services:
  db:
    image: postgres:13-alpine
    restart: always
    environment:
      - POSTGRES_USER=user
      - POSTGRES_PASSWORD=password
    ports:
      - "5432:5432"
    volumes:
      - db:/var/lib/postgresql/data
volumes:
  db:
    driver: local

```

After creating the `docker-compose.yaml` file make sure the Docker app is running on your System and then run the following command on the terminal/command line.

```sh
docker compose up
```

OR

You can get the Docker Extension on VSCode and right click on the `docker-compose.yaml` file and select compose up.


![Using The Docker Extension to Run the Docker Container](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/wc3vdj5xei3kbk0xwtcu.gif)

After some time you should see the container running in your terminal also on the Docker App.

You now have a Postgres Database running on your local system 🥳

Time for the next part...

## ** Setting Up the Postgres Client using Postgres.JS **

Now we would like to connect to our new Database from in our code. To do this we will firstly need a `CONNECTION_URL`, this is like a website link that connects us directly to our SQL database. For our local database we just created it will look like this `postgresql://user:password@127.0.0.1:5432/DB`. Keep this URL safe as we need it to connect to our database.

Next we need to install the Postgres.Js library, you can learn more on this library [here](https://www.npmjs.com/package/postgres).
You can install it by running this command
```sh
npm i postgres
```
We will now create a new file called `db.ts`, here we will connect our database. This is what you should write in you `db.ts` file

```typescript
import postgres from 'postgres'

const CONNECTION_URL = "postgresql://user:password@127.0.0.1:5432/db";

const PGHOST = "127.0.0.1";
const PGDATABASE = "db";
const PGUSER = "user";
const PGPASSWORD = "password";

// export const sql = postgres({ host: PGHOST, database: PGDATABASE, username: PGUSER, password: PGPASSWORD, port: 5432 });

export const sql = postgres(CONNECTION_URL);
```

And Like that we have our database connected. Note we can connect our database in two ways.

- First way: Connecting the database With the `CONNECTION_URL`

```typescript
export const sql = postgres(CONNECTION_URL);

```

- Second way: Connecting the database without the `CONNECTION_URL`

```typescript
export const sql = postgres({ host: PGHOST, database: PGDATABASE, username: PGUSER, password: PGPASSWORD, port: 5432 });
```

You can only use one method, for this example we use the First Method.

Now we can connect to our Database, let us make our Database Useful, we will do this by loading a Schema for our Database. A schema is a plan for our database, and for this database we just want to have a table for users, and this table will hold user's first name, last name, email and password. Lets write some SQL code.

Create a new folder on the Project Root directory and name it `sql`. In this new folder create a new file called `schema.sql` and put in the following code.

```sql
DROP TABLE IF EXISTS public.users;

CREATE TABLE public.users (
  user_ID  SERIAL PRIMARY KEY,
  first_name text NOT NULL,
  last_name text NOT NULL,
  email text NOT NULL,
  password text NOT NULL
);
```

Next we create our database by running this command.

```sh
psql postgresql://user:password@127.0.0.1:5432 -c "create database db;"
```

Now we have our Schema ready we can load it into the database. We can do that by running the following commands.

```sh
psql postgresql://user:password@127.0.0.1:5432/db
```


![Connect to Database](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/iin875ygcnkxtcp057ao.png)


We use this command to connect with the Database from our Terminal/Command Line. Note: You must have the psql CLI downloaded on your system.

After connecting our database we can run the Schema code we made, just copy and paste it into the terminal and click enter.


![Loading the Database Schema](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/gy41q7h1awdv2yxwykw9.png)

You should see some statements telling you the Table has been created. Quit the Connection by using this command.

```sh
\quit
```

![Quitting the Connection](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/rbmf1dqdu9aulxk58e54.png)

## ** Creating Endpoints **

We are at the last step, here we will create our endpoints that users send data to.

Firstly in your `app.ts` file, import the Postgres.JS Library, and other needed Packages

```typescript
import { sql } from "lib/db";
```

Since we are using typescript, for us to keep the type of our user object we will create an interface that matches the user object in our database.

```typescript
interface user {
    first_name: string,
    last_name: string,
    email: string,
    password: string
}
```

Then create the controllers functions and using the Postgres.JS Library to run the SQL queries to GET USERS from the database and to CREATE USERS to the database.

```typescript
const login = async (req: Request, res: Response) => {
    const { email, password } = req.body;

        // Get the user from the DataBase
        const user = await sql<user[]>`
              SELECT
               *
             FROM public.users
             WHERE 
               users.email = ${email + '%'}
        `
}

const signup = async (req: Request, res: Response) => {

    // Get the Signup details that are passed
    const { first_name, last_name, email, password } = req.body;

    try {
        // Get the user from the DataBase
        const oldUser = await sql<user[]>`
              SELECT
               *
             FROM public.users
             WHERE 
               users.email = ${email}
                 `
}
```

This implementation works, but there is a problem, it is vulnerable to **SQL Injection**. This is when users put in malicious sql codes into our Endpoints and these codes can have really bad effects on the database.

The SQL query that searches the database for a user using the `SELECT` statement is the query we will look at.

```typescript
const User = await sql<user[]>`
     SELECT
       *
     FROM public.users
     WHERE 
       users.email = ${email}
       `
```

As it is hackers can put in bad SQL code into the email parameter in our endpoint and this code gets added to the SQL query we have here. There are two ways we can solve this issue.
- The first way we can prevent SQL Injection is by validating the parameters users are inputing before they are used in the database query. Using Libraries like [Joi](https://www.npmjs.com/package/joi), we can make sure that users are only inputing emails not malicious code.
- Another way we can prevent SQL Injection is by using query parameters. This way even if a user inputs malicious SQL code, they will not be handled as part of the query. Conveniently [Postgres.JS](https://www.npmjs.com/package/postgres#queries) supports the use of Query Parameters, and the changes we need to make are not that much.

```typescript
const User = await sql<user[]>`
     SELECT
       *
     FROM public.users
     WHERE 
       users.email like ${email + '%'}
       `
```

Now the email param is safe from SQL Injection.

Next let's complete our Controller Functions to return the right responses.

```typescript
const login = async (req: Request, res: Response) => {
    const { email, password } = req.body;

    try {
        // Get the user from the DataBase and prevent SQL Injection
        const user = await sql<user[]>`
              SELECT
                *
              FROM public.users
              WHERE 
                users.email like ${email + '%'} 
            `

        // Return error if that user cant be found
        if (user.length === 0) {
            return res.status(400).json({ success: false, message: "No user with that Email" })
        }

        // We check if the password in the database is the same password that the user is inputing
        if (user[0].password === password)
            return res.status(200).json({ success: true, user: user, message: "Signed In Successfully...Welcome" })
        else {
            return res.status(400).json({ success: false, message: "Wrong Password" })
        }
    } catch (err) {
        return res.status(400).json({ success: false, message: "Something went wrong" })
    }


}

const signup = async (req: Request, res: Response) => {

    // Get the Signup details that are passed
    const { first_name, last_name, email, password } = req.body;

    try {
        // Get the user from the DataBase
        const oldUser = await sql<user[]>`
              SELECT
               *
             FROM public.users
             WHERE 
               users.email like ${email + '%'}
                 `

        // Check if that user already exists
        if (oldUser[0]) {
            return res.status(400).json({ success: false, message: "User with that Email Already Exisits" })
        }

        // Create the User using SQL 
        const user = await sql<user[]>`
        INSERT INTO public.users
          (first_name, last_name, email, password)
        VALUES (${first_name}, ${last_name}, ${email}, ${password})
        returning *
        `
        // We return the user object
        return res.status(200).json({ success: true, user: user[0] })
    } catch (err) {
        // We return a code 400 if we get an error
        console.log(err)
        return res.status(400).json({ success: false, message: "Something went wrong" })
    }
}
```

Lastly we create our Endpoints

```typescript
app.post('/login', login)
app.post('/signup', signup)
```

Now you can run the NodeJS Server and using a tool like Postman, test the `/sigup` and `/login` endpoints 😎.


![Testing Endpoints](https://media.giphy.com/media/v1.Y2lkPTc5MGI3NjExZDUxMGFjMzhmYTE3ZDg3ZjE1MWY1ZDk5Y2I2NTAwOTIzOTc4ZTJlMCZlcD12MV9pbnRlcm5hbF9naWZzX2dpZklkJmN0PWc/qSHEPeTNV59c283j8k/giphy.gif)

Congrats, you can now use postgreSQL using a low level library, go and create your own backends using this tool. Thank you for reading 🌟

Project Repo - [GITHUB](https://github.com/OpeOginni/postgresjs_and_node)

Feedback and Corrections are encouraged.