A Simple Nodejs App using Postgres and Express
Tags: webdev, javascript, beginners, tutorial
Published: 2023-10-31 15:02:49

## 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: 

![DB credentials](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ybukdh4hyynfc5kxr83a.png)
> 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 

![cmd password](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/anrz3qubionz09rroaog.png)
> 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`

![cmd code](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/q0rsprprugcjudt2rls5.png)
> Now create a table called users with columns id, name and email
```sql
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 
```sql
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;

![cmd code](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/r23kt5umy002q4k5q2ht.png) 
> 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

![package.json](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/cvc5qfofpgkyr66ce2n0.png)
## Now we create our express app
```javascript
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 🙌🙌🙌
```javascript
// 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 


![index.js](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/mltddo5l7es3npl2t39h.png)
> 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

![postman](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/y7wcomjug96m1zrkazz3.png)
> To get users `http://localhost:3000/get_users` a GET request

![postman get request](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/2h28qw23papf7um1n3qq.png)
> To update a user `http://localhost:3000//users/:id` we input  the user's id in the path variable and do a PUT request

![postman update](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/v8o2d865f7n87hwvzdb5.png)
> I just updated the user I created 😎😎😎
> To delete a user `http://localhost:3000/users/:id` and the id and use a DELETE request

![postman delete request](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/8eyeohg2kj168f7p8ltg.png)
> And we have deleted that user.

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

![gif](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/zx1xv29nlzq0lsqjkuyl.gif)