Making the Transition From Flat Files to SQLite

In December, January, and February, I posted a short series of blogs on Flat file systems. The series of blogs focuses on the continued use of flat files and why they are no longer viable for use in the future.

The first installment focused on flat files and why embedded software application developers readily adopted them. Then in the second installment, I discussed why embedded developers are reluctant to use databases. The third installment looked at why developers cling to the flat file systems. The final installment provided a checklist for embedded developers migrating off flat files.

For this next series, I’d like to turn my attention to the next stepping-stone in many embedded developers’ paths away from Flat Files, SQLite. While it’s progress, I’d like to convince you that you should step right over this stone or jump off it if you’re still teetering on it.

Perhaps we should start with the point of why and how it’s a positive step up from flat files. That, of course, would have to be with respect to where and how it’s applied to real-world requirements. According to the SQLite folks themselves, as stated on the SQLite website, Appropriate uses for SQLite: “SQLite does not compete with client/server databases. SQLite competes with fopen().” This statement is tied to their fundamental tenant that they aren’t meant to be compared with SQL database engines because SQL database engines are intended to be a shared repository for Enterprise data. To be a shared repository, according to the SQLite site and therefore the community, an SQL database engine needs: Scalability, Concurrency, Centralization, and Control. SQLite, on the other hand, is focused on local data storage for individual applications and devices where there is a requirement for zero database administration (they then provide the standard laundry list of typical IoT devices).

The SQLite folks also say it would be great in embedded environments if you could have a single compact file format to support cross-platform data transfer and as well as ad-hoc, home-grown data sets of multiple data types. In fact, SQLite has done benchmarking to show they are 35% faster than file systems and reading and writing this type of data through fread() or fwrite()¹.

We agree with most of what SQLite folks are saying: yes, SQL database engines should meet the requirements listed above. Yes, there is a need for local data management that is portable across platforms and can support multiple data types; and, finally, there is definitely no way to avoid a zero-administration environment at the edge for mobile and IoT.

So, with that being said, “U” know what’s coming next, that three-letter word that begins with a “B” and ends with a “T”: BUT. But what if you could have your cake and eat it too? But what if you could get all the features and advantages of Enterprise shared datastore characteristics from an SQL database engine, yet have the ability to scale it down and embed it directly into a local Mobile or IoT application, supporting multiple data types, portability across platforms and required zero database administration?

The answer you should give is yes. However, you may push back with the following reasonable positions:

Firstly, SQLite and file systems are free and popular. Open source SQL database engines (MySQL, Postgres, MariaDB, etc.) aren’t able to run scaled down to an IoT or Mobile footprint. Neither can I run them embedded in my applications.

Secondly, why do people use such a stupid analogy like you can have your cake and eat it too? It’s more like I need a car, not a truck, that’s overkill, so why bother?

Well, my response to you would be that’s so 2015 (by the way, that’s the last time SQLite updated their “appropriate use” page). The fact is, in 2020 and definitely, in 2025, you will need the functionality of a car and a truck with any application you build anyway. Yes, you need to store and analyze far more data locally, even with WiFi-6 and 5G bandwidth. Yet, the sheer increase in the volume of data and the need to handle peer-to-peer and edge-to-cloud device management, sharing of contextual data for governance, common operational pictures, and the like will dictate as much as possible and will still need to take place locally to avoid latency.

Furthermore, many peer-to-peer and edge-to-cloud operations – not to mention gateway operations where you’d take in data from multiple downstream sources – require concurrency for, and control of, those downstream data sources. Gateways and edge datastores will also require scalability such that you can use the same architecture and data portability across platforms. Finally, as you move more functionality to that gateway that would have been in the data center or the cloud, what was considered centralization functionality needs to move there as well.

So, think of this as your car needs two-wheel drive in most instances, but it needs the option of all-wheel drive in the rest. Your car also may need towing capacity. But, also think of this in the reverse, your truck isn’t always towing a boat or being used for off-roading, and maybe it’s carrying additional passengers, and you want many of the comforts of a luxury car.

To summarize, with respect to data management and this analogy, the edge in 2020 and into the next five years requires everything that was needed in the data center for an SQL Data Store (your truck requirements). But also, all that was needed for local device data management when it was standalone (your car requirements).

In essence, you need an SUV that scales from a very small CRV up to a monster size one. This is, unfortunately, not what SQLite is capable of doing, and invariably, when you use SQLite, you’re forced to bolt it on to some other database on the other end. We’ll discuss the drawbacks of this forced marriage in the next blog.

Ready to reconsider SQLite, learn more about Actian Zen. Or, you can just kick the tires for free with Zen Core which is royalty-free for development and distribution.

About Actian Corporation

Actian makes data easy. We deliver cloud, hybrid, and on-premises data solutions that simplify how people connect, manage, and analyze data. We transform business by enabling customers to make confident, data-driven decisions that accelerate their organization’s growth. Our data platform integrates seamlessly, performs reliably, and delivers at industry-leading speeds.