Speed up your REST API development using bookshelf and knex.js extended base code.

The base code will use knex query builder and bookshelf. On top of bookshelf. The will reduce the REST API development many folds, it takes care of basic REST API principles as well as custom relation and extra resource management for the nested and custom object generation.

import Knex from 'knex';
import Bookshelf from 'bookshelf';
import configVar from './envconfig';
import {logger} from '../config/winston';

const DBConfig = {
    client: 'mysql',
    connection: {
        host: configVar.config.database.HOST,
        port: configVar.config.database.PORT,
        user: configVar.config.database.USER,
        password: configVar.config.database.PASSWORD,
        database: configVar.config.database.DATABASE,
        charset: 'utf8mb4'
    }
};

const knex = Knex(DBConfig);

knex.on('query', (query) => {
    for (const bind of query.bindings) {
        query.sql = query.sql.replace('?', bind);
    }

logger.info(`${Date()}QUERY:*****************************${query.sql}*********************************`);
});

const bookshelf = Bookshelf(knex);

export default bookshelf;

Bookshelf base code to use pagination, limit, where query, relations and sorting for Collection and Models for your mysql database table.

Fields we can consider for query string

[
  "sort",
  "fields",
  "page",
  "limit",
  "relations",
  "resourceToFetch"
]

base_code.js

import extend from 'xtend';
import bookshelf from '../config/db';

// This is included to return all the fields for the save operation,
// as bookshelf save returns only fields that are updated
const save = bookshelf.Model.prototype.save;

bookshelf.Model.prototype.save = function (_, fetchOpts) {
    return save.apply(this, arguments).then(model =>
        // fetchOpts contains transaction info
        (model ? model.refresh(fetchOpts) : model));
};

bookshelf.Model = bookshelf.Model.extend({
    toJSON(options) {
        return bookshelf.Model.prototype.serialize.call(this, extend(options || {}, {
            omitPivot: true
        }));
    }
});

bookshelf.Collection = bookshelf.Collection.extend({

    limit: 1000,
    page: 1,
    maxLimit: 5000,

    fetchCollection(bigObject) {
        const self = this;
        const whereObj = {};
        const sortArray = [];
        let page = self.page;
        let limit = self.limit;
        const maxLimit = self.maxLimit;
        let fetchObject = {};

        let fieldsToReturn = bigObject.allowedFields;

        for (const x in bigObject.queryParameter) {
            if (bigObject.expectedWhereFields.includes(x)) {
                whereObj[x] = bigObject.queryParameter[x];
            }
            if (x === 'sort') {
                const sortFields = bigObject.queryParameter[x].split(',');
                for (const y in sortFields) {
                    sortFields[y] = sortFields[y].trim();
                    const firstChar = sortFields[y].charAt(0);
                    const sortField = (firstChar === '-') ? sortFields[y].substr(1) : sortFields[y];
                    const order = (firstChar === '-') ? 'desc' : 'asc';
                    if (bigObject.expectedSortFields.includes(sortField)) {
                        sortArray[y] = {
                            field: sortField,
                            direction: order
                        };
                    }
                }
            }
            if (x === 'fields') {
                fieldsToReturn = bigObject.queryParameter[x].split(','); // array
                if (bigObject.withRelatedObject !== undefined) {
                    fieldsToReturn = fieldsToReturn.concat(bigObject.withRelatedObject.mandatory);
                }
            }


            if (x === 'page') {
                page = bigObject.queryParameter[x]; // integer
            }
            if (x === 'limit') {
                limit = bigObject.queryParameter[x]; // integer
            }
        }
        if (limit > maxLimit) {
            limit = maxLimit;
        }
        const offset = (page - 1) * limit;

        if (bigObject.withRelatedObject !== undefined) {
            fetchObject = {
                withRelated: bigObject.withRelatedObject.relation,
                columns: fieldsToReturn
            };
        } else {
            fetchObject = {
                columns: fieldsToReturn
            };
        }

        return self.constructor
            .forge()
            .query((qb) => {
                for (const x in whereObj) {
                    qb.andWhere(x, 'in', whereObj[x].split(','));
                }
                for (const i in sortArray) {
                    qb.orderBy(sortArray[i].field, sortArray[i].direction);
                }
                qb.offset(offset).limit(limit);
            })
            .fetch(fetchObject);
    },

    nthGetCount(whereField, whereValue) {
        const yourObject = {};

        if (whereField) {
            yourObject[whereField] = whereValue;
        }
        const self = this;
        return self.constructor
            .forge()
            .query('where', yourObject)
            .count('id');
    }
});


bookshelf.Model = bookshelf.Model.extend({

    fetchSingle(singleFetchId, bigObject) {
        const self = this;
        const whereObj = {};
        let fetchObject = {};

        let fieldsToReturn = bigObject.allowedFields;

        for (const x in bigObject.queryParams) {
            if (bigObject.expectedWhereFields.includes(x)) {
                whereObj[x] = bigObject.queryParams[x];
            }
            if (x === 'fields') {
                fieldsToReturn = bigObject.queryParams[x].split(',');
                if (bigObject.withRelatedObject !== undefined) {
                    fieldsToReturn = fieldsToReturn.concat(bigObject.withRelatedObject.mandatory);
                }
            }
        }

        if (bigObject.withRelatedObject !== undefined) {
            fetchObject = {
                withRelated: bigObject.withRelatedObject.relation,
                columns: fieldsToReturn
            };
        } else {
            fetchObject = {
                columns: fieldsToReturn
            };
        }

        return self.constructor
            .forge({id: singleFetchId})
            .fetch(fetchObject);
    }
});

export {
    bookshelf
};

Any collection or model can be called with the global services

import Bookshelf from '../config/db';

const runRawQuery = (rawSql, callback) => {
    Bookshelf.knex
        .raw(rawSql)
        .then(data => callback(null, data))
        .catch(err => callback(err, null));
};

const getByObject = (bigObject, collection, callback) => {
    collection
        .forge()
        .fetchCollection(bigObject)
        .then(data => callback(null, data))
        .catch(err => callback(err, null));
};

const getSingleObject = (singleFetchId, bigObject, model, callback) => {
    model.forge()
        .fetchSingle(singleFetchId, bigObject)
        // Custom MTP fetch using extended collection
        .then(data => callback(null, data))
        .catch(err => callback(err, null));
};


const findByObject = (queryObject, model, callback) => {
    model
        .forge(queryObject)
        .fetch()
        .then(data => callback(null, data))
        .catch(err => callback(err, null));
};

const createByObject = (insertObject, collection, callback) => {
    collection
        .forge(insertObject)
        .save()
        .then(data =>
            callback(null, data))
        .catch(err => callback(err, null));
};

const postMultipleRow = (objArray, collection, callback) => {
    collection
        .forge(objArray)
        .invokeThen('save')
        .then(data => callback(null, data))
        .catch(err => callback(err, null));
};

const updateByObjectId = (updateObject, collection, callback) => {
    collection
        .forge({id: updateObject.id})
        .fetch({require: true,})
        .then((data) => {
            data.save(updateObject)
                .then(() => callback(null, data))
                .catch(err => callback(err, null));
        })
        .catch(err => callback(err, null));
};
const upsertRawQueryGenerator = (table, inputObject) => {
    let rawSql = `INSERT INTO ${table} ( `;
    for (const key in inputObject.key) {
        rawSql += `${inputObject.key[key]},`;
    }
    rawSql = rawSql.slice(0, -1);
    rawSql += ') VALUES ';
    for (const key in inputObject.values) {
        rawSql += '(';
        for (const value in inputObject.values[key]) {
            rawSql += `${inputObject.values[key][value]},`;
        }
        rawSql = rawSql.slice(0, -1);
        rawSql += '),';
    }
    rawSql = rawSql.slice(0, -1);
    rawSql += ' ON DUPLICATE KEY UPDATE ';
    for (const key in inputObject.update) {
        rawSql += `${inputObject.update[key]} = VALUES( ${inputObject.update[key]} ) ,`;
    }
    rawSql = rawSql.slice(0, -1);
    return rawSql;
}
export default {
    runRawQuery,
    getByObject,
    getSingleObject,
    findByObject,
    createByObject,
    postMultipleRow,
    updateByObjectId,
    upsertRawQueryGenerator
};

Use the object parameter to pass to the service and general bookshelf model using relations to speed up rest API development.

The will be released as package manager and will be made open source project and invites for development will be appreciated for further enhancement.

Partial credits to Akshay Maheshwari LNMIIT Jaipur as our intern at nthexam.com during summers of 2018.