putting the js in a worker ?

[jalsco]

Hi there ! and what a great project is wa-sqlite ! 😁😁

I am loving it, and all thanks to @rhashimoto for his work.

Mostly my project (porting a bunch of old access sql tools) is working now on wa-sqlite - and it only took a couple of days of work to get to that point. For me - data persistence, and the ease to get it running are the important parts. I am not a JS coder, I tinker with JS maybe once or twice a year. It was easy enough to get running, and WA-sqlite is plenty fast ! I tried to do it first with absurb-sql they boast lightning speed, but after a few days of configuring, i could never quite get it running.. In contrast wa-sqlite was up and running within first couple attempts, and really did not show signs of trouble (for someone inexperienced/unskilled).

So I am very very grateful to @rhashimoto thx ! 🙏🏻

The next part of the plan - is to convert to PWA... Which I have never done before. But I believe the first step is putting all JS into worker threads. So that is the plan.... My JS is all just interacting with the SQlite db stored in indexedDb.... using wa-sqlite.

Please any advice ? tips ? samples ? examples ? thoughts ? or comments about how I should approach this task. Any comments would be warmly received.

Chat-GPT3 told me that it should be easy 🙄 I prefer to trust humans 🙏🏻😊

Two other unrelated questions - which (further) demonstrate my near-beginner skill-level: 😂

1. My code mostly adds or updates single rows, and later extracts other rows for display/reporting. The SELECT statements usually get back small numbers of rows (usually between 1-20) . My question: should I be doing COMMITs or closing the DB ... ever ? I just do the open in same way as the demo

const db = await sqlite3.open_v2(
      vfs,
      SQLite.SQLITE_OPEN_CREATE | SQLite.SQLITE_OPEN_READWRITE | SQLite.SQLITE_OPEN_URI,
      vfs);

and then never close that db or do anything else about the connection. Is that the usual way of doing it ?

2. What about importing data from existing Sqlite files , or - more importantly - exporting to a file (from the IndexedDB storage) ?? The idb storage is awesome for set-and-forget persistence. But I will start to get a bit nervous as the data in there grows. It seems like it is just one forgetful running of CCleaner and it potentially gets wiped. Some option to save into a file would be great. Or if there are some code snippets of how to do that, that would be wonderful.

[rhashimoto]

My question: should I be doing COMMITs or closing the DB ... ever?

BEGIN...COMMIT defines a transaction in SQL, so whether you use that or not depends on what operations on your database you want to be semantically atomic. That's really a question for your particular application and has nothing to do with closing the database.

Closing any resource you open is good software development practice in general. However, in the typical case of a web app that uses a single database until dismissed, there is no practical drawback for leaving it open.

Some option to save into a file would be great. Or if there are some code snippets of how to do that, that would be wonderful.

There is currently no example code in this repo for this and I have no plans to add it. I would probably approach it by instantiating a VFS and calling its methods - xOpen, xLock, xFileSize, xSectorSize, xRead, xUnlock, xClose - to read data in sector sized chunks...but that requires an understanding of both the SQLite VFS interface and how wa-sqlite adapts it to Javascript. A less general but probably easier way to do it would be to copy the code that opens the IndexedDB database and then read the blocks directly with the IndexedDB API.

Using wa-sqlite in a Worker is no different than using it in a Window, except that you will need to bundle your Javascript because not all browsers support ES modules in Workers. There is a FAQ item about that.

[jalsco]

Hi @rhashimoto and thanks for your reply ! 🙏🏻

Noted about BEGIN ... COMMIT - I will keep it in mind as I update all the code (that is now working) 💪

I came to same conclusion about close db , as web users are a skittish bunch and can be expected to just abandon a tab or nuke it without warning so, in practice code that explicitly "closes" is probably never/rarely/on-ocassion-only going to get run. For now, my code will stay as is and I will point to your words if ever I am asked "there is no practical drawback for leaving it open" 😊

About saving to an sqlite file - I read closely (and googled) the 2 approaches that you described. I think both are beyond my current skill level. But if anyone else writes some code there - I will eagerly test it for them 😁 There is an easier approach - I know it would be far less of a complete solution - and that is table by table, with the goal to just save as a CSV. That CSV then can be easily imported anywhere, without code, so data is not lost. As long as can just get it showing in the browser as a RAW csv file, can (manually) do a save-page-as. Even that is just out of reach of my current skills, but I will try to do it one weekend in the future when I am stuck in the house by rains and flood (unless someone else does it first).

"Using wa-sqlite in a Worker is no different than using it in a Window, except that you will need to bundle your Javascript because not all browsers support ES modules in Workers. There is a #4. "

That is GREAT NEWS ! ❤ thanks again 🙏🏻