SQLite and SQLite alternatives for the Mobile and IoT edge

Updated comparison of mobile databases / edge databases

Note: This is an updated version of an earlier Mobile Database Comparison. Last Update: 2020.

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 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 important to distinguish databases that run locally – “on the edge” – as opposed to “in the cloud”. A mobile database is a subset of edge databases, meaning the only difference is the device the database runs on. The main difference is the operating system support: There simply are edge databases that do not run on Android and / or iOS. Thus, these databases, while small enough for Edge Computing, and indeed qualifying as edge databases, are not suited for typical mobile devices, and therefore no mobile 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, we look at databases that run on Raspberry Pi type size of devices (Rule of thumb).

Edge Devices to run mobile /edge databases on

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
  • Toolchain, e.g. DB browser
  • No dependencies, is included with Android and iOS
  • Developers can define exactly the data schema they want
  • Developers have 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 analyze it
  • Rock-solid, widely used technology, established since the year 2000
  • Using SQLite means a lot of boilerplate code and thus inefficiencies (also in the long run with the app maintenance)
  • 1 MB BLOB Limitation on Android
  • No compile time checks (e.g. SQL queries)
  • The performance of SQLite is unreliable
  • SQL is another language to master
  • 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 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

Side note: According to the database of databases there are more than 700 databases as of 2020. 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 2020.

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.