What is Database Migration?

Database schema changes is typically a concern in relational DBMS (Database Management Systems), document based DBMS doesn’t have this problem. Why? Relational DBMS often rely on established schema to enforce the relationships between records, so it is crucial to maintain these relationships. Document based DBMS often has no schema (at least not enforced by the DBMS itself), so as developers we could store anything we like in any records e.g. I could have a record storing user data, and the next record could be about different types of potatoes.

Making changes to a database is probably unavoidable, sooner or later we will find ourselves in situations where we need to make some modifications. If we add a new field, then what should the default value be? If no default value is provided then the old records will have no data for that field, the application assuming the data might struggle to coup with this edge case since the app might always expect some data to be returned. Or worse, we might need to add or delete tables to a production database. What is the best way to make these changes, what should we do if something goes wrong?

These questions are also solved by a database migration tool. A database migration tool should be able to iteratively introduce changes by based on its versioning system e.g. it might decide to do nothing if it is already on the latest version, but if it find it self behind in version it could then run the versions that’s missing and introduce those changes to the relevant database.

Investigation

Having decided we wanted to implement DB migrations and settled on the Database schema as a team, the next step is to investigate what might be the best tool for database migrations. Our API is using Node, so the solution we are looking for here is a JavaScript based tool.

Expectations

In terms of expectations of this spike, we hoped to find something that:

• Support adding, editing and deleting tables and column

e.g. we might want to add new tables in the future or modify fields in a given table

• Support inserting, editing and deleting records

e.g. similarly, we have seeded data that we might want to modify later on

• Support seeding data

e.g. put add in static data such as brands, cookie categories, cookies and etc

• Support versioned incremental migration changes (automatically)

e.g. we should be able to add new changes into a version controlled file, this should then be picked up and only new changes should be picked up and ran by the migration tool

• Support migration reverts (in the event of causing issues on production)

e.g. the tool should be able to take revert migration logic, which means we can safely and easily undo our migration if the changes causes issue on production

Different choices

After doing a bit of research online, these seems to be the top choices right now:

• Knex - already using this in our app
• TypeORM - is built for TypeScript and may end up being a better choice than Knex
• Sequelize seems to be very popular as well

It is worth noting that all of the above supports TypeScript according to their documentation pages.

Knex v.s. TypeORM v.s. Sequelize

Expectations

For TypeORM, there’s this nice unofficial plugin we can use TypeORM Seeding

Codebase

All three libraries are actively maintained, with frequent commits with the last 30 days and before that.

Here’s a breakdown of the numbers of issues and PRs on each repo:

Documentation

I went through the documentation all 3 libraries. Knex’s documentation is detailed but very difficult to read, both in terms of how the content is structured and presented. I found that everything I needed are documented, just not in an ideal way. TypeORM is on the other side of the scale, it is very well presented and has plenty of examples. I was able to find what I needed very quickly, and everything seems intuitive enough. However, there seems to be a lack of explanation for some of the sections. Often the documentation involves a brief statement, then shows an example and that’s it. Finally, Sequelize is somewhere in between. It seems to have the best documentation in terms of detail, its presentation isn’t as good as TypeORM but definitely better than Knex.

Testability

All three options has similar levels of testability. It is possible to extract out migration logic to functions and then unit testing those functions. The only exception is that due to TypeORM doesn’t have official seeding functionality, unit testing it might be slightly complicated.

Typescript Support

On paper all three tools support Typescript.

Knex admits that Typescript isn’t first class citizen.

However it is to be noted that TypeScript support is currently best-effort. Knex has a very flexible API and not all usage patterns can be type-checked and in most such cases we err on the side of flexibility. In particular, lack of type errors doesn't currently guarantee that the generated queries will be correct and therefore writing tests for them is recommended even if you are using TypeScript.

Sequelize also mentions that Typescript probably will do very little when they are paired together.

As Sequelize heavily relies on runtime property assignments, TypeScript won't be very useful out of the box. A decent amount of manual type declarations are needed to make models workable.

TypeORM just mentions the fact they support Typescript. After some deeper digging around, I found the TypeORM Typescript example. I could really find cases where TypeORM really made use of Typescript, see the code snippets below:

// typescript-example/src/index.ts
  
import {createConnection} from "typeorm";
import {Post} from "./entity/Post";
import {Category} from "./entity/Category";

// connection settings are in the "ormconfig.json" file
createConnection().then(async connection => {

    const category1 = new Category();
    category1.name = "TypeScript";
    await connection.manager.save(category1);

    const category2 = new Category();
    category2.name = "Programming";
    await connection.manager.save(category2);

    const post = new Post();
    post.title = "Control flow based type analysis";
    post.text = `TypeScript 2.0 implements a control flow-based type analysis for local variables and parameters.`;
    post.categories = [category1, category2];

    await connection.manager.save(post);

    console.log("Post has been saved: ", post);

}).catch(error => console.log("Error: ", error));
// typescript-example/src/entity/Category.ts

import {Column, PrimaryGeneratedColumn, Entity} from "typeorm";

@Entity()
export class Category {
    @PrimaryGeneratedColumn()
    id: number;

    @Column()
    name: string;
}

Suggestion

Best to avoid Knex since it seems to be the worse choice in many areas, there’s very little that it outshines either TypeORM or Sequelize.

Out of TypeORM and Sequelize, Sequelize seems like the safer option with the added benefit of a bigger community. This means nice plugin and tools such as (sequelize-ui, sequelizer, etc).

Last but not least, TypeORM looks like to be the new kid of the block making use of decorators and etc. Compared to Sequelize’s philosophy of “getting migration done in a stable way”, TypeORM seems to be more focused on “doing migration in the right way”. This can be seen from its documentation, there’s a lot of emphasis on “reducing duplication”.

Update (8th March 2020)

After going with Sequelize and working with it for a few months, here are my latest update on this subject Why I would avoid Sequelize in the future.