Topic

data access

Bearcat-dao — a SQL mapping dao framework

Overview

bearcat-dao is a DAO (domain access objects) framework for node.js. It implements SQL mapping as its main concept compared to O/R mapping, therefore SQL is still the main concern using with bearcat-dao, and bearcat-dao will map the datebase resultset into bearcat model object.

SQL mapping vs O/R mapping

Structured Query Language (SQL) has been around for a long time, relational database and SQL have been claimed to have stood the test of time. Moreover, we have experiences whereby the database and even the SQL itself have outlived the application source code, and even mulitiple versions of the source code.
SQL mapping is on the idea that there is value in relational database and SQL, developers write SQL and maps data resultsets into objects. Therefore, it is easy for enterprise application to optimize, reuse SQL, maintain.
In another way, O/R mapping enables developers to write mapping object to database table, ORM framework then generates the specific SQL to execute on the database. So, as we can see, developers have to take great knowledge of the ORM framework in order to use the database well, especially when optimization is needed.

Model

model definition is using bearcat model
therefore it is easy to be mapped into table and setup constraint, relation

for example, if we have a test table with single primary id 

1
2
3
4
5
create table test(
    id bigint(20) NOT NULL COMMENT 'id',	
    
    PRIMARY KEY (id)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

the we can define the following model 

1
2
3
4
5
6
7
var TestModel = function() {
    this.$mid = "testModel";
    this.$table = "test";
    this.id = "$primary;type:Number";
}
  
module.exports = TestModel;

in the TestModel, we use $table attribute to setup the mapping table name, in id attribute we use primary to mark it as a primary key, then we add with a type constraint

Relation

Tables in relational database can have relations, there are one-to-one relation, one-to-many relation, many-to-many relation

One-to-one relation

One-to-one relation means in two models, one model has the reference of the other model

for example, if we have a test1 table with primary id and reference id of the test2 table 

1
2
3
4
5
6
create table test1(
    id bigint(20) NOT NULL COMMENT 'id',	
    rid bigint(20) NOT NULL COMMENT 'reference to test2 id',	
      
    PRIMARY KEY (id)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
1
2
3
4
5
create table test2(
    id bigint(20) NOT NULL COMMENT 'id',	
    
    PRIMARY KEY (id)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

then we can define the following two models 

1
2
3
4
5
6
7
8
var Test1Model = function() {
    this.$mid = "test1Model";
    this.$table = "test1";
    this.id = "$primary;type:Number";
    this.rid = "$type:Object;ref:test2Model"
}
  
module.exports = Test1Model;
1
2
3
4
5
6
7
var Test2Model = function() {
    this.$mid = "test2Model";
    this.$table = "test2";
    this.id = "$primary;type:Number";
}
  
module.exports = Test2Model;

as we can see, in Test1Model.rid attribute we use ref:test2Model to set the reference to test2Model

One-to-many relation

One-to-many relation means one model refer to the array of other model. In the real world, for example, we can have a blog, and a blog have many commnets, so blog and comment are one-to-many relation. 

1
2
3
4
5
6
7
8
var Test1Model = function() {
    this.$mid = "test1Model";
    this.$table = "test1";
    this.id = "$primary;type:Number";
    this.rid = "$type:Array;ref:test2Model"
}
  
module.exports = Test1Model;

therefore, in the above example, we simply modify the rid attribute type to Array, it becomes a one-to-many relation

Many-to-many relation

many-to-many relation can be spilted into two one-many relation through middle table

SQL template

When writing complex sql, it is not quite well writing as a String, the better way is using SQL template.

write SQL tempalte is easy

for example, we can define SQL template with id testResultSql

1
2
3
sql testResultSql
select * from test 
end

then we can use this sql in dao, like this 

1
2
3
domainDaoSupport.getList("$testResultSql", null, "testModel", function(err, results) {
     // results is testModel type array
});

in domainDaoSupport.getList api, the first argument can be SQL tempalte id, the second argument is the SQL arguments, the third argument is the SQL result mapping model id, then in the callback function, we can get the results which are already mapped with testModel array

Moreover, SQL template can include other SQL template

for example 

1
2
3
4
5
6
7
sql testResultSql
select * from ${testResultTable} 
end
sql testResultTable
test
end

then testResultSql template is equal to the above

ResultSet mapping

ResultSet is an array of field/value objects, therefore the process of mapping resultSet is like filling objects with specific key/value pairs, to make the key match with the resultSet, we can use prefix in model magic attribute value or use prefix in model attribute to mark all attributes in this model will be prefixed

for example, if you query for a resultSet like this 

1
2
3
4
5
6
7
[{
	"id": 1,
	"title": "blog_title",
	"content": "blog_content",
	"create_at": 1234567,
	"update_at": 1234567
}]

then mapping model can be like this 

1
2
3
4
5
6
7
8
9
10
11
12
var BlogModel = function() {
    this.$mid = "blogModel";
    this.$table = "ba_blog";
    this.id = "$primary;type:Number";
    this.aid = "$type:Number";
    this.title = "$type:String";
    this.content = "$type:String";
    this.create_at = "$type:Number";
    this.update_at = "$type:Number";
}
  
module.exports = BlogModel;

if your resultSet is prefixed with blog_ like this 

1
2
3
4
5
6
7
[{
	"blog_id": 1,
	"blog_title": "blog_title",
	"blog_content": "blog_content",
	"blog_create_at": 1234567,
	"blog_update_at": 1234567
}]

then mapping model will be like this 

1
2
3
4
5
6
7
8
9
10
11
12
13
var BlogModel = function() {
    this.$mid = "blogModel";
    this.$table = "ba_blog";
    this.$prefix = "blog_";
    this.id = "$primary;type:Number";
    this.aid = "$type:Number";
    this.title = "$type:String";
    this.content = "$type:String";
    this.create_at = "$type:Number";
    this.update_at = "$type:Number";
}
  
module.exports = BlogModel;

just add this.$prefix model attribute

DAO

DAO is short for domain access object, we can use DAO objects to manage database

bearcat-dao provides domainDaoSupport wrapping basic sql and cache operations
add it with properties dependency injection, and init it by invoking initConfig method
then you can use domainDaoSupport convenient methods to wrap your own daos

simpleDao.js

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
var SimpleDao = function() {
    this.$id = "simpleDao";
    this.$init = "init";
    this.$domainDaoSupport = null;
}
  
SimpleDao.prototype.init = function() {
    // init with SimpleModel id to set up model mapping
    this.domainDaoSupport.initConfig("simpleModel");
}
  
// query list all
// callback return mapped SimpleModel array results
SimpleDao.prototype.getList = function(cb) {
    var sql = ' 1 = 1';
    this.$domainDaoSupport.getListByWhere(sql, null, null, cb);
}
  
module.exports = SimpleDao;

api reference for domainDaoSupport

Configuration

add bearcat-dao to your project 

1
npm install bearcat-dao --save

modify context.json used by your project
placeholds can be nicely used to switch between contexts 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
"dependencies": {
	"bearcat-dao": "*"
},
"beans": [{
    "id": "mysqlConnectionManager",
    "func": "node_modules.bearcat-dao.lib.connection.sql.mysqlConnectionManager",
    "props": [{
        "name": "port",
        "value": "${mysql.port}"
    }, {
        "name": "host",
        "value": "${mysql.host}"
    }, {
        "name": "user",
        "value": "${mysql.user}"
    }, {
        "name": "password",
        "value": "${mysql.password}"
    }, {
        "name": "database",
        "value": "${mysql.database}"
    }]
}, {
    "id": "redisConnectionManager",
    "func": "node_modules.bearcat-dao.lib.connection.cache.redisConnectionManager",
    "props": [{
        "name": "port",
        "value": "${redis.port}"
    }, {
        "name": "host",
        "value": "${redis.host}"
    }]
}]

if you do not use redis, you can remove redisConnectionManager definition

Transaction

bearcat-dao provides transaction support based on bearcat AOP. The aspect is transactionAspect which provides around advice, when target transaction method calls cb function with err, rollback will be emited, otherwise it will commit the operations.

The pointcut defined is: 

1
"pointcut": "around:.*?Transaction$"

Therefore, any POJO method match this pointcut can a transcation method
Since transaction must be within the same connection, in Bearcat-dao it is transactionStatus, daos under the transaction method must hold the same transactionStatus 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
SimpleService.prototype.testMethodTransaction = function(cb, txStatus) {
    var self = this;
  
    this.simpleDao1.transaction(txStatus).addPerson(['aaa'], function(err, results) {
        if (err) {
             return cb(err); // if err occur, rollback will be emited
        }
  
        self.simpleDao2.transaction(txStatus).getList([1, 2], function(err, results) {
            if (err) { 
                 return cb(err); // if err occur, rollback will be emited
            }
            cb(null, results); // commit the operations
        });
    });
}

Enable Debug Mode

run with BEARCAT_DEBUG flag true 

1
BEARCAT_DEBUG=true node xxx.js

Examples

Caught a mistake or want to contribute to the documentation? Fork this site on Github!