node-mysql-basemodel v1.0.1
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) => {
...
});