CODEWITHSUNDEEP
mysqlnode.jsmodels
Josh Smith
Josh Smith

Reputation: 15028

Writing MySQL Node.js models using node-mysql

I'm trying to find some good practices for writing MySQL models using node-mysql.

Here's what I have so far:

var client = app.settings.client; // Client is set in Express settings

var table = 'users';

var User = function() {

}

// Create the user with data from Facebook
User.prototype.createFromFacebook = function (name, first_name, last_name, email, location, gender, facebookId, facebookToken, callback) {
  client.query(
    'INSERT INTO ' + table +
    ' SET name = ?, first_name = ?, last_name = ?, email = ?, location = ?,' +
    ' gender = ?, facebook_id = ?, facebook_token = ?',
    [ name, first_name, last_name, email, location, gender, facebookId, facebookToken ],
    function selectCb(err, results, fields) {
      if(err) {
        callback(err);
      } else {
        callback(null, results);
      }
    }
  );
}

// Get user with just their email address
User.prototype.getByEmail = function (email, callback) {
  client.query(
    'SELECT * FROM ' + table +
    ' WHERE email = ?',
    [ email ],
    function selectCb(err, results, fields) {
      if(err) {
        callback(err);
      } else {
        callback(null, results);
      }
    }
  );
}
module.exports = User;

Any critiques or improvements on this would be awesome. Thanks!

Upvotes: 11

Views: 12057

Answers (3)

Prashant Biradar
Prashant Biradar

Reputation: 323

As a improvement, you can refactor your code

  1. Datasourceobject for db connection
const db = require(__dirname + '/myDatabaseInstance.js');
  1. Create UserSchema model user.js defines fields, data type, validation rules, etc.

user.schema.js

{
 "ID" : {
      "field" : "id",
       "type" : 'integer',
       "validation_rules" : "onlyInteger"     
  },
"name" : {
      "field" : "name",
      "type" : "string",
      "validation_rules": "usernameregex"   
 }
}
  1. Create Controller.js where you can have findAll, findById, create, update, delete, search API methods.
 class _controller {
        constructor(object, model_schema) {
            this._object = object;
            this._model_schema = model_schema;
        }
        setId(_id) {
            this.id = _id;
        }

        validateRules(validation_rules) {
            return new Promise((resolve, reject) => {
                let error = {};
                let validation = new Validator(this._object, validation_rules);
                if (validation.fails()) {
                    error.type = "validation";
                    error.errors = validation.errors.errors;
                    reject(error);
                } else {
                    resolve(true);
                }
            });
        };

        create() {
            return new Promise((resolve, reject) => {
                let response = {};
                let error = {};
                this.validation_rules= 
                UserSchema.getRules(this._model_schema).get_validation_rules;
                this
                    .validateRules(this.validation_rules )
                    .then(validation_result => {
                        db.insert(this.table, this._object)
                          .then(result => {
                                response.status = 201;
                                resolve(response);
                            });

                    })

            });
        }
})
  1. Create user.js which extends to common controller.js using user schema
 class user extends _controller {
        constructor(object) {
            super(object, "user_schema");
            this.object = object;
        }
  }

You can add new functionalities in user.js code.

Upvotes: 2

Akanksha Agrawal
Akanksha Agrawal

Reputation: 1

Create model name abc.js with following code:
var mysqlModel = require('mysql-model');
var MyAppModel = mysqlModel.createConnection({
  host: 'localhost',
    user: 'ghgh',
    password: 'gfhgfh',
    database: 'gfhgh',
});


movie = new MyAppModel({tableName: "users"});



Now use this as following :
var abc = require(__dirname+'/routes/abc')
movie.find('id','all', function(err, rows) {
//console.log(rows)
})
movie.query("SELECT * from users", function(err, rows) {   
});
var value  ={'name': "value3"};
movie.save(value);

Upvotes: 0

leeway
leeway

Reputation: 484

Your way looks just fine.

Make sure when you get an error your return:

if (err) {
return callback(err)
}

or just use the else like you're doing.

Just for the sake of critique, here is what I do:

var db = require(__dirname + '/myDatabaseInstance.js');

var create = function (vals, next) {
  db.query('Insert INTO users SET ?', vals, next);
};

var load = function (selector, next) {
 db.query('SELECT * FROM users WHERE ?', selector, function (err, vals) {
   if(err) { 
     return next(err);
    }
    //at this point you could return a user object
    //next(new User(vals[0]));
     //or just return the array.
    next(null, vals);
 });
};
module.exports = create;
module.exports = load;

I call it this way

var User = require(__dirname + '/user.js');
User.load({'id' : 1}, function (err, vals) {
  if (err) throw err;
  console.log(vals);
});

My personal preference is to not use instances for my models. I found it started to get messy when I had relations.

For example, say you have a blog with posts that are stored in a separate table related to the User. It is tempting to make the User object have an array of posts. Now if you change this posts array, you have to remember to save the changed User to the database. I kept forgetting, so I just stopped using instances.

Upvotes: 0

Related Questions