Create a rest api using expressjs and postgresql
Tags: javascript, tutorial, api
Published: 2022-10-20 01:45:00

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/](https://nodejs.dev/en/learn/)
2. [https://www.postgresql.org/docs/current/sql-commands.html](https://www.postgresql.org/docs/current/sql-commands.html)
3. [http://expressjs.com/](http://expressjs.com/)

Installer links
1. NodeJS: [https://nodejs.org/en/download/](https://nodejs.org/en/download/)
2. Postgresql: [https://www.postgresql.org/download/](https://www.postgresql.org/download/)

First we will initialize the project folder with a "package.json".
```batch
npm init -y
```

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

```batch
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**
```batch
npm i -D nodemon
```

Before connecting to database, we will first create the database

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

![Create a new database](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/oy3n3fz2ytuhquzjhrym.png)

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

![Create a new table named blogs](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/7ps7e6af26vybox39x3l.png)

- Create the specified columns for the table

![Create the specified colums for the table](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/mxem3k7h9hx1mys4wis3.png)

----- 
Create a file "db.js", where we will connect to the database
```javascript
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
```javascript
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.
```javascript
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.
```javascript
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.
```javascript
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.
```javascript
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.
```javascript
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
```javascript
module.exports = router;
```
-----

Create a "index.js" file to handle all the api routes.
Make the necessary imports
```javascript
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
```javascript
app.use(cors()); // enable cross origin requests
app.use(express.json()); // use default json body parser to parse the data.
```

Use the api routes
```javascript
app.use('/api/blogs', require('./routes/blog'))
```

Create a custom 404 message
```javascript
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
```javascript
app.listen(port, () => {
    console.log(`RestAPI listening on http://localhost:${port}`)
})
```

**Yay! You have successfully created your rest api.**