Ajibadde

Build Simple Node.js CRUD APIs using Sequelize ORM

Photo by Desola Lanre-Ologun on Unsplash

Build Simple Node.js CRUD APIs using Sequelize ORM

Olaoluwa Ajibade's photo
Olaoluwa Ajibade
·

10 min read

Table of contents

Sequelize is a promise-based Node.js Object-Relational Mapping (ORM) tool. An ORM is a library that simplifies SQL database management by making query execution and database setup as simple as possible. Sequlize supports PostgreSQL, MySQL, and SQL Server dialects.

This tutorial will demonstrate how to build a Node.js Restful CRUD API using Express, Sequelize, and a PostgreSQL database.

Prerequisites

Before diving into this article, readers should have the following.

Setting Up Node.js Application

Let's get our project started:

Create an empty folder named movie-app in the system file folder. We will initialize Node.js into this application by typing the following command in our project's root directory terminal:

npm init -y

After that, run the command below to install all of our application's dependencies.

npm install express sequelize pg pg-hstore dotenv --save

Install Sequelize-CLI globally

npm install -g sequelize-cli

In the above command we installed:

  • pg: this is a Node.js nonblocking PostgreSQL client. pg provides a collection of Node.js modules responsible for creating applications database connection.

  • pg-hstore: this implements the hstore datatype for storing sets of key-value pairs within a single PostgreSQL value.

  • sequelize: A Node.js ORM tool.

  • express: minimal and flexible Node.js web application framework.

  • sequelize-cli: Using the terminal in Windows, MacOS, or Linux. sequelize-cli can be used to create a database and generate models, migrations, and seed files quickly. We installed the CLI tool globally because it is used from the command line.

  • dotenv: automatically loads environment variables from .env file into the process.env object.

Next, we will run the command below in our terminal. This will create two new files:

  • The index.js file: serve as our application server entry point and

  • .env: file holds all of our application environment variables.

touch .env server.js

Set up a simple Node.js application server. By Pasting the following code in the server.js file.

const express = require('express');
const app = express();
const port = process.env.PORT || 30000
app.use(express.json());
app.use(express.urlencoded({
  extended: false
}));

// Endpoints
app.get("/", (req, res) => {
    res.send("Movie Application API");
});

app.listen(3000, (req, res) => {
  console.log(`Server started at: http://localhost:${port}`);
})

To start our server, run the following command in the terminal

node server.js

Once we run the above command, we will have the following output:

Server started at: http://localhost:30000

Hit the route http://localhost:3000 to test the new server endpoint.

Hence our Server is up and running.

Next, we will initialize and generate Sequelize required files into our application, by running the following command in the terminal:

sequelize init

The command above provides structure for our application, you will notice some newly created directories and files in the application. This article will primarily focus on the database config and models directory, while subsequent articles will go into more detail about how to use migrations and seeders in Sequelize.

Setting Sequelize Database Configuration

To begin using Sequelize in our application, we will need to configure our database connection. It should be noted that Sequelize CLI assumes MySQL dialect by default.

Before we can connect our application to Sequelize, we must first define the database credentials, which include the database username, password, dialect, host, and database name. All of this will be saved as environmental variables in the .env file we previously created.

In .env file and add the following code:

DB_USERNAME=postgres
DB_PASSWORD= #yourDatabasePassword
DB_DATABASE=movie_crudapp
DB_HOST=127.0.0.1
DB_DIALECT=postgres

Ensure you insert #yourDatabasePassword and manually create a movie_crudapp database in the Postgres DB.

We are setting the database dialect to postgres because we are using a Postgres DB and the host is 127.0.0.1 (localhost) by default.

Next, navigate to the config.json file in the config directory and rename it to config.js. Here we are changing the JSON file to a javascript file to enable us to use stored and secret credentials, instead of exposing them to the public.

This keeps our credentials hidden while loading the config file

Replace the code in the config/config.js file with this:

require("dotenv").config();

module.exports = {
 development: {
    username: process.env.DB_USERNAME,
    password: process.env.DB_PASSWORD,
    database: process.env.DB_DATABASE,
    host: process.env.DB_HOST,
    dialect: process.env.DB_DIALECT,
 },
 test: {
    username: "root",
    password: null,
    database: "database_test",
    host: "127.0.0.1",
    dialect: "postgres",
 },
 production: {
    username: "root",
    password: null,
    database: "database_production",
    host: "127.0.0.1",
    dialect: "postgres",
 },
};

The above file contains all the credentials required to connect to our database.

In the code above we started by importing dotenv library. This library allows us to use the credentials stored in the .env file. These variables are assigned to the development environment of our application.

Next, navigate to index.js file in the models directory and replace the following line:

const config = require(__dirname + '/../config/config.json')[env];

with:

const config = require(__dirname + '/../config/config.js')[env];

This file index.js contains a Sequelize instance, which is used to establish a connection with our database using our stored credentials from the config file.

The index.js file also connects all models created in the models directory automatically to our database using the fs module.

Next, to explore Sequelize models we will define a Movie Model for our application.

Creating Sequelize Model definition

Here we are creating a Movie model, this is the blueprint of how our Movie data will be saved in the database.

To define a Movie model. Create a new Movie.js file in the models directory.

Copy the code below and paste it Into the Movie.js file.

module.exports = (sequelize, DataTypes) => {
const  Movie =  sequelize.define("Movie", {
id: {
type: DataTypes.UUID,
defaultValue: new  DataTypes.UUIDV4(),
unique: true,
primaryKey: true,
},
title: {
type: DataTypes.STRING,
},
director: {
type: DataTypes.STRING,
},
rating: {
type: DataTypes.INTEGER,
},
});
return  Movie;
};

A Model represents a table in the database. The Sequelize .define() method defines a new model. Here we structure our Movie.js schema using Sequelize Datatypes. Some of the data types supported by Sequelize are STRING, INTEGER, FLOAT, and a lot more.

Syncs Sequelize Model To Database

Next, we will need to synchronize our model with our database tables. we can do this using the Sequelize instance method sync().

The synchronization happens inside our database. The sync() method will create a table if one doesn't currently exist or update an existing one. It is used to keep Sequelize model in sync with database tables.

Update the code in the server.js file with the code below:

const  express = require("express");
const  app = express();

app.use(express.json());
app.use(express.urlencoded({extended: false}));

const  db = require("./models/index");
// Sync database
db.sequelize
.sync()
.then(() => {
console.log("Database Synced.");
})
.catch(err  => {
console.log("Failed to sync db:  " +  err.message);
});

// Endpoints
app.get("/", (req, res) => {
    res.send("Movie Application API");
});

// Create Movie
// Read All Movies
// Read One Movie
// Update Movie
// Delete Movie

// Server Listening On PORT: 3000
app.listen(3000, (req, res) => {
console.log("Server Started");
});

The sync() method also has options for altering or dropping a table before it is created. More information on the .sync method can be found here.

We can now begin synchronizing our application with the database, enter the following command in the terminal:

node server.js

Our server is up and running. We should be able to see all queries Sequelize sent to our database on the terminal log.

Let's get started creating our Movie CRUD APIs now that our database and model are up and running.

N:B: All CRUD API for our Movie Application will be created in the server.js file.

Creating CRUD Rest APIs in Sequelize

We'll implement a basic CRUD operation that can accept and query data from our database using tools like postman or a frontend client.

C - Creating Movie Data

Using the Sequelize model .create() method, we can insert movie data into our database Movie table.

add the following code to the server.js file:

// Create Movie
app.post("/movies", async (req, res) => {
    const  data =  req.body;
    try {
        const  movie = await  db.Movie.create(data);
        res.status(200).send(movie);
    } catch (err) {
        res.status(500).send({
        message: err.message
        });
    }
});
// Read Movie
// Update Movie
// Delete Movie

In the above code, the data passed from the request body will be mapped and saved to the corresponding database table attributes.

Test:

Hit http://localhost:3000/movies as a POST request with the following request body on postman.

create moive api

You change the movie details above to include movies of your choice.

A faster way to create more movies is to replace the db.Movie.create(data) method in the code block above with db.Movie.bulkCreate(data). The .bulkCreate method creates more than one movie at a time.

Using .bulkCreate() method, we can send an array of movies data in the request body like this:

[
 {
    "title":  "Bones and All",
    "director":  "Luca Guadagnino",
    "rating":  8
 },
 {
    "title":  "Boiling Point",
    "director":  "Philip Barantini",
    "rating":  8
 },
 {
    "title":  "All Quiet on the Western Front",
    "director":  "Edward Berger",
    "rating":  8
 }
]

Above we're sending multiple movies, this will all be stored in the database at the same time.

R- Read Movie Data

We can read the whole movie table data from the database using the Sequelize model .findAll() method or read a single movie data using the .findOne() method.

add the following code to the server.js file:

// Read All Movies
app.get("/movies", async (req, res)=>{
    try{
        const  movies = await  db.Movie.findAll();
        res.status(200).send(movies);
    }catch(err) {
        res.status(500).send({
        message: err.message
        });
    }
})

Test: Hit http://localhost:3000/movies as a GET request on Read All Movies.

GetAllMovies

add the following code to the server.js file:

// Read One Movie
app.get("/movies/:id", async (req, res) => {
try {
    const  id =  req.params.id;
    const  movie = await  db.Movie.findOne({
     where: { id },
  });
  if (!movie) {
     res.send({ Movie: "Movie not found" });
    }
res.status(200).send(movie);
} catch (err) {
    res.status(500).send({
    message:  err.message,
 });
}
});
// Update Movie
// Delete Movie

Sequelize provides us with the where option for filtering the query, and an attributes option, to select only some specific movie properties.

Test: Hit http://localhost:3000/movies/:id as a GET request on Read One Movie where :id is the movie id been passed a parameter.

GetOneMovie

U- Update Movie Data

To update our application's movie data, Sequelize provides the .update() method. The Update queries also accept the where option, to specify exclusively the data we want to be updated

add the following code to the server.js file:

// Update Movie
app.put("/movies/:id", async (req, res) => {
    const  id =  req.params.id;
    const  data =  req.body;
    try {
        const  movie = await  db.Movie.update(data, {
        where: { id },
        returning: true,
        });
    res.status(200).send({
            msg: "movie updated"
        });
    }catch(err) {
        res.status(500).send({
        message: err.message
        });
    }
});
// Delete Movie

When updating you also need to provide the new data.

In the postman, we will send a PUT request with the same URL as the get request. For example, to update a movie with the Id 123 we send a PUT to [http://localhost:3000/movies/:123](http://localhost:3000/movies/123) with a request body.

Test: Hit http://localhost:3000/movies/:id as a PUT with updated data in the request body.

UpdateMovie

D- Delete Movie Data

To delete movie data. We'll make use of the Sequelize .destroy() method. The where clause object specifies which items in the database should be deleted.:

add the following code to the server.js file:

// Delete Movie
app.delete("/movies/:id", async (req, res) => {
    const  id =  req.params.id;
    try {
        const  movie = await  db.Movie.destroy({
        where: { id },
        });
        if (movie === 0) {
        res.send("No records were deleted");
        } else {
        res.send(`${movie} number of records were deleted`);
        }
    } catch (err) {
        res.send(err);
    }
});

Test: Hit http://localhost:3000/movies/:id as a DELETE.

DeleteMovie

Conclusion

Today we learned how to create Node.js Rest APIs using Sequelize, Express, and PostgreSQL databases. That's it for this article. If you get stuck, Please feel free to contact me directly in the comments section. I'm happy to answer questions or troubleshoot. Have fun coding!

I'd love to connect with you on Twitter | LinkedIn | GitHub | Portfolio

SequelizeNode.jsJavaScript