Corruption with JSI and OS-SQLite

[scarlac]

Is it possible to write custom Kotlin code in an app such that it uses the bundled JSI version of SQLite (sqlite/sqlite-amalgamation-3360000/sqlite3.c) that watermelon-jsi ships with?
I do not have the option to use the non-JSI version, as I need the synchronous interface in RN.

Or alternatively change the watermelon-jsi package so it uses the Android-bundled version of SQLite instead?

The issue is that WatermelonDB uses a SQLite version that's different than the OS's, thus they don't share database locks which can lead to corruption of the database:
https://ericsink.com/entries/multiple_sqlite_problem.html

[...] Part of that work-around involves keeping a global list (mutex protected) of open SQLite database files. But, if multiple copies of SQLite are linked into the same application, then there will be multiple instances of this global list.

Also happy to take any other suggestions on how to expose the shared SQLite instance so it's usable by the app and doesn't cause corruption? For example, will simply #include ".../../../ ... sqlite.h be sufficient, when it comes from a separate module?

For context, I have come across DB corruption and was able to recreate the corruption by writing to the same DB file and table with the OS-bundled SQLite (android.database.sqlite.SQLiteDatabase) in Kotlin, while having the RN side also write to that same table. The issue happens with rapid writes.

[radex]

Correct, if you want to interact with the instance from outside of JSI, you want to use the same version of sqlite

Or alternatively change the watermelon-jsi package so it uses the Android-bundled version of SQLite instead?

Is that possible? I'm not an Android expert, but my understanding was that Android-bundled SQLite is only available via the the Java interface, not from C++. That's one reason why I decided to bundle SQLite there, whereas on iOS I use bundled version. (The other reason being that Android devices are often very outdated and the variance in sqlite versions is large, which can cause practical problems). But maybe I didn't look deeply enough into it. It'd probably be easier for users if we used OS version by default and replacing it was an option for advanced users.

Also happy to take any other suggestions on how to expose the shared SQLite instance so it's usable by the app and doesn't cause corruption? For example, will simply #include ".../../../ ... sqlite.h be sufficient, when it comes from a separate module?

If you're looking for a quick sketchy hack, without properly engineering this feature (which could be very useful), you could pass pointer address to the JSI's sqlite instance through JS to wherever you need.

[scarlac]

Or alternatively change the watermelon-jsi package so it uses the Android-bundled version of SQLite instead?
Is that possible? [...]

From my research, it looks like the answer is "no". SQLite is not exposed in C++, so you'd have to create bindings in Java/Kotlin that re-exposes them and then call the Java/Kotlin bindings from C++ somehow.

If you're looking for a quick sketchy hack, without properly engineering this feature (which could be very useful), you could pass pointer address to the JSI's sqlite instance through JS to wherever you need.

Thanks, although I don't quite know if this would even work across the various bridges. You mean to say simply serialize the memory address as hex, pass it into Kotlin/Java, which passes it to JNI the main app module, and then create a pointer to that address, then deference and that would work (assuming the exact same sqlite.c file is imported)?

[radex]

Thanks, although I don't quite know if this would even work across the various bridges. You mean to say simply serialize the memory address as hex, pass it into Kotlin/Java, which passes it to JNI the main app module, and then create a pointer to that address, then deference and that would work (assuming the exact same sqlite.c file is imported)?

Yes. Naturally you have to be super careful with it so you're not accessing a freed db. But it does work.

Android's RN Bridge implementation exposes a pointer to the underlying JSI CxxBridge in this manner - as a plain long number you can pass around and convert back to raw pointer.

[scarlac]

@radex I appreciate the help here, but this is still a consistent need for us. I see that experimentalUnsafeNativeReuse was added in 0.26 - is it possible to have this work with the JSI adapter as well? I've tried working with the C++ code and it's too confusing to me, unfortunately.

[scarlac]

Follow up time!
It is possible to connect using the WMDB-bundled version of SQLite, but it requires a huge amount of effort. The plan is roughly:

1. Find out where the libwatermelon-jsi module is outputted to
2. Write a C++ module integration in your project that dynamically links to the libwatermelon.so file (ensuring compilation order!)
3. Write a C++ API using JNI that allows you to call those native C++ functions from Kotlin or Java
4. Start using your new C++/JNI API in Kotlin

While this seems simple on the surface, the challenge is that each step requires several setup changes, many of which you'll find are "not possible" according to various (answered) Stack Overflow answers. However, it is.
Once you've linked together your modules and you have a Kotlin -> JNI -> C++ (with sqlite.h/cpp import) -> libwatermelondb setup, you can extend it to your liking, allowing native calls using WMDB.

Unfortunately, you will find that WMDB was not designed with a shared database in mind even after this. WMDB will fail to handle read errors, and it has not been set up to wait for locks, so it'll fail immediately instead of waiting for the lock. This is the default SQLite behavior, so it's not a huge surprise, but it'd be possible to fix in WMDB.

Overall, I think it's fair to say that WMDB does not support native integrations. There are simply too many caveats, and it'd be better to use a different SQLite library or write your own, if you have plans to share data with any native code.