Building a RESTful API with Node.js, Express, PostgreSQL, and JWT Authentication

In this tutorial, we’ll guide you through the process of creating a RESTful API using Node.js, Express, PostgreSQL for database operations, and JWT authentication for secure endpoints. The project structure includes directories for custom libraries, routes for authentication and API endpoints, middleware for JWT validation, and a migration route for database setup.

If you’d like to explore the project further or skip the setup process, you can clone the repository from GitHub with the following command:

git clone https://github.com/samsmithKruz/node_trial_stage_2/
cd node_trial_stage_2/
npm install

Add environment variables in the .env file for database configuration, JWT secret, and server port:

DB_HOST=localhost
DB_USER=your_db_user
DB_PASSWORD=your_db_password
DB_NAME=your_db_name
DB_PORT=5432
JWT_SECRET=your_jwt_secret
PORT=3030

Run the Project

npm run dev

And navigate to http://localhost:3030/migrate to run database migrations before testing the endpoints.

Project Structure

To follow along the building process and as well learn how to achieve this API and endpoints, you can continue below

The project is organized as follows:

1. Libraries: Custom libraries used in the project.
2. Database.js: Handles PostgreSQL database connection and queries.
3. JWT.js: Manages JWT token generation and validation.
4. Routes: auth.js: Routes for user authentication (/auth), api.js: Routes for main API endpoints (/api), migrate.js: Route for database migration (/migrate).
5. Middleware: authMiddleware.js: Middleware function to validate JWT tokens for the /api route.
6. .env: Configuration file for environment variables such as database connection details, JWT secret, and server port.
7. server.js: Main server file where the application is initialized, middleware is set up, and routes are defined.

Step-by-Step Guide

1. Setting Up the Project

Start by initializing a new Node.js project and installing necessary dependencies:

mkdir node-express-postgresql-api
cd node-express-postgresql-api
npm init -y
npm install express pg dotenv jsonwebtoken

2. Creating Project Files

Create the required project files and directories based on the structure outlined:

mkdir libraries
mkdir routes
touch libraries/Database.js
touch libraries/JWT.js
touch routes/auth.js
touch routes/api.js
touch routes/migrate.js
touch middleware/authMiddleware.js
touch .env
touch server.js

3. Defining Environment Variables

Set up environment variables in the .env file for database configuration, JWT secret, and server port:

DB_HOST=localhost
DB_USER=your_db_user
DB_PASSWORD=your_db_password
DB_NAME=your_db_name
DB_PORT=5432
JWT_SECRET=your_jwt_secret
PORT=3030

4. Implementing Database Connection (libraries/Database.js)

Create the database connection setup using the pg library:

const { Pool } = require('pg');
require('dotenv').config();

class Database {
    constructor() {
      this.pool = new Pool({
        host: process.env.DB_HOST,
        user: process.env.DB_USER,
        password: process.env.DB_PASSWORD,
        database: process.env.DB_NAME,
        port: process.env.DB_PORT,
      });
  
      // Optional: Log when connection to the database is established
      this.pool.on('connect', () => {
        console.log('Connected to the PostgreSQL database');
      });
  
      // Optional: Log any errors with the database connection
      this.pool.on('error', (err) => {
        console.error('Unexpected error on idle client', err);
        process.exit(-1);
      });
    }
  
    async query(text, params) {
      this.client = await this.pool.connect();
      const res = await this.client.query(text, params);
      await this.close();
      return res;
    }
  
    async close() {
      await this.client.end();
      console.log('Closed database connection');
    }
  }
  
  module.exports = new Database();

5. Implementing JWT Library (libraries/JWT.js)

Create the JWT library for token generation and validation:

const jwt = require('jsonwebtoken');
require('dotenv').config();

class JwtService {
  constructor() {
    this.secret = process.env.JWT_SECRET;
    this.expiresIn = process.env.JWT_EXPIRES_IN; //
  }

  generateToken(payload) {
    return new Promise((resolve, reject) => {
      jwt.sign(payload, this.secret, { expiresIn: this.expiresIn }, (err, token) => {
        if (err) {
          reject(err);
        } else {
          resolve(token);
        }
      });
    });
  }

  verifyToken(token) {
    return new Promise((resolve, reject) => {
      jwt.verify(token, this.secret, (err, decoded) => {
        if (err) {
          reject(err);
        } else {
          resolve(decoded);
        }
      });
    });
  }
}

module.exports = new JwtService;

6. Setting Up Express Server (server.js)

Initialize the Express server, configure middleware, define routes, and start the server:

const express = require('express');
const apiRoute = require('./routes/api')
const authRoute = require('./routes/auth')
const loggedIn = require('./middleware/auth_middleware')
const migrateRoute = require('./routes/migrate')
require('dotenv').config();


const app = express();
const port = process.env.PORT;

// Middleware to parse JSON bodies
app.use(express.json());

// Use routes
app.use('/api', loggedIn, apiRoute);
app.use('/auth', authRoute);
app.use('/migrate', migrateRoute);

app.get("/", (req, res) => {
    res.status(200).json({
        "status": 200,
        "message": "All ok"
    });
})

app.use((req, res, next) => {
    res.status(404).json({
        "statusCode": 404,
        "message": "Endpoint not found"
    });
})
app.use((err, req, res, next) => {
    const statusCode = res.statusCode === 200 ? 500 : res.statusCode;
    res.status(statusCode);
    res.json({
        message: err.message,
        stack: err.stack,
    });
})

app.listen(port, () => {
    console.log(`Server running on port ${port}`);
});

7. Implementing Routes

Define routes for authentication (auth.js), API endpoints (api.js), and database migration (migrate.js).

Example routes/auth.js (Authentication Routes)

const express = require('express');
const router = express.Router();
const db = require('../Libraries/Database')
const jwt = require('../Libraries/Jwt')
const crypto = require('crypto');

router.post("/register", async (req, res) => {
    const { firstName, lastName, email, phone, password } = req.body
    let errors = [];

    if (!firstName || firstName.length == 0) {
        errors.push({
            "field": "firstName",
            "message": "First name is invalid"
        })
    }
    if (!lastName || lastName.length == 0) {
        errors.push({
            "field": "lastName",
            "message": "Last name is invalid"
        })
    }
    if (!password || password.length == 0) {
        errors.push({
            "field": "password",
            "message": "Password is invalid"
        })
    }
    if (!email || email.length == 0 || !/^[^\s@]+@[^\s@]+\.[^\s@]+$/.test(email)) {
        errors.push({
            "field": "email",
            "message": "Email is invalid"
        })
    }

    if (errors.length > 0) {
        return res.status(422).json({
            "errors": errors
        })
    }

    // Hash password
    let hashedPassword = crypto.createHash('md5').update(password).digest('hex')

    return await db.query(`
        INSERT INTO users (firstName, lastName, email, password, phone)
      VALUES ($1, $2, $3, $4, $5)
      RETURNING userId
        `, [
        firstName,
        lastName,
        email,
        hashedPassword,
        phone || ""
    ])
        .then(async () => {
            const userid = await (await db.query(`SELECT userId as userid from users where email=$1`, [email])).rows[0]


            await db.query(`
                INSERT INTO organisation(orgid,name,owner,description) 
                values($1,$2,$1,$3)`, [
                userid.userid,
                `${firstName}'s Organisation`,
                ""
            ]);
            await db.query(`
                INSERT INTO organisation_user(orgid,userid) 
                values($1,$1)`, [
                userid.userid
            ]);



            const token = await jwt.generateToken({
                userId: userid.userid
            })

            return res.status(201).json({
                "status": "success",
                "message": "Registration successful",
                "data": {
                    "accessToken": token,
                    "user": {
                        "userId": "" + userid.userid,
                        "firstName": firstName,
                        "lastName": lastName,
                        "email": email,
                        "phone": phone || "",
                    }
                }
            })
        }).catch(error => {
            console.log(error)
            return res.status(400).json(
                {
                    "status": "Bad request",
                    "message": "Registration unsuccessful",
                    "statusCode": 400
                }
            )
        })
})
router.post("/login", async (req, res) => {
    const { email, password } = req.body
    if (!email || !/^[^\s@]+@[^\s@]+\.[^\s@]+$/.test(email) || !password || password.length == 0) {
        return res.status(401).json({
            "status": "Bad request",
            "message": "Authentication failed",
            "statusCode": 401
        })
    }

    // Hash password
    let hashedPassword = crypto.createHash('md5').update(password).digest('hex')

    const user = await db.query(`SELECT * from users where email =$1 and password =$2`, [
        email,
        hashedPassword
    ])
    if (user.rowCount > 0) {
        let { userid, firstname, lastname, phone } = user.rows[0];
        return res.status(200).json(
            {
                "status": "success",
                "message": "Login successful",
                "data": {
                    "accessToken": await jwt.generateToken({
                        userId: userid
                    }),
                    "user": {
                        "userId": "" + userid,
                        "firstName": firstname,
                        "lastName": lastname,
                        "email": email,
                        "phone": phone,
                    }
                }
            }
        )
    } else {
        return res.status(401).json({
            "status": "Bad request",
            "message": "Authentication failed",
            "statusCode": 401
        })
    }

})
module.exports = router;

Example routes/api.js

const express = require('express');
const router = express.Router();
const db = require('../Libraries/Database')
router.get("/users/:id", async (req, res) => {
    const { id } = req.params
    const userId = req.userId

    let record = await db.query(`
        SELECT 
                users.userid::VARCHAR,
                users.firstname,
                users.lastname,
                users.email,
                users.phone
            FROM 
                users
            LEFT JOIN 
                organisation_user ON organisation_user.userid::VARCHAR = users.userid::VARCHAR
            LEFT JOIN 
                organisation ON organisation.orgid::VARCHAR = organisation_user.orgid::VARCHAR
            WHERE
                users.userid::VARCHAR = $1 and organisation_user.orgid::VARCHAR = $2
        `, [id, userId]);

    if (record.rowCount > 0) {
        let { userid, firstname, email, lastname, phone } = record.rows[0];
        return res.status(200).json(
            {
                "status": "success",
                "message": "Fetched successful",
                "data": {
                    "userId": userid,
                    "firstName": firstname,
                    "lastName": lastname,
                    "email": email,
                    "phone": phone,
                }
            }
        )
    } else {
        return res.status(400).json({
            "status": "Bad Request",
            "message": "Client error",
            "statusCode": 400
        })
    }
})
router.get("/organisations", async (req, res) => {
    const userId = req.userId
    let record = await db.query(`
        SELECT 
                organisation.orgid::VARCHAR,
                organisation.name::VARCHAR,
                organisation.description::VARCHAR
            FROM organisation
            LEFT JOIN organisation_user ON
                organisation_user.orgid::VARCHAR = organisation.orgid::VARCHAR
            WHERE 
                organisation_user.userid::VARCHAR = $1
        `, [userId]);

    if (record.rowCount > 0) {
        record.rows = record.rows.map(e => {
            e.orgId = e.orgid;
            delete e.orgid
            return e
        })
        return res.status(200).json(
            {
                "status": "success",
                "message": "Fetched successful",
                "data": {
                    "organisations": record.rows
                }

            }
        )
    } else {
        return res.status(400).json({
            "status": "Bad Request",
            "message": "Client error",
            "statusCode": 400
        })
    }
})
router.post("/organisations", async (req, res) => {
    const userId = req.userId
    const { name, description } = req.body
    if (!name) {
        return res.status(400).json({
            "status": "Bad Request",
            "message": "Client error",
            "statusCode": 400
        })
    }
    let orgid = +userId;
    while (true) {
        orgid = orgid + Math.floor(Math.random() * 10)
        let _ = await db.query(`SELECT orgid from organisation where orgid=$1`, [orgid])
        if (_.rowCount == 0) {
            break;
        }
    }
    let record = await db.query(`
        INSERT INTO organisation(orgid,name,description,owner)
        values($1,$2,$3,$4)
        `, [
        orgid,
        name,
        description || "",
        userId
    ]);

    if (record.rowCount > 0) {
        await db.query(`
            INSERT INTO organisation_user(orgid,userid)
            values($1,$2)
            `, [
            orgid,
            userId
        ]);
        return res.status(201).json(
            {
                "status": "success",
                "message": "Organisation created successfully",
                "data": {
                    "orgId": "" + orgid,
                    "name": name,
                    "description": description,
                }

            }
        )
    } else {
        return res.status(400).json({
            "status": "Bad Request",
            "message": "Client error",
            "statusCode": 400
        })
    }
})
router.post("/organisations/:orgId/users", async (req, res) => {
    const { orgId } = req.params
    const { userId } = req.body

    if (!userId) {
        return res.status(400).json({
            "status": "Bad Request",
            "message": "Client error",
            "statusCode": 400
        })
    }

    let record = await db.query(`
        INSERT INTO organisation_user(orgid,userid)
        values($1,$2)
        `, [orgId, userId]);

    if (record.rowCount == 0) {
        return res.status(400).json({
            "status": "Bad Request",
            "message": "Client error",
            "statusCode": 400
        })
    }
    return res.status(200).json({
        "status": "success",
        "message": "User added to organisation successfully"
    })

})
router.get("/organisations/:orgId", async (req, res) => {
    // const userId = req.userId
    const { orgId } = req.params

    let record = await db.query(`
        SELECT * from organisation where orgid::VARCHAR=$1
        `, [orgId])


    if (record.rowCount > 0) {
        return res.status(200).json(
            {
                "status": "success",
                "message": "Fetched successful",
                "data": record.rows[0]

            }
        )
    } else {
        return res.status(400).json({
            "status": "Bad Request",
            "message": "Client error",
            "statusCode": 400
        })
    }

})
module.exports = router;

Example routes/migrate.js

const express = require('express');
const db = require('../Libraries/Database');
const router = express.Router();

router.all("/", async (req, res) => {
    await db.query(`
        DROP TABLE IF EXISTS users;
        CREATE TABLE users (
            userId SERIAL PRIMARY KEY,  
            firstName VARCHAR(255) NOT NULL,  
            lastName VARCHAR(255) NOT NULL,   
            email VARCHAR(255) NOT NULL UNIQUE,  
            password VARCHAR(255) NOT NULL,  
            phone VARCHAR(50)                
            );

        `)
    await db.query(`
        DROP TABLE IF EXISTS organisation;
        CREATE TABLE organisation (
            orgId VARCHAR(255) NOT NULL UNIQUE,  
            name VARCHAR(255) NOT NULL,  
            owner VARCHAR(255) NOT NULL,  
            description VARCHAR(255) NULL        
            );
        `)
    await db.query(`
        DROP TABLE IF EXISTS organisation_user;
        CREATE TABLE organisation_user (
            orgId VARCHAR(255) NOT NULL,  
            userId VARCHAR(255) NOT NULL        
            );
        `)
        res.status(201).json({
            "message":"migrated successfully"
        })
})
module.exports = router;

And navigate to http://localhost:3030/migrate to run database migrations before testing the endpoints.

Conclusion

By following these steps, you’ve set up a structured Node.js application with Express, PostgreSQL database integration, JWT authentication, and organized routing using the project structure you specified. This provides a foundation for building scalable and secure RESTful APIs using modern web development practices.

This guide serves as a comprehensive starting point for developing robust backend APIs with Node.js, Express, PostgreSQL, and JWT authentication.

Additional Resources

• GitHub Repository: https://github.com/samsmithKruz/node_trial_stage_2/
• Author’s Twitter Account: https://x.com/Samsmith_Kruz