SQLite and SQLite alternatives - databases for the Mobile and IoT edge
Updated comparison of mobile databases / edge databasesNote: This is an updated version of an earlier Mobile Database Comparison. Last Update: 2021.
According to the database of databases there are more than 700 databases as of 2021. However, that list does include hobby projects. DB-engines “only” lists databases that have significant traction and are well-maintained; they still count more than 300 databases as of 2021. However, only a handful of these databases are edge database.
What is an edge database?
An edge database is a database that runs directly on restricted devices locally. It is a local on-device database as opposed to a cloud database. The term edge database is too young to have a Wikipedia article. However, we see it used in the IoT industry increasingly. In the field of IoT applications, it is especially important to distinguish databases that run locally on the device (“on the edge of the network”) and thus support Edge Computing from databases that run “in the cloud”. A mobile database is an edge database that runs on mobile devices, meaning the only difference between the terms edge database and mobile database is the operating system / device support they offer.
What is a mobile database?
While Wikipedia defines a mobile database as “Mobile computing devices (e.g., smartphones and PDAs) store and share data over a mobile network, or a database which is actually stored by the mobile device,” we solely refer to the latter ones as a mobile database. Meaning only databases that run on the mobile device (as the edge device) itself, locally, and store the data on the device. Therefore, we also refer to it as “on-device” database.
What is an edge device?
An edge device may be any device from a sensor, to an IoT gateway, to a car, to a Raspberry Pi, to a mobile phone (smartphone) to an on-premise server. Typically, the challenge arises when running on the smaller, more restricted devices. Generally, any database can run on a big on-premise server or cloud infrastructure with unlimited resources, but only few fit on a Raspberry Pi Zero. The other way around is no issue, meaning an edge databases can run well on a server. Therefore, in this comparison we look at databases that run on Raspberry Pi type size of devices (rule of thumb).
What are the advantages and disadvantages of working with SQLite?
SQLite is easily the most established edge database and probably the only “established” mobile database. SQLite is public domain and maintained by Richard Hipp. SQLite database has been around since the year 2000 and been embedded with iOS and Android since the beginning. SQLite is a relational database.
Advantages | Disadvantages |
|
|
What are SQLite alternatives?
There are plenty of alternatives to working with SQLite directly. If you simply want to avoid writing lots of SQL and boilerplate code, you can use an object abstraction on top of SQLite. This abstraction layer is usually an ORM (object/relational mapper), e.g. greenDAO. While an ORM makes it easy to use SQLite at the beginning, there typically comes a point “where you hit SQLite”; so even when using an abstraction layer you need to understand SQLite and SQL in the longrun.
However, if you rather seek a complete replacement for SQLite, there are a few alternative databases: Couchbase Lite, Interbase, LevelDB, ObjectBox, Oracle Berkeley DB (formerly Oracle’s mobile database was “Oracle Database Lite”), Realm (now Mongo Realm), SnappyDB, SQL Anywhere, and UnQLite.
Obviously, if your also looking for alternatives that run in the cloud, there are a lot of cloud / server options out there that you can use as a replacement like e.g. Firebase. Though, with these your app will not work offline, response rates will be slower than with an on-device database and cannot be guaranteed, and last not least you will have much higher networking / cloud costs. You can find out more about the benefits of Edge Computing.
To give you an overview, we have compiled a small comparison table:
Edge Database | Android / iOS* | Type of data stored | Sync Central | Sync P2P | Offline Sync | Data level encryption | License / business model | Short description | Minimum Footprint size | Company |
Azure SQL Edge (in preview) | No | Relational DB | No | No | No | will provide encryption | Proprietary | Designed as a SQL database for the IoT edge; however, due to the footprint it is no edge database | 500 MB+ | Microsoft |
Couchbase Mobile (prior Couchbase Lite) | Android / iOS | JSON Documents / NoSQL db | Yes | Yes | No | Database encryption with SQLCipher (256-bit AES) | Apache 2.0 | 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 | Embedded relational database | 200kB | McObject LLC |
ForestDB | Android / iOS | Key-value pairs / NoSQL db | No | No | No | No | Apache 2.0 | Portable lightweight key-value store, NoSQL database | ||
InterBase ToGo / IBLite | Android / iOS | Relational | No | No | No | 256 bit AES strength encryption | Proprietary | Embeddable SQL database. | 400 KB | Embarcadero |
LevelDB | Android / iOS | Key-value pairs / NoSQL db | No | No | No | No | New BSD | Portable lightweight key-value store, NoSQL, no index support; benchmarks from 2011 have been removed unfortunately. | 350kB | LevelDB Team |
LiteDB | Android / iOS (with Xamarin only) | NoSQL document store | No | No | No | Salted AES | MIT license | A .Net embedded NoSQL database | 350kb | |
Mongo Realm (acquired in 2019) | Android / iOS | Object Database | Yes | No | Yes | Proprietary with Apache 2.0 License APIs | Embedded object database | 5 MB+ | Realm 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 | Embedded object-oriented NoSQL high-performance edge database with out-of-the-box data synchronization; fully ACID compliant; benchmarks available. | < 1 MB | ObjectBox |
Oracle Database Lite | Android / iOS | Relational | Yes | Yes | No | 128-bit AES Standard encrytion | Proprietary | 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 | 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) |
Snappy DB | Android | Key-value pairs / NoSQL db | No | No | No | Apache 2.0 | Portable lightweight key-value store, NoSQL database based on LevelDB | Nabil HACHICHA | ||
SQL Anywhere | Android / iOS | Relational | Dependent | No | AES-FIPS cipher encryption for full database or selected tables | Proprietary | Embedded / portable database with central snyc support with a stationary database | Sybase iAnywhere | ||
SQLite | embedded on iOS and Android | Relational | No | No | No, Use SQLCipher to encrypt SQLite | Public domain | C programming library; probably 90% market share (very personal assumption, 2016) | 500KiB | Hwaci | |
SQL Server Compact | Android / iOS | Relational | No | No | Yes | Proprietary | 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 / document store / NoSQL db | No | No | 2-Clause BSD | Portable lightweight embedded db; self-contained C library without dependency. | Symisc systems |
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.
Thanks for reading and sharing. Please let us know what you’re missing.
Realm has centralized sync.
LiteDB seems a good alternative to. Here is the official repo https://github.com/mbdavid/litedb