expo-sqlite as a backend storage

[martin-ecatch]

First of all, thanks for this library!

My app was built with the previous generation of this adapter, using expo-sqlite. It had been running perfectly for years. The only thing I needed to do was patch the \u0000 issue in sqlite. However, after upgrading to this new adapter last year, our app spiked with crashes and ANR's, which I attribute to op-sqlite. Upgrading op-sqlite to the next (compatible) version made things even worse with random errors about transactions not started, etc. Newer versions of op-sqlite changed the API and are not backwards compatible.
Therefore, I don't trust op-sqlite any more, I just don't think it's mature/stable enough yet. Maybe it needs a couple more years.
I was thinking about refactoring this library and adding expo-sqlite as an optional backend. Would you be interested in a PR to merge into this library? It could be a configuration switch, so you can still use op-sqlite if you want.

Thinking more long-term... my business is very much dependent on this piece of technology. I think CouchDB+PouchDB is the greatest thing ever. What are your plans maintaining this in the future? Do you want any help? Would you be interested in having a chat with me about this?
Sorry for mixing all this into one issue but GitHub doesn't have a way to message you directly.

[craftzdog]

Hi,

One question: op-sqlite doesn't have \u0000 issue. Have you migrated your dbs to get rid of the null escapes?
In my app, op-sqlite is quite stable so far. I don't know but it'd be nice to give feedback to the author if possible.

This library is mainly for my app, and I'm just sharing the source here.
It is possible to support other sqlite adapters like expo-sqlite in this library though, I'm worried if it is a sustainable approach because I'm not interested in maintaining them.
There were a few guys who contributed to this repo and related repos, but they haven't always been able to keep updating.

Maybe you can start off by forking this repo and hacking it yourself.
Then, if you'd think it's good to port it back to this repo, please let me know.

[martin-ecatch]

Thanks for your reply! My "migration" was deleting all the "mrview" databases and leting PouchDB regenerate all views. It did the trick. Allegedly, the new expo-sqlite (from SDK 50) doesn't have the \u0000 issue. I shall confirm.
I have a fork (I have contributed to this project in the past). I'll have a go at implementing this. I'm assuming the "main" branch has all the latest stuff...? Do you consider the main branch stable at this point?
Thanks for your help!

[craftzdog]

Your migration sounds good.
Yes, the main branch is the latest.
I'm currently waiting for pouchdb/pouchdb#9028 to be published but it hasn't yet.
My app is still RN@0.74.6, but when I upgrade it, I'll be testing and publishing the new version on npm. But I think it should be stable already, as I've tested it in the example project.

[BingCoke]

Based on the author's library, I am currently developing an sqlite-adapter that aims to be compatible with the vast majority of sqlite libraries, such as exposqlite, opsqlite, or capacitor sqlite, etc. I plan to release it on GitHub in the next few days. The principle is similar to that of websql-core; as long as the corresponding interfaces are properly implemented for different sqlite frameworks, the adapter can be realized, and the implementation is quite straightforward. At present, it is already compatible with opsqlite, exposqlite, and capacitor sqlite. I am now grappling with binary data. Unfortunately, the sqlite version in expo 52 still has an escaping issue. However, they proposed a solution, which is to use an arrayBuffer, but I haven't tried it yet. Perhaps I will allow each sqlite compatibility layer to provide a conversion function to offer greater flexibility, such as allowing opsqlite to convert binary data to arrayBuffer, and providing escaping solutions for databases with escaping issues, etc.
If you are interested, I can release it on GitHub first without solving the binary issue, and let everyone work on it. To be honest, I have spent very little time dealing with the adapter issues, but the binary problem has already taken me nearly two days to handle.

[BingCoke]

Hey bro! Based on the author's library, I've developed pouchdb-adapter-sqlite, which isn't tied to any specific SQLite implementation - it just needs to implement the corresponding interface. It now works with opsqlite, exposqlite, capacitorsqlite, and others, with binary data issues already resolved! Using expo-sqlite, you can directly view images stored in SQLite. It also provides examples to solve the problem where arrayBuffer fails to create Blob (this issue would prevent document synchronization when multiple attachments are added, since attachment syncing relies on Blob creation).

If you're interested, feel free to check it out: https://github.com/BingCoke/pouchdb-adapter-sqlite Let me know if you'd like any adjustments!

[martin-ecatch]

Nice! Looks like you beat me to it :-). I'll check it out!

[BingCoke]

Nice! Looks like you beat me to it :-). I'll check it out!太棒了！看来你抢先了 :-)。我会看看的！

Next, I will decouple SQLite and PouchDB to allow multiple PouchDB instances to be stored in the same SQLite database. As you know, every time you create a view, it creates a new database, which is completely unnecessary. Currently, each new index creates a new SQLite database and a new SQLite connection.