Introduction
In this Blog I will describe how to create an API Backend with NodeJS using PostgreSQL as Database. We will create API for public events with CRUD options. CRUD means: create, read, update and delete. Firstly, we will create our PostgreSQL Tables and history tables. And then we will continue with NodeJS.
If you don’t have PostgreSQL installed yet, please follow these steps in our Blog. As alternative you can also follow the steps in PostgreSQL Page. As Database client I will use DBeaver to create the tables.
After you installed PostgreSQL we need to create database from the psql client. For that open terminal.
psql -U postgres postgres=# postgres=# create database events; postgres=# \q
Now we can connect with our DBeaver client.
PostgreSQL is creating public schema by default. I recommended to delete it.
Now we can create our events table. For that open new SQL-Editor.
CREATE SCHEMA data;
create sequence data.seq_events start with 1;
create table data.events
( id int primary key default nextval ('data.seq_events')
, title varchar(255) not null
, description varchar(255) not null
, price varchar(255) not null
, organizator varchar(255) not null
, startdate date not null
, starttime time not null
, endtime time not null
, location_name varchar(255) not null
, location_street varchar(255) not null
, location_zipcode varchar(255) not null
, location_city varchar(255) not null
, location_country varchar(255) not null
, created_at date default now()
, updated_at date default now()
);
create sequence data.history_seq_events start with 1;
create table data.history_events
( id int primary key default nextval ('data.history_seq_events')
, event_id int
, title varchar(255) not null
, description varchar(255) not null
, price varchar(255) not null
, organizator varchar(255) not null
, startdate date not null
, starttime time not null
, endtime time not null
, location_name varchar(255) not null
, location_street varchar(255) not null
, location_zipcode varchar(255) not null
, location_city varchar(255) not null
, location_country varchar(255) not null
, created_at date
, updated_at date
);
Also create the trigger to save all changes automatically into the history table
create function data.tf_update_events_history()
returns trigger
language plpgsql
as
$$
begin
insert into data.history_events
( event_id
, title
, description
, price
, organizator
, startdate
, starttime
, endtime
, location_name
, location_street
, location_zipcode
, location_city
, location_country
, created_at
, updated_at
)
values
( old.id
, old.title
, old.description
, old.price
, old.organizator
, old.startdate
, old.starttime
, old.endtime
, old.location_name
, old.location_street
, old.location_zipcode
, old.location_city
, old.location_country
, old.created_at
, old.updated_at
);
return new;
end
$$;
-- trigger before update
create trigger t_before_update_log_event_history
before update on data.events
for each row
execute procedure data.tf_update_events_history();
And finally, we can test if our history trigger is working
insert into data.events
( title
, description
, price
, organizator
, startdate
, starttime
, endtime
, location_name
, location_street
, location_zipcode
, location_city
, location_country
)
values
( 'PostgreSQL tutorial'
, 'In this event we will show PostgreSQL'
, 'Free'
, 'dbi services'
, '2020-11-16'
, '09:00'
, '17:00'
, 'dbi services sa'
, 'Flurstrasse 30'
, '8048'
, 'Zürich'
, 'Switzerland'
);
update data.events
set startdate = '2020-11-20'
where id = 1
Now once you open the history_events table you should see the old value.
Well done, it is working.
Let’s prepare our NodeJS environment.
You can install NodeJS from https://nodejs.org/en/
I will use Visual Studio Code editor. https://code.visualstudio.com/
Create any folder and open it in Visual Studio Code Editor.
Open Terminal and initial the Project:
npm init -y
After that create the folders and files
mkdir configs controllers db routes services validators && touch .env index.js
routes: Our all routes will be defined here
controllers: Each route has own controller, controller handles the request and response and calls multiple services. Controllers will never access to the Database!
validators: Validators Objects
services: runs one service, for example get data from the Database
configs: Object with configs, reads environment file attributes
db: Database Models and connection
.env: Environment variables such as PostgreSQL connection
index.js: Main File
Now were ready to install the required dependencies:
npm i express dotenv sequelize pg joi && npm i --save-dev nodemon
express: Framework to handle requests and responses
dotenv: Reads from .env file and adds into process variable which is readable globally
sequelize: Sequelize is a promise-based Node.js ORM for Postgres, MySQL, MariaDB, SQLite and Microsoft SQL Server. It features solid transaction support, relations, eager and lazy loading, read replication and more.
pg: NodeJS PostgreSQL connection “driver”
joi: For validation
nodemon: Restarts the server automatically on each file changes
–save-dev dependencies will be not installed on production environment.
Firstly configure the nodemon, for that open package.json and into scripts
"start": "nodemon index.js"
And then configure our .env file
NODE_ENV=development PORT=2000 DB_HOST='localhost' DB_USERNAME='postgres' DB_PASSWORD='postgres' DB_DATABASE='events' DB_PORT=5432 DB_DIALECT='postgres'
Then, create inside the configs folder file named “config.js” and insert:
require('dotenv').config();
var config = {};
// General config
config.node_env = process.env.NODE_ENV;
// WEB config
config.web = {};
config.web.port = process.env.PORT;
// DB config
config.db = {};
config.db.host = process.env.DB_HOST;
config.db.username = process.env.DB_USERNAME;
config.db.password = process.env.DB_PASSWORD;
config.db.database = process.env.DB_DATABASE;
config.db.port = process.env.DB_PORT;
config.db.dialect = process.env.DB_DIALECT;
module.exports = config;
Inside db folder, create two files: “index.js” and “event.js”
Open index.js file and insert:
/* Databse connection
******************************************************************/
'use strict';
const { Sequelize } = require('sequelize');
const config = require('../configs/config');
const db = {};
const sequelize = new Sequelize(
config.db.database,
config.db.username,
config.db.password,
{
host: config.db.host,
dialect: config.db.dialect,
logging: false
}
);
/* init models
******************************************************************/
db.sequelize = sequelize
db.Events = require('./event')(sequelize)
module.exports = db;
After that open event.js
'use strict';
const { Model, DataTypes } = require('sequelize');
module.exports = (sequelize) => {
class Events extends Model {}
Events.init({
id: {
type: DataTypes.INTEGER,
primaryKey: true,
autoIncrement: true
},
title: {
type: DataTypes.STRING(255),
allowNull: false
},
description: {
type: DataTypes.STRING(255),
allowNull: false
},
price: {
type: DataTypes.STRING(255),
allowNull: false
},
organizator: {
type: DataTypes.STRING(255),
allowNull: false
},
startdate: {
type: DataTypes.DATEONLY,
allowNull: false
},
starttime: {
type: DataTypes.TIME,
allowNull: false
},
endtime: {
type: DataTypes.TIME,
allowNull: false
},
location_name: {
type: DataTypes.STRING(255),
allowNull: false
},
location_street: {
type: DataTypes.STRING(255),
allowNull: false
},
location_zipcode: {
type: DataTypes.STRING(255),
allowNull: false
},
location_city: {
type: DataTypes.STRING(255),
allowNull: false
},
location_country: {
type: DataTypes.STRING(255),
allowNull: false
},
}, {
tableName: 'events',
schema: 'data',
sequelize,
modelName: 'Events',
createdAt: 'created_at',
updatedAt: 'updated_at'
});
return Events
};
Now we can create our two validators. One is for creating an event and the other is for updating.
Create two two file inside the validators folder: “eventCreate.js” and “eventUpdate.js
Open eventCreate.js and insert:
const Joi = require('joi')
module.exports = EventCreateValidator = Joi.object({
title: Joi.string().required(),
description: Joi.string().required(),
price: Joi.string().required(),
organizator: Joi.string().required(),
startdate: Joi.string().required(),
starttime: Joi.string().required(),
endtime: Joi.string().required(),
location_name: Joi.string().required(),
location_street: Joi.string().required(),
location_zipcode: Joi.string().required(),
location_city: Joi.string().required(),
location_country: Joi.string().required(),
created_at: Joi.string(),
updated_at: Joi.string(),
})
Open eventUpdate.js and insert:
const Joi = require('joi')
module.exports = EventUpdateValidator = Joi.object({
title: Joi.string(),
description: Joi.string(),
price: Joi.string(),
organizator: Joi.string(),
startdate: Joi.string(),
starttime: Joi.string(),
endtime: Joi.string(),
location_name: Joi.string(),
location_street: Joi.string(),
location_zipcode: Joi.string(),
location_city: Joi.string(),
location_country: Joi.string(),
created_at: Joi.string(),
updated_at: Joi.string(),
})
As you can see on creating event there is all fields required. But if we want to update, no field is requried.
Create event.service.js inside the services folder and insert:
const db = require('../db/index')
module.exports = {
async getEvents() {
try {
let events = await db.Events.findAll()
return events
} catch (e) {
throw e
}
},
async createEvent(event){
try {
let newEvent = await db.Events.create(event)
return newEvent
} catch (e) {
throw e
}
},
async updateEvent(id, event){
try {
let updateEvent = await db.Events.update(event, {
where: {
id: id
}
})
return updateEvent
} catch (e) {
throw e
}
},
async deleteEvent(id){
try {
let deleteEvent = await db.Events.destroy({
where: {
id: id
}
})
if(deleteEvent){
return true
} else {
return false
}
} catch (e) {
console.log(e)
throw e
}
}
}
Now we can create the controller: create a file named “event.controller.js” inside the controllers folder and insert:
const eventCreate = require('../validators/eventCreate')
const eventUpdate = require('../validators/eventUpdate')
const eventService = require('../services/event.service')
module.exports = {
/* get All events */
async getEvents(req, res) {
try {
let events = await eventService.getEvents()
res.send(events)
} catch (e) {
res.send('Internal server error')
}
},
/* add event */
async addEvent(req, res) {
try {
const { body } = req
/* validate input */
let inputIsValid = await eventCreate.validate(body)
if(inputIsValid.error){
res.send(inputIsValid.error.details[0].message)
} else {
let createdEvent = await eventService.createEvent(body)
res.send(createdEvent)
}
} catch (e) {
res.send('Internal server error')
}
},
async updateEventById(req, res) {
try {
const { body } = req
const { id } = req.params
/* validate input */
let inputIsValid = await eventUpdate.validate(body)
if(inputIsValid.error){
res.send(inputIsValid.error.message[0].details)
} else {
let updateEvent = await eventService.updateEvent(id, body)
res.send(updateEvent)
}
} catch (e) {
res.send('Internal server error')
}
},
async deleteEventById(req, res) {
try {
const { id } = req.params
let deleteEvent = await eventService.deleteEvent(id)
res.send(deleteEvent)
} catch (e) {
res.send('Internal server error')
}
},
}
At least we need to create our routes. Create two files inside the routes folder: "index.js" and "event.router.js".
Open "event.router.js" and insert:
//index router
const express = require('express');
const eventController = require('../controllers/event.controller');
const router = express.Router();
/* get events */
router.get('/', eventController.getEvents)
/* post new event */
router.post('/', eventController.addEvent)
/* update event by id */
router.put('/:id', eventController.updateEventById)
/* delete event by id */
router.delete('/:id', eventController.deleteEventById)
module.exports = router
At least open the "index.js" inside the routers folder and insert:
//index router
const express = require('express');
const router = express.Router();
const eventsRouter = require('./event.router')
router.use('/events', eventsRouter)
module.exports = router
And Finally, we can update our main index.js file:
const express = require('express')
const routes = require('./routes/index')
const config = require('./configs/config')
const app = express()
// parse requests of content-type – application/json
app.use(express.json())
// load routes
app.use(‘/api’, routes)
app.listen(config.web.port, () => {
console.log(`server is running at port ${config.web.port}`)
})
Done. Now we can test our API Endpoints. I will use Postman
First test our /api/events endpoint:
Yeaah it is working!
Second, test our put /api/events/:id endpoint:
For that you need to set headers as below:
Now we can update:
Now we can try to add new event. First try to add an empty Object. The Validation should throw error.
As aspected it is throwing error.
Conclusion:
I hope you did understand the main concept. For production you might be protect the delete and put endpoints by allowing only authenticated users. For that you need to implement JWT or session Authentication.
![Thumbnail [60x60]](https://www.dbi-services.com/blog/wp-content/uploads/2022/05/open-source-author.png)
![Thumbnail [90x90]](https://www.dbi-services.com/blog/wp-content/uploads/2022/08/DWE_web-min-scaled.jpg)
![Thumbnail [90x90]](https://www.dbi-services.com/blog/wp-content/uploads/2022/08/NME_web-min-scaled.jpg)
![Thumbnail [90x90]](https://www.dbi-services.com/blog/wp-content/uploads/2022/08/ENB_web-min-scaled.jpg)
![Thumbnail [90x90]](https://www.dbi-services.com/blog/wp-content/uploads/2022/08/FRJ_web-min-scaled.jpg)