Koa2使用sequelize操作Mysql(一) sequelize

Sequelize是用于Postgres，MySQL，MariaDB，SQLite和Microsoft SQL Server的基于promise的Node.js ORM工具。它具有可靠的事务支持，关系，急切和延迟加载，读取复制等功能。

Sequelize遵循语义版本控制，并支持Node v10及更高版本。

sequelize的npm地址:https://www.npmjs.com/package/sequelize

安装:

npm i sequelize

其要和一个数据库结合使用,这里我们选择mysql, 而在我们的koa2工程中, 我们使用mysql2库来操作数据库

npm i mysql2

一. 连接MySQL数据库

1. 首先导入模块

const { Sequelize } = require('sequelize');

2. 用导入的Sequelize 实例化一个sequelize对象:

const sequelize = new Sequelize('db1', 'root', 'xxxxx', {
  host: 'localhost', //数据库地址
  dialect: 'mysql', //指定连接的数据库类型
  pool: {
    max: 5, //连接池最大连接数量
    min: 0, //最小连接数量
    idle: 10000, //如果一个线程 10秒内么有被使用过的话，就释放
  },
})

这样就完成了数据库链接了
我们可以测试一下:
sequelize对象提供了一个authenticate方法,可以测试数据库链接https://sequelize.org/master/class/lib/sequelize.js~Sequelize.html#instance-method-authenticate

我们可以看到它return 一个promise, 所以我们应该在一个async方法里处理这个promise

例如: 我们在index.js里写一个访问127.0.0.1就告诉我们数据库连接是否成功的API,可以:

const router = require('koa-router')()
const { Sequelize } = require('sequelize');

const sequelize = new Sequelize('db1', 'root', 'xxxxxx', {
  host: 'localhost', //数据库地址
  dialect: 'mysql', //指定连接的数据库类型
  pool: {
    max: 5, //连接池最大连接数量
    min: 0, //最小连接数量
    idle: 10000, //如果一个线程 10秒内么有被使用过的话，就释放
  },
})

router.get('/', async (ctx, next) => {
  let str1
  str1 = await sequelize
    .authenticate()
    .then(() => {
      return '连接数据库成功'
    })
    .catch(err => {
      return '连接数据库失败' + err
    });
  ctx.body = str1
})

module.exports = router

二. 创建模型:(获取表)

https://sequelize.org/master/manual/model-basics.html

1. 创建模型

连接数据库成功后, 怎么使用ORM的方式把数据库用起来呢? 答: 创建模型

模型是Sequelize的本质。模型是代表数据库中表的抽象。

Sequelize中的模型有一个名称。此名称不必与它在数据库中表示的表的名称相同。通常，模型具有单数名称(例如User)，而表具有复数名称(例如Users)，尽管这是完全可配置的。

通过创建模型,我们将MySQL中的表和模型对应起来

class Student extends Model { }//创建一个类,继承Model
Student.init({
  id: { type:DataTypes.INTEGER, primaryKey: true },
  name: DataTypes.STRING,
  age: DataTypes.INTEGER,
  score: DataTypes.FLOAT,
  brithday: DataTypes.DATE,
}, { sequelize, modelName: 'Student' })

除了这种声明方法,还有一种方法把一张表声明为一个model,这种方法本质上还是在调用init函数, 用哪种随你喜欢

const Student = sequelize.define('Student111', {
  id: { type: DataTypes.INTEGER, primaryKey: true },
  name: DataTypes.STRING,
  age: DataTypes.INTEGER,
  score: DataTypes.FLOAT,
  brithday: DataTypes.DATE,
}, {sequelize})

2. 模型同步

定义模型时，您要告诉Sequelize有关数据库中表的一些信息。但是，如果该表实际上甚至不存在于数据库中怎么办？如果存在，但具有不同的列，较少的列或任何其他差异，该怎么办？这就需要模型同步。

• User.sync() -如果表不存在，则会创建表(如果已经存在，则不执行任何操作)
• User.sync({ force: true }) -这将创建表，如果该表已经存在，则将其首先删除
• User.sync({ alter: true }) -这将检查数据库中表的当前状态(它具有哪些列，它们的数据类型是什么，等等)，然后在表中进行必要的更改以使其与模型匹配。

Student.sync({ alter: true })

3. 注意一个sequelize的默认行为:时间戳记

默认情况下，Sequelize使用数据类型自动将字段createdAt和添加updatedAt到每个模型中(DataTypes.DATE类型)。

这些字段也会自动进行管理-每当您使用Sequelize创建或更新内容时，这些字段都会被正确设置。该createdAt字段将包含代表创建时刻的时间戳，并且updatedAt将包含最新更新的时间戳。

注意：这是在Sequelize级别完成的(即，不使用SQL触发器完成)。使用者无需干预。

但是通过不使用Sequelize的情况下执行的查询, 将不会导致这些字段自动更新。
可以使用以下timestamps: false选项为模型禁用此行为：

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

---

上面我们得到了数据库的映射对象sequelize,以及表的映射对象User, 接下来, 我们先尝试操作表

三. 创建模型实例 (表中插入数据) // 增

数据就相当于''模型类''的实例化

3.1 build方法

尽管模型是类，但是您不应new直接使用运算符来创建实例。相反，应使用build方法：

const ldy = Student.build({
    name:"林黛玉",
    age:17, 
    score:100,
    brithday: new Date('2010-12-05')
  })
  console.log(ldy instanceof Student); 
  ldy.save()

注意:build根本不与数据库通信(请注意: 它甚至不是异步的)
build完成后应该用save方法保存到数据库中

3.2 create方法

Sequelize提供了create方法将以上的build和save方法组合为一个方法：

const yyh = await Student.create({
    name:"杨玉环",
    age:25, 
    score:95,
    brithday: new Date('2009-12-05')
  })

3.3 Sequelize提供了Model.bulkCreate方法，该方法一次就可以创建多个记录。

Model.bulkCreate与Model.create极为相似，方法接收对象数组而不是单个对象。

const captains = await Captain.bulkCreate([
  { name: 'Jack Sparrow' },
  { name: 'Davy Jones' }
]);

默认情况下，bulkCreate不会在要创建的每个对象上运行验证(create会验证)。要bulkCreate运行验证，您必须传递validate: true选项。但这会降低性能。

const Foo = sequelize.define('foo', {
  bar: {
    type: DataTypes.TEXT,
    validate: {
      len: [4, 6]
    }
  }
});

四. 查找模型中的数据 (查表) //查

4.1 查找模型中所有数据 findAll

相当于SELECT * FROM ...

router.get('/', async (ctx, next) => {
  const students = await Student.findAll();
  console.log(students.every(student => student instanceof Student)); // true
  ctx.body = "All students:" + JSON.stringify(students, null, 2)
})

4.1.1 findAll指定查询的属性

相当于: SELECT foo, bar FROM ...

router.get('/', async (ctx, next) => {
  const students = await Student.findAll({ attributes: ['name', 'age'] });
  console.log(students.every(student => student instanceof Student)); // true
  ctx.body = "All students:" + JSON.stringify(students, null, 2)
})

4.1.2 取别名

相当于: SELECT foo, bar AS baz, qux FROM ...

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

4.2 按条件查找模型 //应用WHERE子句

4.2.1 按条件查找模型

相当于SELECT * FROM post WHERE authorId = 2

router.get('/', async (ctx, next) => {
  const students = await Student.findAll({
    where: { id: 2 }
  });
  ctx.body = "All students:" + JSON.stringify(students, null, 2)
})

如果有多个筛选条件,也是可以的

const students = await Student.findAll({
    where: {
      age: 17, score: 100
    }
  });

4.2.2 where and 条件

如果引入Op对象, 可以用and 筛选多个条件, 效果同上

const { Op } = require("sequelize");
router.get('/', async (ctx, next) => {
  const students = await Student.findAll({
    where: {
      [Op.and]: [
        { age: 17 },
        { score: 100 }
      ]
    }
  });
  ctx.body = "All students:" + JSON.stringify(students, null, 2)
})

4.2.2 where or 条件

如果需要OR限定, 也应该使用Op对象,对象的列表里写的是同一个属性

const students = await Student.findAll({
    where: {
      [Op.or]: [
        { age: 17 },
        { age: 25 }
      ]
    }
  });

还有一种写法,功能是一样的:

const students = await Student.findAll({
    where: {
      age: {
        [Op.or]: [17, 25]
      }
    }
  });

4.2.3 Op对象有好多种用法:

onst { Op } = require("sequelize");
Post.findAll({
  where: {
    [Op.and]: [{ a: 5 }, { b: 6 }],            // (a = 5) AND (b = 6)
    [Op.or]: [{ a: 5 }, { b: 6 }],             // (a = 5) OR (b = 6)
    someAttribute: {
      // Basics
      [Op.eq]: 3,                              // = 3
      [Op.ne]: 20,                             // != 20
      [Op.is]: null,                           // IS NULL
      [Op.not]: true,                          // IS NOT TRUE
      [Op.or]: [5, 6],                         // (someAttribute = 5) OR (someAttribute = 6)

      // Using dialect specific column identifiers (PG in the following example):
      [Op.col]: 'user.organization_id',        // = "user"."organization_id"

      // Number comparisons
      [Op.gt]: 6,                              // > 6
      [Op.gte]: 6,                             // >= 6
      [Op.lt]: 10,                             // < 10
      [Op.lte]: 10,                            // <= 10
      [Op.between]: [6, 10],                   // BETWEEN 6 AND 10
      [Op.notBetween]: [11, 15],               // NOT BETWEEN 11 AND 15

      // Other operators

      [Op.all]: sequelize.literal('SELECT 1'), // > ALL (SELECT 1)

      [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.startsWith]: 'hat',                  // LIKE 'hat%'
      [Op.endsWith]: 'hat',                    // LIKE '%hat'
      [Op.substring]: 'hat',                   // LIKE '%hat%'
      [Op.iLike]: '%hat',                      // ILIKE '%hat' (case insensitive) (PG only)
      [Op.notILike]: '%hat',                   // NOT ILIKE '%hat'  (PG only)
      [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.any]: [2, 3],                        // ANY ARRAY[2, 3]::INTEGER (PG only)

      // In Postgres, Op.like/Op.iLike/Op.notLike can be combined to Op.any:
      [Op.like]: { [Op.any]: ['cat', 'hat'] }  // LIKE ANY ARRAY['cat', 'hat']

      // There are more postgres-only range operators, see below
    }
  }
});

4.2.4 Op.in可以简写

const students = await Student.findAll({
    where: {
      age:  [17, 25]
    }
  });

五. 更多查询方法

1. findByPk 按主键查询

const student = await Student.findByPk(2)

2. findOne 找到第一个条目

const student = await Student.findOne({ where: { age: 17 } })

3. findOrCreate 查询或创建

findOrCreate将在表中找满足查询选项的条目, 没有则创建一个条目。

在这两种情况下，它都会返回一个实例(找到的实例或创建的实例)和一个布尔值，指示该实例是已创建还是已经存在。

const [student, created] = await Student.findOrCreate({
    where: { name: "范冰冰" },
    defaults: {
      age: 35, score: 100,
      brithday: new Date('2020-03-05')
    }
  })

不知道是不是我用的不对, 创建时会在服务器控制台报错, 但仍旧能创建成功

3. findAndCountAll

findAndCountAll方法是组合findAll和的便捷方法count。
该findAndCountAll方法返回一个具有两个属性的对象：

count -整数-符合查询条件的记录总数
rows -对象数组-获得的记录

router.get('/', async (ctx, next) => {
  const {count, rows} = await Student.findAndCountAll({
    where: {
      age: {
        [Op.lt]: 25
      }
    }
  })
  let str1 = ''
  rows.forEach((row) => str1 += JSON.stringify(row, null, 2))
  ctx.body = 'rows:\n' + str1 + '\ncount\n' + count
})

五. order排序查询

order选项采用一项或一个sequelize方法进行排序。
例如:

const students = await Student.findAll({
    order: ['age']
  })

order有好多用法:

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 createdAt using the model name as the association's name.
    [Task, 'createdAt', 'DESC'],

    // Will order through an associated model's createdAt using the model names as the associations' names.
    [Task, Project, 'createdAt', 'DESC'],

    // Will order by an associated model's createdAt using the name of the association.
    ['Task', 'createdAt', 'DESC'],

    // Will order by a nested associated model's createdAt using the names of the associations.
    ['Task', 'Project', 'createdAt', 'DESC'],

    // Will order by an associated model's createdAt using an association object. (preferred method)
    [Subtask.associations.Task, 'createdAt', 'DESC'],

    // Will order by a nested associated model's createdAt using association objects. (preferred method)
    [Subtask.associations.Task, Task.associations.Project, 'createdAt', 'DESC'],

    // Will order by an associated model's createdAt using a simple association object.
    [{model: Task, as: 'Task'}, 'createdAt', 'DESC'],

    // Will order by a nested associated model's createdAt 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()
});

Foo.findOne({
  order: [
    // will return `name`
    ['name'],
    // will return `username` DESC
    ['username', 'DESC'],
    // will return max(`age`)
    sequelize.fn('max', sequelize.col('age')),
    // will return max(`age`) DESC
    [sequelize.fn('max', sequelize.col('age')), 'DESC'],
    // will return otherfunction(`col1`, 12, 'lalala') DESC
    [sequelize.fn('otherfunction', sequelize.col('col1'), 12, 'lalala'), 'DESC'],
    // will return otherfunction(awesomefunction(`col`)) DESC, This nesting is potentially infinite!
    [sequelize.fn('otherfunction', sequelize.fn('awesomefunction', sequelize.col('col'))), 'DESC']
  ]
});

六. 分组查询

const students = await Student.findAll({
    group: 'name'
  })

七. 查询的限制和分页

在limit和offset选项允许您使用限制/分页工作：

// Fetch 10 instances/rows
Project.findAll({ limit: 10 });

// Skip 8 instances/rows
Project.findAll({ offset: 8 });

// Skip 5 instances and fetch the 5 after that
Project.findAll({ offset: 5, limit: 5 });
通常，这些order选项与选项一起使用。

八. 更新模型中的数据(更新表中数据)

8.1. 简单的UPDATE

更新也接受where选项，就像上面的读取一样。

await Student.update(
    { age: 23 },
    {
      where: {
        name: '杨玉环'
      }
    }
  )

九. 删除表中数据

删除也接受where选项，就像上面的读取一样。

await Student.destroy(
    {
      where: {
        name: '杨玉环'
      }
    }
  )

十. 使用原始SQL语句

在很多情况下执行原始SQL查询更加容易，此时可以使用sequelize.query方法。
默认情况下，该函数将返回两个参数: -一个结果数组和一个包含元数据的对象(例如，受影响的行数等), 但对于MySQL，它们将是对同一对象的两个引用。

const [results, metadata] = await sequelize.query("UPDATE users SET y = 42 WHERE x = 12");

关于原始查询:详见:https://sequelize.org/master/manual/raw-queries.html

十一. 建立表间连接

表与表之间无外乎：

一对一 belongsto
外键一对一 hasone
一对多 hasmany
多对多 belongsToMany

Banner.belongsTo(Image, {
  foreignKey: 'img_id',
  targetKey: 'id'
})

---

今天我们熟悉了很多sequelize的特性和方法, 基本可以掌握sequelize的用法, 下一步: 我们将对sequelize进行封装, 具体请参考下一篇文章.