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:
- the basic knowledge of javascript and nodejs
- must be familiar with basic crud operations
- must be familiar with sql commands
- must have a basic knowledge of expressjs
Useful links
- https://nodejs.dev/en/learn/
- https://www.postgresql.org/docs/current/sql-commands.html
- http://expressjs.com/
Installer links
- NodeJS: https://nodejs.org/en/download/
- 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.
Top comments (0)