Hi,I am building an application with a local SQLit...

# server

[Yassine Abou]

Hi,I am building an application with a local SQLite database and a remote PostgreSQL server using Ktor and Exposed. While the basic CRUD operations and authentication are working well, I am struggling to implement the synchronization between the local and server databases. I've encountered many race conditions and bugs, and the synchronization logic is becoming messy. Could anyone recommend a design pattern or architecture specifically for handling client-server data synchronization? Here the file for more context: asyncManagerImp: https://github.com/yassineAbou/LLMS/blob/master/composeApp/src/commonMain/kotlin/org/yassineabou/llms/app/core/data/async/AsyncManagerImpl.kt

[Kobie Botha]

have you checked out powersync? it has a kotlin sdk and it's specifically built to sync between backend dbs like postgres and sqlite (disclaimer i'm on the team)

[Yassine Abou]

Yes I have check it. But this is just a pet project and I want to learn more by implement sync by myself

[Kobie Botha]

ah yeah, a worthy exercise.

many design considerations...

one simple thing to check is that using UUIDs for all primary keys in sqlite is going to make life much easier

but then there are other considerations such as - do you want the server to be authoritative or the client? for example - the server deletes a row that was updated locally while offline. if you download server updates first, then what should happen with that local update

[Yassine Abou]

Thank you Kobie for the UUID suggestion, for me I want this approach: changes happens in local database first and then update server on background. If understand "authorative" correctly I want to client to win when conflict happens between the client and server databases

[Kobie Botha]

are you open to using something like CRDTs to merge conflicts ? or would that defeat the point of the exercise

[Yassine Abou]

I think CRDTs are used when multiple people are editing the same data. my app is for a single user chatting with text models and generating images, I don't think I need them. I could be wrong though, if it that would make the sync logic work I would

[Kobie Botha]

yes pretty much

[Kobie Botha]

for single user then the last question is whether you want to support multiple devices for the same user or not

[Yassine Abou]

Yes, I want to support multiple devices for the same user

[Ohiorenua Aigboje]

You can use room for local and use the exposed tables to create your room entity. You can create a code that update the room anytime you make a CRUD request to the api base on the response of the api.

[Ohiorenua Aigboje]

You should save only the user data and the data you need to the room

[Yassine Abou]

I actually used sqlitenow for my local database, which is pretty similar to sqldelight. Using a local database as a cache is an interesting approach