Sequelize操作MySQL基本用法

Sequelize.js是一款针对nodejs的ORM框架。

使用nodejs连接过数据库的人肯定对数据库不陌生了。如果是直接链接，需要自己建立并管理连接，还需要手动编写sql语句。简单的项目到是无所谓，可是一旦项目设计的东西比较复杂，表比较多的时候整个sql的编写就非常的消耗精力。

在nodejs中我推荐Sequelize。它是一个很成熟的框架，在速度和性能上也非常有优势。而其中最关键的地方就在于，日常开发只需要管理对象的创建、查询方法的调用等即可，极少需要编写sql语句。这一个好处就是省去了复杂的sql语句维护，同时也避免了因sql而引起的不必要的bug。

Sequelize是针对node.js和io.js提供的ORM框架。具体就是突出一个支持广泛，配置和查询方法统一。它支持的数据库包括：PostgreSQL、 MySQL、MariaDB、 SQLite 和 MSSQL。

一：连接数据库

Sequelize的连接需要传入参数，并且可以配置开启线程池、读写分库等操作。

简单的写法是这样的:new Sequelize("表名","用户名","密码",配置)

正常使用中很少使用到所有的参数，这里提供一个常用的模板，只需要修改自己使用的值即可。

const sequelize = new Sequelize('database', 'username', 'password',  {
  host: 'localhost',    //数据库地址,默认本机
  port:'3306',
  dialect: 'mysql',
  pool: {   //连接池设置
    max: 5, //最大连接数
    min: 0, //最小连接数
    idle: 10000
  },
 });

还可以这么写

/**
 * 使用的数据库：mysql
 * 数据库名：root
 * 数据库密码：123456
 * 数据库端口号：3306
 * 链接的数据库名：mysql
 * 修改数据库密码：
 */
const url = 'mysql://root:123456@localhost:3306/mysql';
const mysql = new Sequelize(url, {
    define: {
        timestamps: false
    },
    // 连接池设置
    pool: {
        max: 5,
        min: 0,
        acquire: 30000,
        idle: 10000
    }
})

下面是详细的配置参数

const sequelize = new Sequelize('database', 'username', 'password', {
  // 数据库类型，支持: 'mysql', 'sqlite', 'postgres', 'mssql'
  dialect: 'mysql',
  // 自定义链接地址，可以是ip或者域名，默认本机：localhost
  host: 'my.server.tld',
  // 自定义端口，默认3306
  port: 12345,
  // postgres使用的参数,连接类型，默认：tcp
  protocol: null,
  // 是否开始日志，默认是用console.log
  // 建议开启，方便对照生成的sql语句
  logging: true,
  // 默认是空
  // 支持: 'mysql', 'postgres', 'mssql'
  dialectOptions: {
    socketPath: '/Applications/MAMP/tmp/mysql/mysql.sock',
    supportBigNumbers: true,
    bigNumberStrings: true
  },
  // sqlite的存储位置,仅sqlite有用
  // - 默认 ':memory:'
  storage: 'path/to/database.sqlite',

  // 是否将undefined转化为NULL
  // - 默认: false
  omitNull: true,
  // pg中开启ssl支持
  // - 默认: false
  native: true,
  // 数据库默认参数,全局参数
  define: {
    underscored: false
    freezeTableName: false,
    charset: 'utf8',
    dialectOptions: {
      collate: 'utf8_general_ci'
    },
    timestamps: true
  },
  // 是否同步
  sync: { force: true },
  // 连接池配置
  pool: {
    max: 5,
    idle: 30000,
    acquire: 60000,
  },
  isolationLevel: Transaction.ISOLATION_LEVELS.REPEATABLE_READ
})

二：定义模型对象

在使用之前一定要先创建模型对象。就是数据库中表的名称、使用到的字段、字段类型等。

这里有一个推荐的开发方式。先在nodejs中将对象创建出来，然后调用Sequelize的同步方法，将数据库自动创建出来。这样就避免了既要写代码建表，又要手工创建数据库中的表的操作。只需要单独考虑代码中的对象类型等属性就好了。

如果数据库中已经建好了表，并且不能删除，这个时候就不能自动创建了，因为创建的时候会删除掉旧的数据。

下面是简单的对象创建多数情况下这样就可以了。

const users = db.define('t_user'/*自定义表名*/, {
    id: {
        type: Sequelize.INTEGER,
        primaryKey: true,       //主键
        autoIncrement: true,    //自增
        comment: "自增id"       //注释:只在代码中有效
    },
    //用户名
    username: {
        type: Sequelize.STRING,
        validate:{
	        isEmail: true,   //类型检测,是否是邮箱格式
        }
    },
    //密码
    pwd: {
        type: Sequelize.STRING(10),
        allowNull: false,//不允许为null
    },
    //状态
    status: {
        type: Sequelize.INTEGER,
         defaultValue: 0,//默认值是0
    },
    //昵称
    nickname: {
        type: Sequelize.STRING
    },
    //token
    token: {
        type: Sequelize.UUID
    },
    create_time: {
        type: Sequelize.DATE,
        defaultValue: Sequelize.NOW
    }
}, {
    //使用自定义表名
    freezeTableName: true,
    //去掉默认的添加时间和更新时间
    timestamps: false,
    indexes:[
	    //普通索引,默认BTREE
        {
            unique: true,
            fields: ['pid']
        },
     ]
});

//同步:没有就新建,有就不变
// users.sync();
//先删除后同步
users.sync({
    force: true
});

三：数据类型

对象的创建，里面用到了对象的各种类型。这里再介绍一下类型的具体使用方式。

Sequelize.STRING 		//字符串,长度默认255,VARCHAR(255)
Sequelize.STRING(1234)  //设定长度的字符串,VARCHAR(1234)
Sequelize.STRING.BINARY   //定义类型VARCHAR BINARY
Sequelize.TEXT           //长字符串,文本 TEXT
Sequelize.TEXT('tiny')   //小文本字符串,TINYTEXT

Sequelize.INTEGER      //int数字,int
Sequelize.BIGINT       //更大的数字,BIGINT
Sequelize.BIGINT(11)   //设定长度的数字,BIGINT(11)

Sequelize.FLOAT        //浮点类型,FLOAT
Sequelize.FLOAT(11)     //设定长度的浮点,FLOAT(11)
Sequelize.FLOAT(11, 12)  //设定长度和小数位数的浮点,FLOAT(11,12)

Sequelize.REAL     //REAL  PostgreSQL only.
Sequelize.REAL(11) // REAL(11)    PostgreSQL only.
Sequelize.REAL(11, 12)  // REAL(11,12) PostgreSQL only.

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

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) // mysql 5.6.4+支持,分秒精度为6位
Sequelize.DATEONLY   // 仅日期部分
Sequelize.BOOLEAN   // int类型,长度为1,TINYINT(1)

Sequelize.ENUM('value 1', 'value 2')  // 枚举类型
Sequelize.ARRAY(Sequelize.TEXT)  //PostgreSQL only.
Sequelize.ARRAY(Sequelize.ENUM)  //  PostgreSQL only.

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

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

Sequelize.UUID  // PostgreSQL和SQLite的数据类型是UUID, MySQL是CHAR(36)类型

Sequelize.CIDR  // PostgreSQL中的CIDR类型
Sequelize.INET   // PostgreSQL中的INET类型
Sequelize.MACADDR  // PostgreSQL中的MACADDR类型

Sequelize.RANGE(Sequelize.INTEGER)    //PostgreSQL only.
Sequelize.RANGE(Sequelize.BIGINT)     // PostgreSQL only.
Sequelize.RANGE(Sequelize.DATE)       //PostgreSQL only.
Sequelize.RANGE(Sequelize.DATEONLY)   //PostgreSQL only.
Sequelize.RANGE(Sequelize.DECIMAL)    //PostgreSQL only.

Sequelize.ARRAY(Sequelize.RANGE(Sequelize.DATE)) // PostgreSQL only.

Sequelize.GEOMETRY   //PostgreSQL (with PostGIS) or MySQL only.
Sequelize.GEOMETRY('POINT')  // PostgreSQL (with PostGIS) or MySQL only.
Sequelize.GEOMETRY('POINT', 4326)// PostgreSQL (with PostGIS) or MySQL only.

对照翻译解释一下：

STRING() - 变长字符串
 CHAR() - 定长字符串
 TEXT() - 指定为文本列
 INTEGER() - 整型
 BIGINT() - 长整型
 FLOAT() - 浮点数
 REAL() - 浮点数
 DOUBLE() - 双精度浮点数
 DECIMAL() - 小数
 BOOLEAN() - 布尔
 TIME() - 时间类型
 DATE() - 日期时间类型
 DATEONLY() - 日期类型
 HSTORE() - 键/值类型
 JSON() - JSON字符串类型
 JSONB() - JSONB类型
 NOW() - 时间默认值
 BLOB() - 二进制类型
 RANGE() - Range类型
 UUID() - UUID类型
 UUIDV1() - UUID v1 默认值
 UUIDV4() - UUID v4 默认值
 VIRTUAL() - 虚拟值
 ENUM() - 枚举
 ARRAY() - 数组
 GEOMETRY() - 几何类型
 GEOGRAPHY() - 地理类型

四：数据类型检测

上面可以看到使用validate字段去验证字段的值是否符合标准，这样就可以在入库之前就能知道数据是否符合规则。否则贸然将陌生的数据存入数据库就好像将陌生人带到家里一样，是否安全全靠缘分啊。

Sequelize内置支持的验证还是非常的多的，如果这些都不满意，还可以自己定义一个。

validate: {
    is: ["^[a-z]+$",'i'],     // 全匹配字母
    is: /^[a-z]+$/i,          // 全匹配字母，用规则表达式写法
    not: ["[a-z]",'i'],       // 不能包含字母
    isEmail: true,            // 检查邮件格式
    isUrl: true,              // 是否是合法网址
    isIP: true,               // 是否是合法IP地址
    isIPv4: true,             // 是否是合法IPv4地址
    isIPv6: true,             // 是否是合法IPv6地址
    isAlpha: true,            // 是否是字母
    isAlphanumeric: true,     // 是否是数字和字母
    isNumeric: true,          // 只允许数字
    isInt: true,              // 只允许整数
    isFloat: true,            // 是否是浮点数
    isDecimal: true,          // 是否是十进制书
    isLowercase: true,        // 是否是小写
    isUppercase: true,        // 是否大写
    notNull: true,            // 不允许为null
    isNull: true,             // 是否是null
    notEmpty: true,           // 不允许为空
    equals: 'specific value', // 等于某些值
    contains: 'foo',          // 包含某些字符
    notIn: [['foo', 'bar']],  // 不在列表中
    isIn: [['foo', 'bar']],   // 在列表中
    notContains: 'bar',       // 不包含
    len: [2,10],              // 长度范围
    isUUID: 4,                // 是否是合法 uuids
    isDate: true,             // 是否是有效日期
    isAfter: "2011-11-05",    // 是否晚于某个日期
    isBefore: "2011-11-05",   // 是否早于某个日期
    max: 23,                  // 最大值
    min: 23,                  // 最小值
    isArray: true,            // 是否是数组
    isCreditCard: true,       // 是否是有效信用卡号
    // 自定义规则
    isEven: function(value) {
    if(parseInt(value) % 2 != 0) {
        throw new Error('请输入偶数!')
    }
}

五：API略讲

以下用的查询条件解释：

$and: {a: 5}           // AND 连接: (a = 5)
$or: [{a: 5}, {a: 6}]  // OR 或者(a = 5 OR a = 6)
$gt: 6,                // 大于 id > 6
$gte: 6,               // 大于等于 id >= 6
$lt: 10,               // 小于 id < 10
$lte: 10,              // 小于等于 id <= 10
$ne: 20,               // 不等于 id != 20
$between: [6, 10],     // 中间值 BETWEEN 6 AND 10
$notBetween: [11, 15], // 中间值排除 NOT BETWEEN 11 AND 15
$in: [1, 2],           // 单值数组 IN [1, 2]
$notIn: [1, 2],        // 不在数组中 NOT IN [1, 2]
$like: '%hat',         // 模糊匹配 LIKE '%hat'
$notLike: '%hat'       // 模糊匹配的反向 NOT LIKE '%hat'
$iLike: '%hat'         // PG的模糊 ILIKE '%hat' (case insensitive)  (PG only)
$notILike: '%hat'      // PG的迷糊反向 NOT ILIKE '%hat'  (PG only)
$overlap: [1, 2]       // PG的操作 && [1, 2] (PG array overlap operator)
$contains: [1, 2]      // PG的操作 @> [1, 2] (PG array contains operator)
$contained: [1, 2]     // PG的操作 <@ [1, 2] (PG array contained by operator)
$any: [2,3]            // PG的操作 ANY ARRAY[2, 3]::INTEGER (PG only)

用的查询方法参数opts解释

Model.findAll({
  where: {          //条件判断
    attr0: 5,       //等值判断
    attr1: {
      $gt: 50       //条件比较
    },
  },
  attributes:["attr1"], //返回的字段
  order:["id","desc"],  //排序
  limit:10,             //返回个数,分页用到
  offset:0,             //起始位置,分页用到
  paranoid:false,       //是否返回虚拟删除的记录
  include:[{            //多表查询使用到的另外一个表
      model:model,      //表对象
      as:"asl",         //别名
      where:{},         //条件
      attributes:[],    //返回的属性
  }],  
  logging:false,        //日志,用于打印     
})

具体使用：

Sequelize的API基本覆盖了常用的使用方式，其中单表查询常用的有以下几种。复杂的可以参考更多的API。

1、查询多条 findAll(opts) 或者 all(opts)

查询用的参数普遍通用，只有部分API的有特殊参数。这里展示一次常用参数，下面就略过了。

let list = await model.findAll({
	where:{
		id:{$gt:10},//id大于10的
		name:"test"  //name等于test
	},
	order:[
		"id",   //根据id排序
		["id","desc"]//根据id倒序
	],
	limit:10,//返回个数
	offset:20,//起始位置,跳过数量
	attributes:["attr1","attr2"], //返回的字段
});
//select attr1,attr2 from model where ......

2、通过id查询 findById(id,opts)

这里默认数据的主键是id，查询的时候直接通过id查询数据。这里推荐在新建数据库的时候可以添加id作为唯一主键。

let model = await model.findById(12);
//select a,b,c from model where id=12;

3、查询一条记录 findOne(opts)

根据条件查询记录，这里的条件一定要填写，不然就是返回第一条数据了。

let model = await model.findOne({
	where:{id:12}
});
//select a,b,c from model where id=12;

4、分页查询 findAndCount(opts) 或者 findAndCountAll

分页查询恐怕是另外一个常用方法了。任何一个列表都有需要分页的时候。

这个方法会同时执行2个语句。

let data = await model.findAndCount({
	limit:10,//每页10条
	offset:0*10,//第x页*每页个数
	where:{}
});
let list = data.rows;
let count = data.count;
//select count(*) from model where ...;
//select a,b,c from model where .... limit 0,10;

5、添加新数据 create(model,opts)

添加就非常的自在了。简单的只需要传入model对象即可。这里要保证model对象的属性和字段名要一致。如果不一致就会出错。也可以传入配置参数来增加条件等。

let model= {
	name:"test",
	token:"adwadfv2324"
}
 await model.create(model);
//insert into model (name,token) values("test","adwadfv2324");

6、查询,不存在就返回默认对象 findOrInitialize(opts)

opts.default 默认值对象

这个方法首先会查询数据库，如果没有结果就会返回参数中的default对象。这个比较适合返回默认对象之类的场景。

7、查询,不存在就新建一个 findOrCreate(opts)或者findCreateFind

这个方法用到的情况也比较多。通常用于自动创建不存在的数据。直接就返回了默认值。

8、有则更新,无则添加 upsert(model,opts) 或者 insertOrUpdate(model,opts)

根据主键或者唯一约束键匹配

常用于编辑的时候添加或者更新统一操作。

9、更新记录 update(model,opts)

就是最常用的更新方法，可以传入要更新的model对象，同时用配置参数有条件的区别要更新的对象。

10、删除记录 destroy(opts)

删除有2种情况，一种是物理删除。删除就从表中不存在了。另外一种就是设置paranoid，这个是虚拟删除，默认一个字段表示数据是否删除，查询的时候去掉这个条件即可查询到删除的数据。

11、恢复记录 restore(opts)

恢复多个实例，当启用paranoid时就可以使用这个方法将曾今删除的数据恢复了。

其他常用API

1. 指定字段查询最大值 max("id",opts)
2. 指定字段查询最小值 min("id",opts)
3. 求和 sum("id",opts)
4. 批量添加 bulkCreate([model],opts)
5. 查表结构的信息 describe()
6. 递增 increment("id",{by:1})
7. 递减 decrement("id",{by:1})
8. 统计查询个数 count(opts)

count - 统计数据库中的元素数

count可以统计数据库中的元素数：

Project.count().then(function(c) {
  console.log("There are " + c + " projects!")
})

Project.count({ where: ["id > ?", 25] }).then(function(c) {
  console.log("There are " + c + " projects with an id greater than 25.")
})

max - 查找指定表中最大值

// 数据库中有3条记录，年龄分别是 10, 5, 40
Project.max('age').then(function(max) {
  // 会返回 40
})

Project.max('age', { where: { age: { lt: 20 } } }).then(function(max) {
  // 会返回 10
})

min - 查找指定表中最小值

// 数据库中有3条记录，年龄分别是 10, 5, 40
Project.min('age').then(function(min) {
  // 会返回 5
})

Project.min('age', { where: { age: { $gt: 5 } } }).then(function(min) {
  // 会返回 10
})

sum - 对指定属性求和

// 数据库中有3条记录，年龄分别是 10, 5, 40
Project.sum('age').then(function(sum) {
  // 会返回 55
})

Project.sum('age', { where: { age: { $gt: 5 } } }).then(function(sum) {
  // 会返回 50
})

基础的语法差不多这些

 

交流

1、QQ群：可添加qq群共同进阶学习： 进军全栈工程师疑难解  群号:   856402057

2、公众号：公众号「进军全栈攻城狮」 ，对前端技术保持学习爱好者。我会经常分享自己所学所看的干货，在进阶的路上，共勉！