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
For a complete set of options you can pass to Sequelize, visit the official docs
Store functionality
storeObj.getSequelize() : Sequelize
Returns the Sequelize module. This is useful when your queries include specific sequelize statements or require access to the Sequelize module.
'use strict';
const storeObj = thorin.store('sql'),
   Sequelize = storeObj.getSequelize();   // returns the exported object from require('sequelize');
storeObj.getInstance() : seqObj
Returns the Sequelize instance containing connection details and other information. This should be used when access to the raw Sequelize instance is necessray.
'use strict';
const storeObj = thorin.store('sql'),
   seqObj = storeObj.getInstance(); // returns the equivallent of new (require('sequelize'))()
storeObj.query() : Promise
Manually execute an SQL query using sequelize's native query() function.
storeObj.setup() : Promise
When your application is started with --setup=store.sql or --setup=all, the SQL store will reset the database structure, essentially performing a DROP CREATE on all your models.
DO NOT USE ON A PRODUCTION SERVER as it will wipe out all the database. Useful in development however.
The way it initializes the setup process is described below
  • Connect to the database
  • Sets foreign key check to false
  • Extracts all tables that exist in the database
  • Performs a drop on every table
  • Calls Sequelize's sync() function to re-create tables
  • Reads any .sql file from the patch folder if any.
  • For each patch file, execute the statements (note: statements are delimited by a new line)
  • Sets foreign key check to true
storeObj.sync(model, opt)
Manually execute the sequelize sync() operation on the model or the entire database.
  • modelstringif specified, synchronise only the given model name
  • optobjectadditional sync options (see Sequelize documentation)
storeObj.addModel(item)
Registers a new store model. This function should be done before the run() function of the store is called.
  • itemstring when used as string, we will use it as the absolute path of the model file.
  • itemfunction when used as a function, we will consider the function as the exported model function (see below).
Ways of calling addModel:
  • storeObj.addModel('/path/to/my/model.js') the store will require() the path and load it up (see below the loading procedure)
  • storeObj.addModel({name, code, fullPath}) use the name and code as the model's properties, and use the fullPath as the model's absolute path
The store will automatically load all the models of your application, so you do not have to handle manual model loading. However, if you are writing a thorin component, the standard way of adding a model is
'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
});
storeObj.setPatchPath(path)
Manually set the absolute location of your patch files
  • pathstringthe absolute path of your patch folder. All .sql files will be loaded and executed as patches.
storeObj.transaction(fn, opt)
Initialize an SQL transaction that can be committed or rolled back (see more details here)
  • fnfunctionthe callback function that will be called with the transaction object.
  • optobjectadditional options to pass to sequelize's transaction.
The transaction callback works with promises and promise chains. The moment a promise is rejected, the transaction automatically rolls back. An example can be viewed below.
'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.
});
storeObj.decamelize(str)
helper function that converts camelCase to camel_case
  • strstringthe string to de-camelize
storeObj.camelize(str)
helper function that converts camel_case to camelCase
  • strstringthe string to camelize
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.
Properties
  • tableNamestring che property holds the name of the table associated with the model
  • codestring the code of your model
  • options.timestampsboolean, true use timestamps for time-specific fields.
  • options.createdAtstring, created_at use a field called created_at that will always hold the creation date of every entry.
  • options.updatedAtstring, false whenever an instance is updated, use this field to set the current_timestamp.
  • options.deletedAtstring, false if set, the model will not use DELETE but rather UPDATE with is_active=false and deleted_at field
options
field(name, type, opt)
Adds the given field representation to the model object's structure. The field type must be a Sequelize type and the opt can contain additional field-specific options.
  • namestringthe field name used in the database
  • typeSequelize typefield types exported by Sequelize (see more here)
  • optobjectadditional field-specific options.
Custom non-sequelize types can be defined. Current types:
  • Seq.PRIMARY shorthand for Seq.INTEGER with autoIncrement, primaryKey set to true
  • Seq.UUID shorthand for Seq.STRING(50) that generates a random key, using uuid for its defaultValue and automatically adds an index to your field.
// 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
   });
json(name, fn)
Registers a toJSON function that will be called when the model's toJSON function is called by a transport layer, or performing JSON.stringify() A model might contain multiple json functions, differentiated by their name (see below)
  • namestring if specified, the name of the json function
  • fnfunctionthe function that will override the default toJSON of an object.
Note If you want to have access to this context, you must not use an arrow function.
// 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
   };
});
static(name, val)
Attaches a static property or function to the model. They will then be attached to the constructed Sequelize model.
  • namestring | functionthe name of a static key or the static function
  • valanywhen used with name, the value to attach to the static key.
// 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.
error(err)
Assign a static custom error to the model. This is a utility function that uses static()
  • errthorin.error A thorin.error() or the equivalent arguments of the thorin.error() call
There are a few ways of attaching errors:
modelObj.error('SOME_CODE', 'Some error message', 400);
modelObj.error(thorin.error('SOME.ERROR', 'Some message', 405);
method(name, fn)
Attach the given function to every instance of your model.
  • namestring | functionthe name of your method or a named function
  • fnfunctionwhen used with name, the callback function.
// 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.
setter(name, fn)
Attach a setter function to the model. See here.
getter(name, fn)
Attach a getter function to the model. See here.
index(fields, opt)
Defines a new index for your model.
  • fieldsstring | array(string) the field name(s) to use in the index creation
  • optobject additional index options. See Sequelize docs.
modelObj.index('my_field'); // simple index
modelObj.index(['field_one', 'field_two'], { // multiple fields in a unique index.
   unique: true
});
validate(fn)
Registers a validation function at the model level, called when an instance of the model is saved.
  • fnfunction the callback that handles registration.
// 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');
});
hook(type, fn)
Wrapper function to register model-specific hooks. For more information, see Sequelize's documentation
  • namestringthe hook type
  • fnfunctionthe hook callback function
getPrimary()
Returns the primary key of the model. We currently only support one primary key per model
isValid()
Checks if the model contains enough information about a table. A valid model must have at least one field and the table 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.

belongsTo(name, opt)
Registers a belongsTo association between the current model and the given model.
belongsToMany(name, opt)
Registers a belongsToMany association between the current model and the given model.
hasOne(name, opt)
Registers a hasOne association between the current model and the given model.
hasMany(name, opt)
Registers a hasMany association between the current model and the given model.

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.

storeObj.crudify(model, action, opt)
  • modelstringthe model code to use
  • actionsstringthe crudify action to use.
  • optobjectadditional options used by the store.
'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
create.before(intentObj, newInstanceObj)
Triggered when a new instance was built but not yet persisted to the database
create.after(intentObj, savedInstanceObj)
Triggered right after the newly created instance was persisted to the database.
create.send(intentObj)
Triggered right after the newly saved instance was place under the intent's result and ready to finalize the action.
'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
read.before(intentObj, findQuery)
Triggered when the Sequelize find query object was generated, before sending it to the SQL server. This is where you can manually alter the query by adding other query statements.
read.after(intentObj, instanceObj)
Triggered right after the query was executed and a valid entity instance was found.
read.send(intentObj)
Triggered right after the queried instance was place under the intent's result and ready to finalize the action.
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
find.before(intentObj, findQuery)
Triggered when the Sequelize find query object was generated, before sending it to the SQL server. This is where you can manually alter the query by adding other query statements.
find.after(intentObj, [instances])
Triggered right after the query was executed and an array of model instances resulted.
find.send(intentObj)
Triggered right after the queried instances were place under the intent's result and ready to finalize the action.
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
update.before(intentObj, findQuery)
Triggered when the Sequelize find query object was generated, before sending it to the SQL server. When we update an instance, we read the entity first. This is where you can manually alter the query by adding other query statements.
update.save(intentObj, entityObj)
Triggered right before we persist the changes to the database.
update.after(intentObj, updatedEntityObj)
Triggered right after all changes were persisted to the database.
find.send(intentObj)
Triggered right after the updated instance was place under the intent's result and ready to finalize the action.
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
delete.before(intentObj, findQuery)
Triggered when the Sequelize find query object was generated, before sending it to the SQL server. When we delete an instance, we read the entity first. This is where you can manually alter the query by adding other query statements.
delete.destroy(intentObj, entityObj)
Triggered right before we call the destroy() function of the instance.
delete.after(intentObj, deletedEntityObj)
Triggered right after the entity was deleted from the database.
delete.send(intentObj)
Triggered right after the action finalized and is preparing to send an empty result to the client.
Do you have a question or is something missing?

You can always create a new issue on GitHub or contact one of the core founders by chat.