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

Still not. Didn't find out a good way to solve this problem. If you are facing the same issue? When you solved please give a solution here. I just hold the project for few months. Thanks — Mohammad Ashraful Islam, Dec 18 '18 at 03:06
I will. But for the moment for the sake of progressing, I am using a server side implementation (Laravel + mySQL) to handle managing the data that I initially intended to store on local. I previously used sqlite with electron, but it felt wrong in several ways. Many ORM modules are either deprecated, or missing in features, or do not come with migrations management integrated. Hence why I am using Laravel for now. — joeyj, Dec 19 '18 at 04:41
In my case, I am using PostgreSQL with Python. But I also need client-side storage. Client badly wants to work in offline. So, no way to scape my plan. — Mohammad Ashraful Islam, Dec 19 '18 at 10:44

score 3 · Accepted Answer · answered May 20 '19 at 02:57

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');
    }

};

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

1 Answers1