Is asynchronous processing supported? In Electron.

[ryusei-48]

Hello. I would like to ask another question.
I am currently using Electron to create a desktop application.
I am using "better-sqlite3-multiple-ciphers" built into the database.
I really like this module, but when I run a large number of queries at once, the renderer process freezes. Of course, once the process is finished, it comes out of the freeze state. However, this behavior seems to indicate that the main process is temporarily occupied and not accepting any other processing.

Is this module itself all synchronous processing?

Here is an example of how the problem occurs

renderer process ( TypeScript )

window.electron.ipcRenderer.invoke('bookmark-file', {
  instanceId: CleePIX.currentInstanceId, html: reader.result
}).then( async (res) => {
  if ( res === true ) {
    await window.electron.ipcRenderer.invoke('set-tag-tree-cache', null);
    window.location.reload();
  }
});

main process ( TypeScript )

ipcMain.handle('bookmark-file', async (_, dataString) => {
  const bookmarks = parseByString(dataString.html);
  let results: boolean = false;
  if ( bookmarks.length > 0 ) {
    const importBookmarks = ( bookmarks: IBaseMark[], parentTagId: number | bigint = 0 ) => {
      bookmarks.forEach( item => {
        try {
          if ( item.type === 'folder' ) {
            let tagId: number | bigint = 0;
            const selectedTag = selectTagsTable.get( item.name );
            if ( selectedTag !== undefined && parentTagId != selectedTag.id ) {
              const selectedTagStructure = selectTagsStructureTable.get( parentTagId, selectedTag.id );
              tagId = selectedTag.id;
              if ( selectedTagStructure === undefined ) {
                insertTagStructureTable.run( parentTagId, selectedTag.id );
              }
            }else if ( selectedTag === undefined ) {
              const insertedTag = insertTagTable.run( item.name );
              tagId = insertedTag.lastInsertRowid;
              if ( insertedTag.changes === 1 ) {
                insertTagStructureTable.run( parentTagId, insertedTag.lastInsertRowid );
              }
            }
            if ( item.children.length > 0 ) {
              importBookmarks( item.children, tagId );
            }
          }else if ( item.type === 'site' ) {
            const selectedBookmark = selectBookmarksTable.get( item.href );
            if ( selectedBookmark !== undefined ) {
              const selectedTagBookmark = selectBookmarkTagsTable.get( parentTagId, selectedBookmark.id );
              if ( selectedTagBookmark === undefined ) {
                insertBookmarkTagsTable.run( parentTagId, selectedBookmark.id );
              }
            }else {
              let pageType: string = "general";
              if ( item.href.match(/^https:\/\/www\.youtube\.com\/watch\?v=/) ) {
                pageType = "youtube";
              }
              const insertedBookmark = insertBookmarkTabale.run( item.name, item.href, pageType );
              if ( insertedBookmark.changes === 1 ) {
                insertBookmarkTagsTable.run( parentTagId, insertedBookmark.lastInsertRowid );
              }
            }
          }
          results = true;
        }catch ( e ) { console.log(e); results = false; }
      });
    }
    const selectTagsTable = this.storage[ dataString.instanceId ].db!
        .prepare(`SELECT * FROM tags WHERE name = ?`);
    const insertTagTable = this.storage[ dataString.instanceId ].db!
        .prepare(`INSERT INTO tags ( name ) VALUES ( ? )`);
    const selectTagsStructureTable = this.storage[ dataString.instanceId ].db!
        .prepare(`SELECT * FROM tags_structure WHERE parent_id = ? AND child_id = ?`);
    const insertTagStructureTable = this.storage[ dataString.instanceId ].db!
        .prepare(`INSERT INTO tags_structure ( parent_id, child_id ) VALUES ( ?, ? )`);
    const selectBookmarksTable = this.storage[ dataString.instanceId ].db!
        .prepare(`SELECT * FROM bookmarks WHERE url = ?`);
    const insertBookmarkTabale = this.storage[ dataString.instanceId ].db!
        .prepare(`INSERT INTO bookmarks ( title, url, type ) VALUES ( ?, ?, ? )`);
    const selectBookmarkTagsTable = this.storage[ dataString.instanceId ].db!
        .prepare(`SELECT * FROM tags_bookmarks WHERE tags_id = ? AND bookmark_id = ?`);
    const insertBookmarkTagsTable = this.storage[ dataString.instanceId ].db!
        .prepare(`INSERT INTO tags_bookmarks ( tags_id, bookmark_id ) VALUES ( ?, ? )`);
    importBookmarks( bookmarks );
  }
  return results;
});

Broad description of this program

The browser's bookmark data is exported as an html file, which is read in the renderer process, and the bookmarks and folders are registered one by one in the main process.

issue

Because of the large number of bookmarks and folders to be registered in the database, it took about 10 seconds for registration to finish on mine. During that time, however, the renderer process is blocked and will not accept any processing. The main process is occupied with processing the database.

Is there any way to work around this?

Thank you very much.

[ryusei-48]

I thought about using "worker_thread" to see if there was an alternative, but it was impossible as mentioned in the following issues.
WiseLibs/better-sqlite3#237

[ryusei-48]

It resolved itself a while ago.
Thank you very much.
I may ask for help again if I encounter any problems and struggle with it.

Below is the literature that led me to the solution.

◇ Electron-Vite Worker Thread
https://evite.netlify.app/guide/worker.html
◇ better-sqlite3 Worker threads
https://github.com/WiseLibs/better-sqlite3/blob/master/docs/threads.md

[m4heshd]

Sorry for the late response. I was on vacation.

◇ better-sqlite3 Worker threads
https://github.com/WiseLibs/better-sqlite3/blob/master/docs/threads.md

I was the one who wrote that piece of code. 😄 (Well, made the one previously existed work)

This extension works flawlessly with worker threads. But not in the renderer process. Just make sure to keep them out of the asar when packaging with Electron and make sure you're using WAL journal mode. That's it.

[ryusei-48]

I see you were on vacation. Thank you for your reply. Currently, it is working perfectly in the main thread of Electron. However, only the heavy processing is flowing to threads, so there is a possibility of locking up when manipulating the database in the main thread. I will consider whether to move all database processing to threads.

[m4heshd]

The threading depends heavily on the CPU you're running the application on. That particular mechanism is designed to create a worker thread for each thread on the CPU. As long as you're using WAL, it should work fine except for edge cases. I'm not gonna give any official statements here but running massive asynchronous data transactions on SQLite is not recommended. By massive, I mean MASSIVE.

[ryusei-48]

Okay, then I would like to continue with the current implementation method. Where I used threaded processing this time is where I need to execute a large number of SELECT and INSERT statements at once. We first tried synchronous processing, but found that db.prepare('...') but found that the renderer blocked processing for a few seconds, so I reconsidered using asynchronous processing. This is fine if I am the only one who uses this desktop app, but since it is for public release, it may seem impenetrable to the user.

I also tried another asynchronous module before I had perfect success with threading in better-sqlite3-multiple-ciphers. This appears to be a fork of this module.
https://github.com/mscdex/esqlite
But in the end, coming back to better-sqlite3-multiple-ciphers seems to be the right answer. I will continue to use better-sqlite3-multiple-ciphers as the threading process is working perfectly.