nodejs使用sequelize操作数据库

发表于 | 分类于

前言

前一篇介绍了如何直连mysql，但是两种代码风格都有自身的弊端。这次主要探讨如何利用ORM操作数据库。

官方帮助文档：getting-started

安装

安装Sequelize：

1
npm install --save sequelize

根据连接的数据库的不同，需要手动安装对应的驱动：

1
2
3
4
5
6
# One of the following:
$ npm install --save pg pg-hstore # Postgres
$ npm install --save mysql2
$ npm install --save mariadb
$ npm install --save sqlite3
$ npm install --save tedious # Microsoft SQL Server

连接数据库

要连接数据库，必须创建一个sequelize实例。可以通过向构造函数传参数或者通过连接字符串创建

1
2
3
4
5
6
7
8
9
10
const Sequelize = require('sequelize');

// Option 1: Passing parameters separately
const sequelize = new Sequelize('database', 'username', 'password', {
  host: 'localhost',
  dialect: /* one of 'mysql' | 'mariadb' | 'postgres' | 'mssql' */
});

// Option 2: Passing a connection URI
const sequelize = new Sequelize('postgres://user:pass@example.com:5432/dbname');

如果是SQLite数据库：

1
2
3
4
const sequelize = new Sequelize({
  dialect: 'sqlite',
  storage: 'path/to/database.sqlite'
});

我的代码：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
const Sequelize = require('sequelize');
const config = require('../config/config').db;

const sequelize = new Sequelize(config.database, config.user, config.password, {
    host: config.host,
    dialect: config.type,
    pool: {
        max: 5,
        min: 0,
        acquire: 30000,
        idle: 10000
    },
    define : {
        timestamps: false,
        freezeTableName: true
    }
});

export default sequelize;

pool：每个实例创建一个最多5个连接的连接池

model：禁用每个表的createdAt和updatedAt自动更新，表名不会自动加上s

为每张表创建model

model是Sequelize.Model的子类，通过两种方法创建

第一种：Sequelize.Model.init(attributes, options):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
const Model = Sequelize.Model;
class User extends Model {}
User.init({
  // attributes
  firstName: {
    type: Sequelize.STRING,
    allowNull: false
  },
  lastName: {
    type: Sequelize.STRING
    // allowNull defaults to true
  }
}, {
  sequelize,
  modelName: 'user'
  // options
});

第二种：sequelize.define:

1
2
3
4
5
6
7
8
9
10
11
12
13
const User = sequelize.define('user', {
  // attributes
  firstName: {
    type: Sequelize.STRING,
    allowNull: false
  },
  lastName: {
    type: Sequelize.STRING
    // allowNull defaults to true
  }
}, {
  // options
});

在内部实现上，sequelize.define 调用 Model.init

上面的代码定义了一个users表，字段有id，firstName，lastName，createdAt，updatedAt

其中，表名的s是自动加上去的，通过在options里设置freezeTableName: true可以防止自动添加，或者通过Sequelize的构造函数里的option.define设置成默认不自动添加。createdAt和updatedAt同理。id是自动添加的主键。

同步model和数据库

如果你想让Sequelize通过model的定义自动创建（或按照需要修改）数据库表，可以用sync方法:

1
2
3
4
5
6
7
8
// Note: 使用`force: true`如果表已存在会drop掉
User.sync({ force: true }).then(() => {
  // 现在users表和model的定义是一样的了
  return User.create({
    firstName: 'John',
    lastName: 'Hancock'
  });
});

一次同步所有model

如果不想每个model都调用一次sync()方法，你可以调用sequelize.sync()方法，这将会同步所有model

生产环境

在生产环境，你可能需要考虑在你的代码中使用迁移来代替调用sync()。官方文档：Migrations

简单的增删改查

一些简单的语句：

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
// Find all users
User.findAll().then(users => {
  console.log("All users:", JSON.stringify(users, null, 4));
});

// Create a new user
User.create({ firstName: "Jane", lastName: "Doe" }).then(jane => {
  console.log("Jane's auto-generated ID:", jane.id);
});

// Delete everyone named "Jane"
User.destroy({
  where: {
    firstName: "Jane"
  }
}).then(() => {
  console.log("Done");
});

// Change everyone without a last name to "Doe"
User.update({ lastName: "Doe" }, {
  where: {
    lastName: null
  }
}).then(() => {
  console.log("Done");
});

Promises和async/await

Sequelize支持Promises，用的是bluebird。可以使用async/await语法糖，也可以使用bluebird的API（ finally, tap, tapCatch, map, mapSeries, 等等）

数据类型

mysql常用的类型

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
Sequelize.STRING                      // VARCHAR(255)
Sequelize.STRING(1234)                // VARCHAR(1234)
Sequelize.STRING.BINARY               // VARCHAR BINARY
Sequelize.TEXT                        // TEXT
Sequelize.TEXT('tiny')                // TINYTEXT

Sequelize.INTEGER                     // INTEGER
Sequelize.BIGINT                      // BIGINT
Sequelize.BIGINT(11)                  // BIGINT(11)

Sequelize.FLOAT                       // FLOAT
Sequelize.FLOAT(11)                   // FLOAT(11)
Sequelize.FLOAT(11, 10)               // FLOAT(11,10)

Sequelize.DOUBLE                      // DOUBLE
Sequelize.DOUBLE(11)                  // DOUBLE(11)
Sequelize.DOUBLE(11, 10)              // DOUBLE(11,10)

Sequelize.DECIMAL                     // DECIMAL
Sequelize.DECIMAL(10, 2)              // DECIMAL(10,2)

Sequelize.DATE                        // DATETIME for mysql / sqlite, TIMESTAMP WITH TIME ZONE for postgres
Sequelize.DATE(6)                     // DATETIME(6) for mysql 5.6.4+. Fractional seconds support with up to 6 digits of precision
Sequelize.DATEONLY                    // DATE without time.
Sequelize.BOOLEAN                     // TINYINT(1)

Sequelize.ENUM('value 1', 'value 2')  // An ENUM with allowed values 'value 1' and 'value 2'

Sequelize.JSON                        // JSON column. PostgreSQL, SQLite and MySQL only.

Sequelize.BLOB                        // BLOB (bytea for PostgreSQL)
Sequelize.BLOB('tiny')                // TINYBLOB (bytea for PostgreSQL. Other options are medium and long)

Sequelize.UUID                        // UUID datatype for PostgreSQL and SQLite, CHAR(36) BINARY for MySQL (use defaultValue: Sequelize.UUIDV1 or Sequelize.UUIDV4 to make sequelize generate the ids automatically)

Sequelize.GEOMETRY                    // Spatial column.  PostgreSQL (with PostGIS) or MySQL only.
Sequelize.GEOMETRY('POINT')           // Spatial column with geometry type. PostgreSQL (with PostGIS) or MySQL only.
Sequelize.GEOMETRY('POINT', 4326)     // Spatial column with geometry type and SRID.  PostgreSQL (with PostGIS) or MySQL only.

In addition to the type mentioned above, integer, bigint, float and double also support unsigned and zerofill properties, which can be combined in any order: Be aware that this does not apply for PostgreSQL!

1
2
3
4
5
Sequelize.INTEGER.UNSIGNED              // INTEGER UNSIGNED
Sequelize.INTEGER(11).UNSIGNED          // INTEGER(11) UNSIGNED
Sequelize.INTEGER(11).ZEROFILL          // INTEGER(11) ZEROFILL
Sequelize.INTEGER(11).ZEROFILL.UNSIGNED // INTEGER(11) UNSIGNED ZEROFILL
Sequelize.INTEGER(11).UNSIGNED.ZEROFILL // INTEGER(11) UNSIGNED ZEROFILL

The examples above only show integer, but the same can be done with bigint and float

Usage in object notation:

1
2
3
4
5
6
7
8
// for enums:
class MyModel extends Model {}
MyModel.init({
  states: {
    type: Sequelize.ENUM,
    values: ['active', 'pending', 'deleted']
  }
}, { sequelize })