choong pw

Introduction

In this chapter, I’ll covering a lot more on the programming stuff that is related to our back-end database design, ORM, code partitioning, and wrappers.

In current modern programming practice, interface of front-end have many option to take into consideration. The current most popular interface users will be using to interact with our application are Web Browser and Mobile App. In some case, company even allow application that is develop by third party to interact with your application.

Is never a good idea to program same business logic twice on two different front-end. You’ll need to spend double amount of effort as well as to maintain the same consistency and expectation.

This is where API are widely popular in modern programming practices. As illustrate in the below diagram:

Back-end will the to provide all the needed API for the front-end to function properly. It can be from the Web-app or Mobile, to use the basic CRUD function or some of more complicated business logic that links and update several table at once.

It helps a lot by removing redundant duplicate of work, and also ease the maintenance of work. Also ease the front-end from focusing too much on business logic, when all they need is focusing on building UI.

Another advantage for de-coupling is writing test. Is way much easier to write test for backend than frontend. Reason is that API is just like a black box, you pass in paramenters(INPUT), and it return with a response(OUTPUT). Writing test for this kind of expected behaviour is a breeze.

Let’s Get Started

As seen from the Sequelize documentation, building database schema is done through code as follow:

const User = sequelize.define('user', {
username: Sequelize.STRING,
birthday: Sequelize.DATE
});

However, writing a large application with 10s or 100s of table, is not wise to maintain all the schema within a single file. That is where you’ll need to start breaking individual table into individual file.

Hint, for a quick start and template, try searching for “Sequelize Migration”. This covers a lot of details on how to “up” and “down” version of a database schema and seed data.

I’ll skip that and jump right into our design instead. Using the same theory, we create a wrapper on top, that we can specify our own directory and load it.

function sequelizeImportModels(dirname, tenantId) {
  ...
  if (tenantId) {
  // perform scoping on the model\
  // const _options = _.assign({}, options, {
  // defaultScope: { where: { tenantId } },
  // });
  }

  // import sql model definitions by folder
  const importByDirname = (dirname) => {
  if (!utils.isDirectoryExist(dirname))
    return;

  fs.readdirSync(dirname)
    .filter(file => (file.indexOf('.') !== 0 && (file !== 'index.js')))
    .forEach(file => {
      const func = require(path.join(dirname, file));
      func.call(this, Sequelize, this.db.sql);
    });
  };

  // support multiple folders
  if (_.isArray(dirname)) {
    dirname.forEach(importByDirname);
  } else {
    importByDirname(dirname);
  }

  ...
}

Once we have this, is much easier for us to begin breaking up tables and put it inside another sub-folder separate by modules.

The fun part of Sequelize being javascript is the functional programming. You can literally merge and assign objects. As an example, the user table as the following definition.

define({
  name: 'user',
  columns: {
    userId: {
      type: DataTypes.INTEGER,
      autoIncrement: true,
      primaryKey: true,
      allowNull: false,
    },
    username: {
      type: DataTypes.STRING(50),
      allowNull: false,
    },
    ...
  },
  extend: function (Model) {
    Model.userRoles = Model.hasMany(models.userRole, {
      as: 'userRoles',
      foreignKey: 'userId',
    });
  },
});

Elsewhere in another file, we can continue with assign it back onto the same table. This is particularly useful for us to do association with different modules as well. Reason that, this case is easier for us to know which module is link to another module and what is linked.

define({
  name: 'user',
  extend: function (Model) {
    // generating a hash
    Model.generateHash = function (password) {};

    // checking if password is valid
    Model.validPassword = function (password, hashPassword) {};
  },
});

Auto Generate GraphQL

If you refer to GraphQL-Sequelize, it guide you on how to build a graphql schema base of sequelize model.

We take it a step further, by auto-generating it. This way, we only will need to focus on building the model definition of table base on our database design, and the rest will handle by itself.

If you ever try to do a console.log on the model object that Sequelize generated, you’ll notice there is many information in it. That is where you can start pulling out information such as columns, association and their target name, primaryKey, uniqueKey, etc.

Use those information to build a auto-generate schema once, and you don’t have to worry about creating those graphql anymore in the future, all you need is just to use it.

for (const associationName in model.associations) {
  const modelAssociate = model.associations[associationName];
  associationsMessage[associationName] = modelAssociate.associationType;

  if (modelAssociate.associationType == 'HasMany') {
    const fields = this.db.gql[tableName]._typeConfig.fields;
    fields[modelAssociate.as] = {
      type: new GraphQLList(this.db.gql[modelAssociate.target.getTableName()]),
      args: this.defaultListArgs(),
      resolve: resolver(this.db.sql[tableName][modelAssociate.as]),
    };
  }
  if (modelAssociate.associationType == 'BelongsTo') {
    const fields = this.db.gql[tableName]._typeConfig.fields;
    fields[modelAssociate.as] = {
      type: this.db.gql[modelAssociate.target.getTableName()],
      args: this.defaultArgs(this.db.sql[modelAssociate.target.getTableName()]),
      resolve: resolver(this.db.sql[tableName][modelAssociate.as]),
    };
  }
  if (modelAssociate.associationType == 'HasOne') {
    const fields = this.db.gql[tableName]._typeConfig.fields;
    fields[modelAssociate.as] = {
      type: this.db.gql[modelAssociate.target.getTableName()],
      args: this.defaultArgs(this.db.sql[modelAssociate.target.getTableName()]),
      resolve: resolver(this.db.sql[tableName][modelAssociate.as]),
    };
  }
}

All The Above Setup Are

So, what the point of going all those trouble setting up each components of those loading?

For one, you can have the convenient to not needing to join table to view nested table information

graphql(schema, '{ users { username, password, roles { name } } '))

We also can use sequelize function without worry of data outside of tenantId is also retrieve for the wrong users. By making sure we call the correct tenant database

const db = database[tenantId].sql.user...

Conclusion

That basically sums up the important gist we used and leverage on Sequelize to make our coding experience better.

We split up the database definition in to multiple folder sorted by modules. Those that have overlapping relation between module will have an extension that does object assign on another separate file.

While loading, we also created scoping of all the models using Sequelize API to secure data read/write that only happens on the same tenant.

We automate the creation of GraphQL schema by looking at the object, and using those important name to fit the GraphQL generations.