使用Sequelize连接数据库
Sequelize.js是一款针对nodejs的ORM框架。
使用nodejs连接过数据库的人肯定对数据库不陌生了。如果是直接链接,需要自己建立并管理连接,还需要手动编写sql语句。简单的项目到是无所谓,可是一旦项目设计的东西比较复杂,表比较多的时候整个sql的编写就非常的消耗精力。
在Java、c#等语言中已经有轻量的数据库框架或者解决方案了。在nodejs中我推荐Sequelize。它是一个很成熟的框架,在速度和性能上也非常有优势。而其中最关键的地方就在于,日常开发只需要管理对象的创建、查询方法的调用等即可,极少需要编写sql语句。这一个好处就是省去了复杂的sql语句维护,同时也避免了因sql而引起的不必要的bug。
Sequelize是针对node.js和io.js提供的ORM框架。具体就是突出一个支持广泛,配置和查询方法统一。它支持的数据库包括:PostgreSQL、 MySQL、MariaDB、 SQLite 和 MSSQL。
本文中测试以及API展示地址:github地址
演示
Sequelize的调用突出一个简单快捷。具体情况可以感受一下下面的代码。如果有过开发经验的可以略过。
Table1.findById(23); //select a,b,c,d from table1 where id=23; Table1.findAll({ where:{a:"test",b:76} }); //select a,b,c,d from table1 where a="test" and "b=76;
在单表查询的时候只需要简单的配置即可完成查询。是不是非常的简单方便呢?
连接数据库
Sequelize的连接需要传入参数,并且可以配置开启线程池、读写分库等操作。
简单的写法是这样的:new Sequelize("表名","用户名","密码",配置)
正常使用中很少使用到所有的参数,这里提供一个常用的模板,只需要修改自己使用的值即可。
const sequelize = new Sequelize('database', 'username', 'password', { host: 'localhost', //数据库地址,默认本机 port:'3306', dialect: 'mysql', pool: { //连接池设置 max: 5, //最大连接数 min: 0, //最小连接数 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.
数据类型检测
上面可以看到使用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略讲
Sequelize的API基本覆盖了常用的使用方式,其中单表查询常用的有一下几种。复杂的可以参考更多的API。
查询多条 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 ......
通过id查询 findById(id,opts)
这里默认数据的主键是id,查询的时候直接通过id查询数据。这里推荐在新建数据库的时候可以添加id作为唯一主键。
let model = await model.findById(12); //select a,b,c from model where id=12;
查询一条记录 findOne(opts)
根据条件查询记录,这里的条件一定要填写,不然就是返回第一条数据了。
let model = await model.findOne({ where:{id:12} }); //select a,b,c from model where id=12;
分页查询 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;
添加新数据 create(model,opts)
添加就非常的自在了。简单的只需要传入model对象即可。这里要保证model对象的属性和字段名要一致。如果不一致就会出错。也可以传入配置参数来增加条件等。
let model= { name:"test", token:"adwadfv2324" } await model.create(model); //insert into model (name,token) values("test","adwadfv2324");
查询,不存在就返回默认对象 findOrInitialize(opts)
opts.default 默认值对象
这个方法首先会查询数据库,如果没有结果就会返回参数中的default对象。这个比较适合返回默认对象之类的场景。
查询,不存在就新建一个 findOrCreate(opts)或者findCreateFind
这个方法用到的情况也比较多。通常用于自动创建不存在的数据。直接就返回了默认值。
有则更新,无则添加 upsert(model,opts) 或者 insertOrUpdate(model,opts)
根据主键或者唯一约束键匹配
常用于编辑的时候添加或者更新统一操作。
更新记录 update(model,opts)
就是最常用的更新方法,可以传入要更新的model对象,同时用配置参数有条件的区别要更新的对象。
删除记录 destroy(opts)
删除有2种情况,一种是物理删除。删除就从表中不存在了。另外一种就是设置paranoid,这个是虚拟删除,默认一个字段表示数据是否删除,查询的时候去掉这个条件即可查询到删除的数据。
恢复记录 restore(opts)
恢复多个实例,当启用paranoid时就可以使用这个方法将曾今删除的数据恢复了。
其他常用API
- 指定字段查询最大值 max("id",opts)
- 指定字段查询最小值 min("id",opts)
- 求和 sum("id",opts)
- 批量添加 bulkCreate([model],opts)
- 查表结构的信息 describe()
- 递增 increment("id",{by:1})
- 递减 decrement("id",{by:1})
- 统计查询个数 count(opts)
事务
Sequelize中的事务比较简单。但是如果有多个事务的话写出来的代码会非常的难看。这也算是Sequelize优化的比较差的地方了。
需要记得transaction参数要一致传递就可以了。其他就是一个正常的Promise调用。
//调用Sequelize初始化之后的sequelize对象 return sequelize.transaction(function (t) { //返回最终的Promise return User.create({ firstName: 'Abraham', lastName: 'Lincoln' }, {transaction: t}).then(function (user) { return user.setShooter({ firstName: 'John', lastName: 'Boothe' }, {transaction: t}); }); }).then(function (result) { //主动调用commit提交结果 return t.commit(); }).catch(function (err) { //主动回滚操作 return t.rollback(); });
多表联查
外键可能算是Sequelize中的一个难点了。这里涉及的东西稍微多一点,我们来慢慢捋一遍。
外键知识点
外键的定制作用----三种约束模式:
- district:严格模式(默认), 父表不能删除或更新一个被子表引用的记录。
- cascade:级联模式, 父表操作后,子表关联的数据也跟着一起操作。也是Sequelize的默认模式。
- set null:置空模式,前提外键字段允许为NLL, 父表操作后,子表对应的字段被置空。
使用外键的前提
在Sequelize中使用外键需要提前检查一下下面的这些选项,里面有一条出错就会导致设置失败。
- 表储存引擎必须是innodb,否则创建的外键无约束效果。
- 外键的列类型必须与父表的主键类型完全一致。
- 外键的名字不能重复。
- 已经存在数据的字段被设为外键时,必须保证字段中的数据与父表的主键数据对应起来。
使用示例---默认
默认情况下,主键使用的是主表的id字段,外键是使用的按照table+字段的方式建立的外键。一般情况下需要手动指定。
//主表指定关系 test1.hasMany(test2, { foreignKey: "pid",//外键名称 }); //子表指定关系 test2.belongsTo(test1, { foreignKey: "pid",//外键名称 });
默认就会在子表中添加一条外键记录,指向的就是主表的id。一般情况下这样就能够满足正常的使用了。比如一个主表记录商品信息,一个子表记录多个评论消息。
使用示例---自定义
如果主表使用的主键id并不能满足正常的使用,还可以指定某一个固定的字段作为主表中的约束关系。
tips:主表中如果不是使用id作为主要关系,自定义的字段必须添加索引等条件,作为依赖中的关系。test1.hasMany(test2, { foreignKey: "pid",//外键字段名 sourceKey: "pid",//主键字段名 }); test2.belongsTo(test1, { foreignKey: "pid",//关联名 targetKey:"pid"//自定义外键字段 }); //等待主键建立成功再建立子表的外键关系 setTimeout(() => { test2.sync({ force: true }); }, 2500);
使用示例---伪关系
实际使用的时候我还是倾向于这种关系。即表中关系已定的情况下仅仅指定外键关系。同步的时候仅仅同步表内容,不同步这个外键关系。
真正的建立可以使用手动建表的时候添加。或者也可以在自动建表结束后异步再执行一次外键关系的添加。test1.hasMany(test2, { foreignKey: "pid", sourceKey: "pid", constraints: false //不同步建立外键关系 }); test2.belongsTo(test1, { foreignKey: "pid", targetKey:"pid", constraints: false //不同步建立外键关系 });
示例
实际的操作部分大家可以看github中的test.js。github地址
单表操作
Sequelize在查询结果返回之后会返回一个它自定义的对象。这个对象是支持继续操作的,其中具体的值存放在datavalues中。不过可以放心的是在转化为字符串的时候是不会带有任何Sequelize的属性的。
//根据条件查询一条数据 let model = await test1.findOne({ where:{ id:5, name:"test" } }); //修改其中的name字段的值 model.name="更新"; //保存,会自动update数据库中的值 model.save();
联查
正常的使用过程中很少会说只需要查询一个表就能结果问题的。这里再说一下2个表查询的时候是怎么使用的。
这里的查询默认已经做好了外键的的关系。不过在使用的时候不做也是可以的,就是在查询的时候性能稍微不好而已。
//查询主表list的数据 //一条list中的数据对应多条item中的数据 let data = await models.List.findAll({ where:{id:5},//条件,这里jiashe只需查询一条 include: [{ model: models.Item, as:"items",//返回的对象修改成一个固定的名称 }] }); let list1=data[0];//返回的第一条数据就是要查询的数据 let list2=list1.items;//返回子表数据,items是自定义的名称
总结
上面的介绍已经解决了大多数情况下的查询等操作。而且我也相信,真的遇到了瓶颈,解决方案很可能也并不是在Sequelize方面,或者说主要不是Sequelize的问题。比如大数据量的时候分表操作,就涉及到了更多的知识点。
nodejs在做后端方面还处于发展阶段。希望有更多的前端能够接触并了解它。不仅仅在开发过程中对自己是一个增强,在长期的职业规划中也是一个很好的增强自己的武器。