Nodejs Sequelize手册学习快速入门到应用

 // config.js
 exports const config = {
     database: {
         database: 'management',
         host: 'localhost',
         port: 3306,
         user: 'root',
         passWord: '12345678'
     }
 }

 // db.js
 const Sequelize = require('sequelize');
 import { config } from './config';
 const { database, host, port, user, password } = config; 
 const sequelize = new Sequelize(database, user, password, {
     dialect: 'Mysql',
     host,
     port,
     logging: true,        // logging: true, 打印sql到控制台
     timezone: '+08:00',   //时间上的统一,这里是东八区，默认为0时区
     define: {   //全局的定义，会通过连接实例传递
         // timestamps: false,   //默认情况下,Sequelize 使用数据类型 DataTypes.DATE 自动向每个模                                  型添加 createdAt 和 updatedAt 字段. 这些字段会自动进行管理 - 每当                                  你使用Sequelize 创建或更新内容时,这些字段都会被自动设置.                                          createdAt 字段将包含代表创建时刻的时间戳,而 updatedAt 字段将包含                                  最新更新的时间戳.
                                 //对于带有timestamps: false 参数的模型,可以禁用此行为
         // createdAt: 'created_at',  //自定义时间戳
         // updatedAt: 'updated_at',
         // paranoid: true,
         // deletedAt: 'deleted_at',  //paranoid表示在被告之要删除记录时并不会真正的物理上删除，而                                       是添加一个存有删除请求时间戳deletedAt的特殊字段。传递                                             paranoid: true参数给模型定义中。paranoid要求必须启用时间戳，                                       即必须传timestamps: true
         // 把驼峰命名转换为下划线
         //underscored: false,
         pool: {    // 使用连接池
            max: 5,  // 连接池中最大连接数量
            min: 0,  // 连接池中最小连接数量
            acquire: 30000,
            idle: 10000 // 如果一个线程 10 秒钟内没有被使用过的话，那么就释放线程
         },
     }
 })
 // 测试数据库链接
 sequelize
   .authenticate()
   .then(() => {
     console.log('数据库连接成功');
   })
   .catch((err: any) => {
     // 数据库连接失败时打印输出
     console.error(err);
     throw err;
   });
 export default sequelize; // 将连接对象暴露出去

 const User = sequelize.define('User', {
   // 在这里定义模型属性
   id: {
     type: DataTypes.INTEGER,
     autoIncrement: true,  //允许自增
     primaryKey: true
   }
   firstName: {
     type: DataTypes.STRING,
     allowNull: false
   },
   lastName: {
     type: DataTypes.STRING
     // allowNull 默认为 true
   }
 }, {
   // 这是其他模型参数
   sequelize, // 我们需要传递连接实例
   //局部的定义
   modelName: 'User' // 我们需要选择模型名称
     // 不要忘记启用时间戳！
   timestamps: true,
   // 不想要 createdAt
   createdAt: false,
   // 想要 updatedAt 但是希望名称叫做 updateTimestamp
   updatedAt: 'updateTimestamp'
 });
 // `sequelize.define` 会返回模型
 console.log(User === sequelize.models.User); // true

 sequelize.define('User', {
   // ... (属性)
 }, {
   timestamps: false
 });

 class Foo extends Model {}
 Foo.init({  }, {
   sequelize,
   // 不要忘记启用时间戳！
   timestamps: true,
   // 不想要 createdAt
   createdAt: false,
   // 想要 updatedAt 但是希望名称叫做 updateTimestamp
   updatedAt: 'updateTimestamp'
 });

 npm install -g mysql2
 npm install -g sequelize-auto

 //sequelize-auto -h "数据库地址" -d "数据库名" -u "用户名" -x "密码" -p "端口号"  --dialect mysql
 sequelize-auto -o "./model" -d test -h 127.0.0.1 -u root -p 3306 -x 123456 -e mysql
 Options:
     --help               Show help                                   [boolean]
     --version            Show version number                         [boolean]
 -h, --host               IP/Hostname for the database.                [string]
 -d, --database           Database name.                               [string]
 -u, --user               Username for database.                       [string]
 -x, --pass               Password for database. If specified without providing
                           a password, it will be requested interactively from
                           the terminal.
 -p, --port               Port number for database (not for sqlite). Ex:
                           MySQL/MariaDB: 3306, Postgres: 5432, MSSQL: 1433
                                                                       [number]
 -c, --config             Path to JSON file for Sequelize-Auto options and
                           Sequelize's constructor "options" flag object as
                           defined here:
                           https://sequelize.org/master/class/lib/sequelize.js~Sequelize.html#instance-constructor-constructor
                                                                       [string]
 -o, --output             What directory to place the models.          [string]
 -e, --dialect            The dialect/engine that you're using: postgres,
                           mysql, sqlite, mssql                         [string]
 -a, --additional         Path to JSON file containing model options (for all
                           tables). See the options: Https://sequelize.org/master/class/lib/model.js~Model.html#static-method-                           init
                                                                       [string]
     --indentation        Number of spaces to indent                   [number]
 -t, --tables             Space-separated names of tables to import     [array]
 -T, --skipTables         Space-separated names of tables to skip       [array]
 --caseModel, --cm        Set case of model names: c|l|o|p|u
                           c = camelCase
                           l = lower_case
                           o = original (default)
                           p = PascalCase
                           u = UPPER_CASE
 --caseProp, --cp         Set case of property names: c|l|o|p|u
 --caseFile, --cf         Set case of file names: c|l|o|p|u|k
                           k = kebab-case
 --noAlias                Avoid creating alias `as` property in relations
                                                                      [boolean]
 --noInitModels           Prevent writing the init-models file        [boolean]
 -n, --noWrite            Prevent writing the models to disk          [boolean]
 -s, --schema             Database schema from which to retrieve tables[string]
 -v, --views              Include database views in generated models  [boolean]
 -l, --lang               Language for Model output: es5|es6|esm|ts
                           es5 = ES5 CJS modules (default)
                           es6 = ES6 CJS modules
                           esm = ES6 ESM modules
                           ts = typescript                             [string]
 --useDefine              Use `sequelize.define` instead of `init` for es6|esm|ts
 --singularize, --sg      Singularize model and file names from plural table
                           names                                      [boolean]

 const { Op } = sequelize;
 [Op.and]: {a: 5}           // 且 (a = 5)
 [Op.or]: [{a: 5}, {a: 6}]  // (a = 5 或 a = 6)
 [Op.gt]: 6,                // id > 6
 [Op.gte]: 6,               // id >= 6
 [Op.lt]: 10,               // id < 10
 [Op.lte]: 10,              // id <= 10
 [Op.ne]: 20,               // id != 20
 [Op.eq]: 3,                // = 3
 [Op.not]: true,            // 不是 TRUE
 [Op.between]: [6, 10],     // 在 6 和 10 之间
 [Op.notBetween]: [11, 15], // 不在 11 和 15 之间
 [Op.in]: [1, 2],           // 在 [1, 2] 之中
 [Op.notIn]: [1, 2],        // 不在 [1, 2] 之中
 [Op.like]: '%hat',         // 包含 '%hat'
 [Op.notLike]: '%hat'       // 不包含 '%hat'
 [Op.iLike]: '%hat'         // 包含 '%hat' (不区分大小写)  (仅限 PG)
 [Op.notILike]: '%hat'      // 不包含 '%hat'  (仅限 PG)
 [Op.regexp]: '^[h|a|t]'    // 匹配正则表达式/~ '^[h|a|t]' (仅限 MySQL/PG)
 [Op.notRegexp]: '^[h|a|t]' // 不匹配正则表达式/!~ '^[h|a|t]' (仅限 MySQL/PG)
 [Op.iRegexp]: '^[h|a|t]'    // ~* '^[h|a|t]' (仅限 PG)
 [Op.notIRegexp]: '^[h|a|t]' // !~* '^[h|a|t]' (仅限 PG)
 [Op.like]: { [Op.any]: ['cat', 'hat']} // 包含任何数组['cat', 'hat'] - 同样适用于 iLike 和 notLike
 [Op.overlap]: [1, 2]       // && [1, 2] (PG数组重叠运算符)
 [Op.contains]: [1, 2]      // @> [1, 2] (PG数组包含运算符)
 [Op.contained]: [1, 2]     // <@ [1, 2] (PG数组包含于运算符)
 [Op.any]: [2,3]            // 任何数组[2, 3]::INTEGER (仅限PG)
 [Op.col]: 'user.organization_id'   // = 'user'.'organization_id', 使用数据库语言特定的列标识符, 本例使用

 $and: {a: 5}           // AND (a = 5)
 $or: [{a: 5}, {a: 6}]  // (a = 5 OR a = 6)
 $gt: 6,                // > 6
 $gte: 6,               // >= 6
 $lt: 10,               // < 10
 $lte: 10,              // <= 10
 $ne: 20,               // != 20
 $not: true,            // IS NOT TRUE
 $between: [6, 10],     // BETWEEN 6 AND 10
 $notBetween: [11, 15], // NOT BETWEEN 11 AND 15
 $in: [1, 2],           // IN [1, 2]
 $notIn: [1, 2],        // NOT IN [1, 2]
 $like: '%hat',         // LIKE '%hat'
 $notLike: '%hat'       // NOT LIKE '%hat'
 $iLike: '%hat'         // ILIKE '%hat' (case insensitive) (PG only)
 $notILike: '%hat'      // NOT ILIKE '%hat'  (PG only)
 $like: { $any: ['cat', 'hat']}
                        // LIKE ANY ARRAY['cat', 'hat'] - also works for iLike and notLike
 $overlap: [1, 2]       // && [1, 2] (PG array overlap operator)
 $contains: [1, 2]      // @> [1, 2] (PG array contains operator)
 $contained: [1, 2]     // <@ [1, 2] (PG array contained by operator)
 $any: [2,3]            // ANY ARRAY[2, 3]::INTEGER (PG only)
 $col: 'user.organization_id' // = "user"."organization_id", with dialect specific column identifiers, PG in this example

 //or操作符的两种使用 order_status=0 or order_status=1
 params['$or'] = [{ order_status: 0 }, { order_status: 1 }];   
 params['order_status'] = {
     $or: [
         { $eq: 0 },
         { $eq: 1 }
     ]
 }

 const User = sequelize.import('./model/user'); //导入模型
 const user = await User.create({
   username: 'alice123',
   isAdmin: true
 });

 await User.bulkCreate([
   { username: 'foo', pwd:"123"},
   { username: 'bar', pwd:"345"}
 ], { updateOnDuplicate: true, fields: ['username','pwd','address'] });

 const [user, created] = await User.findOrCreate({
   where: { username: '123' },
   defaults: {
     job: 'Technical Lead javascript'
   }
 });
 console.log(user.username); // 'sdepold'
 console.log(user.job); // 这可能是也可能不是 'Technical Lead JavaScript'
 console.log(created); // 指示此实例是否刚刚创建的布尔值
 if (created) {
   console.log(user.job); // 这里肯定是 'Technical Lead JavaScript'
 }

 const result = await Goods.findAll({
     attributes: [
         'goodsId',
         [sequelize.fn('SUM', sequelize.col('order_count')), 'order_count'],
         [sequelize.fn('SUM', sequelize.col('total_price_sum')), 'total_price_sum'],
         [sequelize.fn('SUM', sequelize.col('goods_cost')), 'goods_cost'],
         [sequelize.fn('COUNT', sequelize.col('goodsId')), 'goods_count']
     ],
     where: params,
     limit: 10,
     offset:0,
     raw: true,
     group: [sequelize.col('goodsId')],
 })

 // 指定全查询字段比较麻烦
 Model.findAll({
   attributes: ['id', 'foo', 'bar', 'baz', 'quz', [sequelize.fn('COUNT', sequelize.col('hats')), 'hats_count']]
 });
 // 这样会比较简短，且在你添加/删除属性后不会出错
 Model.findAll({
   attributes: { include: [[sequelize.fn('COUNT', sequelize.col('hats')), 'hats_count']] }
 });

 Model.findAll({
   attributes: { exclude: ['baz'] }
 });

 where:{  //查询 username='admin' pwd ='123456' user_status=0 or user_status=1
     username: 'admin',
     pwd:"123456",
     user_status:{
         [Op.or]: [
             { $eq: 0 },
             { $eq: 1 }
         ]
     }
 }

 Model.findAll({ limit: 10 }) // 获取 10 条数据（实例）
 Model.findAll({ offset: 8 }) // 跳过 8 条数据（实例）
 Model.findAll({ offset: 5, limit: 5 }) // 跳过 5 条数据并获取其后的 5 条数据（实例）
 //使用场景，分页请求数据
 const page = parseInt(params.page || 1);
 const limit = parseInt(params.limit || 10);
 const currentPage = (page - 1) * limit;
 Model.findAll({
     limit,
     offset: currentPage,
 })

 Model.findAll({
   order: [
      //sequelize.col('date') 有时候可以使用sequelize.col()指定列，防止在子查询或者关联查询时找不到列名
     [sequelize.col('date'), 'DESC'],
     ['date', 'DESC'],
     // 将转义 title 并针对有效方向列表进行降序排列
     ['title', 'DESC'],
     // 将按最大年龄进行升序排序
     sequelize.fn('max', sequelize.col('age')),
     // 将按最大年龄进行降序排序
     [sequelize.fn('max', sequelize.col('age')), 'DESC'],
     // 将按 otherfunction(`col1`, 12, 'lalala') 进行降序排序
     [sequelize.fn('otherfunction', sequelize.col('col1'), 12, 'lalala'), 'DESC'],
     // 将使用模型名称作为关联名称按关联模型的 createdAt 排序.
     [Task, 'createdAt', 'DESC'],
     // 将使用模型名称作为关联名称通过关联模型的 createdAt 排序.
     [Task, Project, 'createdAt', 'DESC'],
     // 将使用关联名称按关联模型的 createdAt 排序.
     ['Task', 'createdAt', 'DESC'],
     // 将使用关联的名称按嵌套的关联模型的 createdAt 排序.
     ['Task', 'Project', 'createdAt', 'DESC'],
     // 将使用关联对象按关联模型的 createdAt 排序. (首选方法)
     [Subtask.associations.Task, 'createdAt', 'DESC'],
     // 将使用关联对象按嵌套关联模型的 createdAt 排序. (首选方法)
     [Subtask.associations.Task, Task.associations.Project, 'createdAt', 'DESC'],
     // 将使用简单的关联对象按关联模型的 createdAt 排序.
     [{model: Task, as: 'Task'}, 'createdAt', 'DESC'],
     // 将由嵌套关联模型的 createdAt 简单关联对象排序.
     [{model: Task, as: 'Task'}, {model: Project, as: 'Project'}, 'createdAt', 'DESC']
   ],
   // 将按最大年龄降序排列
   order: sequelize.literal('max(age) DESC'),
   // 如果忽略方向,则默认升序,将按最大年龄升序排序
   order: sequelize.fn('max', sequelize.col('age')),
   // 如果省略方向,则默认升序, 将按年龄升序排列
   order: sequelize.col('age'),
   // 将根据方言随机排序(但不是 fn('RAND') 或 fn('RANDOM'))
   order: sequelize.random()
 });
 Model.findOne({
   order: [
     // 将返回 `name`
     ['name'],
     // 将返回 `username` DESC
     ['username', 'DESC'],
     // 将返回 max(`age`)
     sequelize.fn('max', sequelize.col('age')),
     // 将返回 max(`age`) DESC
     [sequelize.fn('max', sequelize.col('age')), 'DESC'],
     // 将返回 otherfunction(`col1`, 12, 'lalala') DESC
     [sequelize.fn('otherfunction', sequelize.col('col1'), 12, 'lalala'), 'DESC'],
     // 将返回 otherfunction(awesomefunction(`col`)) DESC, 这种嵌套可能是无限的!
     [sequelize.fn('otherfunction', sequelize.fn('awesomefunction', sequelize.col('col'))), 'DESC']
   ]
 });

 Model.findAll({ group: 'name' });
 Model.findAll({
     group: [sequelize.col('date'),sequelize.col('id')]   //可以指定多个列进行分组
 });
 // 生成 'GROUP BY name'

 const user = await User.findOne({ where: { username: 'admin' } });

 const user = await User.findAll({ where: { username: 'admin' },raw:true });

 const { count, rows } = await User.findAndCountAll({
   where: {
     username: {
       [Op.like]: '%foo%'
     }
   },
   offset: 10,   //查询偏移（跳过）量
   limit: 2      //限制返回结果条数
 });

 let result = await Goods.findAndCount({
     include: [
         {
             attributes: [[sequelize.col('title'), 'goods_name']],
             association: Goods.belongsTo(tbGoods, { targeTKEy: 'goodsId', foreignKey: 'goodsId' , as:'goods_name_info'}),
             model: Goods,
             required: false,
             where: params2,
         }
     ],
     where: params,
     limit,
     offset: currentPage,
     raw: true,
     order: [
         [sequelize.col('date'), 'DESC'],
     ]
 })

 A.hasOne(B, {  });     // A 有一个 B
 A.belongsTo(B, {  });  // A 属于 B
 A.hasMany(B, {  });    // A 有多个 B
 A.belongsToMany(B, { through: 'C',  });   // A 属于多个 B , 通过联结表 C

 //根据goodsId 去匹配 goods_order对应的 goods_name
 let result = await goods_roi.findAll({
     attributes: [
         'goodsId',
         [sequelize.fn('SUM', sequelize.col('order_count')), 'order_count'],
         [sequelize.fn('SUM', sequelize.col('total_price_sum')), 'total_price_sum'],
         [sequelize.fn('SUM', sequelize.col('goods_cost')), 'goods_cost'],
         [
             sequelize.literal(`(
                         SELECT item_title
                         FROM goods_order AS goods_order
                         WHERE
                         goods_order.item_id = goods_roi.goodsId limit 1
                     )`),
             'goods_name'
         ]
     ],
     where: params,
     raw: true,
     group: [sequelize.col('goodsId')]
 })

 const amount = await Model.count({
   where: {
     id: {
       [Op.gt]: 25
     }
   }
 });
 console.log(`这有 ${amount} 个记录 id 大于 25`);

 await User.max('age'); // 40
 await User.max('age', { where: { age: { [Op.lt]: 20 } } }); // 10
 await User.min('age'); // 5
 await User.min('age', { where: { age: { [Op.gt]: 5 } } }); // 10
 await User.sum('age'); // 55
 await User.sum('age', { where: { age: { [Op.gt]: 5 } } }); // 50

 await User.increment({age: 5}, { where: { id: 1 } }) // 将年龄增加到15岁
 await User.increment({age: -5}, { where: { id: 1 } }) // 将年龄降至5岁

 *// 将所有没有姓氏的人更改为 "Doe"*
 await User.update({ lastName: "Doe" }, {
   where: {
     lastName: null
   }
 });

 *// 删除所有名为 "Jane" 的人* 
 await User.destroy({
   where: {
     firstName: "Jane"
   }
 });

 try {
   const result = await sequelize.transaction(async (t) => {
     const user = await User.create({
       firstName: 'Abraham',
       lastName: 'Lincoln'
     }, { transaction: t });  //{transaction: t} 必须作为第二个对象里的参数传递
     return user;
   });
   // 如果执行到此行,则表示事务已成功提交,`result`是事务返回的结果
   // `result` 就是从事务回调中返回的结果(在这种情况下为 `user`)
 } catch (error) {
   // 如果执行到此,则发生错误.
   // 该事务已由 Sequelize 自动回滚！
 }