DataTables/Editor: Direct connexion to SQLite Database on Webview and browser

htuser

Dear All,
I already post about this topic at: https://www.datatables.net/forums/discussion/71524/configuring-for-sqlite-database-for-editor?
However, I find it's very important to highlight this request via a new post.
Right now we can connect dataTable to SQLite using PHP and C#. However, new Javascript frameworks such as CapacitorJS, Cordova allow us to "built-once develop everywhere" And with the latest SQLite WASM https://sqlite.org/wasm/doc/trunk/index.md offering persistent storage in browser/webview, it's very important for DataTables/DataTables Editor to provide us a pure JS API for connecting to SQLite. I was think using AJAX for linking DataTables to SQLite. But, until now, I'm failing.
Please let me know if you're a workaround or you'll provide such API in a future release. Among other things, a such API could easily offer an UI for manipulating SQLite store data in browser and webview context.
Thanks in advance for responding.
Best,

allan

You noted the PHP and C# libraries for Editor, but did you also see the NodeJS libraries? They are "pure JS" and can operate with SQLite just as the other two can. Our Node libraries use Knex for the database interaction. I haven't tried running it client-side, but it might be possible.

Even if it isn't, then it would still be completely possible to write an interface layer without our libraries. In terms of populating data you just need to run your SELECT command and then populate it into DataTables. That could be done with rows.add(), data or if you wanted to, use ajax to intercept the DataTables Ajax request, get your data from the SQLite server and then return it back.

As far as I am aware, there is nothing stopping the use of SQLite in the browser with DataTables at this time.

Allan

htuser

Thanks for responding to my post. I also see NodeJS documentation for DataTable Editor. However, NodeJS is JS on the server. And, because basically Knex use commonJS module, we must use workaround for having it running in the browser. I think we can have simplest JS libs to link SQLite persistent JS with DataTable Editor.
>! you just need to run your SELECT command and then populate it into DataTables. That could be done with rows.add(), data
We would like a demo from you about this.

if you wanted to, use ajax to intercept the DataTables Ajax request, get your data from the SQLite server and then return it back.
Ajax, I would prefer fetch. However, with a locally (in browser) persistent SQLite AJAX or fetch are not appropriate.

DataTable/DataTable editor is the ideal candidate as a UI for SQLite in browser. However, you've interest to help in supporting this with a pure ESM6 JS libs. In the meantime, I'll play with solutions you proposed and I'll let you know about results.
Best

allan

By "Ajax" I meant any async communication - not specifically XMLHttpRequest. Anything can be used - fetch, jQuery.ajax, Firestore, whatever.

We would like a demo from you about this.

I don't have a WASM SQLite instance I can spin up, but in pseudo code it would be something like:

var results = sql.query('select * from users');

let table = new DataTable('#myTable', {
  data: results
});

// or for an existing table
table.clear().rows.add(results);

As long as you can get the data from somewhere, we can easily populate it into the database.

If there is enough demand from customers for 100% client-side libraries to interface with WASM SQLite, it is certainly something I can look at. I would expect however, a syncing / offline database would prove to be more popular (otherwise your changes are always just kept in the browser - unless you connect to a remote SQLite db I suppose?).

Allan

htuser

Thanks a lot @allan for your interest in this topic. We need both syncing to remote server and also for persistent storage in browser PWA, webview, Cordova/CapacitorJS and for any "Built Once Develop Everywhere" JS SDK.

a) I don't have a WASM SQLite instance I can spin up, but in pseudo code it would be something like:
We would like to see a demo from your team for:
i) Select data from sqlite and populate, display them in dataTable;
ii) Use dataTable as a data entry form with sqlite as a backend;
iii) Use dataTable to modify data already stored in sqlite;
This with direct connexion between DataTable and SQLite WASM.
Please can you post a demo similar to this one you posted here: https://live.datatables.net/fokameru/1/edit ?
Normally, I could use PHP WASM (https://wordpress.github.io/wordpress-playground/pages/using-php-in-the-browser.html) for this purpose because you already have example for using PHP with AJAX. However, I think it's possible to have it with pure JS without PHP, this because also PHP WASM is heavy. One of my idea is to a serverless connexion between DataTable and SQLite via service worker and worker thread. What do you think about?

If there is enough demand from customers for 100% client-side libraries to interface with WASM SQLite, it is certainly something I can look at. Sometimes, it's very important to anticipate the demand. You can easily understand that JS/WASM "Built Once Develop Everywhere" is the future. So, client-side libraries to interface with WASM SQLite for DataTable is the most important investment you can do!
Best,

allan

Here is a simple example showing a WASM SQLite database being created and populated, then the data loaded into DataTables:

• Using data: https://live.datatables.net/lopizoqo/1/edit
• Using rows().data(): https://live.datatables.net/lopizoqo/2/edit
• Using ajax as a function: https://live.datatables.net/lopizoqo/3/edit

I've used SQL.js for this, which is SQLite run in WASM.

As you've seen it is basically the same as the pseudo code I posted above. The only real change is that for SQL.js I added a function to convert from their result format of arrays to an array of objects, That isn't required, but I find it easier to work with.

So, client-side libraries to interface with WASM SQLite for DataTable is the most important investment you can do!

I've been running this business for more than 13 years now, so I'm quite aware that I need to be ahead of the curve if I can. I've yet to be convinced that running SQLite in WASM is going to be a major part of my business though. Happy to be proven wrong in future, but at the moment there are other priorities that I need to work on.

Allan

htuser1

Thanks a lot @allan. The demo you posted is enough for DataTablesUsers.
However, as a new DataTables user, I would also like to see an example with DataTables editor were data are editing in the editor and dynamically saved to SQLite. In the future, having Browser/Webview (CapacitorJS, Cordova etc) libraries for DataTables Editor here https://editor.datatables.net/manual/ will be a must.

The same day you posted demo, we also had a npm version of the official SQLite WASM https://www.npmjs.com/package/@sqlite.org/sqlite-wasm
https://github.com/tomayac/sqlite-wasm so, it will be also very important to have a persistent OPFS demo.

The latest days I was very busy, but in the next months, I'll play with and try to post some demos regarding DataTables Editor and SQLite WASM.

“I've yet to be convinced that running SQLite in WASM is going to be a major part of my business though”
You already implemented one of the most demanded and innovative JS libs: DataTables. However, it's not so difficult to predict that DataTables will have more demand because of WASM technologies.
Best,
PS: I'm obliged to post with a new username because you denied my previous email and username.

allan

I'm obliged to post with a new username because you denied my previous email and username

Sorry about that! I wonder if the auto spam detection made a mistake. Either way, I've unbanned that account now!

Regarding the WSAM integration - let me know how you get on with it when you try it. It is amazing that the browser can now run databases completely. I saw a WASM Postgres the other day...!

Allan