Apply Sqlite/Any SQL Database Migrations with Electron [Windows, Mac]

Question

My Scenario,

I am working on a desktop based application. Where My big challenge is keeping data into relational DB(Offline) and sync accordingly(Company have their own syncing algorithm). I am using Electron and VueJS as client side. For building the desktop app I'm using electron-builder. I am able to write migrations with raw SQL or various ORM.

What I want?

While I'll install into a desktop, I want to create the database file and apply all the migrations on the client's computer. I just don't know that part how to do that. I also looked into Electron Builder Docs. But didn't understand. I need an example, any idea.

Please help me. Thanks

Answer 1

After doing a lot of research I found an awesome solution provided by sequalize.js. I found a library Umzug Github. Let's look at the implementation...

    /**
     * Created by Ashraful Islam
     */

    const path = require('path');
    const Umzug = require('umzug');
    const database = /* Imported my database config here */;

    const umzug = new Umzug({
        storage: 'sequelize',
        storageOptions: {
            sequelize: database
        },

        // see: https://github.com/sequelize/umzug/issues/17
        migrations: {
            params: [
                database.getQueryInterface(), // queryInterface
                database.constructor, // DataTypes
                function () {
                    throw new Error('Migration tried to use old style "done" callback. Please upgrade to "umzug" and return a promise instead.');
                }
            ],
            path: './migrations',
            pattern: /\.js$/
        },

        logging: function () {
            console.log.apply(null, arguments);
        }
    });

    function logUmzugEvent(eventName) {
        return function (name, migration) {
            console.log(`${name} ${eventName}`);
        }
    }
    function runMigrations() {
        return umzug.up();
    }

    umzug.on('migrating', logUmzugEvent('migrating'));
    umzug.on('migrated', logUmzugEvent('migrated'));
    umzug.on('reverting', logUmzugEvent('reverting'));
    umzug.on('reverted', logUmzugEvent('reverted'));

    module.exports = {
        migrate: runMigrations
    };

Idea behind the scene
I clearly declare the migration directory. Also, define the file matching pattern. Umzug just read files from there and run the DB migration. An example migration file is following...

// 000_Initial.js

"use strict";

module.exports = {

    up: function(migration, DataTypes) {

        return migration.createTable('Sessions', {

            sid: {
                type: DataTypes.STRING,
                allowNull: false
            },

            data: {
                type: DataTypes.STRING,
                allowNull: false
            },

            createdAt: {
                type: DataTypes.DATE
            },

            updatedAt: {
                type: DataTypes.DATE
            }

        }).then(function() {
            return migration.addIndex('Sessions', ['sid']);

        });

    },

    down: function(migration, DataTypes) {
        return migration.dropTable('Sessions');
    }

};