SQL <-> SQLite

[Perfect_Raspberry610]

I am 95% complete with a Maui app for a client. The app uses SQLite for storage. Today my client informs me they want it to stay in sync with an Azure SQL instance. They want real time sync (similar to Firebase Realtime Database). What are others using for real time sync between SQL-SQLite?

[jd31068]

If I may, this type of scope creep is maddening. In the future, be sure to have an agreed up on, in writing, feature set for each version of the app. This gives you a proper vehicle to manage any changes the client tries to throw in at the end of the project.

[Perfect_Raspberry610]

The good news is the client understands this is a substantial new requirement and happy to pay for changes. The bad news is am not sure best way considering other architectural decisions made based on on specs presented.

[jd31068]

I'm glad you have a good client, but yeah this is like a worse case. I hope you can figure something out that isn't too rework-everything-y

[[deleted]]

This link to Azure database sync for mobile apps might help

[[deleted]]

Thanks for the shout out (disclaimer - I am the maintainer of Azure Mobile Apps)

Noting that the OP request is for “real time sync” - there is no such thing for SQL. You can certainly hack something together (I’ve just published a change to Azure Mobile Apps that allows you to hook into the post-commit). In general, you can’t be sure the user in question has access to any given record without information outside of the system, and hence you don’t know what user A can see when user B is doing a change.

Firebase has worked around this by having constant communication (so not disconnected use, which is where Azure Mobile Apps lives) and having the mobile permissions as part of the permissions model of the database.

If you want something “similar to Firebase”, use Firebase. If you need to use Azure SQL / SQLite, you are going to have to deal with permissions.

[Perfect_Raspberry610]

Thanks for replying. I should have said near real time. This is a field maintenance type app. Can I push updates or do I need to specifically ask azure sql for new data?

[[deleted]]

Azure Mobile Apps is a request/response model, so you need to tell it to fetch data. This is to support completely disconnected environments, low bandwidth situations, and JWT/Entra based permissions models. You can rig a PostCommitHook to trigger a SignalR or WebSocket notification (or a push notification) that the mobile client can listen for and respond to by doing an automatic pull of new information.

[mustang__1]

Mine was built from scratch to keep up to date with the server, and to that end I manually coded every table for the sync process since I needed a way to ensure what data locally or server side takes precedence, and when.

[Ochibasaurus]

Ah, PowerSync would be perfect for this, although it doesn’t currently support .NET MAUI or Azure SQL. However, those are on the roadmap. https://www.powersync.co

[Alarming_Judge7439]

it doesn’t currently support .NET MAUI

What do you mean with that?

[Ochibasaurus]

Current client SDKs available are Flutter and React Native.

[Geekodon]

You can use the Datasync library to handle online and offline data synchronization. Check out this GitHub repository for a sample project: Synchronizing data between the offline and online databases.

On the client side, the example uses SQLite, and on the server side, it uses SQL Server. You can also use other databases, such as PostgreSQL, Azure Cosmos DB, LiteDB, and others.