DEV Community

Cover image for How to use Sequelize ORM in Node Js App
Raj Jeswal
Raj Jeswal

Posted on • Updated on

How to use Sequelize ORM in Node Js App

Sequelize, the very popular and stable promise-based Node.js ORM that has GitHub 25.36k stars, is a mapping tool, or object relational mapper, for sql databases such as Postgres, MySQL, MariaDB, SQLite and Microsoft SQL Server to convert SQL statements to javascript objects. It has strong support for transactions, relationships, lazy and anxious loading, read replication, and more.

Current version is Sequelize v6 supports Node v10 and above.

Prerequisites

This tutorial assumes that you have the following:

  • Basic knowledge of Node.js
  • Node installed on your machine
  • Any code or text editor of your choice
  • Atleast one database like mysql, mariadb, postgresql or sqlite3 installed locally

Sequelize is available via npm. You have to install sequelize as a dependency in your project.

npm install --save sequelize 
Enter fullscreen mode Exit fullscreen mode

You'll also have to install One of the following driver manually for your database of choice:

npm install --save pg pg-hstore (for Postgres)
npm install --save mysql2 (for mysql)
npm install --save mariadb (for mariadb)
npm install --save sqlite3 (for sqlite3)
npm install --save tedious (for Microsoft SQL Server)

Connecting to a database

To connect to the database, you must create a Sequelize instance.

for this, create a new file like sequelize-connection.js.

sequelize-connection.js

const { Sequelize } = require('sequelize');
Enter fullscreen mode Exit fullscreen mode

Connecting to a database

Passing parameters separately (other dialects)

const sequelize = new Sequelize('database', 'username', 'password', {
  host: 'localhost',
  dialect: 'mysql' || 'mariadb' || 'postgres' || 'mssql'
});

module.exports = sequelize;
Enter fullscreen mode Exit fullscreen mode

Testing the connection

const { sequelize } = require('./models');
Enter fullscreen mode Exit fullscreen mode

You can use the .authenticate() function to test if the connection is OK:

try {
    await sequelize.authenticate();
    // await sequelize.sync({ force: true, logging: console.log });
    console.log(`Server started on http://localhost:${port}`);
    console.log('Database connection has been established successfully.');
  } catch (error) {
    console.error('Unable to connect to the database:', error);
}
Enter fullscreen mode Exit fullscreen mode

Closing the connection

Sequelize will keep the connection open by default, and use the same connection for all queries. If you need to close the connection, call sequelize.close() (which is asynchronous and returns a Promise).

To create a model

const { DataTypes } = require('sequelize');
const sequelize = require('./index');

const User = sequelize.define('users', {
  firstname: {
    type: DataTypes.STRING,
    allowNull: false,
    validate: {
      notNull: { msg: 'User must have a firstname' },
      notEmpty: { msg: 'firstname must not be empty' },
    },
  },
  lastname: {
    type: DataTypes.STRING,
    allowNull: false,
    validate: {
      notNull: { msg: 'User must have a lastname' },
      notEmpty: { msg: 'lastname must not be empty' },
    },
  },
  email: {
    type: DataTypes.STRING,
    allowNull: false,
    validate: {
      notNull: { msg: 'User must have a email' },
      notEmpty: { msg: 'Email must not be empty' },
      isEmail: { msg: 'Must be a valid email' },
    },
  },
});
Enter fullscreen mode Exit fullscreen mode

Now all set to write a simple Node.js / Express.js application that uses Sequelize to perform CRUD operations and preserve the data in the database.

Add new User

app.post('/users', async (req, res) => {
  try {
    let { firstname, lastname, email } = req.body;

    firstname = firstname.toLowerCase().trim();
    lastname = lastname.toLowerCase().trim();
    email = email.toLowerCase().trim();

    const user = await User.create({ firstname, lastname, email });

    return res.status(201).json({ status: true, data: user });
  } catch (error) {
    res.status(500).json({
      status: false,
      errors: Object.values(error.errors).map((el) => el.message),
    });
  }
});
Enter fullscreen mode Exit fullscreen mode

List all Users

app.get('/users', async (req, res) => {
  try {
    const user = await User.findAll();
    return res.status(200).json({ status: true, data: user });
  } catch (error) {
    res.status(500).json({
      status: false,
      errors: Object.values(error.errors).map((el) => el.message),
    });
  }
});
Enter fullscreen mode Exit fullscreen mode

Search a single User

app.get('/users/:id', async (req, res) => {
  try {
    const user = await User.findAll({ where: { id: req.params.id } });
    return res.status(200).json({ status: true, data: user });
  } catch (error) {
    res.status(500).json({
      status: false,
      errors: Object.values(error.errors).map((el) => el.message),
    });
  }
});
Enter fullscreen mode Exit fullscreen mode

Update an User

app.put('/users/:id', async (req, res) => {
  try {
    let { firstname, lastname, email } = req.body;

    firstname = firstname.toLowerCase().trim();
    lastname = lastname.toLowerCase().trim();
    email = email.toLowerCase().trim();

    const id = parseInt(req.params.id, 10);
    if (Number.isNaN(id)) return res.status(400).end();

    const isUserExist = await User.findOne({ where: { id } });

    if (!isUserExist)
      return res.status(404).json({ status: false, error: 'No User' });

    const user = await User.findByPk(id);

    user.firstname = firstname ? firstname : user.firstname;
    user.lastname = lastname ? lastname : user.lastname;
    user.email = email ? email : user.email;

    const updatedUser = await user.save();

    return res.status(200).json({ status: true, data: updatedUser });
  } catch (error) {
    res.status(500).json({
      status: false,
      errors: error,
    });
  }
});
Enter fullscreen mode Exit fullscreen mode

Delete an User

app.delete('/users/:id', async (req, res) => {
  try {
    const id = parseInt(req.params.id, 10);
    if (Number.isNaN(id)) return res.status(400).end();

    const isUserExist = await User.findOne({ where: { id } });

    if (!isUserExist)
      return res.status(404).json({ status: false, error: 'No User' });

    const user = await User.findByPk(id);

    await user.destroy();
    return res
      .status(200)
      .json({ status: true, msg: 'User deleted successfully!' });
  } catch (error) {
    console.log(error);
    res.status(500).json({
      status: false,
      errors: error,
    });
  }
});
Enter fullscreen mode Exit fullscreen mode

Conclusion

We learned, how to configured Sequelize.js in our next Node.js project.

Feel free to check the code on GitHub Repository, if you had any trouble following this tutorial.

If you have any questions or comments about this article, please do not hesitate to reach out.

Thank you for reading.

Credits

Node.js, is a JavaScript runtime built on Chrome's V8 JavaScript engine: https://nodejs.org/en/

Express.js, Fast, unopinionated, minimalist web framework for Node.js: https://expressjs.com/

Sequelize, promise-based Node.js ORM: https://sequelize.org/

Discussion (0)