Building CRUD Apis Using Express and Postgresql

There are multiple options for database and often choosing the perfect db for your application depends on the use case of your app. Here in this article we will talk about integrating a popular db postgresql with expressJs. By the end of this article we will learn how to create simple CRUD apis using express and postgresql.

Installing Postgresql in local

There are multiple ways to install postgresql in local and steps are pretty self explanatory in their official page.

However, if you are a mac user I would encourage you to use brew. Steps are given below.

brew install postgresql

This installs postgresql in local, after that run the below.

pg_ctl -D /usr/local/var/postgres start && brew services start postgresql

This starts postgresql instance in local. You can check which version is installed using postgres -V If everything goes well, this will display the current installed version of postgresql in local.

Create Database

We need to create a local db before proceeding to development. For that first enter the db console by running psql postgres This command would enter you to the postgresql CLI.

Now we create our own database by running an SQL query:

CREATE DATABASE testdb;

This will create a database named testdb for us. postgresql creates this database without a password by default. However, to set password we can run the command \password in postgresql CLI. Follow the onscreen instructions and password would be set successfully.

Creating an Express Application

Just to keep it simple and fast I am using create-react-app to scaffold my application architecture. However, running npm init and following the onscreen instructions after that will yield a similar result. In my case i will be writing all my nodejs code inside server folder and my folder structure looks as below. This is the repo, incase of any reference.

Folder Structure in Local

server folder holds all the backend code and src folder will have all the front-end code using React. As this article focuses on creating api, so we will focus only on the server folder henceforth .

Before starting to write code, we will install the following dependencies.

npm i express cors pg nodemon --save

In this article we will use pg module to connect postgresql with node. You can refer this link for deep dive into this package.

Once all the relevant dependencies are installed we dump the following code inside server/index.js

const express = require('express');
const bodyParser = require('body-parser');
const path = require('path');
const app = express();
var cors = require('cors')

const userRoute = require('./routes/user');
const employeeRoute = require('./routes/employee');

app.use(cors());
app.use(express.static(path.join(__dirname, '../build')));
app.use(bodyParser.urlencoded({
    extended: true
}));
app.use(bodyParser.json());
app.use(userRoute);
app.use(employeeRoute);
app.get('/', function (req, res) {
res.sendFile(path.join(__dirname, '../build', 'index.html'));
});
app.listen(process.env.PORT || 3000);

This is standard boilerplate express code for starting a server. Notice that I am adding two express route files as middleware app.use(userRoute) and app.use(employeeRoute) These files are created as server/routes/user.js and server/routes/employee.js . For the moment lets focus only on routes/user.js

Connecting to DB

Inside user.js we try to make our first db connection. For that we need to import Client from pg.

const { Client } = require(‘pg’);

For establishing connection with database we write as below.

const connection = new Client({
   user: 'shouvikbhuiyan',
   host: 'localhost',
   database: 'testdb',
   password: '12345',
   port: 5432,
});
connection.connect();

database and password we had created earlier in this course. user you can see in your database console and port is user defined. Finally using connect method we may connect with the db.

Creating First Table

Going forward using sql queries we will be playing around with our database. postgresql has well documented list of queries in their official page.

For creating a new table named users the query is

CREATE TABLE users (
   email varchar,
   firstName varchar,
   lastName varchar,
   age int
);

varchar and int are types of variable supported by postgresql. Here is the complete list. We can also make some fields mandatory and define primary key. Details are given in this link.

Now to integrate this query in our codebase with pg.

const queryStr = `CREATE TABLE users (
  email varchar,
  firstName varchar,
  lastName varchar,
  age int
);`;
try {
  await connection.query(queryStr);
  console.log('Table Created Successfully');
} catch (e) {
  console.log('Something Went Wrong');
  throw e;
} finally {
  connection.end();
}

remember to close connection using connection.end it may cause problems otherwise.

Below is how the complete function for creating a table with successful connection to db looks.

(async () => {
const connection = new Client({
user: 'shouvikbhuiyan',
host: 'localhost',
database: 'testdb',
password: '12345',
port: 5432,
});
connection.connect();
const checkTableQueryStr = `SELECT EXISTS (
SELECT FROM information_schema.tables
WHERE  table_name   = 'users'
);`;
const response = await connection.query(checkTableQueryStr);
if (!response.rows[0].exists) { //this checks whether the table is already created
const queryStr = `
CREATE TABLE users (
email varchar,
firstName varchar,
lastName varchar,
age int
);`;
try {
await connection.query(queryStr);
console.log('Table Created Successfully');
} catch (e) {
console.log('Something Went Wrong');
throw e;
} finally {
connection.end();
}
}
})();

Inserting First Record

For data insertion we would need to make a POST call as we would need user input for email, firstName, lastName and age.

router.post('/api/insertUser', async (req, res) => {
const connection = new Client({
user: 'shouvikbhuiyan',
host: 'localhost',
database: 'testdb',
password: '12345',
port: 5432,
});
connection.connect();
const queryStr = {
text: `INSERT INTO users (email, firstName, lastName, age) VALUES($1, $2, $3, $4)`,
values: [req.body.email, req.body.firstName, req.body.lastName, req.body.age]
};
try {
await connection.query(queryStr);
res.status(200).send({
isSuccess: true
});
} catch (e) {
res.status(500).send({
isSuccess: false
});
throw e;
} finally {
connection.end();
}
});

Getting Data from DB

For getting data, we either can build a query to fetch all data or we can fetch some data based on condition. Below example uses request param to fetch all data or conditionally fetch some data based on boolean value passed as request param.

router.get('/api/getAllUsers/:getByAge', async (req, res) => {
const getByAge = req.params.getByAge;
const connection = new Client({
user: 'shouvikbhuiyan',
host: 'localhost',
database: 'testdb',
password: '12345',
port: 5432,
});
connection.connect();
const queryStr = getByAge === 'false' ? `SELECT * FROM users;` : `SELECT * FROM users WHERE age > 20`;
try {
const response = await connection.query(queryStr);
res.status(200).send({
result: response.rows
});
} catch (e) {
res.status(500).send({
isSuccess: false
});
throw e;
} finally {
connection.end();
}
});

Update data

For updating data we would make a PUT call and using request body received from user input we would update a particular field. Below example updates firstName of the dataset where its email matches with the user input.

router.put('/api/updateUser/', async (req, res) => {
const connection = new Client({
user: 'shouvikbhuiyan',
host: 'localhost',
database: 'testdb',
password: '12345',
port: 5432,
});
connection.connect();
const queryStr = {
text: `UPDATE users
SET firstName = $2
WHERE email = $1;`,
values: [req.body.email, req.body.firstName]
}

try {
await connection.query(queryStr);
res.status(200).send({
isSuccess: true
});
} catch (e) {
res.status(500).send({
isSuccess: false
});
throw e;
} finally {
connection.end();
}
});

Delete Data

In the below example we will use http delete method, where we will delete user based on their email Id. email id will be passed as request body.

router.delete('/api/removeUser/', async (req, res) => {
const connection = new Client({
user: 'shouvikbhuiyan',
host: 'localhost',
database: 'testdb',
password: '12345',
port: 5432,
});
connection.connect();
const queryStr = {
text: `DELETE FROM users WHERE email = $1;`,
values: [req.body.email]
}
try {
await connection.query(queryStr);
res.status(200).send({
isSuccess: true
});
} catch (e) {
res.status(500).send({
isSuccess: false
});
throw e;
} finally {
connection.end();
}
});

Our simple CRUD application is ready. Here is the complete user.js file. However, there is a problem. Note that every time we create an api, we need to open a connection to db. Each connection takes some time to establish a connection and if you run multiple queries at a time that connection establishment time will be significant.

Also postgresql can handle limited number of connection request at a time. Consider a scenario where a huge number of api calls is being made at a given point of time, this might crash your server.

To solve, this problem we use pg-pool module.

Creating a Pool

For that we import Pool class from pg module.

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

Then we create a new connection object using Pool, its same as co object we created earlier in this course.

const connection = new Pool({
user: 'shouvikbhuiyan',
host: 'localhost',
database: 'testdb',
password: '12345',
port: 5432,
});

Add a listener on this new connection object, in case of there is an error.

connection.on('error', (err, success) => {
console.log('Problem in connecting to db')
});

Creating a table using Pool Object and writing our first Query

Creating table and writing query using Pool is not dramatically different from what we have wrote earlier in this course. using Pool we just avoid making fresh connection request every time in the server.

(async () => {
const client = await connection.connect();
const checkTableQueryStr = `
SELECT EXISTS (
SELECT FROM information_schema.tables
WHERE  table_name   = 'employees'
);
`;
const response = await client.query(checkTableQueryStr);
if (!response.rows[0].exists) { //this checks whether the table is already created
const queryStr = `
CREATE TABLE employees (
email varchar,
firstName varchar,
lastName varchar,
age int
);
`;
try {
await client.query(queryStr);
console.log('Table Created Successfully');
} catch (e) {
console.log('Something Went Wrong');
throw e;
}
}
})();

Note that connection is a async method. So we need to wait for it to return result before making any further query on the db instance. The query below inserts a new employee based on data passed in request body.

router.post('/api/insertEmployee', async (req, res) => {
const client = await connection.connect();
const queryStr = {
text: `INSERT INTO employees (email, firstName, lastName, age) VALUES($1, $2, $3, $4)`,
values: [req.body.email, req.body.firstName, req.body.lastName, req.body.age]
};
try {
await client.query(queryStr);
res.status(200).send({
isSuccess: true
});
} catch (e) {
res.status(500).send({
isSuccess: false
});
throw e;
}
});

We can refactor our previous queries we wrote for user. I have created a new route employee, where all the queries are wrote using Pool. Here is the complete file.

Our very basic CRUD application is now ready. This is the link of the github repository. Happy coding !