How to use Sequelize ORM in Node Js App
Tags: node, express, sequelize
Published: 2021-12-19 08:30:52

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 
```

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');
```
### 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;
```

### Testing the connection

```
const { sequelize } = require('./models');
```
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);
}
```

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

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

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

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

### Conclusion

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

Feel free to check the code on *[GitHub Repository](https://github.com/workforweb/Express-Sequelize-Starter.git)*, 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/