DEV Community

Cover image for Node.js Rest CRUD API with Postgres
Rajan Prasad
Rajan Prasad

Posted on

Node.js Rest CRUD API with Postgres

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

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
Enter fullscreen mode Exit fullscreen mode

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}`);
});

Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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;

Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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,
  },
};

Enter fullscreen mode Exit fullscreen mode

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;

Enter fullscreen mode Exit fullscreen mode

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;
};

Enter fullscreen mode Exit fullscreen mode

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}`,
      });
    }
  });
};

Enter fullscreen mode Exit fullscreen mode

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

node server.js
Enter fullscreen mode Exit fullscreen mode

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

getall.png

Thanks for reading. You can find the code for the tutorial on Github

Top comments (2)

Collapse
 
bias profile image
Tobias Nickel

Good tutorial, I personally recently love the new postgres module, but with Sequelize I also already build a few successful projects.

Collapse
 
rajandmr profile image
Rajan Prasad

Thanks and haven't looked into postgres module yet but definitely going to, might be interesting !!