sequlize学习笔记

1.sequlizejs连接mysql

const { Sequelize, DataTypes } = require('sequelize');
const fs = require('fs')
const path = require('path')

// 多数据库配置
let dbConfig = [
  {
    name: 'fangbao', auto: true, adapter: 'sails-mysql', host: '127.0.0.1', port: 3306, user: 'root', password: '123456', database: 'houseinsurancelinkage_test', modelPath: './dbStorage/fangbao/',
  },
  {
    name: 'linkage', host: '127.0.0.1', port: 3306, user: 'root', password: '123456', database: 'linkagesystem_test', modelPath: './dbStorage/linkage/',
  }
]

// 实例化mysql
const db = {}; 
for (const data of dbConfig) {
  db[data.name] = new Sequelize(data.database, data.user, data.password, {
    host: data.host,
    dialect: 'mysql',
    port: data.port,
    timezone: '+08:00', //改为标准时区
  });
  // 获取所有表model
  try {
    var tables = fs.readdirSync(path.resolve(__dirname, data.modelPath));
  } catch (error) {
    console.log(`读取${data.modelPath}表model失败`, error);
  }
  for (let models of tables) {
    let table = models.split('.js')[0];
    if (table == 'init-models') continue;
    const model = require(`${data.modelPath}/${table}`)(db[data.name], DataTypes)
    db[data.name].models[model] = model;
  }
    // 测试连接
    db[data.name].authenticate().then(() => {
      console.log(`new db-mysql-dev-${data.name} connection is ok`);
    }).catch(
      err => {
          console.log(`new db-mysql-dev-${data.name} connection is err`, err);
      }
    )
}

module.exports = db

2.查询

(async function() {
// 原始sql查询
const data = await db.fangjia.query("select id from mysqluser", { type: QueryTypes.SELECT });
console.log(data);
// squelize方法查询 findAll(查询多条) findOne(查询单条)
const result = await db.fangbao.models.sys_type_dict.findAll({
        attributes: [['dic_value', 'dicValue'], ['dic_type', 'dicType'], ['dic_des', 'dicDes']], 
        where: { id: 1 }
      })
 console.log(result);
})()

3.编辑

(async function() {
const result = await db.fangbao.models.sys_type_dict.update({ name: 'xxx' }, {
        where: {
          id: 1,
        }
      })
 console.log(result);
})()

4.删除

(async function() {
const result = await db.fangbao.models.sys_type_dict.destroy({
        where: {
          id: 1,
        }
      })
 console.log(result);
})()

4.添加

(async function() {
// bulkCreate(添加多条)
const result = await db.fangbao.models.sys_type_dict.create({name: '小米', age: 18})
 console.log(result);
})()

5.事务

(async function() {
const t = await db.fangbao.transaction();
try{
	const result = await db.fangbao.models.sys_type_dict.create({name: '小米', age: 18}, { 	transaction: t })
	await t.commit();
console.log(result);
} catch(err) {
	await t.rollback();
} 
})()