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 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

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

var basename = path.basename(__filename);

const sqlConfig = {
    user: 'root',
    password: 'test',
    server: '',
    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
    .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;

// 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) {

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.


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 => { });

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():

    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: {


Testing with Jest

The Redux Documentation declares Jest as the unit testing framework of choice. This is a beginners introduction to Jest.

Taken from the Jest documentation:

Zero configuration – Jest is already configured when you use create-react-app or react-native init to create your React and React Native projects. Place your tests in a __tests__ folder, or name your test files with a .spec.js or .test.js extension. Whatever you prefer, Jest will find and run your tests.

As it turns out, Jest is already integrated into your codebase should you have used create-react-app.

Error fsevents unavailable

When npm run fails with the following output:

npm test

> cryptofrontend@0.1.0 test /Users/bischowg/dev/react/cryptofrontend
> react-scripts test

Error: `fsevents` unavailable (this watcher can only be used on Darwin)
    at new FSEventsWatcher (/Users/bischowg/dev/react/cryptofrontend/node_modules/sane/src/fsevents_watcher.js:41:11)
    at createWatcher (/Users/bischowg/dev/react/cryptofrontend/node_modules/jest-haste-map/build/index.js:780:23)
    at (<anonymous>)
    at HasteMap._watch (/Users/bischowg/dev/react/cryptofrontend/node_modules/jest-haste-map/build/index.js:936:44)
    at _buildPromise._buildFileMap.then.then.hasteMap (/Users/bischowg/dev/react/cryptofrontend/node_modules/jest-haste-map/build/index.js:355:23)
    at <anonymous>
    at process._tickCallback (internal/process/next_tick.js:160:7)
npm ERR! Test failed.  See above for more details.


npm r -g watchman
brew install watchman

I had to run the brew command three times before it finally worked. npm test should now work without issues.

Testing Action Creators

Given a file actions.js that contains the action creator

function receiveEntriesActionCreator(json) {
  return {
    entries: json

you want to write a test that verifies that the action creator returns an action that has a type property with a value of RECEIVE_ENTRIES and an entries property that contains a specific javascript object.

In order to write the test, add a file called actions.test.js next to actions.js. In actions.test.js insert:

import * as actions from './actions.js';

import {
} from './actions.js'

test('receiveEntriesActionCreator returns a correct action', () => {

  const entries = [{ id: '12345', password: 'abcdef' }]

  const expectedAction = {



On line 10, the test() function is called given a description of what the test is trying to verify. The second parameter is the code that the test should execute. Lines 12 to 17 assemble the expected result. On line 19, expect().toEqual() is called.

In the console type npm run to start a watcher that executes the tests after you save your changes to a file that has a unit test.

Node and MongoDB with Mongoose

This post describes how to add MongoDB with Mongoose as a database to your Node application.

Interesting links
Part 3 of the Mozilla Express Tutorial

Installing and starting MongoDB

First install MongoDB on your system. For MacOS the instructions are available on the MongoDB homepage.
brew update
brew install mongodb
The MongoDB daemon that executes the database is started with:
/usr/local/bin/mongod --config /usr/local/etc/mongod.conf

The mongo shell

The mongo shell is a command line tool that allows you to connect to the mongodb and perform queries. It will connect to mongodb:// if no parameters are specified


To display the database you are using, type db
To switch databases, issue the use <db> command
To show all databases that contain at least one document, type show dbs
To show all collections in a database, type show collections

MongoDB is case sensitive! The schema passwordentry is a different schema than PasswordEntry!


db.PasswordEntry.insertOne( { password: "test" } )

Select all entries:

db.PasswordEntry.find ( {} )

Using MongoDB from within Node

To install the mongoose npm dependency and a JSON middleware type

npm install mongoose
npm install body-parser save

MongoDB is a NoSQL Database that deals with documents that are stored in so called collections. The synonym for a collection is a table. The synonym for a record in a table is a document.

The fields of a document are defined using a schema. Given a schema, you can create a model. A model is a factory that creates documents which then are saved into collections.

Because models can only be defined once (otherwise an exception is thrown) in the application lifecycle (You can update them several times to add more fields, but you can create them only once), you need some centralized code that defines the schema and then creates the models.

const mongoose = require('mongoose');

mongoose.Promise = global.Promise;

// define a schema
var passwordEntrySchema = new mongoose.Schema({
  password: { type: String, required: true }

// this adjusts the conversion of mongoose objects to json when serializing the objects
// it outputs the _id as id and removes the _id. It also removes the __v field.s
passwordEntrySchema.set('toJSON', {
  transform: function (doc, ret, options) { = ret._id;
    delete ret._id;
    delete ret.__v;

// compile the schema into a model
let collectionName = 'peCollection';

// create and export the model
// models can only be created once, that is why this code is put into its own module
module.exports = mongoose.model('PasswordEntry', passwordEntrySchema, collectionName);

Once you have that centralized module, you can import the model into other places. Here is how to create a document and save it:

var express = require('express');
var router = express.Router();

const mongoose = require('mongoose');
var PasswordEntryModel = require('../mongo/mongo.js');'/add', function (req, res, next) {

  console.log('/add ', req.body);

  var myData = new PasswordEntryModel(req.body);
    .then(item => {
      res.send("item saved to database");
    .catch(err => {
      res.status(400).send("unable to save to database");

  console.log('/add end');

module.exports = router;

It is assumed, that a JSON describing a PasswordEntryModel is posted to this router.

Node and Express

Node allows you to start servers written in JavaScript. For executing the JavaScript it uses Chrome’s V8 JavaScript engine.

Express is a framework that is based on Node and allows you to easily implement an API. You can define a API using the routing features of Express. Routing is Expresses term for the definition of how a server answers web requests.

Generating a project skeleton

Express has a tool that initializes a basic project structure. First install then use that tool:

npm install express-generator -g
express --view=pug <Application Name>
cd <Application Name>
npm install

Then you can start the server.

DEBUG=<Application Name>:* npm start

You can visit http://localhost:3000/ to see the servers routing for a GET to the root URL.

Adding a router

A router defines the servers reaction to calls to a URL. A router is registered or bound to a URL in the app.js file

var todosRouter = require('./routes/todos');
app.use('/todos', todosRouter);

The code above creates a router (the router is explained later) and the binds that router to the /todos URL. All calls to http://localhost:3000/todos will now be handled by the todosRouter. 

In order to add the router itself, create a new file in the routes folder called todos.js for the todosRouter.

var express = require('express');
var router = express.Router();

router.get('/', function (req, res, next) {
  res.setHeader('Content-Type', 'application/json');
  res.send(JSON.stringify({ a: 1 }));

module.exports = router;

Note that this router never refers to the /todos URL! Instead it defines a routing for GET requests to / (root). There is one important thing to understand here. All URLs inside a router definition are relative.

A router does not care which URL it is bound to, instead it only defines relative URLs inside its own local context. The absolute URL of a router is constructed from appending the local URLs to the URL that the router is currently bound to.

In the context of the current example, this means that the root of the todosRouter router actually resolves to the absolute URL http://localhost:3000/todos/, because the router was bound to http://localhost:3000/todos.

Connect to An SQL Database

Install the node sql driver

npm install mssql -s
Connect to the Database in a Router (MySQL as an example)

Create a new schema:


Create a table in the schema:

CREATE TABLE `cash`.`account_entry` (
  `amount` INT NULL COMMENT 'Amount in least significant currency units of the currency of the entries account. E.g. Cent for EUR if the account has EUR set as a currency.',
  PRIMARY KEY (`id`));

Define a connection settings object in one of the router files of the express project and use that connection settings object to connect to the schema using a user on the database server.

var express = require('express');
const sql = require('mssql');
var router = express.Router();

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

/* GET home page. */
router.get('/', function (req, res, next) {

    const connection = sql.connect(sqlConfig, (err) => {
        if (err) {
        } else {
            res.send('DB connected');

    //res.render('index', { title: 'Express' });


module.exports = router;
Using an ORM Mapper (Sequelize)

An ORM mapper abstracts away all SQL interface code for translating between your programming language of choice and the SQL server. Instead of dealing with SQL queries, the ORM mapper lets you store and load data using objects used in your application.

When I started writing applications that used SQL databases as datastores, I wanted to write all the SQL code myself and I did not familiarize myself with ORM mappers at all. It is fun to write your own DAO and DTO class hierarchy to map from and to the SQL database but only for the first handfull of classes. As projects grow and more requirements and features and therefore objects to manage get added to the project, you quickly find yourself in a situation where you replicate your more or less dumb boilerplate code over and over for every small little insignificant new object. In reality the only thing you should care about is writing code for business logic. You should not spend time on SQL storage code if ever possible.

Another scenario is that of a high performance application. If you are required to squeeze out the last millisecond of your application, then maybe manually use SQL. In all other cases, I will recommend at least looking into the idea of using an ORM mapper. It will pay off quickly.

I really do not know which ORM mapper is suited best for your needs but as an example, lets use Sequelize. Sequelize internally connects to the SQL server, so it is possible to remove the SQL driver dependency from the example above.

Add sequelize to the project

npm install sequelize

As MySQL is beeing used in this example, install the MySQL dependencies.

npm install --save pg pg-hstore
npm install --save mysql
npm install --save mysql2

Add code to a router that inserts a new row into a table and returns the inserted element.

var express = require('express');
var Sequelize = require('sequelize');
var router = express.Router();

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

var sequelize = new Sequelize(sqlConfig.database, sqlConfig.user, sqlConfig.password, {
    host: 'localhost',
    port: 3306,
    dialect: 'mysql',

    pool: {
        max: 5,
        min: 0,
        idle: 10000

var account_entry = sequelize.define('account_entry', {
    'id': {
        type: Sequelize.INTEGER(11),
        allowNull: false,
        primaryKey: true,
        autoIncrement: true
    'amount': { type: Sequelize.INTEGER }

autoIncrement: true,

    /* GET home page. */
    router.get('/', function (req, res, next) {

        sequelize.sync().then(function () {
            return account_entry.create({
                amount: 123
        }).then(function (new_account_entry) {

            const msg = new_account_entry.get({
                plain: true


module.exports = router;

Add the router to the application configuration (app.js)

var createError = require('http-errors');
var express = require('express');
var path = require('path');
var cookieParser = require('cookie-parser');
var logger = require('morgan');

var indexRouter = require('./routes/index');
var usersRouter = require('./routes/users');
var accountEntriesRouter = require('./routes/account_entries');

var app = express();

// view engine setup
app.set('views', path.join(__dirname, 'views'));
app.set('view engine', 'pug');

app.use(express.urlencoded({ extended: false }));
app.use(express.static(path.join(__dirname, 'public')));

app.use('/', indexRouter);
app.use('/users', usersRouter);
app.use('/account_entries', accountEntriesRouter);

// catch 404 and forward to error handler
app.use(function (req, res, next) {

// error handler
app.use(function (err, req, res, next) {
    // set locals, only providing error in development
    res.locals.message = err.message;
    res.locals.error ='env') === 'development' ? err : {};

    // render the error page
    res.status(err.status || 500);

module.exports = app;

Delete the table account_entry table created manually earlier. The ORM mapper will automatically generate a table creation SQL statement from the type definition and execute that statement agains the MySQL server.

When you call http://localhost:3000/account_entries the table is created in the MySQL schema as it did not exist already and an account_entry row is inserted into the database. All that is possible without writing a single line of SQL thanks to the ORM mapper which intersects your request to store an object and does all the required SQL boilerplate for you!