store-sql

The SQL store is a wrapper over sequelize that provides a more user friendly interface of defining entity models, automatically handling CRUD actions on an entity, with paginated find.

Installation and usage

npm i --save thorin-store-sql@1.x
'use strict';
// app.js entry file
const thorin = require('thorin');

thorin.addStore(require('thorin-store-sql'));   // <- add this line
thorin.run((err) => {});

# run to setup the database and module
node app.js --setup=store.sql
# On each database reset, use the above command

Default configuration

• debugtrueif set to false, disables debugging completely. Can also be an object (see below)
• debug.createtrue enable debugging for INSERT statements
• debug.readtrue enables debugging for SELECT statements
• debug.updatetrue enables debugging for UPDATE statements
• debug.deletetrue enables debugging for DELETE statements
• debug.restifytrue enables debugging for restified entities (see below)
• hostlocalhost the SQL hostname
• usernull the SQL username
• passwordnullthe SQL password
• databasenullthe SQL database
• path.modelsthorin.root + app/models the location of the store model definitions that will be auto-loaded
• path.patchthorin.root + app/models/patch the location of .sql patch files that will be executed on setup
• optionsobject sequelize specific options
• options.dialectmysqlthe default dialect that we use is MySQL
• options.timezone+00:00the default timezone is UTC
• options.pool.maxIdleTime12000the maximum idle time of a connection set to 12 seconds

Store functionality

'use strict';
const storeObj = thorin.store('sql'),
   Sequelize = storeObj.getSequelize();   // returns the exported object from require('sequelize');

'use strict';
const storeObj = thorin.store('sql'),
   seqObj = storeObj.getInstance(); // returns the equivallent of new (require('sequelize'))()

'use strinct';
function buildModel(modelObj, Seq) {
   modelObj
      .field('id', Seq.PRIMARY);
   // other model settings
}
thorin.store('sql').addModel(buildModel, {
   code: 'myModelCode',    // the model code
   name: 'my_model_code'   // the table name
});

'use strict';
const storeObj = thorin.store('sql');
storeObj.transaction((t) => {
   const calls = [];
   let accObj;
   calls.push(() => {
      return storeObj.model('account').find({
         where: {id: 1},
         transaction: t
      }).then((aObj) => {accObj = aObj});
   });
   calls.push((stop) => {
      if(!accObj) return stop(thorin.error('ACCOUNT.NOT_FOUND');
      return accObj.update({is_active: true}, { transaction: t })
   });
   return thorin.series(calls);  // returns a promise
}).then((res) => {
   // transaction committed.
}).catch((err) => {
   // transaction rolled back.
});

Store model

You use a store model to define the fields, indexes, static functions and json representation of your tables. Store models are wrapper over Sequelize's models, used for auto-loading dependencies and relations. By default, a model's toJSON will return all the fields under the dataValues property of the Sequelize model.In most cases, your application's models will be under the app/models folder and export a function that will be called when by the thorin store, when loading up and defining your models.

The default conventions for when defining your models are:

• modelCode - the code of your models should always be camelCase
• table_name - the table name of your models should always be underscored
• field_name - all the fields of your models should always be underscored.
• methodName - all your methods and static functions should always be camelCase.

// Field calls can be chained.
modelObj
   .field('id', Seq.PRIMARY)
   .field('uuid', Seq.UUID)
   .field('name', Seq.STRING(25), {
      defaultValue: 'JohnDow'
   })
   .field('type', Seq.ENUM('one','two'))
   .field('arrow', Seq.STRING, {
      allowNull: true
   });

// The JSON representation when calling instanceObj.toJSON()
modelObj.json(function() {
   return {
      id: this.id,
      name: this.name
   };
});
// The JSON representation when calling instanceObj.toJSON('specific');
modelObj.json('specific', function(){
   return {
      id: this.id,
      name: this.name,
      specific: true
   };
});

// Attach a static function
modelObj.static(function doStatic() {
   log.info('Do static stuff');
});
// Attach a named static function
modelObj.static('doStaticTwo', () => {
   log.info('Do two static stuffs');
});
// Attach an object
modelObj.static('TYPE', {
   SOME_KIND_OF: 'STATIC.VALUES'
});

// Somewhere else in your application
const store = thorin.store('sql'),
   myModel = store.model('myModel');

myModel.doStatic();  //  call or use the static functions
myModel.TYPE;  // contains the object.

// Attach the function to the instance of a model.
modelObj.method(function doSomething(){
   log.info('Do something for model ' + this.id);  // "this" is the model's instance scope.
});

// Somewhere in your app
const Model = thorin.store('sql').model('myModel');
let iObj = Model.build({id: 2});
iObj.doSomething();  // call the function of the model instance.

modelObj.index('my_field'); // simple index
modelObj.index(['field_one', 'field_two'], { // multiple fields in a unique index.
   unique: true
});

// Check if we have a name.
   // The validate function is synchronous, and errors thrown here will be returned by the transport.
modelObj.validate(function() {
   if(!this.name || this.name.length < 3) throw thorin.error('INVALID_NAME', 'Please enter a valid name');
});

You can define relationships between your models by using the functions below. The store takes care of model loading, so you do not have to keep any kind of variable references. We use the model's code as the reference.

When an association is created, some default options are created:
onDelete: 'CASCADE' - when associated entry is deleted, delete this one as well
onUpdate: 'CASCADE' - when associated entry is updated, update the key as well.
foreignKey: 'target_model_id' - the model's table_name with the primary key suffix, usually _id
For a more in-detail view on these associations, see docs.

A full example of a model definition file can be viewed below.

'use strict';
// File: app/models/account.js
module.exports = function(modelObj, Seq) {
   // modelObj is already created by the store, having the code set to account and tableName to account
   modelObj
      .field('id', Seq.PRIMARY)
      .field('name', Seq.STRING(30))
      .field('password', Seq.STRING(200)) // hashed version
      .field('image_url', Seq.STRING(300), {
         defaultValue: null   // this will automatically add allowNull: true
      });
      
   modelObj
      .method(function sayHi() {
         log.info(`${this.name} says hi!`);
      })
      .json(function() {
         let d = {
            id: this.id,
            name: this.name,
            created_at: this.created_at   // auto-generated field.
         };
         if(this.image_url) d.image_url = this.image_url;
         return d;
      })
      .error('NOT_FOUND', 'The account was not found', 404);
      
   modelObj
      .hasMany('application', {
         as: 'applications'
      });
}

'use strict';
// File: app/models/application.js
module.exports = (modelObj, Seq) => {
   modelObj
      .field('id', Seq.PRIMARY)
      .field('name', Seq.STRING)
      
   modelObj
      .belongsTo('account');
};

Model crudification

The SQL Store offers auto-generated CRUDF functionality in the form of actions. This means that in stead of you manually doing a findOne() or findAll() + count() on a model, you can just use storeObj.crudify() and an action with input validation based on your model definition will be registered in the dispatcher.

The process is simple, you just have to call storeObj.crudify() and it will return a generated action that you can extend and inject code in various steps along the way. The best way to inject code before the crudify function is to attach an action template to the generated crudify action. Note that filter triggering is synchronous, any error thrown inside the filter callback will stop the request.

All model instances that are generated or altered by a crudify method will have a fromCrudify=true property attached to them.

Each crudify method will trigger specific filters in various points in time, during the lifecycle of the request. Using these filters enable you to alter incoming, outgoing or generated model instances.

'use strict';
const storeObj = thorin.store('sql');         
storeObj
   .crudify('modelName', 'read', {
      namespace: 'some.namespace.to.attach',
      name: 'account'   // will generate an action called: some.namespace.to.attach.account.read
   })
   .filter('read.before', (intentObj, query) => { 
   // alter the read query to show only active modelName
      query.where.is_active = true;
   });

Create crudify

Handles the CREATE action of a store model. It will look for all the model fields and require them via the input() function of the generated action. By default, it will also create an alias POST /{namespace}/{modelName}.

Options

• namespace - override the default namespace of the action
• name - override the default model name used to build the final action name
• action - manually set the full action name

Model field options

• private - if set to trueon a field, do not accept data associated with the field, coming from the client. (eg: password field should have private: true)
• create - same as private.

Filters

'use strict';
const store = thorin.store('sql');
store
  .crudify('application', 'create')
  .template('session.account')
  .use('application.canCreate')
  .use((intentObj, next) => {
    if(intentObj.session.account) {
      intentObj.input('account_id', intentObj.session.account);
    }
    log.info(`Creating a new application`);
    next();
  })
  .filter('create.before', (intentObj, appObj) => {
    // override any is_administrative fields in the form.
    appObj.set('is_administrative', false);
  })
  .filter('create.after', (intentObj, appObj) => {
     log.info(`Application ${appObj.id} created`);
  });

Read crudify

Handles the READ action of a store model. It essentially builds an action that will perform a SELECT or findOne() a model based on the incoming primary key (eg, id) and limit the results to one. By default, it will also create an alias GET /{namespace}/{modelName}/:{primaryKey}.

Options

• namespace override the default namespace of the action
• name override the default model name used to build the final action name
• action manually set the full action name

Filters

Find crudify

Handles the FIND action of a store model. It essentially builds an action that will perform a SELECT or findAll() on a model, including filtering by public fields of a model. The default functionality includes sorting, pagination and field filtering

Options

• namespace override the default namespace of the action
• name override the default model name used to build the final action name
• action manually set the full action name
• maxLimit the maximum value of the LIMIT statement.

Special input options

When the action is generated, specific input fields will be injected by the crudification. These will allow you to perform the above mentioned actions. Any public model field will also be placed as an input and can filter the results.

• order - the direction of the ORDER BY statement. Values are: asc, desc, default to asc
• order_by - the name of the field to order by, defaults to created_at if available.
• limit - the number value of the LIMIT statement, defaults to 10
• page - useful for pagination, if set, it will change the LIMIT statement to skip x entities and return the next data set.
• start_date - if the model has a createdAt field, you can filter by its creation date (GTE)
• end_date - same as start_date but applies for the end date (LTE)

Model field options

• private - if set to true on a field, disables filtering based on the given field.
• find - same as private.

Associated metadata

The find crudified action will also generate some metadata that can be easily used for pagination. The below fields are placed under the meta field in the resulting JSON

• total_count - total number of entities found
• page_count - total number of available pages
• current_page - the current page
• current_count - the current number of entities

Filters

Update crudify

Handles the UPDATE action of a store model. It essentially builds an action that will perform an UPDATE statement on a model instance. This functionality only works for models that have exactly one primary key defined.

Options

• namespace override the default namespace of the action
• name override the default model name used to build the final action name
• action manually set the full action name

The action will register all the accessible fields of the model as input requirements, using their associated field type.

Model field options

• private - if set to true on a field, disables updates on the given field.
• update - same as private.

Associated metadata

• changed - did any field of the instance change

Filters

Delete crudify

Handles the DELETE action of a store model. It essentially builds an action that will perform a DELETE statement on a model instance. This functionality only works for models that have exactly one primary key defined.

Options

• namespace override the default namespace of the action
• name override the default model name used to build the final action name
• action manually set the full action name

The action will register the model's primary key name as a required input with its associated type and use it to search for the entity we want to delete.
If the entity instance has a canDelete() method attached, we will call it before destroying the entry. If it returns a falsy value, we will not delete it.

Associated metadata

• deleted - was the instance deleted or not

Filters