History
At the time I was programming apps for Windows CE and Windows Mobile, we used SQL CE. It integrated nicely with the Visual Studio tooling at that time and it was easy to work with. The downside of SQL CE was the likelihood of database corruption. The corruption could occur if the battery was pulled, forced reboot, or if the SD card was ejected. On some hardware, the SD Card was ejected (in software) if the device went to sleep. So this was a nightmare.
Can you imagine what happens to customer satisfaction when he loses a whole day of work?
When looking for alternatives, I found SQLite. There was no compiled executable for Windows CE at that time, but I found a source, ready for compilation to Windows CE.
SQLite has proved its value since that day, more than 10 years ago. I never encountered a corrupt database anymore.
Why a local SQL datastore
In today’s connected world, one might ask why there is a need for a local SQL datastore on the device. There is a difference between the requirements of a consumer app and a Line of Business app.
- Network connectivity
- Batch applications
- Speed
- Large datasets
- Complex queries
- Data integrity
- Database versioning
- Downsides
Network connectivity
The work that needs to be done with Line of Business apps is not always at places with a fast or even present network connection. Think of basements, old warehouses, container yards or the countryside. A company wants to use the app reliably at 100% of the locations. Therefore, a lot of Line of Business apps are batch applications.
Batch applications
Batch applications work in cycles. A sample of a cycle is here below:
| # | Network available | Action |
| 1 | yes | At company: download master data |
| 2 | Perform work (user collects data) | |
| 3 | maybe | If connection detected, upload collected data |
| 4 | Goto 2 until finished | |
| 5 | yes | At company: upload collected data |
Speed
With manual data entry, the speed of a lookup is not very important. If it took a user 8 seconds to type a number, it does not matter much if checking the number by the app takes 2 seconds.
But most Line of Business apps that I write, run on hardware with dedicated barcode scanners. Scanning a barcode is almost instant. Waiting 2 seconds for the app to respond is not acceptable in this use case.
Looking up data in an SQLite database is very fast and generally a matter of milliseconds. Even a million rows of master data is accessed without a noticeable delay.
This kind of speed is needed when an employee scans items in a retail shop to order at their supplier.
Large datasets
The consequence of a batch application is that you have to download all the needed master data to the device. Sometimes this is just a few lines, but in some cases, it might be a million rows of data.
An approach is to download the dataset as a zip file. A zip file has the following advantages:
- Size of the transfer
Improves speed over slower network connections - Detect data corruption
When the zip files extract successfully, you have a high degree of certainty that the data is complete and not corrupted - Have a coherent dataset
With a zip file, you can download several files at once and be certain to have a coherent dataset.
It may take a minute to (batch) insert large datasets into an SQLite database. Normally this is not an issue, as it only happens once a day. You can also automate this process in software by starting the download automatically before the workers start their day.
Complex queries
A big advantage of using an SQLite datastore is that you can run complex queries against it.
An application might start simple with a few tables, relations, and statuses. But as the customer explores the power and benefits of the app in their logistical process, it is almost certain that the customer will ask for more functionality. With the power of SQL and SQLite, you can be certain that there will be no limitation on the complexity of the queries.
Data integrity
Data integrity is as important as the integrity of the source code of the app. If the data is corrupted, the program might get in a loop, behave unexpectedly or even crash your application. The size of the database might also get out of control.
A good data model with the right (composite) primary keys and foreign keys will help you to keep your data valid.
Database versioning
As the requirements of the customer changes, it will also influence your data model. It is good to know that SQLite can help you to upgrade and/or downgrade the version of your data model.
With the onUpgrade and onDowngrade methods, you can update or downgrade the data model. Although this is something that you have to write manually, you can take measures that the data in the tables are transformed in the right way.
Downsides
Of course, there are also downsides to a local SQLite datastore:
- Skills needed
You need to know how to design and query SQL databases. This can be a complex and challenging subject and can be done wrong in more than a thousand ways. - Async data access
Every call to the database is asynchronously. Be prepared that when you navigate to a page, the data will always come later. This will make your application more complex. - No web support
There is no direct SQLite support for the web at this time. There are some proof of concepts, like Moor which might be useful for demo purposes. But I don’t expect that serializing the database after each update does anything good for performance and reliability.