[Feature request]: Add support for offline data persistence

[jan-tennert]

General Info

• I installed the latest version of Supabase Kt
• I checked for similar feature requests

Feature request

As requested by a bunch of developers:
In many cases such as Mobile Apps, you will want to allow the user to use your app while having no connection to Supabase. For that, you currently have to implement data caching yourself. supabase-kt should support this, but I'm not completely sure which way I'd go to support:

• Postgrest
• Realtime
• (Storage)

If data gets removed/modified locally while offline, syncing should also be done automatically when back online.

Firebase/Firestore does support this.

Proposal:

For Postgrest:
The idea would be to have a local SQLite database via SQLDelight or SQLite and cache the data when any postgrest request is made.

• Only requested data gets saved, so no additional requests are being made.
• Filter support can probably also be realized by converting the rest query to an SQL query.
• For inserting entries to the local db while offline, we can just save the postgrest request 1:1 and when back online, insert them all at once in one request
• For modifying and deleting entries from the local db while offline, we probably have to make separate requests once the device is back online

With this approach, we have to device how we get the table schemas, which obviously should be created before any caching is done.

For Realtime:
If the device looses connection, the device listens to the local DB for changes*
* Maybe only for the high-level functions such as selectAsFlow

I put Storage in parenthesis, because other libraries such as Coil basically do that for us.

Usecase

No response

[jan-tennert]

@grdsdev @dshukertjr, would love to hear your thoughts on this!

[dshukertjr]

Are you thinking of creating a separate library that adds offline capabilities, or building offline support right into supabase-kt? If it's a library that users can optionally add to make it local-first, then maybe it's less of an issue, but there is always the risk of Supabase bringing a proper local-first solution and having to migrate to the solution.

We are constantly moving towards adding local-first to Postgres, and ultimately Supabase, and we want to make sure that we play the long game, and not come up with a quick solution that will half-solve the problem. Our most recent effort here Postgres has a lot of components to it, and there is a lot that you cannot do with just a sqlite database, such as handling triggers or Postgres functions.

I'm sure there are plenty of libraries that support local-first apps, and perhaps a better solution might be to build an adaptor of Supabase for them. Here is what I did with a community member for Flutter recently: https://supabase.com/blog/offline-first-flutter-apps . I know Powersync also supports Kotlin, although I have never tried it myself. https://github.com/powersync-ja/powersync-kotlin

With that being said, supabase-kt is currently a community library, so there is a bit more room to explore new things in general.

[jan-tennert]

Are you thinking of creating a separate library that adds offline capabilities, or building offline support right into supabase-kt? If it's a library that users can optionally add to make it local-first

Well, I've heard from a few people that data persistence similar to Firestore would be nice (which you can basically just enable), but ideally that would definitely be a separate library, even though we need to make some internal changes to the Kotlin library.

We are constantly moving towards adding local-first to Postgres, and ultimately Supabase, and we want to make sure that we play the long game, and not come up with a quick solution that will half-solve the problem. Our most recent effort here Postgres has a lot of components to it, and there is a lot that you cannot do with just a sqlite database, such as handling triggers or Postgres functions.

Yes, that's what I thought too. A SQLite DB is the simplest solution, but it lacks some of the features supported by Postgres.

I'm sure there are plenty of libraries that support local-first apps, and perhaps a better solution might be to build an adaptor of Supabase for them. Here is what I did with a community member for Flutter recently: https://supabase.com/blog/offline-first-flutter-apps . I know Powersync also supports Kotlin, although I have never tried it myself. https://github.com/powersync-ja/powersync-kotlin

The selection for such libraries especially with support for Kotlin Multiplatform is not very large, the current widespread solution is to use SQLDelight or Room (as recommended in the Android docs), but you still have to implement the whole caching & syncing part yourself. But I haven't looked into Powersync yet.

Maybe a good short-term solution would be to make a sample similar of what you did with Flutter on how to make an offline-first Android/Kotlin MP application.