Sync indexedDB with mysql database

Question

I am about to develop an application where employees go to service repair machines at customer premises. They need to fill up a service card using a tablet or any other mobile device. In case of no Internet connection, I am thinking about using HTML5 offline storage, mainly IndexedDB to store the service card (web form) data locally, and do a sync at the office where Internet exists. The sync is with a MySQL database.

So the question: is it possible to sync indexedDB with mysql? I have never worked with indexedDB, I am only doing research and saw it is a potential.

Web SQL is deprecated. Otherwise, it could have been the closer solution.

Any other alternatives in case the above is difficult or outside the standard?

Your opinions are highly appreciated.

Thanks.

Answer 1

[Update for 2021]

For anyone reading this, I can recommend to check out AceBase. AceBase is a realtime database that enables easy storage and synchronization between browser and server databases. It uses IndexedDB in the browser, and its own binary db format or SQL Server / SQLite storage on the server side. MySQL storage is also on the roadmap. Offline edits are synced upon reconnecting and clients are notified of remote database changes in realtime through a websocket (FAST!).

On top of this, AceBase has a unique feature called "live data proxies" that allow you to have all changes to in-memory objects to be persisted and synced to local and server databases, so you can forget about database coding altogether, and program as if you're only using local objects. No matter if you're online or offline.

The following example shows how to create a local IndexedDB database in the browser, how to connect to a remote database server that syncs with the local database, and how to create a live data proxy that eliminates further database coding altogether.

const { AceBaseClient } = require('acebase-client');
const { AceBase } = require('acebase');

// Create local database with IndexedDB storage:
const cacheDb = AceBase.WithIndexedDB('mydb-local');

// Connect to server database, use local db for offline storage:
const db = new AceBaseClient({ dbname: 'mydb', host: 'db.myproject.com', port: 443, https: true, cache: { db: cacheDb } });

// Wait for remote database to be connected, or ready to use when offline:
db.ready(async () => {

    // Create live data proxy for a chat:
    const emptyChat = { title: 'New chat', messages: {} };
    const proxy = await db.ref('chats/chatid1').proxy(emptyChat);  // Use emptyChat if chat node doesn't exist

    // Get object reference containing live data:
    const chat = proxy.value;

    // Update chat's properties to save to local database, 
    // sync to server AND all other clients monitoring this chat in realtime:
    chat.title = `Changing the title`;
    chat.messages.push({ 
        from: 'ewout', 
        sent: new Date(),
        text: `Sending a message that is stored in the database and synced automatically was never this easy!` +
              `This message might have been sent while we were offline. Who knows!`
    });

    // To monitor realtime changes to the chat:
    chat.onChanged((val, prev, isRemoteChange, context) => {
        if (val.title !== prev.title) { 
            console.log(`Chat title changed to ${val.title} by ${isRemoteChange ? 'us' : 'someone else'}`); 
        }
    });
});

For more examples and documentation, see AceBase realtime database engine at npmjs.com

Answer 2

A little bit late, but i hope it helps.

This is posible, am not sure if is the best choice. I can tell you that am building a webapp where I have a mysql database and the app must work offline and keep trace of the data. I try using indexedDB and it was very confusing for me so I implemented DexieJs, a minimalistic and straight forward API to comunicate with indexedDB in an easy way. Now the app is working online then if it goes down the internet, it works offline until it gets internet back and then upload the data to the mysql database. One of the solutions i read to save the data was to store in a TEXT field the json object been passed to JSON.stringify(), and once you need the data back JSON.parse(). This was my motivation to build the app in that way and also that we couldn't change of database :
IndexedDB Tutorial
Sync IndexedDB with MySQL
Connect node to mysql

Answer 3

This is definitly do able. I am only just starting to learn indexeddb the last couple of days. This is how I would see it working tho. Sorry dont have code to give you.

1. Website knows its in offline mode somehow
2. Clicking submit form saves the data into indexeddb
3. Later laptop or whatever is back online or on intranet and can now talk to main server sends all indexeddb rows to server to be stored in mysql via an ajax call.
4. indexeddb is cleared
5. repeat