Node.js MySQL with Sequelize
Section (2.10) – Node.js MySQL with Sequelize
Sequelize is a powerful Object-Relational Mapper (ORM) for Node.js that allows you to interact with your MySQL database using JavaScript objects and methods. This tutorial will cover the following topics related to Node.js MySQL with Sequelize:
- What is Sequelize?
- Installing and configuring Sequelize
- Defining models and associations
- CRUD operations with Sequelize
- Validating data
1. What is Sequelize?
Sequelize is a popular ORM for Node.js that supports various databases like MySQL, PostgreSQL, SQLite, and MSSQL. It provides an abstraction layer for working with databases, allowing developers to perform CRUD operations using JavaScript objects and methods instead of writing raw SQL queries.
With Sequelize, you can define models representing your database tables, set up associations between them, perform complex queries, and validate data before it gets persisted to the database.
2. Installing and configuring Sequelize
2.1. Installing Sequelize and dependencies
First, install Sequelize and its dependencies:
npm install sequelize sequelize-cli mysql2
2.2. Initializing Sequelize
Initialize Sequelize by creating a configuration file:
npx sequelize-cli init
This command will create a folder structure containing configuration files, models, migrations, and seeders. Update the config/config.json file with your database connection details:
{
"development": {
"username": "your_username",
"password": "your_password",
"database": "your_database",
"host": "localhost",
"dialect": "mysql"
}
}
3. Defining models and associations
3.1. Creating a model
To create a model, use the sequelize-cli command:
npx sequelize-cli model:generate --name User --attributes name:string,email:string,password:string
This will create a User model file in the models directory. Open the file and review the generated code.
module.exports = (sequelize, DataTypes) => {
const User = sequelize.define('User', {
name: DataTypes.STRING,
email: DataTypes.STRING,
password: DataTypes.STRING
}, {});
User.associate = function(models) {
// associations can be defined here
};
return User;
};
3.2. Defining associations
Sequelize supports various types of associations, such as hasOne, hasMany, belongsTo, and belongsToMany. To define an association, update the associate function in the relevant models.
For example, if we have a Post model and want to associate it with the User model, we can define a one-to-many relationship like this:
// models/user.js
User.associate = function(models) {
User.hasMany(models.Post);
};
// models/post.js
Post.associate = function(models) {
Post.belongsTo(models.User);
};
4. CRUD operations with Sequelize
Performing CRUD operations with Sequelize is easy and intuitive. We'll go through each operation and provide examples.
4.1. Create
To create a new record, use the create method on the model. This method takes an object containing the values for the new record.
const { User } = require('./models');
const newUser = {
name: 'John Doe',
email: 'john@example.com',
password: 'password123',
};
User.create(newUser)
.then(user => {
console.log(`User created: ${user.name}`);
})
.catch(error => {
console.error('Error creating user:', error);
});
4.2. Read
Sequelize provides several methods for querying and retrieving data from the database. Some of the most common methods are:
findAll: retrieves all records that match the specified criteriafindOne: retrieves a single record that matches the specified criteriafindByPk: retrieves a single record by primary key
4.2.1. Find all records
User.findAll()
.then(users => {
console.log('All users:', users);
})
.catch(error => {
console.error('Error fetching users:', error);
});
4.2.2. Find one record
User.findOne({
where: {
email: 'john@example.com',
},
})
.then(user => {
console.log('Found user:', user);
})
.catch(error => {
console.error('Error fetching user:', error);
});
4.2.3. Find by primary key
User.findByPk(1)
.then(user => {
console.log('User with primary key 1:', user);
})
.catch(error => {
console.error('Error fetching user:', error);
});
4.3. Update
To update records, use the update method on the model. This method takes an object containing the new values and an options object that specifies the records to update.
User.update(
{ email: 'john.doe@example.com' },
{
where: {
email: 'john@example.com',
},
}
)
.then(rowsAffected => {
console.log(`Updated ${rowsAffected} rows`);
})
.catch(error => {
console.error('Error updating user:', error);
});
4.4. Delete
To delete records, use the destroy method on the model. This method takes an options object that specifies the records to delete.
User.destroy({
where: {
email: 'john.doe@example.com',
},
})
.then(rowsAffected => {
console.log(`Deleted ${rowsAffected} rows`);
})
.catch(error => {
console.error('Error deleting user:', error);
});
5. Validating data
Sequelize provides built-in validation features that allow you to ensure data consistency before it gets persisted to the database. You can define validation rules on your model fields, and Sequelize will automatically validate the data when performing CRUD operations.
5.1. Defining validation rules
To define validation rules, add a validate property to the respective field in your model definition. Here's an example with the User model:
module.exports = (sequelize, DataTypes) => {
const User = sequelize.define('User', {
name: {
type: DataTypes.STRING,
allowNull: false,
validate: {
notEmpty: {
msg: 'Name cannot be empty',
},
len: {
args: [2, 50],
msg: 'Name must be between 2 and 50 characters',
},
},
},
email: {
type: DataTypes.STRING,
allowNull: false,
unique: true,
validate: {
isEmail: {
msg: 'Email must be valid',
},
},
},
password: {
type: DataTypes.STRING,
allowNull: false,
validate: {
len: {
args: [6, 100],
msg: 'Password must be between 6 and 100 characters',
},
},
},
});
User.associate = function(models) {
// associations can be defined here
};
return User;
};
In this example, we've defined validation rules for name, email, and password fields. Sequelize will automatically apply these rules when creating or updating records.
5.2. Handling validation errors
When validation fails, Sequelize will throw a Sequelize.ValidationError. You can catch this error and handle it as appropriate for your application.
Here's an example of handling validation errors when creating a new user:
User.create(newUser)
.then(user => {
console.log(`User created: ${user.name}`);
})
.catch(error => {
if (error instanceof Sequelize.ValidationError) {
console.error('Validation error:', error.errors);
} else {
console.error('Error creating user:', error);
}
});
FAQs
Q1: What are the benefits of using Sequelize over raw SQL queries?
A1: Sequelize provides an abstraction layer that allows developers to work with databases using JavaScript objects and methods, making it easier to write, read, and maintain code. It also provides built-in features like model validation, associations, and transactions that can help ensure data consistency and improve development productivity.
Q2: Can I use Sequelize with other databases besides MySQL?
A2: Yes, Sequelize supports various databases, including PostgreSQL, SQLite, and MSSQL. The process for using Sequelize with these databases is very similar to the one described in this tutorial.
Q3: How do I handle transactions with Sequelize?
A3: Sequelize provides support for transactions, allowing you to group multiple operations into a single atomic unit. To use transactions, first create a new transaction using the sequelize.transaction() method, and then pass the transaction object to each operation that should be part of the transaction. Finally, commit or rollback the transaction as appropriate.
