Hey, I'm bringing up the Realm vs SQLDelight debat...

# multiplatform

[StavFX]

Hey, I'm bringing up the Realm vs SQLDelight debate again because it's been a minute since it was last discussed and I didn't find a lot of great insight into comparing the two.
I'm starting a new (my first) KMM project and I have past experience with Realm, so I when I saw they support KMM now I was obviously curious, but it sounds like SQLDelight is widely used so I wanted to hear if anyone has experience with Realm on KMM? Anyone done any benchmarks to try and compare the two?

[kevin.cianfarini]

You should probably talk about your use case a bit for more nuanced guidance

[StavFX]

That's fair. I don't have a LOT of insight into what the models would actually look like, but we do know we want the DB to be our source of truth, and will be observed for changes (probably using Reaktive, but Coroutines aren't off the table).
One of my concerns is that if the relationships between objects get complex over time, we'll have to write more and more complex SQL queries to get all the data out (e.g. Group has a list of Users , each user has a ContentPolicy etc etc)

[kevin.cianfarini]

Generally speaking SQL is purpose built to model and extract data out of those relations. Trying to execute SQL via a Kotlin (or any application programming language filter) gets messy with lots of sharp edges.

[kevin.cianfarini]

Allow your SQL code to dictate what your Kotlin code looks like (via generation) and you’re working at a much better layer of abstraction.

[kevin.cianfarini]

Having worked with Django’s ORM extensively in the past I find the paradigm that SQLDelight uses to be indispensable.

[StavFX]

Yeah, I like that SQL allows me to SELECT only the data I need, and at the same time I like that I don't have to think about it in Realm and an object is an object, it'll always have all its fields 😅

[Casey Brooks]

You may have seen this article already, but Realm has a good article noting the differences between SQLite and Realm, though it is obviously a bit biased. It suggests that SQLite is more-or-less a “legacy” DB technology because it’s offline-only and lacks many of the features of Realm, but in contrast that is exactly why one might choose SQLite over Realm.

In general, Realm is great when you want to have your app contain a copy of the production database, cached offline for faster access. It basically requires that your app’s data model is exactly the model in the production database, which may or may not work for your needs.

SQLite is better for cases where your local data is more accurately described as a cache, or when you do not need any server-side storage of your data. The data in the local DB is not necessarily the same shape/structure as what’s in the prod DB, and this can be a good thing. For example, you fetch as little data as necessary from the prod servers and compute the rest on the device, rather than fetching an entire copy of the user’s production data. It also makes it easier to store some local data that should not be synced to production DBs (like auth keys or app logs).

Additionally, pure SQL is significantly better at expressing and querying relationships among data than any ORM will ever be. So while Realm may be better than Room or CoreData at managing relationships, that’s really just because those are ORMs. It won’t be as good as pure SQL, which is what SQLDelight provides. And in particular, the more complex the relationships in your data, the better SQL gets at managing them when you use strong foreign keys

[StavFX]

Actually I don't plan to use any of the synching/online capabilities of Realm. I plan to use it exactly as I would an SQLite db

[StavFX]

but regarding expressing relationships, doesn't Realm just do that by having a reference between objects? isn't that pretty expressive and easier to understand/maintain than sql queries?

[Pablichjenkov]

My experience:
2 Production Android Apps, 1+Million install each. Started with realm for easiness endup migrating to Room due to realm internal crashes related to multithreading.
Pet projects Compose-multiplatform, SQDelight, no complaints so far. I won't bother setting up RealmDB. I prefer non relational but probably wait until other solution other than realm db enters the market.

[StavFX]

hmm yeah I remember we had some issues with Realm, but it's been 5+ years since I've dealt with those, so my memory is foggy. This might be reason enough to ditch it

[StavFX]

You may have seen this article already ... though it is obviously a bit biased

haha yeah, that's why I didn't really put much weight on it and wanted to get people's experience/opinions. Thanks for the detailed input 🙂

[Pablichjenkov]

I really would appreciate if they only do file saving/reading stuff and leave the threading to the client.

[Casey Brooks]

Object relationships in Realm and other ORMs are good for simple relationships, but are difficult to do anything other than the most basic one-to-one or one-to-many relationships. For example, recursion, or joins on 3+ models, are things that can quickly become infeasible with an ORM. And like I mentioned before, SQL’s foreign keys are extremely invaluable for keeping the data tidy and preventing orphaned rows when editing/removing values of deeply-nested data (without needing to edit the entire object tree)

[kevin.cianfarini]

I want to chime in on the server<>client db interaction. When I was at CashApp I worked specifically on that functionality on top of SQLDelight. It’s not something that’s built out of the box with SQLDelight, but it’s fairly straightforward to do with some custom code.

As an added benefit you’re not constraining your backend devs to a particular technology that way and can instead choose whatever is best for your org.

[StavFX]

preventing orphaned rows

yeah that's a good point, and an issue i've had with Realm

[StavFX]

slightly off topic, encryption comes out of the box basically with Realm. What do people use with sqldelight? SQLCipher?

[kevin.cianfarini]

Did that, too, at cash. We opted to encrypt the entities within the db that were sensitive. In hindsight I really wish we had chosen something like SQLCipher.

[kevin.cianfarini]

Either strategy should work fine with SQLDelight as the SQLDriver is agnostic

[kevin.cianfarini]

As an added benefit you’re not constraining your backend devs to a particular technology that way and can instead choose whatever is best for your org.

To expand on this further, we originally were delivering data updates via push notifications. We later found them to be unreliable and switched to gRPC. We also augmented that with “sidecar” data in network requests that happened during BaU.

We were able to optimize how often data was synced that way. I haven’t used realm but I can speak highly of CashApp’s ability to own and configure their data layer because of this.

[StavFX]

Thanks for sharing! there's a lot of valuable lessons here that I can immediately use, and todos for the future (like using gRPC)

[Jeff Lockhart]

I've never used Realm in a production project, but if the object threading model is anything like Core Data, that was always a major source of bugs in our iOS app. I definitely agree, databases should leave the object/thread relationship to the client.

I've used SQLCipher for encrypted SQLite and it's worked well for the most part, only running into hiccups on the iOS side trying to use it under Core Data.

[Jeff Lockhart]

I've been using Couchbase Lite on several projects and wrote KMP bindings for the database. I'm planning to release the library soon! I find that Couchbase is a good balance between SQLite and other NoSQL databases, like Realm. It supports N1QL queries, essentially SQL with some additional features for JSON documents. Modeling complex deeply nested relationships that belong in a single object is one thing that JSON does well vs the flat rows and tables of SQLite. N1QL allows for selecting individual fields or an entire document, based on needs you can query either. It works great as a key/value store too, so you don't need a separate `SharedPreferences`/`NSUserDefaults` datastore.

[Jeff Lockhart]

My experience with ORMs is they can be inflexible for most anything other than simple use cases. Couchbase doesn't have an ORM API. It supports raw SQL query strings or a statically typed query builder API. The query result object behaves as a Map or List and can be used directly or mapped to an object as the use case requires. I've written an ODM library that does this mapping (also planning to release soon). There are also Flow APIs to observe a document, query, or database for changes.

[Jeff Lockhart]

And when client <-> server data sync is a requirement, Couchbase handles this too. The binary WebSocket sync protocol is fast compared to HTTP-based solutions I built previously. Its Sync Gateway can be configured for potentially complex data ownership and sharing rules as needed on the backend. The Realm <-> MongoDB sync configuration was more restrictive when I last compared the two.

[Jeff Lockhart]

So yes, it definitely depends on the app, schema, sync, and usage requirements, what database solution makes the most sense for a project. Each has its pros and cons to balance.

[Pablichjenkov]

Looking forward to your release Jeff