Create a rest api using expressjs and postgresql

Hello world! In this article we will learn how to create a rest api using postgresql and expressjs. We will use javascript for this project.

First let us know what are the pre-requisites for this project:-
One must have:

1. the basic knowledge of javascript and nodejs
2. must be familiar with basic crud operations
3. must be familiar with sql commands
4. must have a basic knowledge of expressjs

Useful links

1. https://nodejs.dev/en/learn/
2. https://www.postgresql.org/docs/current/sql-commands.html
3. http://expressjs.com/

Installer links

1. NodeJS: https://nodejs.org/en/download/
2. Postgresql: https://www.postgresql.org/download/

First we will initialize the project folder with a "package.json".

npm init -y

Then we will install express, cors (for allowing cross origin requests) & pg (to connect to postgresql).

npm i express cors pg

Then we will install nodemon as a dev dependency to reload our server automatically.
Do not use nodemon in production servers

npm i -D nodemon

Before connecting to database, we will first create the database

• Create a database and name it of your own choice.

• Then go into the schemas and right click on tables. Give a name of "blogs" to your table.

• Create the specified columns for the table

---

Create a file "db.js", where we will connect to the database

const { Pool } = require('pg');

const pool = new Pool({
    host: 'localhost',
    user: 'username',
    password: 'password',
    port: 5432,
    database: 'db_name'
})

module.exports = pool;

---

Now let's create a folder called routes and create a file "blog.js" inside it.

Then make the required imports

const express = require('express');
const router = express.Router();
const pool = require('../db');

The first api route will be to query all the blogs from the database. We will use pool.query('') to run our sql commands.

router.get('/', async (req, res) => {
    let blogs = await pool.query('SELECT * FROM blogs ORDER BY blogs.timestamp ASC'); // Order posts by ascending order
    res.status(200).json({ status: 200, totalResults:blogs.rowCount ,blogs: blogs.rows });
})

Then the second api route will be to query a specific blog from the database.

router.get('/:slug', async (req, res) => {
    let blog = await pool.query(`SELECT * FROM blogs WHERE slug='${req.params.slug}'`);
    if (blog.rowCount === 0) {
        res.status(404).json({ status: 404, message: 'No such blog was found!' });
    }else {
        res.status(200).json({ status: 200, blog: blog.rows });
    }
})

Then the third api route will be to add a blog to the database the database.

router.post('/addblog', async (req, res) => {
    const { title, tagline, slug, description } = req.body;
    let saveBlog = await pool.query(`INSERT INTO blogs (title, tagline, slug, description) VALUES ('${title}', '${tagline}', '${slug}', '${description}')`);
    if (!saveBlog) {
        res.status(404).json({ status: 404, message: 'Some error occurred! Cannot save blog :(' });
    }else {
        res.status(200).json({ status: 200, messaeg: 'Your blogpost has been added successfully!' });
    }
})

Then the fourth api route will be to update a specific blog in the database.

router.put('/updateblog/:slug', async (req, res) => {
    const { title, tagline, slug, description } = req.body;
    let saveBlog = await pool.query(`UPDATE blogs SET title='${title}', tagline='${tagline}', slug='${slug}', description='${description}' WHERE slug='${req.params.slug}'`);
    if (!saveBlog) {
        res.status(404).json({ status: 404, message: 'Some error occurred! Cannot update blog :(' });
    }else {
        res.status(200).json({ status: 200, messaeg: 'Your blogpost has been updated successfully!' });
    }
})

Then the fourth api route will be to delete a specific blog from the database.

router.delete('/deleteblog/:slug', async (req, res) => {
    let saveBlog = await pool.query(`DELETE FROM blogs WHERE slug='${req.params.slug}'`);
    if (!saveBlog) {
        res.status(404).json({ status: 404, message: 'Some error occurred! Cannot delete blog :(' });
    }else {
        res.status(200).json({ status: 200, messaeg: 'Your blogpost has been deleted successfully!' });
    }
})

Then we will export the router module

module.exports = router;

---

Create a "index.js" file to handle all the api routes.
Make the necessary imports

const express = require('express');
const cors = require('cors'); 
const app = express(); // initialization of express with app
const port = 5000;

Then write these two lines of code

app.use(cors()); // enable cross origin requests
app.use(express.json()); // use default json body parser to parse the data.

Use the api routes

app.use('/api/blogs', require('./routes/blog'))

Create a custom 404 message

app.use((req, res, next) => {
    res.status(404).json({ status: 404, message: 'No such route was found! Please go to /api/blogs.' });
})

Listen on the given port

app.listen(port, () => {
    console.log(`RestAPI listening on http://localhost:${port}`)
})

Yay! You have successfully created your rest api.