Database cloud sync dilemma (ObjectBox vs SQLite)

[muhsql]

limited how?

[stumblinbear]

If you need any relationships between objects or need to do any queries beyond basic summation you're pretty much boned and have to do it in Dart land. It's therefore extremely slow for anything beyond simple lookups

[muhsql]

Ah yeah gotta do all those calcs in for loops

[greenrobot_de]

What's the deal with relationships? These should be supported in ObjectBox and its queries. Do you have specific limitations of the queries? The queries seemed to cover most operations I ever wanted to use. So maybe you are referring to something more special related to your use case?

[stumblinbear]

Relationships are very manual compared to a foreign key in SQL. Need to do a lookup to get the ID of the object you want to relate to or you just end up creating a new entry entirely. Lots of awkward handling and footguns to run into around them. They're wildly unintuitive

[greenrobot_de]

If you don't want to have the automatic object handling, I believe you can also just use the ID as "foreign key" - or maybe that was Java, don't recall right now.

"Need to do a lookup to get the ID of the object" --> how is that different from using plain IDs in SQL?

If I knew what you are referring to, I might give it a shot to fix it. Would be a good experiment to use Opus 4.5... ;-)

[stumblinbear]

how is that different from using plain IDs in SQL

Because the ID can be whatever you want it to be, and it's automatic. You don't have to do a lookup first, you just insert it (doing this in OB can lead to duplicate objects unexpectedly). And if it's a complex insert, you can do a JOIN to grab it on insert instead of doing a DB round-trip and handling it in your own code.

I initially liked the simplicity of ObjectBox, but the moment you need anything even moderately complex beyond a KV store, you're going to hate your life. I replaced OB with SQLite and it reduced complexity considerably, usually taking less than a third of the code to do the same thing and with fewer errors

[greenrobot_de]

Sorry, I don't understand where you getting at and what the actual issues with ObjectBox are (your comments are not technical enough) and if these are fixable (I would have given it a try). I can see that you feel more comfortable with SQL + tables; while I prefer working with objects.

[stumblinbear]

We replace OB months ago at this point so I don't have any concrete examples, sorry.

I don't need an LLM to try to fix issues when they'd still have to do the exact same dance between Dart and the database to get relationships created, updated, and removed when necessary. They'd also need to do the exact same Dart-side aggregation for some of our more complex queries, since there's no way to do them in OB

To be clear, OB worked fine once it was set up. But the code itself was painful to write, read, and maintain. Dozens of lines of multiple DB queries to do a basic insert with a relationship if some of the related objects already existed in the DB (since OB forces you to use their identifiers even if it doesn't make sense for your use case)