sequelize的基本操作

发表于 | 分类于

本文介绍了sequelize的CURD操作

查

find

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
// search for known ids
Project.findByPk(123).then(project => {
  // project will be an instance of Project and stores the content of the table entry
  // with id 123. if such an entry is not defined you will get null
})

// search for attributes
Project.findOne({ where: {title: 'aProject'} }).then(project => {
  // project will be the first entry of the Projects table with the title 'aProject' || null
})


Project.findOne({
  where: {title: 'aProject'},
  attributes: ['id', ['name', 'title']]
}).then(project => {
  // project will be the first entry of the Projects table with the title 'aProject' || null
  // project.get('title') will contain the name of the project
})

findOrCteate

有则查，无则创建

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
User
  .findOrCreate({where: {username: 'sdepold'}, defaults: {job: 'Technical Lead JavaScript'}})
  .then(([user, created]) => {
    console.log(user.get({
      plain: true
    }))
    console.log(created)

    /*
     findOrCreate returns an array containing the object that was found or created and a boolean that
     will be true if a new object was created and false if not, like so:

    [ {
        username: 'sdepold',
        job: 'Technical Lead JavaScript',
        id: 1,
        createdAt: Fri Mar 22 2013 21: 28: 34 GMT + 0100(CET),
        updatedAt: Fri Mar 22 2013 21: 28: 34 GMT + 0100(CET)
      },
      true ]

 In the example above, the array spread on line 3 divides the array into its 2 parts and passes them
  as arguments to the callback function defined beginning at line 39, which treats them as "user" and
  "created" in this case. (So "user" will be the object from index 0 of the returned array and
  "created" will equal "true".)
    */
  })

findAndCountAll

合并了findAll和count。当需要处理limit和offset并且需要计算总数时可以用它

返回带有两个属性的对象：

  • count - 整型，总数。
  • rows - 数组，记录。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
Project
  .findAndCountAll({
     where: {
        title: {
          [Op.like]: 'foo%'
        }
     },
     offset: 10,
     limit: 2
  })
  .then(result => {
    console.log(result.count);
    console.log(result.rows);
  });

支持includes。只有标记成required的才会统计到count。

假设你想查找所有有profile的user：

1
2
3
4
5
6
User.findAndCountAll({
  include: [
     { model: Profile, required: true}
  ],
  limit: 3
});

因为 Profile 设置成了 required ，所以返回inner join，并且只有拥有profile的users才会统计到count。如果我们移除 required ，无论有没有profile，所有的users都将被计算入总数。include中添加 where 条件自动设置成require:

1
2
3
4
5
6
User.findAndCountAll({
  include: [
     { model: Profile, where: { active: true }}
  ],
  limit: 3
});

上面的查询将值计算拥有active profile的user，因为当加入where条件时， required 被隐式的设置成了true。

传入 findAndCountAll的选项和下面的 findAll 相同。

findAll

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
// find multiple entries
Project.findAll().then(projects => {
  // projects will be an array of all Project instances
})

// search for specific attributes - hash usage
Project.findAll({ where: { name: 'A Project' } }).then(projects => {
  // projects will be an array of Project instances with the specified name
})

// search within a specific range
Project.findAll({ where: { id: [1,2,3] } }).then(projects => {
  // projects will be an array of Projects having the id 1, 2 or 3
  // this is actually doing an IN query
})

Project.findAll({
  where: {
    id: {
      [Op.and]: {a: 5},           // AND (a = 5)
      [Op.or]: [{a: 5}, {a: 6}],  // (a = 5 OR 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.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.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)
    },
    status: {
      [Op.not]: false           // status NOT FALSE
    }
  }
})

复杂过滤条件

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
Project.findOne({
  where: {
    name: 'a project',
    [Op.or]: [
      { id: [1,2,3] },
      { id: { [Op.gt]: 10 } }
    ]
  }
})

Project.findOne({
  where: {
    name: 'a project',
    id: {
      [Op.or]: [
        [1,2,3],
        { [Op.gt]: 10 }
      ]
    }
  }
})

等价sql:

1
2
3
4
5
6
7
SELECT *
FROM `Projects`
WHERE (
  `Projects`.`name` = 'a project'
   AND (`Projects`.`id` IN (1,2,3) OR `Projects`.`id` > 10)
)
LIMIT 1;

not 例子:

1
2
3
4
5
6
7
8
9
Project.findOne({
  where: {
    name: 'a project',
    [Op.not]: [
      { id: [1,2,3] },
      { array: { [Op.contains]: [3,4,5] } }
    ]
  }
});

等价sql:

1
2
3
4
5
6
7
SELECT *
FROM `Projects`
WHERE (
  `Projects`.`name` = 'a project'
   AND NOT (`Projects`.`id` IN (1,2,3) OR `Projects`.`array` @> ARRAY[3,4,5]::INTEGER[])
)
LIMIT 1;

limit，offset，order 和 group

你可以使用limit, offset:

1
2
3
4
5
6
7
8
// limit the results of the query
Project.findAll({ limit: 10 })

// step over the first 10 elements
Project.findAll({ offset: 10 })

// step over the first 10 elements, and take 2
Project.findAll({ offset: 10, limit: 2 })

group和order，他们语法相同：

1
2
3
4
5
Project.findAll({order: [['title', 'DESC']]})
// yields ORDER BY title DESC

Project.findAll({group: 'name'})
// yields GROUP BY name

注意上面两个例子，字符串会一字不差的传入到查询语句中而不经过转义，比如列名不会转义。当你传入一个字符串到order/group时，一直会这样处理。如果想转义列名，需要提供一个参数数组，即使你只想order/group一列。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
something.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']
  ]
})

总结：order/group数组元素可以是：

  • String - 直接引用
  • Array - 第一个元素直接引用，第二个逐字追加
  • Object -
    • raw逐字追加，而不作为字符串
    • 如果raw未设置，其他的会被忽略，query失败
  • Sequelize.fn 和 Sequelize.col 返回functions和引用的列名

只获取原始数据

Sequelize返回的是带有update、delete等方法的model实例，如果有几千条数据，可能会比较耗时。如果只是需要展示数据而不需要更新等其他操作，可以使用如下方法：

1
2
3
4
// Are you expecting a massive dataset from the DB,
// and don't want to spend the time building DAOs for each entry?
// You can pass an extra query option to get the raw data instead:
Project.findAll({ where: { ... }, raw: true })

function

count

1
2
3
4
5
6
7
Project.count().then(c => {
  console.log("There are " + c + " projects!")
})

Project.count({ where: {'id': {[Op.gt]: 25}} }).then(c => {
  console.log("There are " + c + " projects with an id greater than 25.")
})

max

1
2
3
4
5
6
7
8
9
10
11
12
13
/*
  Let's assume 3 person objects with an attribute age.
  The first one is 10 years old,
  the second one is 5 years old,
  the third one is 40 years old.
*/
Project.max('age').then(max => {
  // this will return 40
})

Project.max('age', { where: { age: { [Op.lt]: 20 } } }).then(max => {
  // will be 10
})

min

1
2
3
4
5
6
7
8
9
10
11
12
13
/*
  Let's assume 3 person objects with an attribute age.
  The first one is 10 years old,
  the second one is 5 years old,
  the third one is 40 years old.
*/
Project.min('age').then(min => {
  // this will return 5
})

Project.min('age', { where: { age: { [Op.gt]: 5 } } }).then(min => {
  // will be 10
})

sum

1
2
3
4
5
6
7
8
9
10
11
12
13
/*
  Let's assume 3 person objects with an attribute age.
  The first one is 10 years old,
  the second one is 5 years old,
  the third one is 40 years old.
*/
Project.sum('age').then(sum => {
  // this will return 55
})

Project.sum('age', { where: { age: { [Op.gt]: 5 } } }).then(sum => {
  // will be 50
})

只获取需要的列

attributes：

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

别名

1
2
3
Model.findAll({
  attributes: ['foo', ['bar', 'baz']]
});
1
SELECT foo, bar AS baz ...

使用函数作为列：

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

当使用函数时，必须指定别名用来访问。如上方的例子，可以使用instance.get('no_hats')来获取帽子的数量。

如果需要所有的列，然后再加上一个函数的话，全写出来会很麻烦。

1
2
3
Model.findAll({
  attributes: ['id', 'foo', 'bar', 'baz', 'quz', [sequelize.fn('COUNT', sequelize.col('hats')), 'no_hats']]
});

这时可以这样：

1
2
3
4
// 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']] }
});

他们的sql都是：

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

如果只想去除一列：

1
2
3
Model.findAll({
  attributes: { exclude: ['baz'] }
});
1
SELECT id, foo, bar, quz ...

where 条件

findAll/find 或者批量 updates/destroys ，可以使用where过滤。where