Add `dbhash` and `sqldiff` utilities as JS APIs for working with SQLite

[TheOneTheOnlyJJ]

What is the problem this feature will solve?

With SQLite now shipped in Node, as it begins being used in more and more projects, friction points are being discovered.

As pointed to in this issue comment, some features of SQLite require external C/C++ implementations or utility programs to be used.

While not advocating for bloating Node with these programs, I believe a select subset of them can be adopted and integrated directly in Node.

I propose for discussion 2 of them:

1. dbhash

• Documentation
• Code (C) is publicly available here.
• Why? It allows creating a SHA1 hash based exclusively on the schemas and data contained in the database, excluding any other information. Use cases include quick & efficient data tampering detection, both locally and over the network.

2. sqldiff

• Documentation
• Code (C) is publicly available here.
• Why? It allows obtaining a changeset (for which there already is support since sqlite: add support for SQLite Session Extension #54181) based on 2 database files. If sqlite: enable common flags #57621 lands, having access to this utility will enable Node to take advantage of the RBU extension (Documentation, as noted in section 2.2.4).

What is the feature you are proposing to solve the problem?

While I am not experienced enough in C/C++ to work on this, or even acknowledge if it is possible to be done efficiently, I propose investigating the possibility of having Node "wrap" these utility programs and expose them as JS APIs (maybe even in CLI?).
The 2 programs would require separate strategies, as dbhash outputs a SHA1 hash (or errors), and sqldiff creates a file (among other things and outputs, errors, etc.).
If the output could be reliably directed towards a Node processing layer (to filter out error messages meant for stderr, format the data, disallow anything reaching stdout, etc.) maybe this could be technically feasible with minimal to no changes to the original scripts, but I cannot approximate the amount of effort required to achieve this level of integration.

What alternatives have you considered?

I have not found any other way to easily get the hash of a database's contents. Having an API function/method available with a C implementation would ensure the best efficiency.
Installing the utilities as standalone programs creates setup friction and misses a convenient JS API for using them.

[cjihrig]

I am -1 to shipping additional SQLite utility binaries with Node since anyone can obtain these independently from Node.

[geeksilva97]

For RBU, we have the possibility that is exposing RBU API

[TheOneTheOnlyJJ]

For RBU, we have the possibility that is exposing RBU API

I'm not sure what you are referring to, could you please be more specific?

[TheOneTheOnlyJJ]

I am -1 to shipping additional SQLite utility binaries with Node since anyone can obtain these independently from Node.

Understandable. It is peculiar that such useful functionality lives in separate binaries. Could there be any other way to expose it in Node while avoiding including them?

For example, in my current project (Bachelor's thesis) I have good use cases for dbhash, but it is a hassle to integrate it as a separate dependency (also because the project is an Electron.js application). It would be very convenient to have a JS-level API that would achieve the same functionality one way or another.

[cjihrig]

Could there be any other way to expose it in Node while avoiding including them?

I don't think so. The fact that SQLite ships them separately says to me that they shouldn't be included at all. I also haven't heard of any other runtimes/libraries that include them (but I haven't checked either).

Since both of these things are CLIs, you could always interact with them via the child process API or something similar.

It's also important to remember that node:sqlite is not intended to be a dedicated SQLite development suite, and so not every SQLite tool should be included in Node.

[cjihrig]

I'm going to close this since this isn't something we are realistically going to add.