node-mysql-basemodel

Node-mysql-basemodel is an easy to use model from which other models can be extended from. It is designed to provide basic out-of-the-box functionality without making assumptions or providing a complicated API. The focus is very much on development speed and providing an easy to reason about way to structure your application's models. It is intended to be used with node-mysql. It requires IO.js or Node 4.* for es6 support however porting it over to es5 shouldn't be too much trouble.

##Installation

npm install node-mysql-basemodel

##Usage

This module comes with many built on methods for simple and regularly used queries. All methods will return promises. All methods ultimately flow into query(sql). When extending if you have more complicated SQL simply write out whatever SQL you need and pass it into query.

'use strict';

var Base = require('node-mysql-basemodel'),
    pool = require('path/to/dbpool');

class UsersModel extends Base {

    someNonBasicQuery(options) {

        return this.query(`
            ...
        `);

    }

}

module.exports = new UsersModel({
    name: 'users',
    pool: pool
});

##Methods

###model.query(sql)

####Description

Model.query() is the basic method all other methods ultimately call. It takes a single argument which is a sql string it uses to query the database. This is the method you will want to use to make your own custom queries.

####Options

• sql [String] the raw sql to make to query

####Returns

Returns an array containing the results of your query.

####Example

UsersModel.query('SELECT * FROM users u JOIN pets p ON u.id=p.owner_id WHERE p.type = "dog"')
.then((results) => {
    ...
})
.catch((err) => {
    ...
})

###model.all()

####Description

Model.all() returns all records in a table.

####Returns

Returns an array containing all the records in the associated table.

####Example

UsersModel.all()
.then((results) => {
    ...
})
.catch((err) => {
    ...
})

###model.insert(options)

####Description

Model.insert() is used to insert a single record into the model's associated table.

####options

• options [Object] an object with a fields property specifying the data to insert.

####Returns

Returns an array containing the mysql response with the insertId.

####Example

UsersModel.insert({
    fields: {
        name      : 'Bob',
        occupation: 'Plumber',
        active    : 0
    }
})
.then((results) => {
    ...
})
.catch((err) => {
    ...
})

###model.insertMulti(options)

####Description

Inserts multiple records into the model's associated table.

####Options

• options [Object] an object containing a fields property set to an array of records to insert.

####Returns

Returns an array containing information relating to the bulk insert.

####Example

UsersModel.insertMulti({
    fields: [{
        name      : 'Bob',
        occupation: 'Plumber',
        active    : 0
    },
    {
        name      : 'Jim',
        occupation: 'Coach',
        active    : 1
    }]
})
.then((results) => {
    ...
})
.catch((err) => {
    ...
})

###model.insertAndReturnData(options)

####Description

Inserts into the model's associated table and retrieves the newly inserted record and returns it.

####Options

• options [Object] an object containing a fields property specifying data to insert.

####Returns

Returns an array with a single object containing the newly inserted record's data.

####Example

UsersModel.insertAndReturnData({
    fields: {
        name      : 'Bob',
        occupation: 'Plumber',
        active    : 0
    }
})
.then((results) => {
    ...
})
.catch((err) => {
    ...
})

###model.insertMultiAndReturnData(options)

####Description

Inserts multiple records into the model's associated table and returns the newly inserted records.

####Options

• options [Object] an object containing a fields property set to an array of records to insert.

####Returns

Returns an array containing the newly inserted records.

####Example

UsersModel.insertMultiAndReturnData({
    fields: [{
        name      : 'Bob',
        occupation: 'Plumber',
        active    : 0
    },
    {
        name      : 'Jim',
        occupation: 'Coach',
        active    : 1
    }]
})
.then((results) => {
    ...
})
.catch((err) => {
    ...
})

###model.deleteById(options)

####Description

Deletes a record by id.

####Options

• options [Object] An object containining an id property set to the id to delete.

####Returns

Returns the mysql response for the deleted record.

####Example

UsersModel.deleteById({
    id: 5
})
.then((results) => {
    ...
})
.catch((err) => {
    ...
})

###model.delete(options)

####Description

deletes rows based on either the where property and/or the ids property.

####Options

• options [Object] an object containing a where property set to an object and/or an ids property that contains an array of ids to delete.

####Returns

Returns the mysql response to the deletion.

####Example

UsersModel.delete({
    where: {
        name: 'Bob'
    },
    ids: [3,5]
})
.then((results) => {
    ...
})
.catch((err) => {
    ...
})

###model.selectById(options)

####Description

Model.selectById() selects a single record by id.

####options

• options [Object] an object with an id property of the record to select.

####Returns

Returns an array containing one object, the record of the selected id.

####Example

UsersModel.selectById({
    id: 5
})
.then((results) => {
    ...
})
.catch((err) => {
    ...
})

###model.select(options)

####Description

Model.select() selects one record based on the where property.

####options

• options [Object] an object with a where property.

####Returns

Returns an array containing the selected record.

####Example

UsersModel.select({
    where: {
        active: 1,
        type: 'member'
    }
})
.then((results) => {
    ...
})
.catch((err) => {
    ...
})

###model.selectAll(options)

####Description

Model.select() selects multiple records based on the where property.

####options

• options [Object] an object with a where property.

####Returns

Returns an array containing the selected records.

####Example

UsersModel.selectAll({
    where: {
        active: 1,
        type: 'member'
    }
})
.then((results) => {
    ...
})
.catch((err) => {
    ...
})

###model.update(options)

####Description

Model.update() updates records based on the where and set properties.

####options

• options [Object] an object containing where and set properties.

####Returns

The mysql response for the update.

####Example

UsersModel.update({
    set: {
        active: 1
    },
    where: {
        type: 'member'
    }
})
.then((results) => {
    ...
})
.catch((err) => {
    ...
})

##Adding more complicated queries and structure

Node-mysql-basemodel by design only provides basic functionality. However it's extremely easy to add any query you would like. Within your childModel you can create any number of methods that use this.query() to execute your queries. This provides you with the freedom to not wrestle an api that may or may not execute exactly what you're looking for as well as an easy way to group queries related to a specific table.

For example let's assume we're writing a restaurant app and it will have three tables - users, restaurants, and visits.

Schemas:

• users (id, name, created_at, updated_at)
• restaurants (id, name, created_at, updated_at)
• visits (id, user_id, restaurant_id, created_at, updated_at)

The three models might look like this:

'use strict';

var Base = require('node-mysql-basemodel'),
    pool = require('path/to/dbpool');
    
class UsersModel extends Base {

    getUsersWithVisits(options){
    
        var ids = options.ids.join(',');
    
        return this.query(`
            SELECT u.id, u.name, count(v.id) FROM users u  
            LEFT JOIN visits v ON u.id=v.user_id 
            WHERE id IN (${ids}) 
            GROUP BY u.id
        `);
    
    }

}

class RestaurantsModel extends Base {

    getRestaurantsWithVisits(){
    
        return this.query(`
            SELECT r.id, r.name, count(r.id) from restaurants r 
            LEFT JOIN visits v on r.id=v.restaurant_id 
            GROUP BY r.id
        `);
    
    }

}

class VisitsModel extends Base {

    getVisitsForUserForRestaurant(options){
    
        return this.query(`
            SELECT count(v.id) FROM visits v 
            WHERE v.user_id=${options.user_id}
            AND v.restaurant_id=${options.restaurant_id}
        `);
    
    }

}

var Users = new UsersModel({
    name: 'users',
    pool: pool
});

var Restaurants = new RestaurantsModel({
    name: 'restaurants',
    pool: pool
});

var Visits = new VisitsModel({
    name: 'visits',
    pool: pool
});

Ok so now let's chain together a few of these methods

Users.getUsersWithVisits({
    ids: [1]
})
.then((results) => {
    return Visits.getVisitsForUserForRestaurant({
        user_id: results[0].id,
        restaurant_id: 10
    });
})
.then((visits) => {
    return Restaurants.selectById(visits[0].restaurant_id);
})
.then((restaurant) => {
    ...
});