sequelize

sequelize外键关联报错SequelizeDatabaseError: Cant write; duplicate key in table #sql-454_d

月盾
Assistance.belongTo(User)会报以下错误: ALTER TABLE `assistance` ADD CONSTRAINT `assistance_user_id_foreign_idx` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE NO ACTION ON UPDATE CASCADE; Unhandled rejection SequelizeDatabaseError: Can't write; duplicate key in table '#sql-454_d' at Query.formatError (/home/hale/workspace/issue-tracking-system/node_modules/sequelize/lib/dialects/mysql/query.js:223:16) at Query.connection.query [as onResult] (/home/hale/workspace/issue-tracking-system/node_modules/sequelize/lib/dialects/mysql/query.js:55:23) at Query.Command.execute (/home/hale/workspace/issue-tracking-system/node_modules/mysql2/lib/commands/command.js:30:12) at Connection.handlePacket (/home/hale/workspace/issue-tracking-system/node_modules/mysql2/lib/connection.js:515:28) at PacketParser.onPacket (/home/hale/workspace/issue-tracking-system/node_modules/mysql2/lib/connection.js:94:16) at PacketParser.executeStart (/home/hale/workspace/issue-tracking-system/node_modules/mysql2/lib/packet_parser.js:77:14) at Socket.<anonymous> (/home/hale/workspace/issue-tracking-system/node_modules/mysql2/lib/connection.js:102:29) at emitOne (events.js:115:13) at Socket.emit (events.js:210:7) at addChunk (_stream_readable.

typescript开发sequelize返回ModelInstance或null值无法获取属性值

月盾
从图片中的代码可以看出 let userRecord = await item.getUser();获取到的是Bluebird<UserInstance | null>类型,然后在下面获取对象属性的时候报错,错误信息是: [ts] Object is possibly 'null'. let userRecord: UserInstance | null 说对象可能是null,所以无法获取其中的属性。遇到这种情况请设置typescript的编译选项,tsconfig.json文件中的 "strictNullChecks": true, /* Enable strict null checks. */ 默认是true,即严格null检查,设置为falsse即可。

sequelize.js不能将驼峰camelCased命名转化为下划线underscored命名

月盾
定义Model的时候有这样两个参数: underscored,underscoredAll, Converts all camelCased columns to underscored if true. Will not affect timestamp fields named explicitly by model options and will not affect fields with explicitly set field option 其意思是说转化所有驼峰字段为下划线字段,但实际情况并不是如此。 var Model = sequelize.define<ModelInstance, ModelAttributes>( 'Assistance', { title: Sequelize.STRING, description: Sequelize.STRING, fullName: Sequelize.STRING }, { underscored: true, tableName: 'assistance', charset: 'utf8', collate: 'utf8_unicode_ci' } ); CREATE TABLE IF NOT EXISTS `assistance` (`id` INTEGER NOT NULL auto_increment , `title` VARCHAR(255), `description` VARCHAR(255), `fullName` VARCHAR(255), `created_at` DATETIME NOT NULL, `updated_at` DATETIME NOT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_unicode_ci; 其中fullName字段在创建表的时候还是和模型中保持一致,并没有转换为full_name,underscored的设置其实只影响到了createdAt和updatedAt等内置字段。 在github上也有相关讨论:https://github.

Sequelize批量插入数据

月盾
sequelize提供了批量插入数据的方法:Model.bulkCreate([…object])。 User.bulkCreate([ { username: 'barfooz', isAdmin: true }, { username: 'foo', isAdmin: true }, { username: 'bar', isAdmin: false } ]).then(() => { // Notice: There are no arguments here, as of right now you'll have to... return User.findAll(); }).then(users => { console.log(users) // ... in order to get the array of user objects })

sequelizejs删除对象属性

月盾
js中有个delete操作符可以删除对象的属性,比如下面代码: //objtest对象有个prop属性 var objtest = { prop: 'delete me' }; //或者这么声明的对象 var objtest = new Array(); objtest['prop'] = 'delete me'; //删除属性'prop': delete objtest.prop; //或者 delete objtest['prop']; //还可以删除任意变量 var numb = 17; delete numb; 但是在nodejs项目中使用了sequelizejs来读取数据,如果也有想删除的属性,直接使用delete是删不掉的,比如有这样的数据结构: var user = { name: '鸣人', age: 16, gender: '男', friends: [{ name: '小樱', age: 16, gender: '女', friends: [{ name: '井野', age: 16, gender: '女' }] }, { name: '佐助', age: 16, gender: '男' }] } 鸣人有小樱和佐助两个朋友,小樱有井野一个朋友,小樱的朋友也可以当做鸣人的朋友,现在想把井野也提出来并列到鸣人的朋友列表里,这样做很容易,但是既然提出来了就不需要小樱的朋友列表了,应该删掉。但是用delete user.

sequelizejs中where条件与order排序的使用

月盾
最基本的where条件: 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.destroy({ where: { status: 'inactive' } }); // DELETE FROM post WHERE status = 'inactive'; Post.update({ updatedAt: null, }, { where: { deletedAt: { $ne: null } } }); // UPDATE post SET updatedAt = null WHERE deletedAt NOT NULL; 如果涉及到or的使用,sequelize也提供了很好的解决方案

sequelize定义实体对象

月盾
sequelize定义实体对象 var sequelize = require("../utils/sequelizeDB");//连接数据库 var Sequelize = require("sequelize"); var User = sequelize.define("User", { user_id:{ type: Sequelize.STRING, primaryKey: true}, name: Sequelize.STRING, phone: Sequelize.STRING, create_date: Sequelize.DATE, update_date: Sequelize.DATE }, { freezeTableName: true, // 默认false修改表名为复数,true不修改表名,与数据库表名同步 tableName: "user", timestamps: false }); define函数的第一个参数’User’就是定义一个实体对象,名称不必与数据库表名一致,只是为了确定该对象没有重复 { type: Sequelize.STRING, primaryKey: true}将属性作为主键 freezeTableName禁用修改表名;默认情况下,sequelize会自动将模型名称(第一个参数定义‘User’)为复数。值为ture时不修改 tableName数据库表名 timestamps是否自动添加时间戳createAt,updateAt