Decoding Sequelize: An In-Depth Exploration of ORMs for Beginner Developers
Introduction:
Object-Relational Mapping (ORM) is a technique that allows developers to work with databases in an object-oriented way. Sequelize is a popular ORM for Node.js that supports multiple databases such as PostgreSQL, MySQL, SQLite, and MSSQL. Sequelize simplifies the database management process and provides developers with an easy-to-use interface. In this article, we will take a deep dive into Sequelize and explore how it can make database management easier for beginner developers.
Let's Assume You Know About These Already
- What is ORM?
- Why do we need ORM?
- Advantages of using ORM over raw SQL
- Drawbacks of using ORM
Install Sequelize:
Use npm to install the sequelize package in your Node.js project.
npm install sequelize
Set up a connection:
Create a Sequelize instance and pass in the connection details for the database you want to use.
const Sequelize = require('sequelize');
const sequelize = new Sequelize('database', 'username', 'password', {
host: 'localhost',
dialect: 'mysql',
});
Define models:
Define models using the sequelize.define() method. Each model represents a database table and has attributes that define the fields in the table.
const User = sequelize.define('user', {
firstName: {
type: Sequelize.STRING,
},
lastName: {
type: Sequelize.STRING,
},
});
Sync models with database:
Call the sync() method on your Sequelize instance to create the database tables based on your model definitions.
sequelize
.sync()
.then(() => {
console.log('Database tables created successfully');
})
.catch((err) => {
console.error('Error creating database tables: ', err);
});
Query database:
Use Sequelize methods such as findAll(), findOne(), create(), update() and destroy() to query the database.
User.findAll()
.then((users) => {
console.log('All users: ', JSON.stringify(users));
})
.catch((err) => {
console.error('Error retrieving users: ', err);
});
Associations:
Define associations between models using methods such as belongsTo(), hasMany(), and belongsToMany().
const Post = sequelize.define('post', {
title: {
type: Sequelize.STRING,
},
content: {
type: Sequelize.TEXT,
},
});
User.hasMany(Post);
Post.belongsTo(User);
Migrations:
Use Sequelize migrations to version control changes to the database schema.
module.exports = {
up: (queryInterface, Sequelize) => {
return queryInterface.addColumn('users', 'email', {
type: Sequelize.STRING,
allowNull: false,
});
},
down: (queryInterface, Sequelize) => {
return queryInterface.removeColumn('users', 'email');
},
};
Transactions:
Use transactions to ensure data consistency when making multiple changes to the database.
sequelize
.transaction((t) => {
return User.update(
{ firstName: 'John' },
{
where: {
id: 1,
},
},
{ transaction: t },
).then(() => {
return User.destroy(
{
where: {
id: 2,
},
},
{ transaction: t },
);
});
})
.then(() => {
console.log('Changes saved successfully');
})
.catch((err) => {
console.error('Error saving changes: ', err);
});
Hooks:
Use hooks to run code before or after certain events, such as creating or updating a model.
const User = sequelize.define(
'user',
{
firstName: {
type: Sequelize.STRING,
allowNull: false,
validate: {
notEmpty: true,
},
},
lastName: {
type: Sequelize.STRING,
allowNull: false,
validate: {
notEmpty: true,
},
},
},
{
hooks: {
beforeCreate: (user, options) => {
user.firstName = user.firstName.toUpperCase();
user.lastName = user.lastName.toUpperCase();
},
},
},
);
By following these steps, you can use Sequelize to simplify database management in your Node.js project.
Some more examples
const { Op } = require('sequelize');
const User = require('./models/user');
// Find all users with the first name "John"
const johns = await User.findAll({
where: {
firstName: 'John',
},
});
// Find all users with the last name "Doe"
const does = await User.findAll({
where: {
lastName: 'Doe',
},
});
// Find all users with the email containing the word "example"
const examples = await User.findAll({
where: {
email: {
[Op.like]: '%example%',
},
},
});
// Find all users with the first name "John" or the last name "Doe"
const johnsAndDoes = await User.findAll({
where: {
[Op.or]: [{ firstName: 'John' }, { lastName: 'Doe' }],
},
});
// Update the email of a user with the id 1
const userToUpdate = await User.findByPk(1);
userToUpdate.email = 'new-email@example.com';
await userToUpdate.save();
// Create a new user
const newUser = await User.create({
firstName: 'Jane',
lastName: 'Doe',
email: 'jane-doe@example.com',
});
// Delete a user with the id 2
await User.destroy({
where: {
id: 2,
},
});
// Find a user with the id 3
const user = await User.findByPk(3);
More Examples Using Associations
const User = require('./models/user');
const Post = require('./models/post');
// Find all users with at least one post
const usersWithPosts = await User.findAll({
include: {
model: Post,
},
});
// Find all users without any posts
const usersWithoutPosts = await User.findAll({
where: {
id: {
[Sequelize.Op.notIn]: Sequelize.literal('(SELECT DISTINCT "UserId" FROM "Posts")'),
},
},
});
// Find all posts that contain the word "example" in their title
const postsWithExampleTitle = await Post.findAll({
where: {
title: {
[Sequelize.Op.like]: '%example%',
},
},
});
// Find all posts that don't contain the word "example" in their title
const postsWithoutExampleTitle = await Post.findAll({
where: {
title: {
[Sequelize.Op.notLike]: '%example%',
},
},
});
// Find all posts by a user with the id 1, including the user's information
const postsByUser = await Post.findAll({
where: {
UserId: 1,
},
include: {
model: User,
},
});
// Find all users and their posts, sorted by the number of posts in descending order
const usersWithPostCount = await User.findAll({
include: {
model: Post,
attributes: [[Sequelize.fn('COUNT', Sequelize.col('Posts.id')), 'postCount']],
},
group: ['User.id'],
order: [[Sequelize.literal('postCount'), 'DESC']],
});
More example on Models
const { DataTypes } = require('sequelize');
const sequelize = require('../config/database');
const User = sequelize.define(
'User',
{
id: {
type: DataTypes.INTEGER,
primaryKey: true,
autoIncrement: true,
allowNull: false,
},
email: {
type: DataTypes.STRING,
unique: true,
allowNull: false,
validate: {
isEmail: true,
},
},
password: {
type: DataTypes.STRING,
allowNull: false,
validate: {
len: [6, 50],
},
},
firstName: {
type: DataTypes.STRING,
allowNull: false,
},
lastName: {
type: DataTypes.STRING,
allowNull: false,
},
bio: {
type: DataTypes.TEXT,
},
},
{
timestamps: true,
paranoid: true,
underscored: true,
indexes: [
{
name: 'email_index',
unique: true,
fields: ['email'],
},
{
name: 'full_name_index',
fields: ['firstName', 'lastName'],
},
],
},
);