Replies

• allan Posts: 61,972Questions: 1Answers: 10,160 Site admin

  April 2023

  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 Posts: 5Questions: 0Answers: 0

  April 2023 edited April 2023

  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 Posts: 61,972Questions: 1Answers: 10,160 Site admin

  April 2023

  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 Posts: 5Questions: 0Answers: 0

  April 2023 edited April 2023

  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 Posts: 61,972Questions: 1Answers: 10,160 Site admin

  April 2023

  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 Posts: 1Questions: 0Answers: 0

  April 2023

  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 Posts: 61,972Questions: 1Answers: 10,160 Site admin

  May 2023

  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