2. Database Layer with Mysql

Database Layer with Mysql

Driver Implementation

I used node-mysql driver to setup a connection to my mysql database. Which can be downloaded at https://github.com/felixge/node-mysql.

Preparation and Model Initialization

I have create a new model named DBLayer.js

config = require('configuration');
var mysql      = require('mysql');

module.exports = DBLayer;
function DBLayer() {
    this.connection = this.dbinit(config.applicationConfig.dataBase);
    
} 

Connection to Database

You can simply create a connection to the database by using the function mysql.createConnection(config).

DBLayer.prototype.dbinit = function(dbconfig) {
    var connection = mysql.createConnection({
          host     : dbconfig.host, 
          user     : dbconfig.user, 
          password : dbconfig.password, 
          database : dbconfig.database, 
          port    : dbconfig.port
        });
    return connection;
};

By using the connection-object, you can now start and stop a connection to the database to do select and insert-statements. In my case, i have stored the connection in my object attributes.

 DBLayer.prototype.connect = function() {
    this.connection.connect(function(err) {
        if (err) {
          //do some error handling here
        }
    });
};

DBLayer.prototype.end = function() {
    this.connection.end();
};

Select From Database

For selecting defined fields from given table and given select-condition, you use the query-function of the connection-object:

DBLayer.prototype.select = function(fields, tablename, resultFunction) {
 var queryString = 'SELECT '+ fields +' FROM ' + tablename;
 var query = this.connection.query(queryString, function(err, rows) {
  if (err) {
   //handleQueryError;
  }
  resultFunction(rows);
 });
 return query;
};

The resultFunction is the function executed after the query results are returned.
!ATTENSION!: You cannot return the result because the select-function might be left before the query is executed. A callback is used for dealing with the result: The "resultFunction" which is given to the function as parameter is called when the database has delivered the result.

Insert Into Database

To insert a javascript object (obj) for a defined table (tablename), you also use the query-function of the connection. You have an additional parameter "values" where you can put all values to be inserted.

As you might now from SQL or other query languages and persistence-frameworks, you have to use "INSERT INTO <TABLE> SET <COLUMN> = <VALUE>". You can add parameter to the statement using question marks.

DBLayer.prototype.insert = function(tablename, obj, resultFunction) {
 var queryString = 'INSERT INTO ' + tablename + ' SET ';
 var values = [0];
 var i = 0;
 for(var prop in obj) {
  queryString = queryString + prop + ' = ?, ';
  values.push();
  values[i] = obj[prop];
  i++;
 }
 queryString = queryString.substring(0, queryString.length-2);
 var query = this.connection.query(queryString, values, function(err, result) {
  if (err) {
   //handleQueryError;
  } ;
  if (result) {
   resultFunction(result.insertId); 
  };
 });
};