A Simple Nodejs App using Postgres and Express

Building a CRUD API with Node.js and PostgreSQL

Introduction

In this article, we will walk through a codebase for creating a CRUD (Create, Read, Update, Delete) API using Node.js and PostgreSQL. This codebase serves as the foundation for a basic web application that allows you to perform essential database operations.

Technologies Used

• Node.js
• Express.js
• PostgreSQL
• morgan (for logging)

Note that you need to have postgres installed on your local machine

Firstly we need to get our database credentials, something like this:

Note that I created a database called test. You can do this after logging in to your psql with either cmd or PgAdmin app.

On cmd you can do this psql -U postgres you'll be prompted to input your password. The command simply means I'm logging in to psql with the user postgres. If you want to use the user root use psql only and input your password.

After inputting your password you should see this

Now create your database CREATE DATABASE test. To confirm your list of database use \l. You should have test there now

Now we'll tell our laptop on cmd we want to use the test database 😉😉 \c test

Now create a table called users with columns id, name and email

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL
);

The UNIQUE attached to email means that the email must not be the same for any users

Now we seed a dummy data into the table

INSERT INTO users (name, email)
VALUES
    ('John Doe', 'john.doe@example.com'),
    ('Jane Smith', 'jane.smith@example.com');

You can check if all these is successful by checking the content of your table with SELECT * FROM users mine looks like this;

Note I previously have other data in my table that's why it's more than two.

Now we can code 😋😋😁😁😋😋

Create a folder, and give it any name of your choice, mine is Src. Then create the entry file mine is index.js

Now do npm init -y

Next we install our dependencies npm I express morgan pg

You should have a node_modules folder and package.json. If you want to you can edit it like mine

Now we create our express app

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

// Create an instance of the Express application
const app = express();
const port = 3000;

// PostgreSQL connection configuration
const pool = new Pool({
    user: 'postgres',
    host: '127.0.0.1',
    database: 'test',
    password: 'qwer',
    port: 5432 // Change this if your PostgreSQL server is running on a different port
});

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

// Home Page
app.get('/', (req, res) => {
    res.send('<h1>Welcome to the Home Page</h1><p>This is the home page of the CRUD API.</p>');
  });

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

I added all the required comments for the code that follows you get 😎😎
Now go to http://localhost:3000/ you should see your homepage😚...

Finally we can add our CRUD functions 🙌🙌🙌

// Define routes for CRUD operations

// Create (POST)
app.post('/users', async (req, res) => {
  try {
    const { name, email } = req.body;
    const query = 'INSERT INTO users (name, email) VALUES ($1, $2) RETURNING *';
    const values = [name, email];
    const result = await pool.query(query, values);
    res.json(result.rows[0]);
  } catch (err) {
    console.error(err);
    res.status(500).json({ error: 'An error occurred' });
  }
});

// Read (GET)
app.get('/get_users', async (req, res) => {
  try {
    const query = 'SELECT * FROM users';
    const result = await pool.query(query);
    res.json(result.rows);
  } catch (err) {
    console.error(err);
    res.status(500).json({ error: 'An error occurred' });
  }
});

// Update (PUT)
app.put('/users/:id', async (req, res) => {
  try {
    const id = req.params.id;
    const { name, email } = req.body;
    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);
    res.json(result.rows[0]);
  } catch (err) {
    console.error(err);
    res.status(500).json({ error: 'An error occurred' });
  }
});

// Delete (DELETE)
app.delete('/users/:id', async (req, res) => {
  try {
    const id = req.params.id;
    const query = 'DELETE FROM users WHERE id = $1';
    const result = await pool.query(query, [id]);
    res.json({ message: 'User deleted successfully' });
  } catch (err) {
    console.error(err);
    res.status(500).json({ error: 'An error occurred' });
  }
});

Your whole code should look like this now

Now start your server with npm run dev
To create a user http://localhost:3000/users for a POST request, I use postman to test my endpoints

To get users http://localhost:3000/get_users a GET request

To update a user http://localhost:3000//users/:id we input the user's id in the path variable and do a PUT request

I just updated the user I created 😎😎😎
To delete a user http://localhost:3000/users/:id and the id and use a DELETE request

And we have deleted that user.

That's it! You've created a CRUD API with Node.js and PostgreSQL. 😁😁😁