Herbs DB Migrations - Change your domain and your database will follow

[dalssoft]

Problem

Changing a working software is hard. Especially when it is connected to a database.

Keeping the database schema in sync with the domain is timing consuming and error prone.

Solution

Herbs mantra is to make developers focus on the domain and let the tools handle the infrastructure code.

Herbs DB Migrations should help developers to keep your database schema in sync with your domain in a simple and easy way.

Ex: on herbs update it should check if the database schema is in sync with the domain and if not, it should update the database schema.

How it should work

The idea is to have a three phase process:

1. Generate a Virtual Schema from Domain (VSD)
2. Generate a Virtual Schema from Migrations (VSM)
3. Diff VSD and VSM and generate the new migrations

Generate a Virtual Schema from Domain (VSD)

The VSD is a representation of the domain in a database schema. It should be generated from the domain entities.

It should handle the one-to-many and many-to-many relationships. It also should be agnostic to the database type.

Ex:

const User = 
    entity('User', {
        id: id(Number),
        name: field(String),
        lastAccess: field(Date),
        accessCount: field(Number),
        hasAccess: field(Boolean),
        plan: field(Plan),
        groups: field([Group])
    })

Should generate something like this:

const VSD = {
    tables: [
        {
            name: 'users',
            fields: [
                { name: 'id', type: 'number', isId: true },
                { name: 'name', type: 'string' },
                { name: 'lastAccess', type: 'date' },
                { name: 'accessCount', type: 'number' },
                { name: 'hasAccess', type: 'boolean' },
                { name: 'plan_id', type: 'number', fk: { table: 'plans', field: 'id' } },
            ]
        },
        {
            name: 'plans',
            fields: [
                { name: 'id', type: 'number', isId: true },
                ...
            ]
        },
        {
            name: 'groups',
            fields: [
                { name: 'id', type: 'number', isId: true },
                ...
            ]
        },
        {
            name: 'users_groups',
            fields: [
                { name: 'user_id', type: 'number', fk: { table: 'users', field: 'id' } },
                { name: 'group_id', type: 'number', fk: { table: 'groups', field: 'id' } },
            ]
        }
    ]
}

Generate a Virtual Schema from Migrations (VSM)

The VSM is a representation of the database schema from the migration files. In the example above, it comes from the Knex migration files.

Example of Knex migration files:

exports.up = function(knex) {
    return knex.schema
        .createTable('users', function (table) {
            table.increments('id')
            table.string('name')
            table.date('lastAccess')
            table.integer('accessCount')
            table.boolean('hasAccess')
            table.integer('plan_id').references('id').inTable('plans')
        })
        .createTable('plans', function (table) {
            table.increments('id')
            ...
        })
        .createTable('groups', function (table) {
            table.increments('id')
            ...
        })
        .createTable('users_groups', function (table) {
            table.integer('user_id').references('id').inTable('users')
            table.integer('group_id').references('id').inTable('groups')
        })
}

Instead of running the migration file using Knex and change the DB schema, we should implement the same functions / API exposed by Knex and generate the VSM.

Should generate something like this:

const VSM = {
    tables: [
        {
            name: 'users',
            fields: [
                { name: 'id', type: 'number', isId: true },
                { name: 'name', type: 'string' },
                { name: 'lastAccess', type: 'date' },
                { name: 'accessCount', type: 'number' },
                { name: 'hasAccess', type: 'boolean' },
                { name: 'plan_id', type: 'number', fk: { table: 'plans', field: 'id' } },
            ]
        },
        {
            name: 'plans',
            fields: [
                { name: 'id', type: 'number', isId: true },
                ...
            ]
        },
        {
            name: 'groups',
            fields: [
                { name: 'id', type: 'number', isId: true },
                ...
            ]
        },
        {
            name: 'users_groups',
            fields: [
                { name: 'user_id', type: 'number', fk: { table: 'users', field: 'id' } },
                { name: 'group_id', type: 'number', fk: { table: 'groups', field: 'id' } },
            ]
        }
    ]
}

As you can see, the structure is the same as the VSD. It means it is agnostic to the database type. So in the future, we could support other libs like Sequelize or TypeORM.

Diff VSD and VSM and generate the new migrations

Since we have the VSD as the desired state and the VSM as the current state, we can diff them and generate the new migrations.

There are some libs that can help us to do this diff. Ex: https://github.com/flitbit/diff

Once the diff format is generated, we can use the Knex API to generate the migration files.

Conclusion

As far as I know, there is no such tool that does anything similar to what I described above. Been able to change your domain and your database will follow is a game changer.

This is no small task and I bet there are lots of corner cases that would make this task harder than I described above. But I think it is feasible and worth the effort.

Extra Info

There are other issues and efforts related to this topic that I think are worth mentioning:

herbsjs/herbs-cli#145

The reason I suggested this is beacuse it is more generic, works not only with new entities but also with existing ones: if you change a existing entity, it should generate the new migrations.