dborm-mysql
v2.2.3
Published
a NodeJs ORM for mysql
Downloads
64
Readme
DbORM
- DbOrm is a NodeJs ORM for mysql.
- All function return a promise.
- Each table should have a primary key named id.
Start
init
let dbConfig = {
"connectionLimit": 100,
"database": "bigviz",
"host": "10.11.11.11",
"user": "bigviz",
"password": "123456",
"multipleStatements": true
};
let db2ramFieldMap = {
"bigviz_user": {
"id": "id",
"subline": "subline",
"department": "department",
"nick": "nick",
"email": "email",
"last_project": "lastProject",
"details": "details"
}
};
let textDbFieldsMap = {
"bigviz_user": ['details']
}
let dbORM = require('dborm-mysql')({
dbConfig, // Database configuration information
db2ramFieldMap, // defined the table structure map from Ram to Database
textDbFieldsMap, // if these is a field in mysql table need to JSON.parse or JSON.stringify, you need to add it.
options: {
log: true, // when need to print the logs of sql, it is true, default false.
dbCode: 733, // when these is a Error, defined the code of Error, default 733.
noConvertDbCodes: [734, 735] // if the code of Error in noConvertDbCodes, it will not convert it to dbCode. default []
}
});
Main Function
Init one tableDao for getting the Orm functions.
let userDao = dbORM('bigviz_user');
add
Insert one row to table.
userDao.add({
nick: 'zhangsan',
email: '[email protected]',
details: {
a: 1,
b: 2
}
});
get
Get one row from table by id.
userDao.get(1);
getList
Get rows from table by query. The query may includes keyword, sort, offset, limit, selectFields, inFields and the fields of table.
- keyword: defined mysql "like" info.
- offset/limit: defined mysql "limit" info.
- selectFields: defined mysql "select" info.
- sort: defined mysql "order by" info.
// get userList where department is qa;
userDao.getList({
deparment: 'qa'
});
// get userList where department is qa and from offset=0, limit number is 20.
userDao.getList({
department: 'qa',
offset: 0,
limit: 20
});
// get userList where department in ['qa', 'developer']
userDao.getList({
inFields: {
department: ['qa', 'developer']
}
});
// get userList where department like "%qa%" and subline is 1
userDao.getList({
keyword: 'department:qa',
subline: 1
});
// get userList where department in ['qa', 'developer'] and order by id desc, department asc
userDao.getList({
keyword: "department:qa",
inFields: {
department: ['qa', 'developer']
},
sort: ['id:2', 'department:1'] // 1 is asc, 2 is desc
});
// get useList with selected fields where department in ['qa', 'developer']
userDao.getList({
selectFields: ['qa', 'developer'],
inFields: {
department: ['qa', 'developer']
}
});
// get useList with "as field" where userNick like "%zhang%" and department like "%qa%"
userDao.getList({
selectFields: ['qa', 'developer', 'nick as userNick'],
keyword: {
userNike: 'zhang',
department: 'qa'
}
});
update
Update one row from table by id. If you want to set some fields to NULL, you need to add the 'nullFields'
userDao.update({
nullFields: ['name'], // set the name field to NULL
department: 'qa',
info: {
a: 1
}
}, 1);
updateByIds
Update rows from table by ids.
userDao.updateByIds({
department: 'qa',
info: {
a: 1
}
}, [1, 2]);
updateByQuery
Update rows from table by query. The query may includes keyword, inFields and the fields of table.
userDao.updateByQuery({
department: 'qa',
info: {
a: 1
}
}, {
inFields: {
id: [1, 2]
}
});
getCount
Get the count of rows from table by query. The query may includes keyword, inFields and the fields of table.
// get count of userList from table where department in ['qa', 'developer']
userDao.getCount({
inFields: {
department: ['qa', 'developer']
}
});
delete
Delete the rows from table by query. The query may includes keyword, inFields and the fields of table.
// delete userList from table where department in ['qa', 'developer']
userDao.delete({
inFields: {
department: ['qa', 'developer']
}
});
deleteByIds
Delete the rows from table by table ids.
// delete userList from table where id in [1,2]
userDao.deleteByIds([1, 2]);
createBulk
Batch insert rows to table.
userDao.createBulk([
{nick: 'zhangsan', email: '[email protected]'},
{nick: 'lisi', email: '[email protected]'}
])
pageQuery
Query database for paging, you must have an exist sql for quering data. You can add keyword, sort, offset, limit based on the exist sql. The function return an object with count and list field.
userDao.pageQuery({
initSql: 'select nick, email from bigviz_user where id in (?)',
initParams: [[1, 2, 3, 4, 5, 6]],
offset: 0,
limit: 2,
sort: ['age:2'], // sort by the age
keyword: {
nick: 'xx' // search by the nick
}
});
// return {count: 4, list: [...]}
db
Get the db for original Query and Transaction processing.
let db = dbORM.db;
db.query(sql, params)
db.query('select * from bigviz_user where id = ?', [3]);
db.wrapTransaction(fn, nth)
Wrap fn for Transaction processing. The nth is the position of 'conn' in fn parameter list.
If these is a Error in fn, it will rollback all sql-querys with conn. Each sql-query must have a "conn" param.
db.wrapTransaction(async function(addUsers, deleteUserIds, conn){
await userDao.createBulk(addUsers, conn);
await userDao.deleteByIds(deleteUserIds, conn);
await db.query('select * from bigviz_user where id = ?', [3], conn);
}, 2);