Skip to main content

Building RESTful API with Express, Sequelize, and PostgreSQL

· 5 min read
Huseyin BABAL
Huseyin BABAL
Software Developer

Introduction​

RESTful APIs are essential for enabling smooth communication between client applications and databases, allowing you to perform actions like creating, reading, updating, and deleting records. Using Node.js and Express for API development offers flexibility and scalability. Sequelize, an ORM, simplifies database interactions and also provides migration tools to ensure your PostgreSQL schema stays in sync with the API’s needs as the database evolves.

What is Sequelize?​

Sequelize is a Node.js ORM that simplifies working with SQL databases like PostgreSQL. It supports migrations, making schema changes manageable, and integrates well with RESTful APIs.

Persistence Layer​

In this article, we will be using PostgreSQL as our database. You can maintain your database in any database management system. For a convenient deployment option, consider cloud-based solutions like Rapidapp, which offers managed PostgreSQL databases, simplifying setup and maintenance.

tip

Create a free database in Rapidapp in seconds here

Step-by-Step Guide: Sequelize Migrations and REST API​

Project Setup and Dependencies​

To start, create a Node.js project and install the required packages:

mkdir node-movies-api
cd node-movies-api
npm init -y
npm install express sequelize pg pg-hstore sequelize-cli body-parser --save

Initialize Sequelize with:

npx sequelize-cli init

Now that we initialized the project, go to the folder node-movies-api and open it with your favourite IDE.

Configuring the Database​

Edit config/config.json to include your database connection settings:

config/config.json
{
  "development": {
    "username": "your_db_user",
    "password": "your_db_password",
    "database": "database_name",
    "host": "pg_host",
    "port": "port",
    "dialect": "postgres",
    "dialectOptions": {
      "ssl": {
        "require": true,
        "rejectUnauthorized": false

      }
    }
  }
}

Creating a Migration for Movies​

Generate a migration file for the Movies table:

npx sequelize-cli migration:generate --name create-movies-table

Edit the generated migration file in migrations folder:

module.exports = {
  up: async (queryInterface, Sequelize) => {
    await queryInterface.createTable('Movies', {
      id: {
        type: Sequelize.INTEGER,
        autoIncrement: true,
        primaryKey: true,
      },
      title: {
        type: Sequelize.STRING,
        allowNull: false,
      },
      genre: {
        type: Sequelize.STRING,
        allowNull: false,
      },
      releaseDate: {
        type: Sequelize.DATE,
        allowNull: false,
      },
      rating: {
        type: Sequelize.FLOAT,
      },
      createdAt: Sequelize.DATE,
      updatedAt: Sequelize.DATE,
    });
  },
  down: async (queryInterface) => {
    await queryInterface.dropTable('Movies');
  }
};

As you can see, we have up and down functions in the migration file. The up function is used to create the table, and the down function is used to drop the table.

Run the migration to create the table:

npx sequelize-cli db:migrate

Defining the Movie Model​

Create a models/movie.js file to define the Movie model:

module.exports = (sequelize, DataTypes) => {
  const Movie = sequelize.define('Movie', {
    title: {
      type: DataTypes.STRING,
      allowNull: false,
    },
    genre: {
      type: DataTypes.STRING,
      allowNull: false,
    },
    releaseDate: {
      type: DataTypes.DATE,
      allowNull: false,
    },
    rating: {
      type: DataTypes.FLOAT,
    },
  });
  return Movie;
};

Setting Up Express and Routes​

Create an index.js file to set up the Express server:

const express = require('express');
const bodyParser = require('body-parser');
const { Sequelize } = require('sequelize');
const db = require('./models');

const app = express();
const PORT = process.env.PORT || 3000;

app.use(bodyParser.json());

db.sequelize.sync().then(() => {
  console.log('Database connected');
});

// Define routes here

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

Implementing the /movies Endpoint​

Add a routes/movies.js file to define the CRUD operations for the /movies endpoint:

const express = require('express');
const router = express.Router();
const { Movie } = require('../models');

// Create a new movie
router.post('/', async (req, res) => {
  try {
    const movie = await Movie.create(req.body);
    res.status(201).json(movie);
  } catch (error) {
    res.status(400).json({ error: error.message });
  }
});

// Get all movies
router.get('/', async (req, res) => {
  try {
    const movies = await Movie.findAll();
    res.json(movies);
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

// Get a movie by ID
router.get('/:id', async (req, res) => {
  try {
    const movie = await Movie.findByPk(req.params.id);
    if (movie) {
      res.json(movie);
    } else {
      res.status(404).json({ error: 'Movie not found' });
    }
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

// Update a movie
router.put('/:id', async (req, res) => {
  try {
    const movie = await Movie.findByPk(req.params.id);
    if (movie) {
      await movie.update(req.body);
      res.json(movie);
    } else {
      res.status(404).json({ error: 'Movie not found' });
    }
  } catch (error) {
    res.status(400).json({ error: error.message });
  }
});

// Delete a movie
router.delete('/:id', async (req, res) => {
  try {
    const movie = await Movie.findByPk(req.params.id);
    if (movie) {
      await movie.destroy();
      res.status(204).end();
    } else {
      res.status(404).json({ error: 'Movie not found' });
    }
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

module.exports = router;

In index.js, include this new route:

const movieRoutes = require('./routes/movies');
app.use('/movies', movieRoutes);

Now you can run the application with:

node index.js

Demo​

Create a new movie​

curl -X POST http://localhost:3000/movies \
-H "Content-Type: application/json" \
-d '{
  "title": "Inception",
  "genre": "Sci-Fi",
  "releaseDate": "2010-07-16",
  "rating": 8.8
}'

Retrieve all movies​

curl -X GET http://localhost:3000/movies

Get a movie by ID​

curl -X GET http://localhost:3000/movies/1

Update a movie​

curl -X PUT http://localhost:3000/movies/1 \
-H "Content-Type: application/json" \
-d '{
  "rating": 9.0
}'

Delete a movie​

curl -X DELETE http://localhost:3000/movies/1

Best Practices for REST API with Sequelize​

  • Validation: Use Sequelize’s validation features for better data integrity.
  • Error Handling: Include error messages to make debugging easier.
  • Environment Configuration: : Use environment variables for sensitive data like database credentials.

Conclusion​

Integrating Sequelize migrations with a RESTful API allows for powerful database management and user interaction. This guide provides a blueprint for creating and maintaining a Node.js API that interacts seamlessly with a PostgreSQL database.

tip

You can find the complete source code for this project on GitHub.