CRUD REST API with Node.js, Express.js, and PostgreSQL
Beginner guide to start
API or Application Program Interface serves as a communication facility between services or software. In web programming, APIs are used to access available data and services.
REST API (RESTful API)
Representational State Transfer (REST) contains standards for a web service. REST API is an API architectural style that uses the HTTP protocol to access and use data. The following are some data types that REST API has:
GET— Get dataPUT— Modify the state or value of data or resourcePOST— Create data or resourceDELETE— Delete data or resource
Which can be used to create a CRUD (Create, Read, Update, Delete) service/application.
CRUD
When creating a service, we want some basic functionalities to manipulate the resource. These functionalities are for creating, reading, updating, and deleting data commonly referred to as CRUD.
CRUD stands for Create, Read, Update, and Delete, which is actually an application of the data types in REST API. The details are as follows:
CREATE— To create data (POST)READ— To retrieve/read existing data (GET)UPDATE— To modify/update data (PUT)DELETE— To delete data (DELETE)
Things Required
Before following this article, there are several things that need to be installed first:
Postman application (to hit the REST API Endpoint)
Installation procedures can be found in the respective websites' documentation.
Setting up the PostgreSQL Database
Firstly, we will create a database in PostgreSQL that will be used by the service. We use psql program. Here are the steps.
Login using the command:
psql -U postgres
By using the above command, we have logged in to PostgreSQL as the postgres user.
postgres=#
Creating a Database To create a database, we use the command:
postgres=# CREATE DATABASE tutorial
The above command is used to create a database with the name tutorial. To use/connect to the tutorial database, use the command:
postgres=# \c tutorial
The prompt will change to the following:
tutorial=#
indicates that we have successfully connected to the tutorial database.
Creating a Table
Next, create a table in the tutorial database. We will create a table named students in the public schema. Use the following command:
tutorial=# CREATE TABLE public.students (
id serial NOT NULL,
firstname varchar(40) NULL,
lastname varchar(40) NULL,
origin varchar(50) NULL,
CONSTRAINT students_pkey PRIMARY KEY (id)
);
Use the command \dt to check if the table has been successfully created.
tutorial=# \dt
List of relations
Schema | Name | Type | Owner
-------+----------+-------+---------
public | students | table | postgres
(1 row)
To check the details of the table, use the command \d students.
tutorial=# \d students
Table "public. students”
Column | Type | Collation | Nullable | Default
----------+-----------------------+-----------+----------+-----------------------------------------
id | integer | | not null | nextval(’students_id_seq’::regclass)
firstname | character varying(40) | | |
lastname | character varying(40) | | |
origin | character varying(56) | | |
Indexes:
"students_pkey" PRIMARY KEY, btree (id)
Setting up Express.js Server
1. Project Structure
In the end of this article, the structure of the project will be like this :
tutorial
├───node_modules
│ └─── ....
├───mode
│ └───response.js
├───index.js
├───queries.js
├───package-lock.json
└───package.json
You can use this structure to avoid confusion.
2. Project Setup
First step, create a project directory with name tutorial, then initialize Node.js using this command:
mkdir tutorial
cd tutorial
npm init -y
The npm init -y command will generate package.json file with the following contents:
{
"name": "tutorial",
"version": "1.0.0",
"description": "",
"main": "index.js",
"scripts": {
"test": "echo \"Error: no test specified\" && exit 1"
},
"keywords": [],
"author": "",
"license": "ISC"
}
Next, install dependencies (for Express.js and PostgreSQL) using this command :
npm install express dan pg
After the execution finished, the dependency files will be stored inside node_modules folder and list of dependencies will be listed in package.json file like below :
{
"name": "tutorial",
"version": "1.0.0",
"description": "",
"main": "index.js",
"scripts": {
"test": "echo \"Error: no test specified\" && exit 1"
},
"keywords": [],
"author": "",
"license": "ISC",
"dependencies": {
"express": "^4.17.1",
"pg": "^8.7.1"
}
}
Next, create the index.js file in the root folder. This file is the main file that needs to be executed to run the Node.js server. Inside the index.js file, we add the following script :
const express = require("express");
const bodyParser = require("body-parser");
const app = express();
const port = 3000;
app.use(bodyParser.json());
app.use(
bodyParser.urlencoded({
extended: true
})
)
The require function is used to all dependency to our project.
Next, we create a root URL (/) that returns JSON.
app.get("/", (request, response) => {
response.json({
info: 'Hello world!'
});
})
Save the file. To run the server, use this command :
node index.js
If the server running successfully, you will get this on your terminal :
$ node index.js
Server is running on 3000
If you access http://localhost:3000 from your client app (Postman or web browser) you will get a JSON response like below :
{
"info": "Hello world!"
}
3. Connect Express with PostgreSQL
The next important step is to connect Node.js with PostgreSQL. We create a file named queries.js (the name doesn't have to be exactly the same as in the article, but the usage in the program should be consistent) which will be used to set up the database connection and the queries used to access and manipulate data.
The first thing we need to do in this section is to set up a Pool connection to the database that will be used. This can be done using the following script:
const Pool = require("pg").Pool;
const pool = new Pool({
user: 'postgres',
host: 'localhost',
database: 'nama_db_kamu',
password: 'password_kamu',
port: 5432
});
4. Create Response Class (Optional)
This is an optional step, which is my personal preference. The purpose of creating the Response class is to standardize the response or output of the API we create. The Response class that I commonly use is as follows:
class Response {
constructor(status = false, code = 400, message = "", data = null) {
this.status = status;
this.code = code;
this.message = message;
this.data = data;
}
}
module.exports = Response;
The Response class will be used in the queries.js file. In this article, we will always use this Response class for JSON formatting in API responses.
const Pool = require("pg").Pool;
const pool = new Pool({
user: 'postgres',
host: 'localhost',
database: 'testing_db',
password: 'development',
port: 5432
});
const ResponseClass = require("./model/response") // opsional
Creating CRUD Operation Functions
In this section, we will create 5 CRUD functions:
getStudents()— to retrieve all student datagetStudentById()— to retrieve data of a student based on IDcreateStudent()— to create a student's dataupdateStudent()— to update a student's data based on IDdeleteStudent()— to delete a student's data based on ID
We will declare these five functions in the queries.js file, which will be accessed by index.js.
GET | getStundents()
const getStudents = (request, response) => {
var responseReturn = new ResponseClass();
pool.query('SELECT * FROM students ORDER BY id ASC', (error, results) => {
if (error) {
throw error
}
responseReturn.status = true;
responseReturn.code = 200;
responseReturn.message = "Success";
responseReturn.data = results.rows;
response.status(200).json(responseReturn);
})
}
GET | getStudentById()
const getStudentById = (request, response) => {
var responseReturn = new ResponseClass();
const id = parseInt(request.params.id)
pool.query('SELECT * FROM students WHERE id = $1', [id], (error, results) => {
if (error) {
throw error
}
if (results.rowCount == 0) {
responseReturn.status = true;
responseReturn.code = 404;
responseReturn.message = "User not found";
responseReturn.data = null;
} else {
responseReturn.status = true;
responseReturn.code = 200;
responseReturn.message = "Success";
responseReturn.data = results.rows[0];
}
response.status(200).json(responseReturn);
})
}
POST | createStudent()
const createStudent = (request, response) => {
const { firstname, lastname, origin } = request.body;
pool.query('INSERT INTO students (firstname, lastname, origin) VALUES ($1, $2, $3)', [firstname, lastname, origin], (error, results) => {
if (error) {
throw error
}
response.status(201).send("Student added");
})
}
PUT | updateStudent()
const updateStudent = (request, response) => {
const id = parseInt(request.params.id);
var responseReturn = ResponseClass();
try {
const { firstname, lastname, origin } = request.body;
pool.query('UPDATE students SET firstname = $1, lastname = $2, origin = $3 WHERE id = $4', [firstname, lastname, origin, id], (error, results) => {
if (error) {
throw error
}
responseReturn.status = true;
responseReturn.code = 200;
responseReturn.message = "User modification successed";
responseReturn.data = null;
response.status(200).send(responseReturn);
})
} catch (error) {
responseReturn.status = false;
responseReturn.code = 500;
responseReturn.message = error.message;
responseReturn.data = null
response.status(500).json(responseReturn);
}
}
The complete code for queries.js is as follows:
const Pool = require("pg").Pool;
const pool = new Pool({
user: 'postgres',
host: 'localhost',
database: 'testing_db',
password: 'development',
port: 5432
});
const ResponseClass = require("./model/response") // opsional
const getStudents = (request, response) => {
var responseReturn = new ResponseClass();
pool.query('SELECT * FROM students ORDER BY id ASC', (error, results) => {
if (error) {
throw error
}
responseReturn.status = true;
responseReturn.code = 200;
responseReturn.message = "Success";
responseReturn.data = results.rows;
response.status(200).json(responseReturn);
})
}
const getStudentById = (request, response) => {
var responseReturn = new ResponseClass();
const id = parseInt(request.params.id)
pool.query('SELECT * FROM students WHERE id = $1', [id], (error, results) => {
if (error) {
throw error
}
if (results.rowCount == 0) {
responseReturn.status = true;
responseReturn.code = 404;
responseReturn.message = "User not found";
responseReturn.data = null;
} else {
responseReturn.status = true;
responseReturn.code = 200;
responseReturn.message = "Success";
responseReturn.data = results.rows[0];
}
response.status(200).json(responseReturn);
})
}
const createStudent = (request, response) => {
const { firstname, lastname, origin } = request.body;
pool.query('INSERT INTO students (firstname, lastname, origin) VALUES ($1, $2, $3)', [firstname, lastname, origin], (error, results) => {
if (error) {
throw error
}
response.status(201).send("Student added");
})
}
const updateStudent = (request, response) => {
const id = parseInt(request.params.id);
var responseReturn = new ResponseClass();
try {
const { firstname, lastname, origin } = request.body;
pool.query('UPDATE students SET firstname = $1, lastname = $2, origin = $3 WHERE id = $4', [firstname, lastname, origin, id], (error, results) => {
if (error) {
throw error
}
responseReturn.status = true;
responseReturn.code = 200;
responseReturn.message = "User modification successed";
responseReturn.data = null;
response.status(200).send(responseReturn);
})
} catch (error) {
responseReturn.status = false;
responseReturn.code = 500;
responseReturn.message = error.message;
responseReturn.data = null
response.status(500).json(responseReturn);
}
}
const deleteStudent = (request, response) => {
const id = parseInt(request.params.id)
pool.query('DELETE FROM students WHERE id = $1', [id], (error, results) => {
if (error) {
throw error
}
response.status(201).send("Student deleted");
})
}
module.exports = {
getStudents,
getStudentById,
createStudent,
updateStudent,
deleteStudent
}
Creating REST API Endpoints
The next step is to create the endpoints that will be used in the REST API. Endpoints are URLs that can be accessed according to their request methods (GET, POST, PUT, DELETE).
The first step is to import the functions from queries.js by adding the following code in index.js:
const db = require('./queries');
Next, the code that needs to be added is as follows:
const express = require("express");
const bodyParser = require("body-parser");
const app = express();
const port = 3000;
const db = require('./queries');
app.use(bodyParser.json());
app.use(
bodyParser.urlencoded({
extended: true
})
)
app.listen(port, () => {
console.log("Server is running on " + port);
});
app.get("/", (request, response) => {
response.json({
info: 'Hello world!'
});
})
app.get("/students", db.getStudents);
app.get("/students/:id", db.getStudentById);
app.put("/students/:id", db.updateStudent);
app.post("/students", db.createStudent);
app.delete("/students/:id", db.deleteStudent);
Now you can run the server and access the endpoints following by correct HTTP datatype for each.
Conclusion
Thus, we have successfully created a REST API with basic CRUD functionality. Hopefully, it will be useful 🙂