Xamarin apps: Sqlite vs Realm. What’s the best mobile DB solution?

Last week Realm.io introduced Realm for Xamarin. Realm promises an easy object database with full query options and better performance than existing solutions (so also sqlite which is probably the most used database solution). Time to put both sqlite and Realm to the test. which is better?

When comparing these solutions i wanted to test out the full relational options of the database because that is what Realm advertises with. if we are looking at just plain key value storing we could use something like akavache (based on sqlite back end) which is probably faster because of lots of optimisations in queries. I do recommend using akavache for caching scenarios or when you are just storing simple types of data that do not require complex querying.

To test sqlite and Realm i’ve created a simple datamodel with orders and order lines. each order has a list of order lines and i would like to be able to query orders and get the underlying orders back immediately. but also the other way around. i would like to query certain orders which have order lines that contain a certain product. those kind of queries happen often when you are working in a real world business app.

So now we have this datamodel + some queries we want to execute. lets define some criteria on how to score both solutions. I came up with the following critters: Ease of use, speed, & maintainability.

let’s talk about these criteria in a bit more depth. I’ve created a sample project on github that has the samples and source code for you to experiment.

You can download the source here: https://github.com/Geertvdc/Xamarin-RealmVsSqliteCompare

Ease of use

Sqlite and Realm are both pretty easy to set up. I’ve used Sqlite in the past before and had never used Realm so i had to do some reading up on Realm. I didn’t have to read that much though because the basic explanation of Xamarin.Realm on the Realm.io site explains the most used features and all the features i need for this first test.

Realm is actually really simple to set up. Where Sqlite does require some plumbing (getting a location for a file to store your DB in and creating a SqlAsyncConnection) this is not needed for Realm. In Realm you can just call the GetInstance(); method and you are ready to go. For both we need to install nuget packages of course which can be though for sqlite as well since there are so many different packages it’s sometimes hard to know which ones to use.

Sqlite needs 3 nuget packages for the basic functionality:  SQLite.Net-PCL, SQLite.Net.Core-PCL and SQLite.Net.Async-PCL. next to that i use 2 extra packages to give me some extra extension methods: SQLiteNetExtensions & SQLiteNetExtensions.Async.

For Realm we just need to add Realm nuget package to all our projects.

After we have an instance of both Sqlite and Realm we can start inserting some data into our database. Below is some sample code to insert 1000 orders which each have 5 order lines. I want to be able to insert all of them and make sure that there is a relation stored between the orders and the order lines so i can retrieve both in 1 query.

Looking at the code Realm just works a bit easier. we don’t have to think about doing sql transactions (if you forget/remove the transaction it is about 3x slower.) We just need to create the objects in a Realm.Write block and Realm will handle all the things for us. I do have to say i’m no fan of having my database objects have to inherit from RealmObject and that the constructor has to be Realm.CreateObject() but more on that in the Maintainability criteria. When looking at ease of use only i have to say Realm is easier because it does all the plumbing and yak shaving for you. Winner: Realm

Speed

Realm promised us a faster database than the competition so this was the reason i started this investigation. Sqlite can be slow some times but most often this is because of bad use. Paul Betts (creator of Akavache) did a great session at Xamarin Evolve this year on why sqlite is often slow and how to use it properly. To test the speed I’ve created a xamarin forms app and ran some tests on both Android and iOS. speeds do change a bit from platform to platform and also there is a some difference in using a device or emulator/simulator. Overall Realm seemed a bit faster in querying complex queries and it seems to use some caching looking at the query that took 0 ms on iOS. A note here is that Sqlite can probably be even a bit faster when you optimize the queries. What i used now for Sqlite is just a generic query to retrieves all objects with it’s children.

After looking at the test data there are some differences and sometimes Sqlite is faster then Realm and the other way around. Sqlite can be fast if you know what you are doing and Realm seems to do the job quite well without any special thought on how to do things like transactions. scoring points for ease of use again.

Winner: No clear winner between Sqlite & Realm purely regarding speed

Maintainability

The last criteria on which i wanted to test these 3 solutions is maintainability. Sure the ease of use of Realm seems nice but how would this work in actual apps that need to be maintained and tested for long periods of time.

The first think i noticed when looking at Realm is that all your database object need to inherit from RealmObject. Because of this inheritance Realm knows how to do all it’s magic but i really don’t like this approach. creating objects cannot be done by using a simple constructor you always need to use Realm.CreateObject, creating a tight reference to Realm. Same goes for the lists of objects within a RealmObject which have to be of the type RealmList<T>. I like Sqlites approach much better where you just use simple POCOs (Plain Old CLR Objects) with some attributes attached so Sqlite knows how to store certain things.

In a proper testable software architecture you want to keep the dependency on either Realm or Sqlite to a minimum of places and preferably only in the classes that handle the database communication. for Sqlite this is possible however for Realm this will be quite hard since creating new objects can only be done by Realm.CreatObject. You could do a mapping from Realm to POCOs in your repository but that defeats the purpose of the ease of use of Realm which offers functionality for listeners on object changes to easily update your UI.

another thing that is really missing from Realm is support for Async. In my opinion every mobile library should have async features as default (or as only option). It is on the “missing features” list of Realm so they are aware that this is an important feature. But at it’s current state in the beta it’s not in.

The last thing i noticed was the Fody Weaver that is being used by Realm. At first it’s not that important for you as a developer because it just works. the weaver will do some changes to your IL to add stuff Realm needs. As long as everything works it’s no problem, however when you run into problems you might have issues finding the issue because the code that is being executed is actually a bit different from what you wrote.

Combining these things related to maintainability i think Sqlite is a clear winner here.

Winner: Sqlite

Conclusion

Looking at these 3 criteria i think Sqlite is still the best solution. Sure Realm is pretty fast and needs almost no plumbing i think the maintainability of Sqlite will win it in the end. I do see some use for Realm, especially for POC’s or small projects. (although for POCs i have to much experience of POCs running in production in the end or growing to larger apps, if that is the case i would pick Sqlite).

We have to take into account that Realm is still in beta and Sqlite has been there for ages but the things i don’t like about Realm are basically in it’s base so i don’t see them changing that quite soon. I’ll be watching Realm to see what they are up to in the future but for now i’ll keep using Sqlite.

Let me know your experiences with Realm in the comments below. i’m curious if people have different opinions than me after playing with it for a few days.

Happy Coding

Geert van der Cruijsen

The post Xamarin apps: Sqlite vs Realm. What’s the best mobile DB solution? appeared first on Mobile First Cloud First.