Sequelize

Introduction

Sequelize describes itself on the sequelize homepage:

Sequelize is a promise-based Node.js ORM for Postgres, MySQL, MariaDB, SQLite and Microsoft SQL Server. It features solid transaction support, relations, eager and lazy loading, read replication and more.

Sequelize is an ORM (Object Relational Mapper) which allows you to interface with a SQL database without writing SQL but purely using the domain objects of your node application.

Sequelize shields you of any SQL to the point where Sequelize will automatically generate and execute table creation statements and all the insert, update, select and delete statements for you! You do not have to create tables in your database server, Sequelize will do it for you.

While reading this article, you can look at this repository. It is not good code by any means but maybe it helps you to see a running example which you can modify and do your own tests on.

Defining Models

A model is an object that has attributes which will be stored in the columns of a database table. Models can be connected to each other using associations.

An example would be an application that manages bank accounts. There is a model for an account, each account has amounts of money in them and money is transferred between accounts by transactions. You could model Accounts, Amounts and Transactions. Sequelize will allow you to perform CRUD for those objects without writing a single line of SQL.

The model folder and index.js

In order for Sequelize to manage your objects, you have to define the models first so Sequelize knows about their structure and can generate SQL for you.

This approach is taken from https://github.com/sequelize/express-example/blob/master/models and it works very well.

One way of managing your models is to have a separate folder to store all your model definitions in. A model definition is a node module that exports a model. A model optionally has a function that defines it’s associations to the other models.

Inside that model folder, you will also have a index.js file. This index.js file will first scan for all the models defined in the models folder, set them up and then export an object called db. db will be the object your node application uses to interface with sequelize. db contains a connection to the database and all the model definitions that you can store in the database.

index.js will perform the following steps to set up models:

  • It will connect to the database and store the connection into the db object
  • It will scan the model folder and load all model definitions it finds
  • It will call the associate() function on model (if defined) so that a model can define it’s association to the other models
TypeError: defineCall is not a function

One very import thing to remember is the following: The error “TypeError: defineCall is not a function” is thrown if your model folder does contain any files except valid Sequelize Model-Modules or the index.js file! If you put any non-Sequelize code into the model folder or if you even comment out the entire contents one of your Model-Module files, Sequelize will get confused and throw the “defineCall” error! So do not comment out any of your Model-Modules and do not put any other files into the model folder!

An example Model-Module (models/account.model.js) for accounts is:

module.exports = (sequelize, DataTypes) => {

    var Account = sequelize.define('Account', {
        'id': {
            type: DataTypes.INTEGER(11),
            allowNull: false,
            primaryKey: true,
            autoIncrement: true
        },
        'name': {
            type: DataTypes.STRING(255)
        }
    });

    Account.associate = function (models) {

        // optional, foreign key is stored in the source model 
        // (= Account has foreign key to RealAccount)
        models.Account.belongsTo(models.RealAccount, {
            foreignKey: 'realaccountid',
            targetKey: 'id'
        });

        models.Account.hasMany(models.Amount, {
            foreignKey: 'virtualaccountid',
            targetKey: 'id'
        })
    };

    return Account;
};

This module defines a RealAccount model and it’s associations to a Account Model and several Amount models.

The index.js file looks like this:

// inspired by https://github.com/sequelize/express-example/blob/master/models

var fs = require('fs');
var path = require('path');
var Sequelize = require('sequelize');

var basename = path.basename(__filename);

const sqlConfig = {
    user: 'root',
    password: 'test',
    server: '127.0.0.1:3306',
    database: 'cash'
}

var sequelizeConnection = new Sequelize(sqlConfig.database, sqlConfig.user, sqlConfig.password, {
    host: 'localhost',
    port: 3306,
    dialect: 'mysql',
    logging: false,
    pool: {
        max: 5,
        min: 0,
        idle: 10000
    }
});

var db = {
    Sequelize: Sequelize,
    sequelize: sequelizeConnection
};

// collect all model files in the models folder to automatically load all the defined models
fs.readdirSync(__dirname)
    .filter(file => {
        return (file.indexOf('.') !== 0) && (file !== basename) && (file.slice(-3) === '.js');
    })
    .forEach(file => {
        var model = db.sequelize['import'](path.join(__dirname, file));
        db[model.name] = model;
    });

// if a model has an associtate methode, call it.
// The associate method will define the relationships between the models.
Object.keys(db).forEach(modelName => {
    if (db[modelName].associate) {
        db[modelName].associate(db);
    }
});

module.exports = db;

 

Using the db object

To store and load into the database, you have to use the db object. Import the db object:

var db = require('../model/index.js');

Now the db object is ready.

For Testing: Erase and recreate all the tables for all models

Note: Never have this in your production code! All data will be lost! This is usefull for testing!

During the testing phase, it is usefull to let Sequelize erase all tables and recreate them for you. To do so execute sync() with the force parameter:

// delete the entire database
await db.sequelize.sync({
    force: true
});
Inserting a Model

The functions that operate on models are added to a mode_services folder into the service.js file.

It is a mistake to insert the services.js file into the model folder as Sequelize will get confused and produce the “TypeError: defineCall is not a function” error.

The module to insert a model looks like this:

async function addAmountByIds(db, realAccountId, virtualAccountId, amount) {
    return db.Amount.create({
        amount: amount,
        realaccountid: realAccountId,
        virtualaccountid: virtualAccountId
    });
}

module.exports = {

    addAmountByIds: addAmountByIds

};

To use this method:

var services = require('../persistence_services/services');
var db = require('../persistence/index.js');

var realAccount = await services.addAmountByIds(db, 1, 2, 299);

You can see that the function addAmountByIds() is defined async because Sequelize is inherently async and uses Promises for everything.

addAmountByIds() will not return the created Amount object instantly but it will return a Promise. The Promise is your handle to an asynchronous flow of operation which is creating the Amount object in the database asynchronosly. As soon as that flow finishes, the Promise will return the actual result which is the created amount object.

Declaring addAmountByIds() async makes it a non-blocking call which means that when you call it, it immediately returns a Promise and your normal program flow continues while a parallel thread starts in the background.

This way of dealing with asynchronicity is awesome but my brain just can’t handle it. I cannot write a working application dealing with hundreds of Promises instead of real objects. Because of my own imperfection, the examples will always call the service functions with the await keyword.

The await keyword turns the non-blocking calls into plain old blocking functions. The program flow will block after calling the service functions until the real object is returned from the promise. That way, you are sure that the result returned is a valid object that is no persisted into your database. You can now write sequential code and sole your problems without thinking about asynchronous code.

then() – chaining

An alternative way of dealing with asynchronous Promises is to chain Promises together with calls to the then() function.

The callback specified in the then() function is called as soon as the Promise returns the real object. Besides await, this is another way to block until the result is ready. then() chains are a very akward way of writing sequential code in my humble opinion. I maybe have never seen code that is high-quality and easily readable using then() chains, but I cannot imagine easy to read code using then() changes as of now.

Accessing Associations via Properties

If you have objects associated to each other such as a BlogPost and all Comments people left on that BlogPost, you can access all Comments if you have a reference to the BlogPost object just by calling a property on the object.

This means, you do not have to explicitly query for the Comments instead Sequelize will lazy load the Comments for you in the background.

var comments = await blogPost.getComments();

Again, the Promise is resolved by the await keyword and the comments variable contains the loaded comments.

Associatons

How do you connect a BlogPost to a Comment for example?

Remember that index.js will call the associate() function of each model during initialization? The associate() function is where you define the associations for each model.

You should consult the Sequelize documentation to find the right type of association that describe the situation you want to model best.

As an example, if a BlogPost should be associated to many Comments, define:

BlogPost.associate = function (models) {

    models.BlogPost.hasMany(models.Comment, {
        foreignKey: 'blogpostid',
        targetKey: 'id'
    })

};
Not creating Objects in Associations

I have not fully understood why but calling Setters will automatically insert new objects into the database unless you use the save: false parameters:

transaction.setSource(targetAccountObject, { save: false });
Updating Objects

If you want to change the amount in an account, you have to change the value and trigger an update:

amount.amount += 299;
await amount.save().then(() => { });

Here, the amount is increased by 299 and an await is used to wait for the result of the save() call which updates the object in the database.

Another way is to use update():

amountObject.update({
    amount: newAmount
}).then(() => { });
Querying Objects

The findByPk() convenience method allows you to query by primary key.

var result = await db.RealAccount.findByPk(amountObject.realaccountid);

General queries are executed using findAll() and a where clause:

var result = await db.Amount.findAll({
    where: {
        virtualaccountid: targetAccountObject.id
    }
});

 

Leave a Reply