Learning Backend Part 3: Integrating Express.js with PostgreSQL for a Full CRUD API

Note: This article assumes you have already configured PostgreSQL and initialized your Node.js project (as covered in previous articles). Here, we focus on integrating PostgreSQL with Express.js and refactoring our project using best practices and SOLID principles to implement full CRUD operations for a users resource.

Brief Introduction to Express.js and Its Use Cases

Express.js is a minimal, fast, and flexible Node.js web application framework that provides a robust set of features to develop web and mobile applications. Its flexible design lets you organize your application exactly how you prefer.

Key Features:
- Routing: Easily define routes for handling various HTTP requests (GET, POST, PUT, DELETE, etc.).
- Middleware: Supports an extensive ecosystem of middleware to process requests and responses (logging, authentication, error handling, etc.).
- Performance: Lightweight and optimized for handling concurrent connections.
- Extensibility: Integrates seamlessly with other libraries and databases.
Use Cases:
- RESTful APIs: Build APIs to serve data to web and mobile applications.
- Server-side Rendering: Create dynamic web pages rendered on the server.
- Microservices: Develop small, independent services that work together.
- Rapid Prototyping: Quickly build and iterate on web applications.

Updated Folder Structure

For better maintainability and adherence to SOLID principles, our updated folder structure is as follows:

/my-backend-project
├── package.json
├── .env
├── src
│   ├── config
│   │   └── db.js             # PostgreSQL connection pool and configuration
│   ├── controllers
│   │   └── userController.js # Handles HTTP requests for user operations
│   ├── routes
│   │   └── userRoutes.js     # Defines API endpoints for users
│   ├── services
│   │   └── userService.js    # Business logic and database queries for users
│   └── index.js              # Application entry point
└── README.md

Step 1: Integrate PostgreSQL into the API

We’ll connect our Express API to PostgreSQL using the pg library. Create the following file:

File: `src/config/db.js`

<!-- src/config/db.js -->
require('dotenv').config();
const { Pool } = require('pg');

const pool = new Pool({
  host: process.env.DB_HOST,
  port: process.env.DB_PORT,
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  database: process.env.DB_NAME
});

pool.on('error', (err) => {
  console.error('Unexpected error on idle client', err);
  process.exit(-1);
});

module.exports = pool;

Step 2: Implement CRUD Operations

Next, we implement our business logic, controller, and routes for a users resource.

File: `src/services/userService.js`

<!-- src/services/userService.js -->
const pool = require('../config/db');

const createUser = async (name, email) => {
  const query = 'INSERT INTO users (name, email) VALUES ($1, $2) RETURNING *';
  const values = [name, email];
  const result = await pool.query(query, values);
  return result.rows[0];
};

const getAllUsers = async () => {
  const result = await pool.query('SELECT * FROM users');
  return result.rows;
};

const getUserById = async (id) => {
  const result = await pool.query('SELECT * FROM users WHERE id = $1', [id]);
  return result.rows[0];
};

const updateUser = async (id, name, email) => {
  const query = 'UPDATE users SET name = $1, email = $2 WHERE id = $3 RETURNING *';
  const values = [name, email, id];
  const result = await pool.query(query, values);
  return result.rows[0];
};

const deleteUser = async (id) => {
  const query = 'DELETE FROM users WHERE id = $1 RETURNING *';
  const result = await pool.query(query, [id]);
  return result.rows[0];
};

module.exports = {
  createUser,
  getAllUsers,
  getUserById,
  updateUser,
  deleteUser
};

File: `src/controllers/userController.js`

<!-- src/controllers/userController.js -->
const userService = require('../services/userService');

const createUser = async (req, res) => {
  const { name, email } = req.body;
  try {
    const newUser = await userService.createUser(name, email);
    res.status(201).json(newUser);
  } catch (error) {
    console.error('Error creating user:', error);
    res.status(500).json({ error: error.message });
  }
};

const getAllUsers = async (req, res) => {
  try {
    const users = await userService.getAllUsers();
    res.json(users);
  } catch (error) {
    console.error('Error fetching users:', error);
    res.status(500).json({ error: error.message });
  }
};

const getUserById = async (req, res) => {
  const { id } = req.params;
  try {
    const user = await userService.getUserById(id);
    if (!user) return res.status(404).json({ error: 'User not found' });
    res.json(user);
  } catch (error) {
    console.error('Error fetching user:', error);
    res.status(500).json({ error: error.message });
  }
};

const updateUser = async (req, res) => {
  const { id } = req.params;
  const { name, email } = req.body;
  try {
    const updatedUser = await userService.updateUser(id, name, email);
    if (!updatedUser) return res.status(404).json({ error: 'User not found' });
    res.json(updatedUser);
  } catch (error) {
    console.error('Error updating user:', error);
    res.status(500).json({ error: error.message });
  }
};

const deleteUser = async (req, res) => {
  const { id } = req.params;
  try {
    const deletedUser = await userService.deleteUser(id);
    if (!deletedUser) return res.status(404).json({ error: 'User not found' });
    res.json({ message: 'User deleted successfully', user: deletedUser });
  } catch (error) {
    console.error('Error deleting user:', error);
    res.status(500).json({ error: error.message });
  }
};

module.exports = {
  createUser,
  getAllUsers,
  getUserById,
  updateUser,
  deleteUser
};

File: `src/routes/userRoutes.js`

<!-- src/routes/userRoutes.js -->
const express = require('express');
const userController = require('../controllers/userController');

const router = express.Router();

router.post('/', userController.createUser);
router.get('/', userController.getAllUsers);
router.get('/:id', userController.getUserById);
router.put('/:id', userController.updateUser);
router.delete('/:id', userController.deleteUser);

module.exports = router;

File: `src/index.js`

<!-- src/index.js -->
require('dotenv').config();
const express = require('express');
const userRoutes = require('./routes/userRoutes');

const app = express();
const PORT = process.env.PORT || 3000;

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

// Test endpoint to verify server is running
app.get('/api/ping', (req, res) => {
  res.json({ success: true, message: 'Server is running' });
});

// Mount user routes
app.use('/api/users', userRoutes);

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

Step 3: Starting the Server

Before starting a new instance of your server, make sure to stop any previously running instance by pressing Ctrl+C in the terminal.

Then, start your server by running:

node src/index.js

You should see a log message indicating that the server is running (e.g., Server is running on port 3000).

Step 4: Testing Your CRUD API

Use Postman, curl, or another API testing tool to verify the following endpoints:

Create a User: POST to http://localhost:3000/api/users with JSON body:
```
{
  "name": "Alice",
  "email": "alice@example.com"
}
```
Retrieve All Users: GET request to http://localhost:3000/api/users
Retrieve a Specific User: GET request to http://localhost:3000/api/users/1 (replace 1 with a valid user ID)
Update a User: PUT request to http://localhost:3000/api/users/1 with JSON body:
```
{
  "name": "Alice Smith",
  "email": "alice.smith@example.com"
}
```
Delete a User: DELETE request to http://localhost:3000/api/users/1

Verify that each endpoint returns the expected response.

Debugging Common Errors

If you encounter issues while calling the API, here are some common errors and how to fix them:

Error: “role "my_app_user" does not exist”
This error means that PostgreSQL cannot find the role my_app_user. To fix this, log in to your PostgreSQL shell or use PGAdmin and run:
```
CREATE USER my_app_user WITH PASSWORD 'mypassword';
```
Then, grant the necessary privileges to your database:
```
GRANT ALL PRIVILEGES ON DATABASE my_test_db TO my_app_user;
```
Error: “database "my_test_db_2" does not exist”
This error indicates that the application is trying to connect to a database named my_test_db_2, which hasn’t been created. To resolve this, either create the database by running:
```
CREATE DATABASE my_test_db_2;
```
or update your .env file with the correct database name.
Error: “relation "users" does not exist”
This error means that the users table hasn’t been created in your database. To fix this, connect to your database and run the following SQL command:
```
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(255) UNIQUE NOT NULL
);
```

Conclusion

In this article, we extended our Express.js API by integrating PostgreSQL and refactoring our project to follow best practices and SOLID principles. We focused on:

Integrating PostgreSQL via a dedicated configuration file
Organizing code into services, controllers, and routes
Implementing full CRUD endpoints for the users resource
Starting the server properly by stopping any previous instances
Testing the API using tools like Postman or curl
Debugging common errors that beginners might encounter

This structured approach not only improves maintainability but also sets a strong foundation for future enhancements such as authentication and advanced error handling.

Happy coding, and I look forward to your feedback on this new architecture!

Brief Introduction to Express.js and Its Use Cases

Updated Folder Structure

Step 1: Integrate PostgreSQL into the API

File: src/config/db.js

Step 2: Implement CRUD Operations

File: src/services/userService.js

File: src/controllers/userController.js

File: src/routes/userRoutes.js

File: src/index.js

Step 3: Starting the Server

Step 4: Testing Your CRUD API

Debugging Common Errors

Conclusion

You Might Also Like

How to Set Up a PostgreSQL Database in PGAdmin: A Step-by-Step Guide

Learning Backend – Part 1: Setting Up Your Backend Environment

Learning Backend – Part 2: Building a REST API with Express.js

Leave a Reply Cancel reply

File: `src/config/db.js`

File: `src/services/userService.js`

File: `src/controllers/userController.js`

File: `src/routes/userRoutes.js`

File: `src/index.js`