Using Kotlin Exposed - ORM layer

[Retirement-App]

Hi there.

So I'm aware that for the time being, we need to use raw SQL code to interact with the Powersync database locally. I also understand that the Powersync sdk itself, is not 100% kotlin. I'm aware of an engine being made recently being designed in Rust (I'd love to take a look at it if that is open source?). But at the end of the day, it shouldn't matter since it's multiplatform.

So I was wondering, are there any limitations to making use of a pre-existing ORM framework like Kotlin Exposed? I know that the focus of this sdk is to be platform agnostic, hence why it's for multiple platforms (KMP). So choosing something like Kotlin Exposed in my head sounds like a good choice in this direction. Also, it (appears) that a dedicated ORM framework might make things easier, then it's not like you have to code your own plugins, compile-time inspections etc.

I also come from Android Rooms, so I very much miss the ORM approach to interacting with the Sqlite database (perhaps now it's time to take the training wheels off), but using an ORM feels a lot more standard and safer. But I do understand that too many frameworks and tools could ruin it with more gears to keep track of.

Not a request, but purely curious as to the tradeoffs one would face, and to what extent the possibility it would be to implement that as the bridge between the kotlin developer and the synchronized Sqlite database.

Thank you for reading.

[simolus3]

Thanks for reaching out! Adding support for query builders and ORMs is definitely something we have in mind. We are currently looking at SQLDelight and Room specifically, but Exposed is also an interesting option.

I'm aware of an engine being made recently being designed in Rust (I'd love to take a look at it if that is open source?)

Of course it is :) All components of the PowerSync client SDKs are open source. It is worth noting that this engine is mostly responsible for applying data (received from a PowerSync serivce) to the local SQLite database. Apart from that, queries are run using regular SQLite functions.

Not a request, but purely curious as to the tradeoffs one would face, and to what extent the possibility it would be to implement that as the bridge between the kotlin developer and the synchronized Sqlite database

The nice thing is that, since PowerSync is backed by a standard SQLite database, you could in theory use any of these libraries and just point them to the same file you're using with PowerSync. That will allow both PowerSync and Room/SQLDelight/Exposed to share the database and read/write the same data. However, there are some important caveats:

1. With SQLite, there can only be a single writer at a time. So if you open the same database both with PowerSync (which has a mutex for this internally) and another library (which will also have that), it's possible to accidentally attempt concurrent writes (which can lead to "database is locked" errors).
2. One aspect we think is very important is that you get reactive updates for your queries, i.e. with the watch methods returning a Flow. These are currently implemented with update hooks in SQLite, and those wouldn't fire if there are multiple database connections.

So for these two reasons, we need to provide closer integrations with those libraries for a decent experience.

[Retirement-App]

I understand. Thank you for getting back to me.

Yes, I can imagine that its quite a painful point, especially with the locks. I'm guessing that from the official documentation and that this kotlin sdk also exposes some SQLDelight interface and class references, I'm guessing that the focus is more towards SQLDelight (at least for the time being)?

If so, what would you say is the current progress? My quick LLM research on the two options (lol) tells me that SQLDelight appears better, since Powersync already uses a SqliteDriver. I'd love to get stuck into the current SQLDelight progress to perhaps get closer to a working ORM (or least a DSL of some kind).

[simolus3]

It's true that we use some SQLDelight interfaces internally, but that's mostly a historical artifact and we're not actually using SQLDelight internally. We are looking at SQLDelight as a primary option at the moment because Room requires additional changes wrt. schema management.

There's a first PR to migrate our internal SQLite drivers to something more stable, and another one adding SQLDelight support. These are still work in progress though.