How work was carried out

Due to how our project work was broke down and prioritised, I ended up writing the migration scripts first and integrated with codebase later.

Here's the order of tasks:

• Database table & data migration
• Integrated with CI/CD pipeline
• Creating data models
• Linking up codebase to DB through Sequelize

In hindsight, if it might have worked better if we completed Database table & data migration last. Not that it would have made much difference in terms of implementation approach, but for sure it would have highlighted some issues much earlier on.

Writing migration scripts

When I wrote the initial migration scripts it was straight forward. The documentation wasn’t perfect, but with some light Googling, I was able to get it done. At this stage I haven’t spotted anything off-putting about Sequelize yet.

Side note: It might be good to clarify how to add static data (record with fixed id values) into the database. It could either be done as DB migration or DB data seed. Just want to mention it here as we debated the two approaches. Just FYI, we have gone with the DB migration approach at the end.

Migration pipeline

This part is not too relevant to the title of the blog, feel free to skip to the next section.

The initial idea is to run the DB migration scripts for each commit pushed to master. This way we can version control our DB changes which is the main reason for using a DB migration tool. Therefore we could have a simple rollback strategy in case we find a problem after a deployment. The project's pipeline is fairly complex, this is partly due to infrastructure is in the process of being migrated to a more modern setup.

Although Sequelize provides a rollback solution natively, we still experienced problems with how to actually trigger it. The environment setup is super strict and for good reasons e.g. security. However, this means developers can't easily access the Database (not even on QA environment), so don't even ask me about Production.

Given these strict environment configurations, how coule we rollback migration changes?

We had a few ideas as a team:

• Ideal case: the CI pipeline would do Database migration first when something is pushed to master. We'd also have a final step of the CI to run rollback if the build isn't successful. Unfortunately, making such a change wouldn't be easy or straightforward as this will require a lot of CI rework. Due to the fact no such setup has been done previously and the pipeline inherits from shared Jenkins scripts.
• Running rollback manually: because of strict envrionment setup, it wouldn't be easy to convince people to give the whole team access to Production. Personally, I think this is still useful to have in case of emergencies, even if there was a way to do auto rollback in CI.

Unfortunately, we are still trying to find a nice solution for this. Maybe I'll post an update if we find a good solution.

Creating Models

This is the first time I started using Sequelize with Typescript, and this is where the pain started.

Lack of Typescript related documentation

Right at the start, I was hit with an issue. The Sequelize documentation for Typescript is lacking to say the least. Here is the official documentation and see for yourself. There is barely enough content to fit on 1 page of A4.

Unclean approach

Even if I look past the lack of documentation issue, there are other problesm.

If you read the example provided on the doc, the way the models are declared and used isn't very clean in my opinion. The example shows that, to initialise a model Sequelize needs to establish a connection to the database and then passed into each model.

const sequelize = new Sequelize('mysql://root:asd123@localhost:3306/mydb');

class Project extends Model {
  public id!: number;
  public ownerId!: number;
  public name!: string;

  public readonly createdAt!: Date;
  public readonly updatedAt!: Date;
}

Project.init({
  id: {
    type: DataTypes.INTEGER.UNSIGNED,
    autoIncrement: true,
    primaryKey: true,
  },
  ownerId: {
    type: DataTypes.INTEGER.UNSIGNED,
    allowNull: false,
  },
  name: {
    type: new DataTypes.STRING(128),
    allowNull: false,
  }
}, {
  sequelize,
  tableName: 'projects',
});

Notice how sequelize is passed to the init() function after a connection has been established. The model itself is a Typescript interface, table column types and everything else is defined during the init() function, what?!

When I was working on this, I really struggled to see how this was a good approach to both declaring models and using them like this. The fact there is a dependency between model initilisation and sequelize having established a DB connect feels odd.

After I have spent more time with it, I can see how I could work around this issue. I also want to clarify, there isn't as much of a problem with the tool, instead, I feel like the documentation itself could be better. For example, if the example read something like the follow it would have made a lot more sense for first timers like me.

// server.ts
import Project, { projectModelConfig } from './models/project.model.ts';

const sequelize = new Sequelize('mysql://root:asd123@localhost:3306/mydb');
Project.init(projectModelConfig, {
  sequelize,
  tableName: 'projects',
});

// project.model.ts

export const projectModelConfig = {
  id: {
    type: DataTypes.INTEGER.UNSIGNED,
    autoIncrement: true,
    primaryKey: true,
  },
  ownerId: {
    type: DataTypes.INTEGER.UNSIGNED,
    allowNull: false,
  },
  name: {
    type: new DataTypes.STRING(128),
    allowNull: false,
  }
};

export default class Project extends Model {
  public id!: number;
  public ownerId!: number;
  public name!: string;

  public readonly createdAt!: Date;
  public readonly updatedAt!: Date;
}

In the end, I decided to use sequelize-typescript library instead, mainly because their syntax seemed a lot cleaner and more maintainable.

// server.ts
import Project, { projectModelConfig } from './models/project.model.ts';

const sequelize = new Sequelize({
    ...
    models: [__dirname + '/**/*.model.ts']
});

// project.model.ts

import { Table, Column, Model } from 'sequelize-typescript';
 
@Table
class Project extends Model<Project> {
 
  @Column({
    type: DataTypes.INTEGER.UNSIGNED,
    autoIncrement: true,
    primaryKey: true,
  })
	id: number;

  @ForeignKey(() => User)
  @Column
  ownerId: number;

	@Column({
		type: new DataTypes.STRING(128),
    allowNull: false,
	})
  name: string;
}

Issue 3 - Decorators

By choosing sequelize-typescript I created another problem for myself. It uses decorators by default as you can see from the example above, which means I also had to turn on experimental decorators feature in Typescript. It wasn't until later I found out that it caused other issues when we tried to write unit tests.

Linking up Sequelize to Codebase

Code

This process was painful, partly because we have a strict typing configuration in our project, but the fact Sequelize and Typescript doesn't work well together is definately a contributing factor as well.

I'll just come out and say it Sequelize with Typescript is just confusing, even after adopting sequelize-typescript this statment still holds true. I encountered several problems caused by Typescript types, but I'll just focus on the most memorable one here.

I wanted to inject Sequelize into a function to make testing a bit easier. But I simply couldn't get the type to work, I was really confused. After trial and error for ages, here is what I found out.

Have a look at the code example below. The fact Sequelize (the one we import) is not Sequelize type blows my mind. But an instance of Sequelize (connectedSequelize in the example) can be assigned the Sequelize type. Again, what?!

import { Sequelize } from 'sequelize-typescript';

const connectedSequelize = new Sequelize(...);

// "Sequelize" is NOT Sequelize type
// "connectedSequelize" is Sequelize type

So basically, I can't give Sequelize (the function param) a type because there is no type for it unless we create an instance of it. In the end, I had to leave the type to any and I feel like a horrible human being for doing it.

Unit Testing

There is litterally no documentation around how to do this properly nor are there good example shared online.

Unit testing, in general, is messy for Sequelize. Paired it with Typescript is almost asking for trouble. I won't bore you with all the problems I experienced in too much depth, so here are some key ones I experienced:

• There are Decorators errors when trying to create a new instance of a Sequelize model in test. I ended up mocking all the models in functions that require them.
• Get error complaining the model isn't initialised by Sequelize. So I added the models without any test, probably something for me to revisit now I have some ideas.
• Sequelize query returns a list of data in its own prototype, so the type doesn't match with what you'd expect the results to be. I had to resolve to any type in many instances when trying to mock something.

As you can see I didn't have a great deal of fun testing Sequelize code, especially considering this is with dependency injection (just imagine without). Normally, this would make the unit test extremely easy to write, that is not the case here.

Summary

We picked Sequelize as our Database Migration tool for the same reasons highlighted in the post (see first paragragh). At the time, it seemed more mature than TypeORM and more modern than Knex. Unfortunately, as I found out the hard way that it isn’t modern enough.

If you plan to use Sequelize in a pure JavaScript project, it will probably serve you fine. However, if there is a chance to be adding Typescript into your backend stack, then I’d recommend trying out TypeORM instead.

Other Thoughts

When I was talking to our DevOps team, I was introduced to the concept of Event Sourcing. Now looking back, maybe we could have approached our data differently and then there might not be a need for relational data structure. Thus avoiding using database migration tools altogether.

Here is some top Google suggested reading materials on Event Sourcing:

• https://microservices.io/patterns/data/event-sourcing.html
• https://docs.microsoft.com/en-us/azure/architecture/patterns/event-sourcing
• https://eventuate.io/whyeventsourcing.html
• https://martinfowler.com/eaaDev/EventSourcing.html