dborm-mysql

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: '[e-mail]',
    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: '[e-mail]'},
    {nick: 'lisi', email: '[e-mail]'}
])

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);