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
- Already set up NodeJS Backend Sever with Express.JS and Typescript.
- If needed clone the repo, Project Repo - GITHUB
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.
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.
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.
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.
You can install it by running this command
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
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
export const sql = postgres(CONNECTION_URL);
- Second way: Connecting the database without the
CONNECTION_URL
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.
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.
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.
psql postgresql://user:password@127.0.0.1:5432/db
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.
You should see some statements telling you the Table has been created. Quit the Connection by using this command.
\quit
** 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
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.
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.
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.
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, 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 supports the use of Query Parameters, and the changes we need to make are not that much.
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.
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
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 😎.
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
Feedback and Corrections are encouraged.
Top comments (0)