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, 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/
Oldest comments (1)
What is Sequelize ORM in node JS? istikhara dream for marriage