Comparing SQLite (Room & Core Data) and MongoDB

Modern applications today attempt to reach a global audience in a network-connected world. However, there is demand for local storage and database capabilities in many devices and processes. Those capabilities should be fast, reliable and eventually scalable when it comes to sharing and syncing information.

SQLite was initially developed to solve a local access SQL solution with relational concepts. Having said that, most of today's mobile development will most likely use one of the popular frameworks operating as an ORM (Object to Relational Mapping) for SQLite, like Room for Android or Core Data for iOS. These frameworks manage the entire object to data store life cycle, which is comparable to Atlas Device Sync & SDKs', two key components of Atlas for the Edge.

In this article, we will show how the on-device database of the SDKs compares and surpasses SQLite frameworks (Room and Core Data) for the described local/mobile development challenges.

What is SQLite?

SQLite is a C-based library that allows applications to store and retrieve data from local files on devices via a SQL interface. SQLite was started as an open-source project in September 2000 and It is supported on a few platforms, mostly mobile devices and mobile/desktop operating systems.

However, it is maintained by a certain group of people and contribution is very limited and governed closely by the creators.

What are Core Data and Room?

Apple’s Core Data and Android Room are frameworks used to manage the lifecycle of objects in a mobile application which often use SQLite as a local/on-device data store. These frameworks provide a structured way for developers to ensure that the data passing through their application is always up to date and in the correct format for whichever part of the stack it is in. Examples of this include updating a datapoint in the UI when it changes in the underlying database, or converting data types between those used in front-end languages and ones supported in the underlying database.

What are the Atlas Device SDKs?

The Atlas Device SDKs are a part of MongoDB Atlas for the Edge, a solution to streamline the management of data generated across various sources at the edge, including mobile devices, on-premises data centers, and the cloud.

Each one of the SDKs provides, in the programming language of your choice, a fast, scalable on-device database, alternative to SQLite, that makes storing, querying, and syncing data simple for modern mobile applications.

The object-oriented data model enables developers to work directly with native objects – no ORMs or DAOs required. And built-in edge-to-cloud data sync lets developers easily backup, manage, and analyze mobile app data in the cloud, using the full power of MongoDB Atlas.

Atlas Device SDKs are open source and are available for most popular languages, frameworks, and platforms.

When to use SQLite for mobile applications

SQLite is a veteran in the mobile self-contained database world and therefore, developers with preference to SQL syntax with static schemas might prefer SQLite. However, as mentioned earlier, SQLite is usually not used on its own and will be a part of a Room or Core Data implementation. This added complexity and the need for an additional ORM layer will result in additional coding challenges and more dependencies introduced in your product.

With that said, offline-first and complex schema evolution with agile changes has become a wide challenge for lots of mobile applications and websites. Therefore, a majority of customers should consider the benefits of Realm’s modern solution over the legacy SQLite. If you require a scalable solution with high concurrently and offline synchronization, SQLite (Room and/or Data) might not be enough for your needs.

When to use the Atlas Device SDKs for mobile applications

The real question should be why not use the SDKs for developing local or/and offline sync databases? The SDKs are comparable to Room and Core Data in terms of use cases it serves, and offers some additional features that bring extra value to your application.

Mobile development is constantly evolving, requiring new capabilities and features as user trends change. Having a reactive datastore with a flexible schema lets developers make changes fast, and Atlas Device Sync seamlessly syncs those changes across devices in real time.

Additionally, Atlas Device Sync users can easily take advantage of Atlas App Services that include serverless functions, triggers, a GraphQL API, HTTPS Endpoints and more. These services make Atlas Device SDKs + Atlas App Services a compelling choice for mobile developers looking to modernize mobile workloads and ensure scalable, speedy go-to-markets.

SQLite (Room and Core Data) and Atlas Device SDKs comparison

Let's look at a simple initial code snippets to query each one of the database implementations for data of Planet Earth:

Core Data Swift Example

xml

let fetchRequest: NSFetchRequest 
fetchRequest = Entity.fetchRequest()

fetchRequest.predicate = NSPredicate(
    format: "name = %@", "Earth"
)

let context = persistentContainer.viewContext

let objects = try context.fetch(fetchRequest)

Swift SDK Examples

xml

let realm = try! Realm()

let planets = realm.objects(Planet.self)

let objects = planets.where {
  $0.name == "Earth"
}

Room Android Example

xml

@Dao
public interface planetDao {
...
@Query("SELECT * FROM PLANETS WHERE name = :name")
    Person loadPlanetByName(string name);
}

final List planets = mDb.planetDao().loadPlanetByName("Earth");

Android SDK Example

xml

Realm realm = Realm.getDefaultInstance();

RealmResults planets = realm.where(Planet.class)
       .equalTo("name", "Earth")

xml

let fetchRequest: NSFetchRequest 
fetchRequest = Entity.fetchRequest()

fetchRequest.predicate = NSPredicate(
    format: "name = %@", "Earth"
)

let context = persistentContainer.viewContext

let objects = try context.fetch(fetchRequest)

xml

let realm = try! Realm()

let planets = realm.objects(Planet.self)

let objects = planets.where {
  $0.name == "Earth"
}

xml

@Dao
public interface planetDao {
...
@Query("SELECT * FROM PLANETS WHERE name = :name")
    Person loadPlanetByName(string name);
}

final List planets = mDb.planetDao().loadPlanetByName("Earth");

xml

Realm realm = Realm.getDefaultInstance();

RealmResults planets = realm.where(Planet.class)
       .equalTo("name", "Earth")

As you can see, the SDKs' code is much lighter and method oriented. It allows object-oriented languages and frameworks to utilize it with method based access, unlike the Room and Core Data packages which use SQL strings and are susceptible to untidy code and SQL injections.

Let's cover some of the key comparison points.

	SQLite + Room/Core Data	Atlas for the Edge
Data Model	Based on Relational tables with ORM concepts. Objects need to be transformed and broken into row based stores.	The on-device database of the SDKs uses an object-oriented model. Objects in code do not need to undergo any transformation before being written to the database. This eliminates the need for an ORM layer and vastly enhances schema flexibility.
Query System	Leans on SQL query syntax. Room - Relies on the DAO concept, which requires queries to be predefined with respectful predictions. Core Data - Relies on a similar concept, called managed object contexts. The syntax varies slightly but it is functionally identical. SQLite as well as the frameworks will not have built-in reactive elements to refresh the UI upon changes. The developer will need to use coding or third-party frameworks to support that.	The SDKs offers rich query capabilities in language-native syntax, making it feel like you are seamlessly interacting with objects vs querying a database. Additionally, the on-device database supports the MongoDB Aggregation framework to power the most complex and sophisticated calculations. Objects in client devices are live objects that update automatically to reflect data changes, including synced remote changes. Objects also emit notification events that you can subscribe to whenever their underlying data changes.
Sync Services and Backup	SQLite ORMs’ as well as SQLite do not provide any cross-device or offline-first sync capabilities. The data is managed on the device only.	Atlas Device Sync offers automatic conflict resolution using operational transformation, and takes care of network retry/error-handling – keeping teams efficient by eliminating the need for developers to invent their own sync logic. The offline-first methodology of the SDKs means all changes are automatically persisted as fast as possible, with no further work from developers. This is accomplished by always reading from the local copy of the on-device database first, before going over the network. Atlas clusters have a full built-in backup strategy for snapshots and point-in-time recovery.
Security	SQLite as well as the ORM’s do not provide user based management. The application needs to cover the security for the database. Additionally, there is no native built in encryption and users need to use third-party encryption libraries.	Atlas Device Sync provides built-in authentication and authorization controls that make it simple to control which users can read and write to different partitions of data. Atlas Device Sync complies with a number of strict security standards such as HIPAA, PCI, and SOC. Atlas Device SDKs provide native encryption.
Platform and Driver Support	SQLite in conjunction with Room is built for Android applications, and Core Data is built for iOS based applications. If you wish to develop cross-platform or additional web interfaces you will need to depend on your project on the different ORMs and concepts that each framework brings.	Idiomatic SDKs are available for most common platforms and programming languages, with equally descriptive documentation and code examples for each. Dedicated team of engineers to continuously improve and enrich documentation, examples, and platform support.

SQLite + Room/Core Data
Data Model	Based on Relational tables with ORM concepts. Objects need to be transformed and broken into row based stores.
Query System	Leans on SQL query syntax. Room - Relies on the DAO concept, which requires queries to be predefined with respectful predictions. Core Data - Relies on a similar concept, called managed object contexts. The syntax varies slightly but it is functionally identical. SQLite as well as the frameworks will not have built-in reactive elements to refresh the UI upon changes. The developer will need to use coding or third-party frameworks to support that.
Sync Services and Backup	SQLite ORMs’ as well as SQLite do not provide any cross-device or offline-first sync capabilities. The data is managed on the device only.
Security	SQLite as well as the ORM’s do not provide user based management. The application needs to cover the security for the database. Additionally, there is no native built in encryption and users need to use third-party encryption libraries.
Platform and Driver Support	SQLite in conjunction with Room is built for Android applications, and Core Data is built for iOS based applications. If you wish to develop cross-platform or additional web interfaces you will need to depend on your project on the different ORMs and concepts that each framework brings.

Atlas for the Edge
Data Model	The on-device database of the SDKs uses an object-oriented model. Objects in code do not need to undergo any transformation before being written to the database. This eliminates the need for an ORM layer and vastly enhances schema flexibility.
Query System	The SDKs offers rich query capabilities in language-native syntax, making it feel like you are seamlessly interacting with objects vs querying a database. Additionally, the on-device database supports the MongoDB Aggregation framework to power the most complex and sophisticated calculations. Objects in client devices are live objects that update automatically to reflect data changes, including synced remote changes. Objects also emit notification events that you can subscribe to whenever their underlying data changes.
Sync Services and Backup	Atlas Device Sync offers automatic conflict resolution using operational transformation, and takes care of network retry/error-handling – keeping teams efficient by eliminating the need for developers to invent their own sync logic. The offline-first methodology of the SDKs means all changes are automatically persisted as fast as possible, with no further work from developers. This is accomplished by always reading from the local copy of the on-device database first, before going over the network. Atlas clusters have a full built-in backup strategy for snapshots and point-in-time recovery.
Security	Atlas Device Sync provides built-in authentication and authorization controls that make it simple to control which users can read and write to different partitions of data. Atlas Device Sync complies with a number of strict security standards such as HIPAA, PCI, and SOC. Atlas Device SDKs provide native encryption.
Platform and Driver Support	Idiomatic SDKs are available for most common platforms and programming languages, with equally descriptive documentation and code examples for each. Dedicated team of engineers to continuously improve and enrich documentation, examples, and platform support.

Conclusion

The Atlas Device SDKs combined with Atlas App Services offer a modern and robust mobile database solution, which is superior to SQLite with Core Data and/or Room in many areas. It allows users to build cross-platform, offline-first applications backed by the entire Atlas for the Edge and MongoDB Atlas cloud platform.

Try the Atlas Device SDKs now to build modern mobile data-driven applications. See the following tutorials and links for more information: