Learn Sequelize in 7 Minutes: Part 1
ORM or “Object-relational mapping” is a programming technique for converting data between incompatible type systems using object-oriented programming languages. This means it is designed to make managing databases easier. 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.” — Definition from 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.js framework. so let’s begin.
Step 1: Install Sequelize and Sequelize CLI using NPM.
npm install --save sequelize
npm install --save sequelize-cli
npm install --save mysql2Step 2: Initialize Sequelize with the following command. This command will create basic config file & database connections.
node_modules/.bin/sequelize initThis will create:
a. A config folder with config.json file, which contains database connection parameters for three environments.
b. migration folder which is used to migrate the data.
c. models folder with index.js file which contains the 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.Posts, {
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. Models can be defined in two equivalent ways in Sequelize:
- 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. Let’s understand each parameter:
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 the column. For eg. string, float, boolean, etc. learn more about datatype here.
b. unique contains a boolean value that specifies whether a column has a unique constraint or not. If the duplicate value is inserted sequelize will throw an error “SequelizeUniqueConstraintError”
c. primary key contains a boolean value that specifies whether a column is a primary key or not.
d. defaultValue specifies the default value to be inserted if null is passed while inserting a row.
e. 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.
f. 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. ModelName.associate function is used to specify the association between two models. You can learn more about the association in the next post.
Step 4: Model Instances
router.post('/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 the build & save method to insert records. The build method creates an instance of the model & save method is used to insert the record. Sequelize uses ModelName.create a method to insert a record in a table that internally calls the build and 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.put('/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.put('/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.delete('/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.delete('/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 the 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.
i. FindAll generates a standard SELECT query that will retrieve all entries from the table, it is 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 methods obtain the first entry it finds that fulfills the optional query options. Generally, it comes with a 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 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 where the defaults do not contain values for every column.
v. findAndCountAll method is a convenient 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.
In the above example, we can pass options to find records like
a. attributes contain an array of column names to be written. This is useful when a user only wants specific columns rather than all the columns.
b. where is an object contains a column name and condition to be applied to it. For eg., you want to find all the users in India.
where: {
country: 'India'
}c. order contains an array of columns with the ‘ASC’ & ‘DESC’ options. For eg., you want a list of users in the descending order of created date.
d. Limit & offset is used for limitation and pagination.
We will cover advanced topics like Sequelize associations, relationships, eager loading, lazy loading, transactions, and much more in upcoming posts.
Thank you for reading.
More content at plainenglish.io
