It sounds like you are looking for offline-first architecture. In short, in this architecture, your app communicates only to the local database. And your local database (SQLite) would communicate with the server database (MySQL).
To sync the two databases, you’ll have to send (via API) the updated or new data from the local database to a server where you’ll handle the merge of the data and then get back the merged data to save it back in the local database in case there is any updated or new data on the server database.
While syncing the database the most significant issue you’ll face is conflict in data from multiple client apps. For example, two people update the same data at the same time. So, you will have to make some decisions on the server side like who’s data shall be of higher priority.
In the local database, you’ll have to maintain the sync status of each table like the last synced data-time and last updated date-time. And on the server database, you’ll need the last updated date-time of each table. Then with the help of these dates, you can figure out if there are any new or updated data that needs to be synced.
2
Answers
It sounds like you are looking for offline-first architecture. In short, in this architecture, your app communicates only to the local database. And your local database (SQLite) would communicate with the server database (MySQL).
To sync the two databases, you’ll have to send (via API) the updated or new data from the local database to a server where you’ll handle the merge of the data and then get back the merged data to save it back in the local database in case there is any updated or new data on the server database.
While syncing the database the most significant issue you’ll face is conflict in data from multiple client apps. For example, two people update the same data at the same time. So, you will have to make some decisions on the server side like who’s data shall be of higher priority.
In the local database, you’ll have to maintain the sync status of each table like the last synced data-time and last updated date-time. And on the server database, you’ll need the last updated date-time of each table. Then with the help of these dates, you can figure out if there are any new or updated data that needs to be synced.
You want to keeps data in sync offline or online, between devices and servers?
Use package: https://pub.dev/packages/objectbox
For more information, visit: https://objectbox.io/sync