How to create online offline database together

#1

been a while here actually guys!

I actually need help on a project am working on. what I want to do is have my application use an sql database offline and also have an online database, something like Google mail, you know the contact backup in Google that works offline, you save a contact and it automatically updates it when you are connected to the internet. just something exactly like that. I don’t know if am explanatory enough with this anyways but just something that works offline and can always migrate changes to the online database when connected.

I really don’t know how or where to start from and I really hope to get my way around here. thanks in advance

#2

Hey @meshelleva4815 thanks for your question, sorry for the delay – I recently had a baby and I’ve been a little busy!

What you’re describing is an online/offline sync. There are a lot of complications with doing this – for example, can your app be run in multiple different places? If so, you need sync to work in two directions (from the server back to the client) rather than in one way. This also introduces the possibility of conflict.

Normally what you would do is –

  1. When starting the client should (try and…) sync the latest complete state with the remote server. This can be done by retrieve the full db (if it is small), or by retrieving the sequence of operations since our last connect.
  2. Once synced, the client should record it’s operations. For a database this can be a series of database operations (update/insert) etc. along with time (order) that they occurred.
  3. If connected, these can be sent to the server as they happen, or batched. If disconnected, the client should keep a log of them, to send when the reconnection occurs.

If you have conflicts – e.g. two separate clients have made conflicting changes, such as edited a field to different values – you’ll need some way to resolve this. Usually you would ask the user.

Hope that’s enough to get started?

#3

sorry am just replying to this. I replied back with a mail actually, didn’t know it’s not going to reflect here.
thanks for the ideas you gave but my main problems now are:

  1. understanding the working principles. that is how it works in the background. I guess I will definitely need two databases for it, but does it have to be same language(like sqlite, or mysql) for the two db, if same language is possible, do I just duplicate the one I have and paste in another folder for testing.
  2. how do I create the log that helps keep track on the activities of the offline db since the last login…

now I have an idea and a rough picture of what I should do to start with your exposition but will be glade if you can help with this above 2concerns. thanks in advance boss. how is your baby…:grin::grin:

#4

Hey @meshelleva4815

It would definitely be easier for you if both databases were using the identical version/dialect. Firstly, for writing/debugging you can test both sides of the setup without needing to translate between them. Secondly, you know any operations you do one one can be done on the other – different databases have different limitations, particularly around more complex field types, etc.

That said, using sqlite on your local database does have the advantage that it’s very portable and doesn’t require a separate database installation. It’s a trade off, which will depend on what sort of data you’re storing. For simple stuff, and sticking to standard SQL you should be OK with sqlite + mysql/postgresql.

As for how to log these changes… you have a lot of options. The simplest would be to simply log the SQL operations to file or and to submit these to the server (executing them) on reconnection. Another would be to define a custom “language” to describe the record mutations used to regenerate the SQL operation on the target system, but probably a bit redundant.

This only works if you have a single client though. Do you? If not, you’re going to need to think of a way to handle and resolve conflicts – e.g. if two clients edit the same record, and sync later, what do you do? As soon as one operation fails, any subsequent operation cannot be assumed to work (use transactions and you can roll back, but then?)