Tech Stack
I have my go to technology when building mobile apps, and it changes sometimes. Right now it's Ruby on Rails and Flutter. It seems to just work, but this is not about framework choice. You can use SQLite do built this system in any mobile app. I want to outline how I did it, and the edge cases I ran into. It doesn't work perfectly, but it works. It works really well actually. It works so well that I've worked it into other apps.
What was I Building?
Turnboards is a way for me to keep track of all the projects that I work on. It groups work items into Boards which represent each thing I'm working on. A board can be anything - a mobile app, a sewing project, a fabrication job, a landscaping job, etc. It's a collection of work items. Sounds easy right? Well, not so much once you get into it.
I wanted it to work for a company - where you could assign work to everyone and each person could select themselves and just see what they have to do. It's a lot easier than writing things on paper, and a lot less complicated than other "project management" apps.
The whole point of this was to make it easy to use, otherwise I may as well use one of the thousands of other apps out there that manage work items. I wanted it to be super simple, have no spinners and need no internet - yet at the same time it has to sync back to the server and store data in the cloud.
A lot of models
My rails backend has a lot of models, as most do. Between notes, boards, organizations, users, items, and relationships between them - I'm at 31 models so far so that’s thirty one Postgres tables in my server database. Not all of those are needed on the app side, but a lot of them are.
I did a lot of research on offline data management for mobile apps, and there’s just not a lot out there. I’ve seen a few libraries here and there that are designed for this, but here’s the problems I was dealing with:
- Need CRUD operations to work on the mobile app indefinitely without internet
- It needs to sync all those actions back to the database once it has a network connection again without the user ever having to know about it.
- The database has many relationships between models that all have to be in sync.
- Multiple users can be logged into the same organization so they are sharing data, and all updating separately from different devices.
- Data needs to be synced across all the devices that are sharing data.
- Errors have to happen gracefully so syncing doesn't get "stuck"
My Solution: A queuing system with SQLite
Logically I just thought of how to do this myself - didn’t get any advice. I wasn’t finding much online. I still haven’t thought of another way. Here’s how I did it:
Build a queuing system that every time a CRUD action takes place on the device, you record an api call row in a queue with the action, model name, and local model id. So I’m running Postgres on the backend and SQLite on the client. So there is a backend primary key and a frontend primary key. Every action you take in the app, records what api call needs to happen (create, update, destroy) to update the backend. I just do the action in the SQLite db, and it happens instantly because it’s on the client - no spinners, no waiting, and no internet required.
Then I have a timer on the queue table and every few seconds or so, it will get the oldest record in that queue and build an api call that does what that action originally did in the UI. If it fails, it will exit and retry that same record the next time. It has to chew through the api call queue in the order that those records were created so nothing gets out of whack. So it’s recording the action, saving it for later, and mimicking what you did in the UI.
Lots of Edge Cases
- What happens if you delete a record on your device and the api queue succeeds and deletes the record on the server. Someone else that’s logged in has that record on their device and they try to update it after you have deleted it. That api call would fail and their queue would get stuck infinitely because that call would never succeed and delete the that api queue row on the return. That’s how this would work - on the success of the api call done through the queue it would delete that queue record on success. Then the queue timer would move on to the next one. If the chewing through of that api queue table gets stuck at any point, this system fails.
- The return of the api create call has to update the local record with the primary key from the database, so that future updates will work.
- Any records with associations need to update the SQLite record with foreign keys from the database so that syncing can work correctly on the device.
Does anyone else have any experience with all of this? It gets complicated really fast, having to manage two sets of primary keys and foreign keys for both server and client. You also have to run migrations on the device with app updates.
DRIFT & Flutter
I used this library
https://pub.dev/packages/drift. It’s the SQLite wrapper for flutter and lets you do migrations and build relational database in a flutter app. I don’t personally know of a lot people using this library, and I looked at other options like
https://github.com/isar/hive. I used this for other things, but went with SQLite because I needed to model my backend data structures on the client.
Test it out - this is the app where I did all of this - it’s a slimmed down work item creation tool that I use to keep track of my own projects.
The only real issue I’ve run into is if I delete an item and then re-sync, it can come back because the api call hasn’t had time to run and delete the record on the server before I sync.
This seems like overkill to me. I couldn’t think of another way to do something like this other than recording everything and doing everything in order and ensuring that the api calls don’t fail, and the queue gets stuck.
Positives
This system is very repeatable - just write the code to handle the CRUD action for each model, and you can do this for as much of your database as you want. Except for stuff like login (obviously), you could convert an entire app's api calls to run through a queue instead of happening in response to a UI action. This could be the standard way companies solve this problem - I've just never seen it written out like this.