Node.js MySQL model designing

Question

I'm developing a node.js application using MySQL database but i'm stuck with making models on the node.js side of my application. I've used mongoose before to produce schemas and use models to do database functions but i couldn't find such support for MySQL. Can anyone suggest a proper way to isolate my database functions in node.js like i could do with mongoose. here's my app.js and users model i'm using right now.

app file:

var express= require("express");
var bodyParser = require("body-parser");
var mysql = require("mysql");
var UserModel= require("./models/User.js")
var app=express();

var sql = mysql.createConnection({
    host: "localhost",
    user: "root",
    password: "1234",
    database: "dricm"
});

sql.connect(function (err) {
    if(err){
        console.log("error");
    }else{
        console.log("connected");
    }
});

app.set("views", "./views");

app.use(express.static("node_modules/bootstrap/dist"));
app.use(express.static("public"));
app.use(bodyParser.json());
app.use(bodyParser.urlencoded({ extended: false}));

app.get('/', function (req, res) {
    res.render("signup.jade");
});

app.post('/signup', function (req, res) {
    var obj= {
        username: req.body.username,
        password: req.body.password
    };
    UserModel.createUser(obj);
    res.redirect("/");
});

app.listen(3000, function () {
    console.log("server running at 3000");
});

User(probable model)

var mysql= require("mysql");
var bcrypt = require("bcryptjs");

var sql = mysql.createConnection({
    host: "localhost",
    user: "root",
    password: "1234",
    database: "dricm"
});

sql.connect(function (err) {
    if(err){
        console.log("error");
    }else{
        console.log("connected");
    }
});

var User= {

}

User.createUser = function createUser(newUser) {
    bcrypt.genSalt(10, function(err, salt){
        bcrypt.hash(newUser.password,salt, function (err, hash) {
            newUser.password = hash;
            var query = sql.query("INSERT INTO USERS set ?", newUser, function (err, res) {
                console.log(query);
                if(err) {
                    console.log("error");
                }
                else{

                    console.log(res.insertId);
                }
            });
        });
    });

}

module.exports= User;

Answer 1

What you are looking for is called an ORM (Object-relational mapping) Mongoose is one for MongoDB (Which is a NOSQL document oriented database)

There are other ORMs for relational databases that work with Node.js, The most popular right now is Sequelize which I have personally used and recommend.

With Sequelize you can put your models in different files just like Mongoose however in order to load them on, you need to add them with a simple script inside your index.js

Imagine the following Workspace:

--models/
----User.js
----Permissions.js
--index.js

And your model definitions are something like this:

User.js

const UserModel = (sequelize, Sequelize) => {
    const {INTEGER, STRING, FLOAT, BOOLEAN, DATE} = Sequelize
    const User = sequelize.define('User', {
        UserId: {type: INTEGER, primaryKey: true, autoIncrement: true},
        Username: {type: STRING, primaryKey: true, allowNull: false},
        Password: STRING
    })
    return User
}

module.exports = UserModel

Permissions.js

const PermissionsModel = (sequelize, Sequelize) => {
    const {INTEGER, STRING, FLOAT, BOOLEAN, DATE} = Sequelize
    const Permissions = sequelize.define('Permissions', {
        Role: {type: STRING, allowNull: false},
        ControllerAddress: {type: STRING, allowNull: false}
    })
    return Permissions
}

module.exports = PermissionsModel

Now you need to use the following script to use them inside your index.js

let normalizedPath = require('path').join(__dirname, "models")
    require('fs').readdirSync(normalizedPath).forEach((file) => {
        sequelize.import('./models/' + file)
    })
    let {User, Permissions} = sequelize.models

Now you can use the User and Permissions instances to control them and call functions like

User.create({Username, Password})