NODE SEQUALIZE - MYSQL CRUD
"dependencies": {
"cors": "^2.8.5",
"express": "^4.17.1",
"mysql2": "^2.1.0",
"sequelize": "^6.3.4"
}
RELATIONAL DATABASE MODEL IN SEQUALIZE
EXAMPLE :-
tutorial.model.js
module.exports = (sequelize, DataTypes) => {
const Tutorial = sequelize.define("tutorial", {
title: {
type: DataTypes.STRING
},
description: {
type: DataTypes.STRING
}
});
return Tutorial;
};
comment.model.js
module.exports = (sequelize, DataTypes) => {
const Comment = sequelize.define("comment", {
name: {
type: DataTypes.STRING
},
text: {
type: DataTypes.STRING
}
});
return Comment;
};
index.js
const dbConfig = require("../config/db.config.js");
const Sequelize = require("sequelize");
const sequelize = new Sequelize(dbConfig.DB, dbConfig.USER, dbConfig.PASSWORD, {
host: dbConfig.HOST,
dialect: dbConfig.dialect,
operatorsAliases: false,
pool: {
max: dbConfig.pool.max,
min: dbConfig.pool.min,
acquire: dbConfig.pool.acquire,
idle: dbConfig.pool.idle,
},
});
const db = {};
db.Sequelize = Sequelize;
db.sequelize = sequelize;
db.tutorials = require("./tutorial.model.js")(sequelize, Sequelize);
db.comments = require("./comment.model.js")(sequelize, Sequelize);
db.tutorials.hasMany(db.comments, { as: "comments" });
db.comments.belongsTo(db.tutorials, {
foreignKey: "tutorialId",
as: "tutorial",
});
module.exports = db;
create comment controller
exports.createComment = (tutorialId, comment) => {
return Comment.create({
name: comment.name,
text: comment.text,
tutorialId: tutorialId, //(comes from req.body of router)
})
.then((comment) => {
console.log(">> Created comment: " + JSON.stringify(comment, null, 4));
return comment;
})
.catch((err) => {
console.log(">> Error while creating comment: ", err);
});
};
fetch fomment by tutorial
exports.findTutorialById = (tutorialId) => {
return Tutorial.findByPk(tutorialId, { include: ["comments"] })
.then((tutorial) => {
return tutorial;
})
.catch((err) => {
console.log(">> Error while finding tutorial: ", err);
});
};
fetch the comments for a given comment id
exports.findCommentById = (id) => {
return Comment.findByPk(id, { include: ["tutorial"] })
.then((comment) => {
return comment;
})
.catch((err) => {
console.log(">> Error while finding comment: ", err);
});
};
Get all Tutorials include comments
exports.findAll = () => {
return Tutorial.findAll({
include: ["comments"],
}).then((tutorials) => {
return tutorials;
});
};
Operators
const Op = Sequelize.Op
[Op.and]: [{a: 5}, {b: 6}] // (a = 5) AND (b = 6)
[Op.or]: [{a: 5}, {a: 6}] // (a = 5 OR a = 6)
[Op.gt]: 6, // > 6
[Op.gte]: 6, // >= 6
[Op.lt]: 10, // < 10
[Op.lte]: 10, // <= 10
[Op.ne]: 20, // != 20
[Op.eq]: 3, // = 3
[Op.is]: null // IS NULL
[Op.not]: true, // IS NOT TRUE
[Op.between]: [6, 10], // BETWEEN 6 AND 10
[Op.notBetween]: [11, 15], // NOT BETWEEN 11 AND 15
[Op.in]: [1, 2], // IN [1, 2]
[Op.notIn]: [1, 2], // NOT IN [1, 2]
[Op.like]: '%hat', // LIKE '%hat'
[Op.notLike]: '%hat' // NOT LIKE '%hat'
[Op.iLike]: '%hat' // ILIKE '%hat' (case insensitive) (PG only)
[Op.notILike]: '%hat' // NOT ILIKE '%hat' (PG only)
[Op.startsWith]: 'hat' // LIKE 'hat%'
[Op.endsWith]: 'hat' // LIKE '%hat'
[Op.substring]: 'hat' // LIKE '%hat%'
[Op.regexp]: '^[h|a|t]' // REGEXP/~ '^[h|a|t]' (MySQL/PG only)
[Op.notRegexp]: '^[h|a|t]' // NOT REGEXP/!~ '^[h|a|t]' (MySQL/PG only)
[Op.iRegexp]: '^[h|a|t]' // ~* '^[h|a|t]' (PG only)
[Op.notIRegexp]: '^[h|a|t]' // !~* '^[h|a|t]' (PG only)
[Op.like]: { [Op.any]: ['cat', 'hat']}
// LIKE ANY ARRAY['cat', 'hat'] - also works for iLike and notLike
[Op.overlap]: [1, 2] // && [1, 2] (PG array overlap operator)
[Op.contains]: [1, 2] // @> [1, 2] (PG array contains operator)
[Op.contained]: [1, 2] // <@ [1, 2] (PG array contained by operator)
[Op.any]: [2,3] // ANY ARRAY[2, 3]::INTEGER (PG only)
[Op.col]: 'user.organization_id' // = "user"."organization_id", with dialect specific column identifiers, PG in this example
[Op.gt]: { [Op.all]: literal('SELECT 1') }
// > ALL (SELECT 1)
Ordering
Subtask.findAll({
order: [
// Will escape title and validate DESC against a list of valid direction parameters
['title', 'DESC'],
// Will order by max(age)
sequelize.fn('max', sequelize.col('age')),
// Will order by max(age) DESC
[sequelize.fn('max', sequelize.col('age')), 'DESC'],
// Will order by otherfunction(`col1`, 12, 'lalala') DESC
[sequelize.fn('otherfunction', sequelize.col('col1'), 12, 'lalala'), 'DESC'],
// Will order an associated model's created_at using the model name as the association's name.
[Task, 'createdAt', 'DESC'],
// Will order through an associated model's created_at using the model names as the associations' names.
[Task, Project, 'createdAt', 'DESC'],
// Will order by an associated model's created_at using the name of the association.
['Task', 'createdAt', 'DESC'],
// Will order by a nested associated model's created_at using the names of the associations.
['Task', 'Project', 'createdAt', 'DESC'],
// Will order by an associated model's created_at using an association object. (preferred method)
[Subtask.associations.Task, 'createdAt', 'DESC'],
// Will order by a nested associated model's created_at using association objects. (preferred method)
[Subtask.associations.Task, Task.associations.Project, 'createdAt', 'DESC'],
// Will order by an associated model's created_at using a simple association object.
[{model: Task, as: 'Task'}, 'createdAt', 'DESC'],
// Will order by a nested associated model's created_at simple association objects.
[{model: Task, as: 'Task'}, {model: Project, as: 'Project'}, 'createdAt', 'DESC']
]
// Will order by max age descending
order: sequelize.literal('max(age) DESC')
// Will order by max age ascending assuming ascending is the default order when direction is omitted
order: sequelize.fn('max', sequelize.col('age'))
// Will order by age ascending assuming ascending is the default order when direction is omitted
order: sequelize.col('age')
// Will order randomly based on the dialect (instead of fn('RAND') or fn('RANDOM'))
order: sequelize.random()
})
Relationship
One:One
// foreign key has to be defined on both sides.
Parent.hasOne(Child, {foreignKey: 'Parent_parentId'})
// "Parent_parentId" column will exist in the "belongsTo" table.
Child.belongsTo(Parent, {foreignKey: 'Parent_parentId'})
One:Many
Parent.hasMany(Child, {foreignKey: 'Parent_parentId'})
Child.belongsTo(Parent, {foreignKey: 'Parent_parentId'})
Many:Many
Book.belongsToMany(Article, {foreignKey: Book_id});
Article.belongsToMany(Books, {foreignKey: Article_id});});