Базы данных - MySQL - Sequelize

Sequelize - это ORM модуль для nodejs. Sequelize поддерживает PostgreSQL, MySQL, SQLite и MSSQL диалекты.

Как обычно, сперва установим модуль:

npm i sequelize sqlite

Для подключения MySQL, сперва создадим конфигурационный файл. Содержимое файла config/config.js

var Sequelize = require('sequelize');

var sequelize = new Sequelize('pomnyu','root','2244', {
	host : 'localhost', 
	port : 3306,
	dialect: 'mysql',    
	define: {
	    timestamps: false
	}
});

//var sequelize = new Sequelize();
console.log(typeof sequelize)
//global.sequelize = sequelize;
//module.exports = sequelize;
exports.sequelize = sequelize;

Модель City, использующая sequelize-схемы:

 var config  = require('../config/config');
var Sequelize = require('sequelize');

const City = config.sequelize.define('city', {

  region_id : Sequelize.TEXT,
  name : Sequelize.TEXT

}, {
  tableName: 'geo_city'
});

module.exports = City;

Запросы через модель:

findAll

Model.findAll({
  attributes: ['foo', 'bar']
});
//SELECT foo, bar ...

Model.findAll({
  attributes: ['foo', ['bar', 'baz']]
});

//SELECT foo, bar AS baz ...

Model.findAll({
  attributes: [[sequelize.fn('COUNT', sequelize.col('hats')), 'no_hats']]
});

//SELECT COUNT(hats) AS no_hats ..

// This is a tiresome way of getting the number of hats...
Model.findAll({
  attributes: ['id', 'foo', 'bar', 'baz', 'quz', [sequelize.fn('COUNT', sequelize.col('hats')), 'no_hats']]
});

// This is shorter, and less error prone because it still works if you add / remove attributes
Model.findAll({
  attributes: { include: [[sequelize.fn('COUNT', sequelize.col('hats')), 'no_hats']] }
});

//SELECT id, foo, bar, baz, quz, COUNT(hats) AS no_hats ...

Запросы с использованием Where

const Op = Sequelize.Op;

Post.findAll({
  where: {
    authorId: 2
  }
});
// SELECT * FROM post WHERE authorId = 2

Post.findAll({
  where: {
    authorId: 12,
    status: 'active'
  }
});
// SELECT * FROM post WHERE authorId = 12 AND status = 'active';

Post.findAll({
  where: {
    [Op.or]: [{authorId: 12}, {authorId: 13}]
  }
});
// SELECT * FROM post WHERE authorId = 12 OR authorId = 13;

Post.findAll({
  where: {
    authorId: {
      [Op.or]: [12, 13]
    }
  }
});
// SELECT * FROM post WHERE authorId = 12 OR authorId = 13;

Post.destroy({
  where: {
    status: 'inactive'
  }
});
// DELETE FROM post WHERE status = 'inactive';

Post.update({
  updatedAt: null,
}, {
  where: {
    deletedAt: {
      [Op.ne]: null
    }
  }
});
// UPDATE post SET updatedAt = null WHERE deletedAt NOT NULL;

Post.findAll({
  where: sequelize.where(sequelize.fn('char_length', sequelize.col('status')), 6)
});
// SELECT * FROM post WHERE char_length(status) = 6;

Возможно использование следующих операторов:

const Op = Sequelize.Op

[Op.and]: {a: 5}           // AND (a = 5)
[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.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'

С использованием стрелочных функций:

    City.findAll({}).then(data => {
        console.log(data);
});

Метод findOne() с выводом ошибок:

    City.findOne({where: {id: id}})
        .then(data => {
        console.log(data);
}).catch(err => {
        console.log({'page': 'not found'});
});

Добавление данных с помощью метода save():

    var obj = new City(req.body);
    obj.save().then(rooms => {
        console.log(req.body);
});

Обновление данных:

    City.update(
        {
            page_id: page_id,
            date: date,
            name: name,
            flag: flag,
            uv_show: uv_show,
            description: description,
            picture: picture,

        },
        { where: { id: id } }
    ).then(result => {
        res.json(req.body)

Удаление данных:

Model.destroy({
   where: {
      id: 123 //this will be your id that you want to delete
   }
}).then(function(rowDeleted){ // rowDeleted will return number of rows deleted
  if(rowDeleted === 1){
     console.log('Deleted successfully');
   }
}, function(err){
    console.log(err); 
});