ExpressJS Tutorials | Projects | Interview Questions Tutorial

Express js Interview Questions for Fresher and Experience

How to Connect Postgres with Express

❮ Previous Next ❯

How to Connect PostgreSQL with Express?

PostgreSQL, also known as Postgres, is a free and open-source relational database management system emphasizing extensibility and SQL compliance.
Download Postgres in your system using the link.
Install Postgres in your system.
To Download and install the Postgres module, open the command Terminal and execute the following:

npm install pg

Let's start with opening SQL Shell(psql), entering a password, and creating a database.

SQL Shell

Create a table using the following queries

SQL Shell create_table

Now let's move to the code part, open vs code, and run the command on terminal

npm init -y

Now let's move to the code part. here is an application file structure.

folder structure

For connecting the server with Postgres do the following code in dB.js

const Pool = require("pg").Pool;
const pool = new Pool({
user: "postgres",
host: "localhost",
database: "students",
password: "YOUR_PASSWORD",
port: 5433,
});
module.exports = pool;

All the crud operation using queries is coded in the following files src/student/controller.js, src/student/queries.js, and src/student/routes.js.

In controller.js

const pool=require('../../db');
const queries=require('./queries')
const getStudents= (req, res) => {
    pool.query(queries.getStudents,(error,results)=>{
        if(error) throw error;
        res.status(200).json(results.rows);
    });
};
const getStudentById = (req, res) =>{
    const id = parseInt(req.params.id);
    pool.query(queries.getStudentById, [id], (error, results) => {
    if(error) throw error;
    res.status(200).json(results.rows);
    });
};

const addStudent = (req, res)=> {
    const{ name, email, age, dob } = req.body;
    pool.query(queries.checkEmailExists, [email], (error, results) =>{
    if(results.rows.length){
    res.send("Email already exists.");
}
    pool.query(
        queries.addStudent,
        [name, email, age, dob],
        (error, results) =>{
        if(error) throw error;
        res.status(201).send("Student Created Successfully!");
         }
         );
        });
    };

const removeStudent= (req, res) =>{
        const id = parseInt(req.params.id);
        pool.query(queries.getStudentById, [id], (error, results) =>{
        const noStudentFound =! results.rows.length;
        if(noStudentFound){
        res.send("Student does not exist in the database");
        }
        pool.query(queries.removeStudent, [id], (error, results) =>{
        if(error) throw error;
        res.status(200).send("Student removed successfully.");
        });
    });
    };


const updateStudent= (req, res) =>{
        const id = parseInt(req.params.id);
        const { name } = req.body;
        pool.query(queries.getStudentById, [id] , (error, results) =>{
        const noStudentFound =! results.rows.length;
        if(noStudentFound) {
        res.send("Student does not exist in the database");
        }
        pool.query(queries.updateStudent, [name], (error, results) =>{
        if(error) throw error;
        res.status(200).send("Student updated successfully");
        });
    });
};
module.exports = {
        getStudents,
        getStudentById,
        addStudent,
        removeStudent,
        updateStudent
        };

In queries.js

const getStudents = "SELECT * FROM students";
const getStudentById = "SELECT * FROM students WHERE id = $1";
const checkEmailExists = "SELECT s FROM students s WHERE s.email = $1";
const addStudent = "INSERT INTO students(name, email, age, dob)VALUES($1, $2, $3, $4)";
const removeStudent = "DELETE FROM students WHERE id = $1";
const updateStudent = "UPDATE students SET name = $1 WHERE id = $2";

module.exports = {
getStudents,
getStudentById,
checkEmailExists,
addStudent,
removeStudent,
updateStudent,
};

In routes.js

const{ Router } = require("express");
const controller = require("./controller");

const router = Router();

router.get("/", controller.getStudents);
router.post("/", controller.addStudent);
router.get("/:id", controller.getStudentById);
router.put("/:id", controller.updateStudent);
router.delete("/:id", controller.removeStudent);

module.exports = router;

In server.js

const express = require("express");
const studentRoutes = require("./src/student/routes");

const app = express();
const port = 3000;

app.use(express.json());
app.get("/", (req, res) => {
res.send("Hello World!");
});
app.use("/api/v1/students", studentRoutes);
app.listen(port, () => console.log('app listening on port ${port}'));