Migrate from Realm to SQLDelight

[bmarty]

Snapshot of the internal study:

Current status

We have currently 3 DB instances in the RiotX SDK:
DB for session authentication (AuthDB). Small DB to store credentials and pending session data (when creating account)
DB for the crypto store (CryptoDB). Medium size DB to store OlmDevice and any Crypto related elements (users’ devices, keys, etc.)
DB for the session data (sync response, etc.) (sessionDB). Large DB containing Rooms, Events, etc.

Problem we have with Realm

Problems with Realm:

Crashes we cannot fix, in the native code
Performance are bad on insertion, when too many DB observers are set up
We do not take advantage of Realm lazy loading because of the SDK. So we copy the whole objects, which is not efficient and requires lots of memory
Not thread safe, so we are forced to use the main thread to observe the database (as long as the LiveData)
Not maintained seriously (could change soon with version 7.0?)

Some advantages of Realm (and why we choose it first):

Quite easy to use for common use cases
Encryption of the DB out of the box
Quite easy to migrate scheme
Already used on the legacy Android SDK for Crypto DB, and also on Riot-iOS

SqlDelight

From https://github.com/cashapp/sqldelight:

SQLDelight generates typesafe APIs from your SQL statements. It compile-time verifies your schema, statements, and migrations and provides IDE features like autocomplete and refactoring which make writing and maintaining SQL simple. SQLDelight currently supports the SQLite dialect and there are supported SQLite drivers on Android, JVM, iOS, and Windows.

What has been done so far by François:

During the week-end: study of other DB solutions.
After 1 day of work (Thursday): Implementation with SDL Delight, of the AuthDB, with migration of the data from the Realm authDB. User will not have to login again (Realm objects has to be deleted) -> it’s working

What we have to do to finish the study:

Ensure that we can use an encrypted DB for SQL (SqlCipher)
Benchmark performance on insertion, read, and compare with Realm
Check and test how we listen to the DB updates (live queries)

What will have to be done for full migration:

Implement for cryptoDB, with migration of data from the Realm cryptoDB
Implement for sessionDB, data from Realm sessionDB can be lost. An initial sync will restore them
Estimate time to fully implement it: 20 md, mainly spent on the timeline (ChunkEntity, TimelineEventEntity and RoomEntity)
Estimate time to implement a first version for the timeline without all features support: 15md
After a few months, Realm dependency will be removed from RiotX, along with the data migration code.

To sum up

Pros:

Developed by Square, a company which provides top-class Android libraries, used by every Android developers
Rely on SqlLite DB, which is natively supported DB solution in the Android SDK (since Android 1.0). So stable, reliable, highly tested
Write any sql queries, even the most advanced one
We can write TU, run on a classical JVM (so not on an Android emulator)
Designed to be multiplatform: can generate Kollin native code. Could generate Swift code for iOS (?)
Tools to develop are good and user friendly (code coloration, code completion, code navigation avoid navigating to the generated class, but goes directly to the SQL file, etc.)
(clear) DB can be inspected with Stetho, or with any tool able to open a SQLLite file.
We have already interfaces and efficient dependency injection, so for some cases, it’s super fast to migrate from Realm to any other implementation

Cons:

Need knowledge on SQL language
Needs to adapt the way we manage the timeline. Other Tables should be less painful
Time spent to change the DB
Generate some useless classes. Could be removed by the code shrinker (not yet activated)
How do we certify that this will be a long term solution?

Other solutions quickly taken into account

Room

Developed by Google
Could be a good candidate.
It’s more like an ORM, so do too many things (?)
Developers will have to define twice the queries.
Also not every SQL facilities are available (triggers are missing for instance)

Less control given to the developer than SqlDelight

ObjectBox

Same approach than Realm
About the timing
We can finish the study (about 1md)
We will wait for after the FOSDEM to spend more time on this, and decide together for a GO to migrate the SqlDelight.

[Half-Shot]

I heard on a sync at some point that this was decided against. Should this be closed if the team isn't persuing it?

[bmarty]

@Half-Shot : We had performance pb and also issues related to threading, but this is not a final decision to stick on Realm. So we can keep that open for the moment. The dev on SQLDelight is just hibernating :)