node.js-ORM数据库框架sequelize使用总结 超时查询、缓存查询、多表关联查询
一、npm安装：install --save sequelizenpm install --save pg pg-hstorenpm install --save mysql2npm install --save sqlite3npm install --save tedious // MSSQL二、基本配置最好配置文件单独新建一个文件 dbconfig.jsva
·
一、npm安装：
install --save sequelize
npm install --save pg pg-hstore
npm install --save mysql2
npm install --save sqlite3
npm install --save tedious // MSSQL
二、基本配置
最好配置文件单独新建一个文件 dbconfig.js
var Sequelize = require('sequelize');
var mssql_data_db= new Sequelize(
"表明",
"登陆名",
"密码",{
dialect:'mssql',
host:'10.4.18.100',
port:1433
}
);
var mysql_db = new Sequelize(
"表明",
"登陆名",
"密码",{
dialect:'mysql',
host:'10.7.19.58',
port:3306
});
exports.mysql_db = mysql_db ;
exports.mssql_data_db= mssql_data_db;其中一些配置
比如数据量过大避免超时
var mysql_db = new Sequelize(
"表明",
"登陆名",
"密码",{
dialect:'mysql',
host:'10.7.19.58',
port:3306,
dialectOptions:{
requestTimeout: 999999,
// instanceName:'DEV'
} //设置MSSQL超时时间
});三、使用缓存查询
使用了轻量级的缓存 —node-cache
安装方法：1. npm install node-cache
使用：单独新建一个js，便于重复使用
var Q = require("q");
var NodeCache = require( "node-cache" );
var myCache = new NodeCache({ stdTTL: 3600*6});
var mssql_data_db= require('../../../confs/dbconfig').mssql_data_db;
var mysql_db= require('../../../confs/dbconfig').mysql_db ;
function mydb__cacheQuery(sql,cb){
var rtn = myCache.get(sql);
if(rtn) {
return cb(null,rtn)
}
mydb.query(sql).then(function (records) {
myCache.set(sql,records);
return cb(null,records);
})
};
function mysql_db__cacheQuery(sql,cb){
var rtn = myCache.get(sql);
if(rtn) {
//deferred.resolve(rtn);
//return deferred.promise;
return cb(null,rtn)
}
mysql_db.query(sql).then(function (records) {
myCache.set(sql,records);
return cb(null,records);
})
};
var mydb_denodeify = Q.denodeify(mydb__cacheQuery);
mydb.cacheQuery = mydb_denodeify;
var mysql_db_denodeify = Q.denodeify(mysql_db__cacheQuery);
mysql_db.cacheQuery = mysql_db_denodeify;
exports.mssql_data_db = mssql_data_db;
exports.mysql_db = mysql_db;熟悉的人可以看出，这个仅仅是对query查询加缓存。也就是将查询sql和查询后的数组保存在键值对中，sql变了，数据就不一样，最上面
var myCache = new NodeCache({ stdTTL: 3600*6});就是缓存时间，当然既然使用Sequelize了大对数就不会直接写sql了，原理也一样，这里就不多啰嗦了。
四.定义model
先给一个完成例子：
var mysql_db= require('../models/db').mysql_db;
var Sequelize = require('sequelize');
var uuid = require('node-uuid');
var sys_user = mysql_db.define('sys_user', {
KeyId: {
type: Sequelize.STRING(36),
field: 'KeyId',
primaryKey: true,
defaultValue:uuid.v1()
},
CorporationKeyId: {
type: Sequelize.STRING(36),
field: 'CorporationKeyId',
},
Account: {
type: Sequelize.STRING(32),
field: 'Account',
},
Nickname: {
type: Sequelize.STRING(32),
field: 'Nickname'
},
Status: {
type: Sequelize.INTEGER,
field: 'Status'
},
DomainAccount: {
type: Sequelize.STRING(45),
field: 'DomainAccount'
},
Email: {
type: Sequelize.STRING(200),
field: 'Email'
}
}, {
timestamps: false,
freezeTableName: true, // Model tableName will be the same as the model name
tableName: 'sys_user'
});
module.exports = sys_user;数据类型有：
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, 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) // 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.ARRAY(Sequelize.TEXT) // Defines an array. 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 // 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.UUID这个，亏我这多年还在用node-uui包创建！
模型大致知道基本就行，就不要在模型上添加外键约束，我们单独来创建约束！
五、关联查询
我先举个例子
还有表org_centaAreas、org_centaCorporations
然后就说怎么关联查询了:
//关联延时查询
contract_info.hasMany(contract_agent, {foreignKey: 'ContractKeyId'})
contract_info.hasMany(contract_property_info, {foreignKey: 'ContractKeyId'})
contract_info.belongsTo(sys_dictionary, {foreignKey: 'BigClientKeyId'});
contract_info.belongsTo(org_centaCorporations, {foreignKey: 'CorporationKeyId'});
org_centaCorporations.belongsTo(org_centaAreas, {foreignKey: 'Org_CentaAreaKeyID'});
contract_info.findAll({
include: [{
model: contract_agent
}, {
model: contract_property_info
}, {
model: sys_dictionary
}, {
model: org_centaCorporations,
include:[{
model: org_centaAreas,
}]
}], where: {
CorporationKeyId: sys_usermodel.CorporationKeyId
}, order: [
["AddTime", 'desc']
]
}).then(function (data) {
//do
})
转换成sql：
SELECT * FROM `contract_info` AS `contract_info`
LEFT OUTER JOIN `contract_agent` AS `contract_agents` ON `contract_info`.`KeyId` = `contract_agents`.`ContractKeyId` LEFT OUTER JOIN `contract_property_info` AS `contract_property_infos` ON `contract_info`.`KeyId` = `contract_property_infos`.`ContractKeyId` LEFT OUTER JOIN `sys_dictionary` AS `sys_dictionary` ON `contract_info`.`BigClientKeyId` = `sys_dictionary`.`KeyId` LEFT OUTER JOIN `org_centaCorporations` AS `org_centaCorporation` ON `contract_info`.`CorporationKeyId` = `org_centaCorporation`.`KeyId` LEFT OUTER JOIN `org_centaAreas` AS `org_centaCorporation.org_centaArea` ON `org_centaCorporation`.`Org_CentaAreaKeyID` = `org_centaCorporation.org_centaArea`.`KeyId` WHERE `contract_info`.`CorporationKeyId` = '572281c3-3d0b-11e6-883d-005056bbde85' ORDER BY `contract_info`.`AddTime` desc;这个特别注意 A关联B、A关联C 当C关联D适合就需要在
org_centaCorporations.belongsTo(org_centaAreas, {foreignKey: 'Org_CentaAreaKeyID'});
include这样写 两层include
{
include: [{
model: contract_agent
}, {
model: contract_property_info
}, {
model: sys_dictionary
}, {
model: org_centaCorporations,
include:[{
model: org_centaAreas,
}]
}还有一种就说 表A关联表A
//建立主外键连接
org_centaAreas.hasMany(org_centaCorporations, {foreignKey: 'Org_CentaAreaKeyID'});
org_centaCorporations.belongsTo(org_centaAreas, {foreignKey: 'Org_CentaAreaKeyID'});
org_centaCorporations.hasMany(submit_mail_log, {foreignKey: 'CorporationKeyId', as: "submit_mail_logstj"});
org_centaCorporations.hasMany(submit_mail_log, {foreignKey: 'CorporationKeyId', as: "submit_mail_logscs"});
org_centaCorporations.findAll({
include: [{
model: org_centaAreas
}, {
model: submit_mail_log,
as: 'submit_mail_logstj',
// where: { OperatingTime: {gt: new Date(nowdata+"-01"), lt: new Date(nowdata + "-31 23:59:59")},OperatingType:3 }
}, {
model: submit_mail_log,
as: 'submit_mail_logscs',
// where: { OperatingTime: {gt: new Date(nowdata+"-01"), lt: new Date(nowdata + "-31 23:59:59")},OperatingType:1 }
}],
where: ["(submit_mail_logstj.OperatingTime>= ? and submit_mail_logstj.OperatingTime <= ? and submit_mail_logstj.OperatingType=? )and( submit_mail_logscs.OperatingTime>= ? and submit_mail_logscs.OperatingTime <= ? and submit_mail_logscs.OperatingType=? ) ",
new Date(nowdata + "-01"), new Date(nowdata + "-31 23:59:59"), 3, new Date(nowdata + "-01"), new Date(nowdata + "-31 23:59:59"), 1],
order: "submit_mail_logscs.OperatingTime asc, submit_mail_logstj.OperatingTime asc"
}).then(function (results) {使用这种查询好处就是：
查询得到的数据是一层包一层
无需安装部署，在线快速体验 Byzer
更多推荐
0
0- 0
已为社区贡献1条内容
所有评论(0)