SQLite and SQLite alternatives - databases for the Mobile and IoT edge

Updated in March 2022: comparison of mobile databases / edge databases

Digitalization is still on the rise, and so is the number of digital devices (from 15 billion mobile devices operating + 13 billion connected IoT devices in 2021 already). Due to this, data volumes grow accordingly (estimated 79 zettabytes of data created in 2021), and centralised computing can no longer support the current needs. This has led to new challenges in computing and subsequently is leading to a shift from the cloud to the edge.

Consequently, the Edge Computing market is expected to grow by 14.8% in 2022 and the years to come (IDC 2022). Hence, there is an increased need for data persistence on the edge. Data flows to / from and between edge devices can be done with Edge Databases and Data Sync. For this reason, we have a look at the edge database market in the following review.

Databases

While being quite established and somewhat saturated, the database market is still growing consistently and significantly. The reason is that databases are at the core of almost any digital solution, and therefore never out of fashion. With the rapid evolvements in the tech industry, however, databases need to evolve too. This, in turn, yields new database types and categories. We have seen the rise of NoSQL databases in the last 20 years, and more recently some novel database technologies, like graph databases and time-series databases. Both the shift from a centralised towards a decentralised paradigm and the growing number of restricted devices call for a new type of database. Thus, we expect Edge databases to become more prominent in the coming years.

What is an Edge Database?

Edge databases are a type of databases that are optimised for local data storage on restricted devices, like embedded devices, Mobile, and IoT. Because they run on-device, they need to be especially resource-efficient (e.g. with regards to battery use, CPU consumption, memory, and footprint). The term “edge database” is becoming more widely-used every year, especially in the IoT industry. In IoT, the difference between cloud-based databases and ones that run locally (and therefore support Edge Computing) is crucial.

What is a Mobile Database?

We look at mobile databases as a subset of edge databases that run on mobile devices. The difference between the two terms lies mainly in the supported operating systems / types of devices. Unless Android and iOS are supported, an edge database is not really suited for the mobile device / smartphone market. In this article, we will use the term “mobile database” only as “database that runs locally on a mobile (edge) device and stores data on the device”. Therefore, we also refer to it as an “on-device” database.

What are the advantages and disadvantages of working with SQLite?

SQLite is a relational database that is clearly the most established database suitable to run on edge devices. Moreover, it is probably the only “established” mobile database. It was designed in 2000 by Richard Hipp and has been embedded with iOS and Android since the beginning. Now let’s have a quick look at its main advantages and disadvantages:

Advantages  Disadvantages
  • 20+ years old (should be stable ;))
  • Toolchain, e.g. DB browser
  • No dependencies, is included with Android and iOS
  • Developers can define exactly the data schema they want
  • Full control, e.g. handwritten SQL queries
  • SQL is a powerful and established query language, and SQLite supports most of it
  • Debuggable data: developers can grab the database file and analyse it
  • 20+ years old ( less state-of-the-art tech)
  • Using SQLite means a lot of boilerplate code and thus inefficiencies ( maintaining long running apps can be quite painful)
  • No compile time checks (e.g. SQL queries)
  • SQL is another language to master, and can impact your app’s efficiency / performance significantly…
  • The performance of SQLite is unreliable
  • SQL queries can get long and complicated
  • Testability (how to mock a database?)
  • Especially when database views are involved, maintainability may suffer with SQLite

 

What are the SQLite alternatives?

There are a bunch of options for making your life easier, if you want to use SQLite. You can use an object abstraction on top of it (ORM), for instance greenDAO, to avoid writing lots of SQL. However, you will typically still need to learn SQL and SQLite at some point. So what you really want is a full blown database alternative, like any of these: Couchbase Lite, Interbase, LevelDB, ObjectBox, Oracle Berkeley DB, Mongo Realm, SnappyDB, SQL Anywhere, or UnQLite.

While SQLite really is designed for small devices, people do run it on the server / cloud too. However, for server / cloud databases, there are a lot of alternatives you can use as a replacement like e.g. MySQL, MongoDB, or Cloud Firestore.

Bear in mind that, if you are looking to host your database centrally with apps running on small distributed devices (e.g. mobile apps, IoT apps, any apps on embedded devices etc.), there are some difficulties. Firstly, this will result in higher latency, i.e. slow response-rates. Secondly, the offline capabilities will be highly limited or absent. As a result, you might have to deal with increased networking costs, which is not only reflected in dollars, but also CO2 emissions. On top, it means all the data from all the different app users is stored in one central place. This means that any kind of data breach will affect all your and your users’ data. Most importantly, you will likely be giving your cloud / database provider rights to that data. (Consider reading the general terms diligently). If you care about privacy and data ownership, you might therefore want to consider an Edge Database. This way you can limit what data you sync to a central instance (like the cloud). 

SQLite alternatives Comparison Matrix

To give you an overview, we have compiled a comparison table including SQLite and SQLite alternatives. In this matrix we look at databases that we believe are apt to run on edge devices. Our rule of thumb is the databases’ ability to run on Raspberry Pi type size of devices.

Edge Database Android / iOS Type of data stored Sync Central Sync P2P Offline Sync Data level encryption License / business model Flutter support Short description Minimum Footprint size Company
Azure SQL Edge No Relational DB for IoT No No No will provide encryption Proprietary No Designed as a SQL database for the IoT edge; however, due to the footprint it is no edge database 500 MB+ Microsoft
Couchbase Lite Android / iOS JSON Documents / NoSQL db Yes Yes No Database encryption with SQLCipher (256-bit AES) Apache 2.0 Unofficial Flutter plugin for Couchbase Lite Community Editionavialable Embedded / portable database with P2P and central synchronization (sync) support. Secure SSL. < 3,5 MB Couchbase
extremeDB iOS In-memory relational DB, hybrid persistence No No No AES encryption Proprietary No Embedded relational database < 1 MB McObject LLC
InterBase ToGo / IBLite Android / iOS Relational No No No 256 bit AES strength encryption Proprietary No Embeddable SQL database. < 1 MB Embarcadero
LevelDB Android / iOS Key-value pairs / NoSQL db No No No No New BSD Unofficial client that is very badly rated Portable lightweight key-value store, NoSQL, no index support; benchmarks from 2011 have been removed unfortunately < 1 MB LevelDB
Team
LiteDB Android / iOS (with Xamarin only) NoSQL document store, fully wirtten in .Net No No No Salted AES MIT license No A .Net embedded NoSQL database < 1 MB LiteDB team
Mongo Realm (acquired by Mongo in 2019) Android / iOS Object Database Yes, tied to using MongoDB servers No No Yes Proprietary with Apache 2.0 License APIs Unofficial Flutter plugin, in Alpha according to their website [11.03.2022] Embedded object database 5 MB+ MongoDB Inc.
ObjectBox Android / iOS / Linux / Windows / any POSIX Object-oriented NoSQL edge database for high-performance on edge devices in Mobile and IoT Yes WIP Yes transport encryption; additional encryption upon request Apache 2.0 and Proprietary Yes High-performance NoSQL Edge Database with out-of-the-box Data Sync for Mobile and IoT; fully ACID compliant; benchmarks available. < 1 MB ObjectBox
Oracle Database Lite Android / iOS Relational Yes Yes No 128-bit AES Standard encrytion Proprietary No Portable with P2P and central sync support as well as support for sync with SQLite < 1 MB Oracle Corporation
redis DB No K/V in-memory store, typically used as cache No No No TLS/SSL-based encryption can be enabled for data in motion. Three clause BSD license, RSAL and Proprietary Unofficial redis Dart client available High-performance in-memory Key Value store with optional durability An empty instance uses ~ 3MB of memory redislabs (the original author of redis left in 2020)
SQL Anywhere Android / iOS Relational Dependent No No AES-FIPS cipher encryption for full database or selected tables Proprietary No Embedded / portable database with central snyc support with a stationary database   Sybase iAnywhere
SQLite embedded on iOS and Android Relational No No No No, Use SQLCipher to encrypt SQLite Public domain Flutter plugins (ORMs) for SQLite, but nothing from Hwaci C programming library; probably 90% market share (very personal assumption, 2016) < 1 MB Hwaci
SQL Server Compact Android / iOS Relational No No No Yes Proprietary No Small-footprint embedded / portable database for Microsoft Windows mobile devices and desktops, supports synchronization with Microsoft SQL Server 2 MB Microsoft
UnQLite Android / iOS Key-value pairs / JSON store / NoSQL db No No No 128-bit or 256-bit AES standard encryption 2-Clause BSD not yet; might be coming though; there was a 0.0.1 released some time ago Portable lightweight embedded db; self-contained C library without dependency. ~ 1.5 MB Symisc systems

Star this matrix on GitHub. If you are interested in an indication of the diffusion rate of databases and mobile databases, check out the following database popularity ranking: http://db-engines.com/en/ran.

Is there anything we’ve missed? What do you agree and disagree with? Please share your thoughts with us via Twitter or email us on contact[at]objectbox.io. 

Make sure to check out the ObjectBox Database & try out ObjectBox Sync. You can get started in minutes. More than 1,000,000 developers already use this Edge Database designed specifically for high performance on embedded devices.