Node.js Rest CRUD API with Postgres
Tags: node, restapi, postgres
Published: 2020-08-29 06:10:28

PostgreSQL is a powerful, open source object-relational database system  that has earned it a strong reputation for reliability, feature robustness, and performance. In this article, We will build Rest Apis that can create, retrieve, update and delete Notes.

First we will start setting up express server and routes with express router. Next we add configuration for PostgreSQL database and create Note Model with sequelize. Our File structure at the end will be 
![folderstructure.png](https://cdn.hashnode.com/res/hashnode/image/upload/v1598679371188/P1DBpvBiA.png)

To create express server and install required dependencies, in your terminal type

```
mkdir postgres-crud
cd postgres-crud
npm init -y
touch server.js
mkdir app
npm i express cors body-parser pg pg-hstore sequelize@5.21.13
``` 
This will create a folder called postgres-crud, initialize a node project and install the required dependencies. Now, we need to set-up our express server code and configure the route. To set up the server, Edit the **server.js** file as



```
// Bring in required Modules
const express = require("express");
const bodyParser = require("body-parser");
const cors = require("cors");

const app = express();

// Bring in the route
const routes = require("./app/routes");

var corsOptions = {
  origin: "http://localhost:8081",
};

app.use(cors(corsOptions));

app.use(bodyParser.json());

app.use(bodyParser.urlencoded({ extended: true }));

const db = require("./app/models");
db.sequelize.sync();

app.use("/api/notes", routes);

// Define PORT
const PORT = process.env.PORT || 8080;

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

``` 
Now, to set up the routes, we will create a folder called *routes* in our *app* folder and under *routes* folder, we will create a file called **index.js**. You can do these all through the editor or type the following command in your terminal

```
mkdir app/routes
touch app/routes/index.js
``` 
Now, edit the *app/routes/index.js* file as


```
// Bring in the express server
const express = require("express");

// Bring in the Express Router
const router = express.Router();

// Import the Controller
const controller = require("../controllers");


// Create a new Note
router.post("/", controller.create);

// Get all Notes
router.get("/", controller.findAll);

// Get Note by Id
router.get("/:id", controller.findOne);

// Modify existing Note
router.put("/:id", controller.update);

// Delete Note by Id
router.delete("/:id", controller.delete);

module.exports = router;

``` 
Now, the next step is to configure the Database. In order to do so, we create *config* folder inside our *app* folder then create a file **db.config.js** file under the config folder. To do these through command line 


```
mkdir app/config
touch app/config/db.config.js
``` 
Now, edit the **db.config.js** file as below. You have to replace the HOST, USER, PASSWORD values with your own db values.

```
module.exports = {
  HOST: "localhost", // Replace it with your own host address
  USER: "user123", // Replace with your own username
  PASSWORD: "12345", // Replace with your own password
  DB: "testdb",
  dialect: "postgres",
  pool: {
    max: 5,
    min: 0,
    acquire: 30000,
    idle: 10000,
  },
};

``` 
Now, the setting up db configuration part is done. Next is to define the db model. To do so, create a folder called *models* inside *app* folder and initialize two files namely *index.js* and *notes.model.js*. Now, edit the index.js file as 


```
const dbConfig = require("../config/db.config");

const Sequelize = require("sequelize");
const sequelize = new Sequelize(dbConfig.DB, dbConfig.USER, dbConfig.PASSWORD, {
  host: dbConfig.HOST,
  dialect: dbConfig.dialect,
  operatorAliases: 0,

  pool: {
    max: dbConfig.pool.max,
    min: dbConfig.pool.min,
    acquire: dbConfig.pool.acquire,
    idle: dbConfig.pool.idle,
  },
});

const db = {};
db.Sequelize = Sequelize;
db.sequelize = sequelize;

db.notes = require("./notes.model.js")(sequelize, Sequelize);
module.exports = db;

``` 
Also, edit the notes.model.js file as 

```
const { sequelize, Sequelize } = require(".");

module.exports = (sequelize, Sequelize) => {
  const Note = sequelize.define("note", {
    title: {
      type: Sequelize.STRING,
    },
    description: {
      type: Sequelize.STRING,
    },
    published: {
      type: Sequelize.BOOLEAN,
    },
  });
  return Note;
};

``` 

Now, the model is all set. Last thing remaining to do is defining controller. To do so, create a folder called *controllers* inside *app* folder and initialize a file namely **index.js** inside *controllers* folder. Edit the **index.js** file as 

```
const db = require("../models");
const Notes = db.notes;
const Op = db.Sequelize.Op;

exports.create = (req, res) => {
  if (!req.body.title) {
    res.status(400).send({
      message: "Content can not be empty !",
    });
    return;
  }

  const note = {
    title: req.body.title,
    description: req.body.description,
    published: req.body.published ? req.body.published : false,
  };

  Notes.create(note)
    .then((data) => {
      res.send(data);
    })
    .catch((err) => {
      res.status(500).send({
        message: err.message || "Some error occurred while create the Notes",
      });
    });
};

exports.findAll = (req, res) => {
  const title = req.query.title;

  Notes.findAll()
    .then((data) => {
      res.send(data);
    })
    .catch((err) => {
      res.status(500).send({
        message: err.message || "Some error occured while retrieving Notes",
      });
    });
};

exports.findOne = (req, res) => {
  const id = req.params.id;
  Notes.findByPk(id)
    .then((data) => {
      res.send(data);
    })
    .catch((err) => {
      res.status(500).send({
        message: "Error retrieving Notes with id=" + id,
      });
    });
};

exports.update = (req, res) => {
  const id = req.params.id;

  Notes.update(req.body, {
    where: { id: id },
  }).then((data) => {
    if (data) {
      res.send({
        message: "Note was updated successfully",
      });
    } else {
      res.send({
        message: `Cannot update Note with id=${id}`,
      });
    }
  });
};

exports.delete = (req, res) => {
  const id = req.params.id;

  Notes.destroy({
    where: { id: id },
  }).then((data) => {
    if (data) {
      res.send({
        message: "Note was delete successfully!",
      });
    } else {
      res.send({
        message: `Cannot delete Note with id=${id}`,
      });
    }
  });
};

``` 
Now, we can finally run the application. To do so run

```
node server.js
``` 
If you've provided valid credentials for db and followed the steps correctly, you'll see the message *Server is running on port 8080* and we'll be able to test the endpoints on Postman. The test result will be similar to mine for create and getAll methods


![create.png](https://cdn.hashnode.com/res/hashnode/image/upload/v1598681245519/5qGmH8jh8.png)




![getall.png](https://cdn.hashnode.com/res/hashnode/image/upload/v1598681253574/iryPrzK1a.png)


> Thanks for reading. You can find the code for the tutorial on  [Github](https://github.com/rajandmr/postgres-node-sequelize)