Reading Existing Data

Getting Started

The first task was to see if we could mock up an app in Flutter and check that it could actually read the sqlite database from the existing application.

This is key as:

  • we've over 30,000 transactions in our existing database and we don't want to lose those

  • user's need a migration path to the new app. We don't have many users, but it would be nice to support the ones we have

Flutter does support sqlite with plugins. A quick perusal at some of the plugins led us to choose Moor as it can read existing sqlite databases and has some nice features above the basic sqlite offerings.

Initial Android App

Flutter provides a simple way to create an example app, and with this and a few hours of effort, including some head scratching with Moor (a "not equal" query with select count(*)), we got an app up and running that could read the existing database. Moor has a nice feature that can create a database from a copy of an sqlite db so this was trivial to get the data in.

At the side is an image showing it running on an Android phone, with a basic about screen that mimics the original's information screen above.

And There's More

Now, a big selling point of Flutter is that it supports many platforms. Initially this was just Android and iOS, but over time it has expanded to Desktop and Web.

We've already done Flutter apps for Android and iOS, but those apps have some plugin dependency constraints that make them unable to run on other platforms without some major rework. However, since this is a new project, we thought we'd set out to make it compatible with as many platforms as possible.

So again, the challenge was can we read the database on multiple platforms. And the answer is yes, so here are some screen shots of the same app running as an app on Windows and as a web app in a browser.

One of the reasons we chose Moor is that it does support all these platforms which is an amazing achievement.

What Now?

Having proved we can read the database on multiple platforms, we are enthused by this idea. Running in the web browser needs some careful thought, as the data might easily be blown away by clearing the browser cache, so we're not sure of the long-term viability of that unless we have a simple and fool-proof backup system.

The "elephant in the room" though, is can we synchronise the data between all these clients? That would be a great thing to do and we're contemplating how. We already hold all the transactions in a single database file, so using something like Google's cloud firestore to hold a transaction log that could be synchronised to all devices is conceptually simple. The challenge is managing the inevitable conflicts around synchronising the case where one device has new transactions for an account that has been renamed or deleted (not that we allow account deletion, but you get the idea).