SQLite vs Realm vs ObjectBox - Comparison

Development of a complex application is impossible without the use of databases that provide powerful functionality for storing, sorting and retrieving information. Their application in Android development has its own specifics caused by the features of mobile devices: less hardware resources, battery saving, mobile application architecture. Therefore, the use of databases in Android development is a separate topic that requires close study.

AAs of 2020, SQLite, Realm and ObjectBox remain the most popular database management systems (DBMS) for Android applications. Each of them occupies its own niche in the market of mobile applications, offering developers various approaches to the storage and use of structured data.

SQLite – the Leader in Popularity

SQLite was first introduced in 2000 and has since become one of the most popular DBMSs. It is based on a relational approach to data storage and uses the SQL query language to manage information. Unlike other well-known DBMSs, such as MySQL and PostgreSQL, SQLite does not require a separate database server, stores all information in one file and takes up little disk space. These features make it the most optimal among relational DBMS for use in mobile devices. At the moment, SQLite is the most frequently used DBMS in the development of Android applications. Pure SQLite is currently rarely used in the development of Android applications. To simplify the work, the ORM Room from Google is widely used, which avoids writing boilerplate code. Data tables are created using annotations in the model class, which determine the transformation of class attributes into names and properties of table columns.

Realm – Object Oriented Alternative

An alternative to SQLite is Realm. Its first stable version for Android appeared in 2014. The creators conceived Realm as a database that will avoid writing cumbersome boilerplate code in SQL, will make it possible to work with data as objects, and increase the speed of CRUD operations. In 2019, Realm was acquired by MongoDB Inc. and added the serverless platform option to the original functionality. Realm belongs to the noSQL DBMS group, using model classes to describe the data structure, not tables and the relationships between them. This removes the problem of object-relational mapping, relevant for relational databases and reduces the cost of resources for data conversion.

ObjectBox – Leader in Performance

ObjectBox – the newest of the considered DBMS. It was created by Green Robot, a well-known Android developer for its GreenDao, EventBus, and Essentials products. ObjectBox was originally developed as a DBMS for mobile and IoT devices; therefore, it compares favorably with its competitors in the speed of operation and the convenience of integration into mobile applications. Like Realm, it implements a noSQL approach in which the attributes of model classes and the relationships between them are directly written to the database.

Using the database in your Android application in most cases means that you have to deal with structured and interconnected information. Simple storage of primitive data is straightforward. However, more complex manipulations with information can be a real challenge for a developer, requiring good knowledge of the tools of his chosen DBMS. Let’s try to consider how the most popular mobile databases – SQLite, Realm and ObjectBox – solve complex problems:

storing complex objects
receiving data with many conditions
getting related entities (one-to-one, one-to-many, many-to-many).

As an example, let’s take an application for a library that will work with data about books. Each book has one or more authors, title, place and year of publication, publishing house, current status (issued on hand, available, for restoration), etc. Consider what options for organizing data can be used in the case of using each of the above DBMS.

Storing complex objects

SQLite and Room

One of the features of relational databases is the support of strictly defined data types that can be assigned to table columns. In the case of SQLite, these are INTEGER, REAL, TEXT, BLOB, and NULL. To save objects, you need to convert them into a set of attributes of the corresponding types. Room allows you to do this in several ways:

Adding @Entity annotation to a class that describes a data model. In this case, Room will create a separate table in the SQLite database and save your objects as rows of this table. Using annotations, you can specify column names, necessary fields for saving, data properties, etc.


import androidx.room.ColumnInfo
import androidx.room.Entity
import androidx.room.PrimaryKey

@Entity(tableName = "books")
data class Book(
   @PrimaryKey val id: Int,
   val title: String?,
   val city: String?,
   val year: Int?,
   @ColumnInfo(name = "publishing_house") val publishingHouse: String?
)

import androidx.room.ColumnInfo

import androidx.room.Entity

import androidx.room.PrimaryKey

@Entity(tableName = "books")

data class Book(

@PrimaryKey val id: Int,

val title: String?,

val city: String?,

val year: Int?,

@ColumnInfo(name = "publishing_house") val publishingHouse: String?

)

Using TypeConverters, which describe the logic for converting an object into a simple data type, suitable for storage in 1 cell. A classic example is that the Date type can be saved to the database as a timestamp.


class Converters {
   @TypeConverter
   fun fromUnix(unixTime: Long?): Date? {
       return if (unixTime == null) null else Date(unixTime)
   }

   @TypeConverter
   fun toUnix(date: Date?): Long? {
       return date?.time
   }
}

class Converters {

@TypeConverter

fun fromUnix(unixTime: Long?): Date? {

return if (unixTime == null) null else Date(unixTime)

}

@TypeConverter

fun toUnix(date: Date?): Long? {

return date?.time

}

Convert to primitive values can be more complex objects, such as arrays and lists.


@TypeConverter
public static ArrayList<String> fromString(String value) {
    Type listType = new TypeToken<ArrayList<String>>() {}.getType();
    return new Gson().fromJson(value, listType);
}

@TypeConverter
public static String fromArrayList(ArrayList<String> list) {
    Gson gson = new Gson();
    String json = gson.toJson(list);
    return json;
}

@TypeConverter

public static ArrayList<String> fromString(String value) {

Type listType = new TypeToken<ArrayList<String>>() {}.getType();

return new Gson().fromJson(value, listType);

}

@TypeConverter

public static String fromArrayList(ArrayList<String> list) {

Gson gson = new Gson();

String json = gson.toJson(list);

return json;

}

Using the @Embedded annotation. When using it, Room will automatically create additional fields for the attributes of the nested object in the parent table. With clear-cut simplicity, the main limitation of this approach is obvious – it is only suitable for saving nested objects.

Realm

Since Realm was originally developed as an object-oriented database, the conversion of application models to save them in the database requires minimal effort from the developer. First of all, the model class must be accessible for inheritance (in Java, nothing needs to be changed, in Kotlin – add the open modifier). Also, this class should be the descendant of the abstract class RealmObject, which encapsulates the logic of interaction with the database.


import io.realm.RealmObject
import io.realm.annotations.PrimaryKey

open class Book(
   @PrimaryKey var id: Int,
   var title: String?,
   var city: String?,
   var year: Int?,
   var publishingHouse: String?
) : RealmObject()

import io.realm.RealmObject

import io.realm.annotations.PrimaryKey

open class Book(

@PrimaryKey var id: Int,

var title: String?,

var city: String?,

var year: Int?,

var publishingHouse: String?

) : RealmObject()

Saving nested objects is also not a problem. The requirements for nested objects are the same as for the model class.


open class Author(
   @PrimaryKey var id: Int,
   var firstName: String?,
   var lastName: String?,
   var dateOfBirth: Date?,
   var dateOfDeath: Date?,
   var books: RealmList<Book>
) : RealmObject()

open class Author(

@PrimaryKey var id: Int,

var firstName: String?,

var lastName: String?,

var dateOfBirth: Date?,

var dateOfDeath: Date?,

var books: RealmList<Book>

) : RealmObject()

ObjectBox

The principle of operation of the ObjectBox is similar to Realm, therefore it also requires minimal manipulation of the model classes to save them to the database: adding @Entity and @Id annotations, public attributes or their getters and setters.


@Entity
data class Book(
   @Id var id: Long = 0,
   var title: String?,
   var city: String?,
   var year: Int?,
   var publishingHouse: String?
)

@Entity

data class Book(

@Id var id: Long = 0,

var title: String?,

var city: String?,

var year: Int?,

var publishingHouse: String?

)

Saving links to other objects requires specifying the type of connection – ToOne, if there is only one associated object, or ToMany if there are several. In our case, we can supplement our Book class with a link to the authors.


lateinit var authors: ToMany<Author>

lateinit var authors: ToMany<Author>

Getting data with many conditions

SQLite and Room

They provide powerful functionality for generating complex queries based on SQL expressions. To use it, you must have a good knowledge of SQL, as well as the features of its implementation in SQLite.

For example, a query looks like this, returning all books after a given year, sorted alphabetically.


@Query("SELECT * FROM books WHERE year > :minYear ORDER BY title")
fun loadAllBooksNewerThan(minYear: Int): List<Book>

@Query("SELECT * FROM books WHERE year > :minYear ORDER BY title")

fun loadAllBooksNewerThan(minYear: Int): List<Book>

A query that returns all books with a specific title published between specified years.


@Query("SELECT * FROM books WHERE title LIKE :search " +
       "AND year BETWEEN :dateFrom AND :dateTo")
fun findBooksByTitleForPeriod(search: String, dateFrom: Int, dateTo: Int): List<Book>

@Query("SELECT * FROM books WHERE title LIKE :search " +

"AND year BETWEEN :dateFrom AND :dateTo")

fun findBooksByTitleForPeriod(search: String, dateFrom: Int, dateTo: Int): List<Book>

A query that returns a list of publishers recorded in a database for a specific city.


@Query("SELECT DISTINCT publishing_house from books WHERE city = :city")
fun getPublishingHousesForCity(city: String): List<String>

@Query("SELECT DISTINCT publishing_house from books WHERE city = :city")

fun getPublishingHousesForCity(city: String): List<String>

Realm

This DBMS provides a rich set of operators for fetching and filtering data. Using their combinations, you can create a query of any complexity. Let’s rewrite the SQLite examples for Realm.


fun loadAllBooksNewerThan(minYear: Int): List<Book> {
   val books = realm
       .where(Book::class.java)
       .greaterThan("year", minYear)
       .sort("title")
       .findAll()
   return realm.copyFromRealm(books)
}


fun findBooksByTitleForPeriod(search: String, dateFrom: Int, dateTo: Int): List<Book>? {
   val books = realm
       .where(Book::class.java)
           .like("title", search)
           .and()
           .between("year", dateFrom, dateTo)
           .findAll()
   return realm.copyFromRealm(books)
}

fun getPublishingHousesForCity(city: String): List<String?> {
   val books = realm
       .where(Book::class.java)
       .equalTo("city", city)
       .distinct("publishingHouse")
       .findAll()
   return realm.copyFromRealm(books).map { it.publishingHouse }
}

fun loadAllBooksNewerThan(minYear: Int): List<Book> {

val books = realm

.where(Book::class.java)

.greaterThan("year", minYear)

.sort("title")

.findAll()

return realm.copyFromRealm(books)

}

fun findBooksByTitleForPeriod(search: String, dateFrom: Int, dateTo: Int): List<Book>? {

val books = realm

.where(Book::class.java)

.like("title", search)

.and()

.between("year", dateFrom, dateTo)

.findAll()

return realm.copyFromRealm(books)

}

fun getPublishingHousesForCity(city: String): List<String?> {

val books = realm

.where(Book::class.java)

.equalTo("city", city)

.distinct("publishingHouse")

.findAll()

return realm.copyFromRealm(books).map { it.publishingHouse }

}

ObjectBox

In this DBMS, QueryBuilder is used to create queries, which has an extensive arsenal of functions for creating the desired data selection. The principle of creating queries is very similar to Realm. The main difference is that in Realm the query returns data wrapped in the RealmResults class. Its main task is to keep links to data up to date and provide functionality for accessing them. To get the data in raw form, we must copy it from the database using the copyFromRealm () method. ObjectBox immediately returns data when requested. Its second feature is the ability to cache and reuse requests. Examples already familiar to us when using an ObjectBox will look like this.


fun loadAllBooksNewerThan(minYear: Long): List<Book> {
   return bookBox.query()
       .greater(Book_.year, minYear)
       .order(Book_.title)
       .build()
       .find()
}

fun findBooksByTitleForPeriod(search: String, dateFrom: Long, dateTo: Long): List<Book> {
   return bookBox.query()
       .contains(Book_.title, search)
       .and()
       .between(Book_.year, dateFrom, dateTo)
       .build()
       .find()
}

fun getPublishingHousesForCity(city: String): List<String> {
   return bookBox.query()
       .equal(Book_.city, city)
       .build()
       .property(Book_.publishingHouse)
       .distinct()
       .findStrings()
     .toList()
}

fun loadAllBooksNewerThan(minYear: Long): List<Book> {

return bookBox.query()

.greater(Book_.year, minYear)

.order(Book_.title)

.build()

.find()

}

fun findBooksByTitleForPeriod(search: String, dateFrom: Long, dateTo: Long): List<Book> {

return bookBox.query()

.contains(Book_.title, search)

.and()

.between(Book_.year, dateFrom, dateTo)

.build()

.find()

}

fun getPublishingHousesForCity(city: String): List<String> {

return bookBox.query()

.equal(Book_.city, city)

.build()

.property(Book_.publishingHouse)

.distinct()

.findStrings()

.toList()

}

Getting related entities (one-to-one, one-to-many, many-to-many)

SQLite and Room

Despite the fact that the implementation of relationships between entities is a strong point of relational databases, their implementation in SQLite and Room requires a significant amount of code and developer efforts. The reason is the ban on the use of object references. As the creators of Room explain, in the Android application, accessing nested objects leads to delayed loading from the main thread with all the ensuing consequences: delay in rendering the UI or excessive consumption of resources. Therefore, the most obvious way to implement relationships between entities is not supported.

To implement relationships in Room, you need to create an additional class that will be returned when requesting the corresponding data.

An example of a one-to-one and one-to-many relationship. Let’s create a Meta class that contains service information about each book: code and status. Each book can have only one meta data and each meta data refers to only one book.


@Entity(tableName = "meta")
data class Meta(
   @PrimaryKey val code: Long,
   val status: Int
   )

data class BookWithMeta(
   @Embedded val book: Book,
   @Relation(
       parentColumn = "id",
       entityColumn = "bookId"
   )
   val meta: Meta
)

@Entity(tableName = "meta")

data class Meta(

@PrimaryKey val code: Long,

val status: Int

)

data class BookWithMeta(

@Embedded val book: Book,

@Relation(

parentColumn = "id",

entityColumn = "bookId"

)

val meta: Meta

)

Books with metadata are received in the form of several requests, therefore, they require @Transaction annotation.


@Transaction
@Query("SELECT * FROM books")
fun getBooksWithMeta(): List<BookAndMeta>

@Transaction

@Query("SELECT * FROM books")

fun getBooksWithMeta(): List<BookAndMeta>

The one-to-many relationship in Room is implemented identically, but instead of a reference to a single object, a list link is indicated.

An example of a many-to-many relationship. Let’s create a version of the Author class, already familiar to us from the example from Realm.


class Author(
   @PrimaryKey var authorId: Int,
   var firstName: String?,
   var lastName: String?,
   var dateOfBirth: Date?,
   var dateOfDeath: Date?,
   var books: List<Book>
)

class Author(

@PrimaryKey var authorId: Int,

var firstName: String?,

var lastName: String?,

var dateOfBirth: Date?,

var dateOfDeath: Date?,

var books: List<Book>

)

To implement the relationship, you need to create three additional classes: a link and two query results.


@Entity(primaryKeys = ["bookId", "authorId"])
data class AuthorBookCrossRef(
   val bookId: Int,
   val authorId: Int
)

data class AuthorWithBooks(
   @Embedded val author: Author,
   @Relation(
       parentColumn = "authorId",
       entityColumn = "bookId",
       associateBy = @Junction(AuthorBookCrossRef::class)
   )
   val books: List<Book>
)

data class BookWithAuthors(
   @Embedded val book: Book,
   @Relation(
       parentColumn = "bookId",
       entityColumn = "authorId",
       associateBy = @Junction(AuthorBookCrossRef::class)
   )
   val authors: List<Author>
)

@Entity(primaryKeys = ["bookId", "authorId"])

data class AuthorBookCrossRef(

val bookId: Int,

val authorId: Int

)

data class AuthorWithBooks(

@Embedded val author: Author,

@Relation(

parentColumn = "authorId",

entityColumn = "bookId",

associateBy = @Junction(AuthorBookCrossRef::class)

)

val books: List<Book>

)

data class BookWithAuthors(

@Embedded val book: Book,

@Relation(

parentColumn = "bookId",

entityColumn = "authorId",

associateBy = @Junction(AuthorBookCrossRef::class)

)

val authors: List<Author>

)

Requests for data will be as follows.


@Transaction
@Query("SELECT * FROM books")
fun getBooksWithAuthors(): List<BookWithAuthors>

@Transaction
@Query("SELECT * FROM authors")
fun getAuthorsWithBooks(): List<AuthorWithBooks>

@Transaction

@Query("SELECT * FROM books")

fun getBooksWithAuthors(): List<BookWithAuthors>

@Transaction

@Query("SELECT * FROM authors")

fun getAuthorsWithBooks(): List<AuthorWithBooks>

Realm

The difference in getting related data between Room and Realm is enormous. Getting happens the same way as with a simple access to the property of the object. An important point – when implementing a many-to-many communication, the connection between objects is unidirectional by default. For two-way dependency between objects, you must use the @LinkingObjects annotation.


val book = realm
   .where(Book::class.java)
   .equalTo("id", bookId)
   .findFirst()
val authors = book?.authors

val book = realm

.where(Book::class.java)

.equalTo("id", bookId)

.findFirst()

val authors = book?.authors

ObjectBox

Getting related entities is very similar to Realm and happens in one line.

One-to-one example


val meta = bookBox[bookId].meta.target

val meta = bookBox[bookId].meta.target

Many-to-many example


val authors = bookBox[bookId].authors

val authors = bookBox[bookId].authors

Conclusion

Criterion
Type	Relational with ORM	Object oriented	Object oriented
Entry treshold	Low	Middle	Low
Storing complex objects	It is convenient when working with simple types, it requires additional time to implement relationships between objects.	Storing complex objects requires minimal effort.	Storing complex objects requires minimal effort.
Getting data with many conditions	Great toolkit for the formation of complex multi-level queries.You need to know SQL.	A large set of built-in functions for selecting and sorting data.	A large set of built-in functions for selecting and sorting data.
Retrieving related entities	Requires a significant investment a implementation time.	Occurs in a few lines.	Occurs in one lines.


Type	Relational with ORM
Entry treshold	Low
Storing complex objects	It is convenient when working with simple types, it requires additional time to implement relationships between objects.
Getting data with many conditions	Great toolkit for the formation of complex multi-level queries.You need to know SQL.
Retrieving related entities	Requires a significant investment a implementation time.


Type	Object oriented
Entry treshold	Middle
Storing complex objects	Storing complex objects requires minimal effort.
Getting data with many conditions	A large set of built-in functions for selecting and sorting data.
Retrieving related entities	Occurs in a few lines.


Type	Object oriented
Entry treshold	Low
Storing complex objects	Storing complex objects requires minimal effort.
Getting data with many conditions	A large set of built-in functions for selecting and sorting data.
Retrieving related entities	Occurs in one lines.

Thus, the complex operations of storing and retrieving data in the most popular databases for Android applications have their own specifics. The combination of SQLite and Room, despite the status of the most popular solution for storing structured data, requires significant manipulations to store and retrieve related objects, as well as good knowledge of the SQL basics for efficient work. Realm and ObjectBox, based on an object-oriented approach to data organization, are significantly superior to their competitors in both speed and usability.

See how we came up with a cross-platform database solution using MS SQL

SQLite vs Realm vs ObjectBox: Complex data queries in Android databases

SQLite – the Leader in Popularity

Realm – Object Oriented Alternative

ObjectBox – Leader in Performance

Storing complex objects

SQLite and Room

Realm

ObjectBox

Getting data with many conditions

SQLite and Room

Realm

ObjectBox

Getting related entities (one-to-one, one-to-many, many-to-many)

SQLite and Room

Realm

ObjectBox

Conclusion

See how we came up with a cross-platform database solution using MS SQL

What are you waiting for?

SQLite vs Realm vs ObjectBox: Complex data queries in Android databases

SQLite – the Leader in Popularity

Realm – Object Oriented Alternative

ObjectBox – Leader in Performance

Storing complex objects

SQLite and Room

Realm

ObjectBox

Getting data with many conditions

SQLite and Room

Realm

ObjectBox

Getting related entities (one-to-one, one-to-many, many-to-many)

SQLite and Room

Realm

ObjectBox

Conclusion

See how we came up with a cross-platform database solution using MS SQL

You Might Also Like:

Redwerk’s Report On Software Development Technologies In Demand In New York

Slush 2015. Report On One Of European Major Startup Events

Swift vs Objective-C. 10 Differences

What are you waiting for?