Learn Sequelize in 7 mins: Part 1
Learn Sequelize in 7 mins: Part 1

Learn Sequelize in 7 mins: Part 1

Spread the love

ORM is “Object-relational mapping” is a programming technique for converting data between incompatible type systems using object-oriented programming languages. Managing databases becomes easier using ORM. They automatically map out the objects (entities) from our code in a relational database, as the name implies.

Developers can easily interact with data and perform common operations like create, read, update & delete using native classes instead of writing queries. Since the queries are generated by sequelize, we as developers have a layer of abstraction over the DB and we can technically swap the entire DB with minimal code changes later in the lifecycle of a project. Sequelize is one of the most popular node.js ORM among mongoose, Prisma, TypeORM, and much more. Sequelize is vastly more feature-rich than waterline which is the default ORM for Sails.JS.

“Sequelize is a promise-based Node.js ORM for Postgres, MySQL, MariaDB, SQLite, and Microsoft SQL Server. It features solid transaction support, relations, eager and lazy loading, read replication, and more.”

https://sequelize.org/

In today’s post, we are going to learn how to install sequelize in node.js, what are models, what are model instances & how to query them. We are going to use sequelize with the Express node framework. so let’s begin.


Step 1: Install sequelize & sequelize CLI using NPM.

npm install --save sequelize
npm install --save sequelize-cli
npm install --save mysql2

Step 2: Initialize Sequelize with the following command. This command will create basic config file & database connections.

node_modules/.bin/sequelize init

This will create
a. A config folder with config.json file, which contains database connection parameters for three environments.
b. To migrate the data, the migration folder is used.
c. models folder with index.js file which contains sequelize instance for connection to a database.
d. seeders folder which is contains files with seeders data.

Step 3: Model Definitions.

module.exports = (sequelize, DataTypes) => {
 const User = sequelize.define('User', {
  email: {
   type: DataTypes.STRING,
   primaryKey: true,
   unique: true,
  },
  uuid: {
   type: DataTypes.UUID,
   defaultValue: DataTypes.UUIDV4,
  },
  phoneNo: {
   type: DataTypes.STRING,
  },
  countryCode: {
   type: DataTypes.STRING,
  },
  firstName: {
   type: DataTypes.STRING,
   allowNull: false,
  },
  lastName: {
   type: DataTypes.STRING,
  },
  password: {
   type: DataTypes.STRING,
  },
  age: {
   type: DataTypes.STRING,
  },
  gender: {
   type: DataTypes.STRING,
   validate: {
    isIn: [[
     'FEMALE'
     'MALE',
     'OTHER'
    ]],
   }
  },
 }, {
 indexes: [{
  fields: ['gender'],
 }],
 defaultScope: {
  attributes: {
   exclude: [
    'password',
   ]},
  },
 })
User.associate = function (models) {
 models.User.hasMany(models.VerificationToken, {
  foreignKey: 'email',
  sourceKey: 'email',
 })
 models.User.hasOne(models.UserSubscription, {
  foreignKey: 'email',
  sourceKey: 'email',
 })
}
return User
}

Models in sequelize are nothing but tables in your database. A model tells Sequelize about the entity it represents, such as the name of the table in the database and which columns it has (and their data types). Each instance of the model is a row in the database. Sequelize provides two ways to define models:

  • Calling sequelize.define(modelName, attributes, options)
  • Extending Model and calling init(attributes, options)
module.exports = (sequelize, DataTypes, Model) => {
class User extends Model { }
return User.init({
  firstName: {
   type: DataTypes.STRING,
   allowNull: false
  },
  lastName: {
   type: DataTypes.STRING
  }
 }, {
  sequelize,
  modelName: 'User'
 });
}

Internally sequelize.define calls Model.init method, so both are giving the same result.

Parameters

i. email specifies the name of a column which is an object, that contains the property of each column like

  • A type defines the data type of column. For eg. string, float, boolean, etc. learn more about datatype here.
  • Unique contains a boolean value that specifies whether a column has a unique constraint or not. It will throw an error “SequelizeUniqueConstraintError”, if the duplicate value is inserted
  • The primary key contains a boolean value that specifies whether a column is a primary key or not.
  • The defaultValue contains the default value that is inserted if null is passed while inserting a row.
  • allow null specifies whether a column is allowed to accept the null value or not. If an attempt is made to set null to a field that does not allow null, a “ValidationError” will be thrown without any SQL query being performed.
  • validate is used to validate the column, like is value an email matches email format or isIn validates incoming value must be in one of the specified value. for eg., Gender must be “FEMALE”, “MALE” or “OTHER”. If validation is failed,sequelize will throw an error. Learn more about validation here.

ii. indexes specify the array of fields to be indexed
iii. default scope specifies an object used to help you reuse code. In the above example, we want to exclude password attributes. That means it should not be visible to anyone.
iv. To specify the association between two models, ModelName.associate function is used. You can learn more about the association in the next post.

Step 4: Model Instances

router.get('/create_user', (req, res) => {
 if (!req.body.email) {
  throw new Error('Email is required')
 }
 return models.User.create(req.body).then((user) => {
   res.send(user)
 }).catch((err) => {
  res.status(501).send(err.message)
 })
})

Even though the Model is a class its instance is not created using new(). Instead, sequelize uses build & save method to insert records. Build method creates an instance of model & save method is used to insert record. Sequelize uses ModelName.create a method to insert a record in a table that internally calls build & save method.

const users = await User.bulkCreate([   
 { email: 'Johndoe@example.com' },   
 { email: 'willsmitch@domain.in' } 
]);

Sequelize provides the Model.bulkCreate method to allow creating multiple records at once, with only one query. The usage of Model.bulkCreate is very similar to Model.create, by receiving an array of objects instead of a single object.

// using update method
router.get('/update_user', (req, res) => {
 return models.User.update({
   firstName: 'John',
   age: ' 45 Years'
  },{
   where: {
    email: "testuser@example.com",
   }
 }).then((user) => {
   res.send(user)
 }).catch((err) => {
  res.status(501).send(err.message)
 })
})
// using save method
router.get('/update_user', (req, res) => {
 return models.User.findByPk("testuser@example.com").then(async  (user) => {
   if (!user) {
    throw an Error ('User not found')
   }
   user.firstName = 'John'
   user.age = ' 45 Years'
   await user.save()
   res.send(user)
 }).catch((err) => {
  res.status(501).send(err.message)
 })
})

Sequelize uses the update method to update the instance column values. “where” parameter is compulsory otherwise sequelize will throw an error. You can update the row by first calling either findByPk Or findOne method. This will fetch the model instance & the use instance.save method to update the row.

router.get('/delete_user', (req, res) => {
 return models.User.findByPk("testuser@example.com").then(async  (user) => {
   if (!user) {
    throw an Error ('User not found')
   }
   await user.destroy()
   res.send("user deleted")
 }).catch((err) => {
  res.status(501).send(err.message)
 })
})
router.get('/delete_user', (req, res) => {
  return models.User.destroy({
   where: {
    email: "testuser@example.com",
   }
 }).then(() => {
   res.send("user deleted")
 }).catch((err) => {
  res.status(501).send(err.message)
 })
})

Sequelize uses the destroy method to delete the row from a table. Destroy method also accepts the “where” option, just like the read queries shown above.

Step 5: Model Querying.

router.get('/all_users', (req, res) => {
 if (!req.query.limit) {
  req.query.limit = 10
 }
 if (!req.query.offset) {
  req.query.offset = 10
 }
 return models.User.findAll({
   order: [['createdAt', 'DESC']],
   limit: parseInt(req.query.limit, 10),
   attributes: ['firstName', 'email', 'lastName'],
   offset: parseInt(req.query.offset, 10),
 }).then((users) => {
   res.send(users)
 }).catch((err) => {
  res.status(501).send(err.message)
 })
})

Sequelize provides 4 Finder methods to query the database, which generate “SELECT” queries. By default, the results of all finder methods are instances of the model class. This means that after the database returns the results, Sequelize automatically wraps everything in proper instance objects. In a few cases, when there are too many results, this wrapping can be inefficient. To disable this wrapping and receive a plain response instead, pass{ raw: true }as an option to the finder method.

Finder Methods:

i. FindAll generates a standard SELECT query that will retrieve all entries from the table, it is unless restricted by something like a where clause.
ii . FindByPk method obtains only a single entry from the table, using the provided primary key.
iii. FindOne method obtains the first entry it finds that fulfills the optional query options. Generally, it comes with where clause other than the primary key.
iv. findOrCreate will check if a table contains any record with matching criteria and if not found create an entry in the table. In both cases, it will return an instance (either the found instance or the created instance) and a boolean indicating whether that instance was created or already existed. The where the option is used for finding the record and the defaults option is used to define what must be created in case nothing was found. Sequelize will take the values given to where if the defaults do not contain values for every column.
v. findAndCountAll method is a convenience method that combines findAll and count. This is useful when dealing with queries related to pagination where you want to retrieve data with a limit and offset but also need to know the total number of records that match the query. The findAndCountAll method returns an object with two properties: 1. count – an integer – the total number records matching the query. 2. rows – an array of objects – the obtained records.

Options in Finder methods:

a. An array of column names passed in the attributes option, is useful when a user only wants specific columns rather than all the columns.
b. Specific column along with the conditions passed in Where object. For eg., you want to find all the users in India.

where: {
 country: 'India'
}

c. order contains an array of columns with ‘ASC’ & ‘DESC’ option. For eg., you want a list of users in the descending order of created date.
d. For limitation & pagination, Limit & offset are used.


We will cover advanced topics like sequelize associations, relationships, eager loading, lazy loading, transactions, and much more in upcoming posts.

2 Comments

  1. Pingback: MySQL Transactions with Sequelize Node.js | Noob2Geek

  2. Pingback: Four Sequelize associations you should know | Noob2Geek

Leave a Reply

Your email address will not be published.