QUESTION: Can we compile ourselves to add the SQLite Sessions Extension to better-sqlite3-multiple-ciphers?

[GitMurf]

Sorry @m4heshd but given there is not a Discussions section in the repo I thought it would be appropriate to open a gh issue for this. If for the future you would prefer a different way of handling these types of questions, please let me know!

I am investigating the use of the official SQLite Sessions Extension for purposes of a simple Sync mechanism for my SQLite database across multiple devices. I know this is not included in better-sqlite3 so was going to pursue attempting to compile myself using these instructions: https://github.com/m4heshd/better-sqlite3-multiple-ciphers/blob/master/docs/compilation.md

I know these instructions are the same as from the core better-sqlite3 repo (without encryption)... so my question is, can I follow the same steps to be able to add the SQLite Sessions Extension and compile myself from your multiple ciphers repo? Or are there any caveats / complications given the nature of encryption / multiple ciphers that would not allow this to work?

Lastly, I am using Electron so know that there are some additional steps that better-sqlite3 has to do when an Electron update comes out to make it compatible (I assume something with the prebuilds or something)? So my question is that if I were to compile myself with the instructions linked above so that I can add the Sessions Extension, would there be any issues with Electron? Essentially are you all doing something in addition to get Electron to work that would not be included in the compilation process that I would be doing using: npm install better-sqlite3-multiple-ciphers --build-from-source --sqlite3=/path/to/sqlite-amalgamation?

Thanks so much!

P.S. if we are able to get this to work with better-sqlite3 + multiple-ciphers for encryption (your repo here) + compile to add the Sessions Extension, I surely will convince our team that we need to donate to yourself and the core better-sqlite3 project as well!! We are a small team with no users yet (and no revenue haha) so we wouldn't be able to donate huge values yet, but I would like to be able to show our appreciation and hopefully help motivate you all to keep up the great work!!! :-)

[m4heshd]

Sorry @m4heshd but given there is not a Discussions section in the repo I thought it would be appropriate to open a gh issue for this

That's alright. It reminded me to enable the feature. Thanks for that. 😁

It seems like the Session Extension is baked into the vanilla SQLite amalgamation which can be enabled through compile-time options. Your timing is great since I made a huge update in v11.8.1 that made this a possibility.

SQLite and SQLite3MultipleCiphers source amalgamations are now manually built, which allows setting build-time options

So, by simply adding SQLITE_ENABLE_SESSION and SQLITE_ENABLE_PREUPDATE_HOOK to the DEFINES in update-sqlite3mc.sh, and running npm run build-release would do the trick. This also means that you might have to maintain your own fork. I was recently thinking about adding this as a feature to BS3 where the user can override or append SQLite CTOs.

But here comes the tricky part. From what I can gather, the Session Extension is only exposed through the C and C++ APIs, which means the functionality cannot be accessed through SQL executions. This means you need to write your own bindings to call Session Extension functions via JS, sadly 😟. I did go through the source to make sure. You can write your own extension with only the functions you need exposed as SQL functions.

I surely will convince our team that we need to donate to yourself and the core better-sqlite3 project as well!!

Any sort of support is always appreciated. 😊

[GitMurf]

Thank you so much for the detailed response and feedback!

I didn't think about the bindings! So essentially would I have to implement something like the node sqlite lib did here? nodejs/node#54181

[m4heshd]

So essentially would I have to implement something like the node sqlite lib did here? nodejs/node#54181

That is indeed an option. It will also make it quite difficult to maintain your fork once you have updated the core bindings of bs3mc.

I would recommend writing your own extension which utilizes the Session Extension API which then exposes those functions via SQL. This way, you can easily maintain your extension separately and it can also be loaded using loadExtension(). But remember, it also has to be compiled separately.

The choice is up to you.