Neat project! Curious about the origin story.

[zeluspudding]

If all goes well, I think this module will prove extremely useful for electron based projects. I'm currently developing a VS Code extension and find myself needing something like this since the installation of better-sqlite3 presents a lot of problems for my end users.

@tndrle, can you share a few details as to your motivation to put this module together? The problems you're solving - to make a binary-less sqlite node module with persistent storage support - are clear to me. I'm curious, though - have you created this in service of another project you're working on? Can you share details about that or any plans / timelines to bring this to 1.0? Just trying to gauge your appetite for maintaining this as of today. An answer of "No plans to bring it to 1.0" is of course acceptable.. just want to know if that's the case.

If I can get this working in my existing project, then I would like to make a donation to you. Will try getting it setup today / tomorrow.

[zeluspudding]

Following up - I was able to get this integrated into my project. Two small notes of feedback:

• Your docs are really good. Thank you for taking the time to document!

• I should have assumed all of your Database's methods were async, but coming from better-sqlite3 and judging by your readme's lack of await / promise management, I wasn't sure and ran into several i/o errors resulting from treating things synchronously. Adding a sentence like:

  We handle all database operations asynchronously.

  would make your already excellent docs that much clearer / easier to get started with.

Thanks again for creating this - it's very useful. Please let me know of a way to support this project - would love to send some money your way.

[tndrle]

Thanks a lot for your positive feedback. This module is a side product of a private Electron-based project of mine (a tool to manage a school's books). Originally, I used better-sqlite3 but I often found myself waiting for the newest pre-built binaries. At some point, I decided to do my own SQLite module. This is the story, basically.
The functionality and performance are sufficient for my use case. At the moment, I don't have plans to do any major extensions or optimizations. I have set up GitHub Actions to keep the module up to date with the most recent versions of emscripten and SQLite. Also, I do bug fixes. What would you expect from a version 1.0?

Thank you very much for your offer to donate. If PayPal is fine, you can use this link: https://www.paypal.com/donate/?hosted_button_id=9GPTJ4UZK9F92

Concerning your second note: The module is not asynchronous. It should work fine without await and the like. Where did you run into problems?

[zeluspudding]

Lol, makes sense these methods are indeed synchronous! After you confirmed this, I refactored a few things to remove awaits since a few more tests showed that having them did not solve my issues after all. The issue I'm running into is in the following function that accepts a list of 40 objects (called plugins) that I want to upsert into sqlite. Looping through the list, I clean up each object and then call db.run to insert it:

const upsert_plugins = async (plugins) => {
  for (var plugin of plugins) {
    let column_names = get_list_intersection(Object.keys(plugin), Object.keys(plugin_table_schema))
    let filtered_plugin = {};
    for (const column_name of column_names) {
      const value = plugin[column_name]
      if (typeof value === 'boolean') {
        plugin[column_name] = (value == true) ? 1 : 0
      } else if (Array.isArray(value) || typeof value === 'object') {
        plugin[column_name] = JSON.stringify(value);
      }
      filtered_plugin[`@${column_name}`] = plugin[column_name];
    }
    var sql = `
    INSERT INTO plugin (
      ${column_names.map((column_name) => `${column_name}`).join(', ')}
    ) VALUES (
      ${column_names.map((column_name) => `@${column_name}`).join(', ')}
    )
    ON CONFLICT(_id) DO UPDATE SET
    ${column_names.map((column_name) => `${column_name} = excluded.${column_name}`).join(', ')}
    ;`
    db.run(sql, filtered_plugin);
  }
}

The problem is that it works without a hitch 60ish% of the time and then just doesn't. I've rerun said function maybe 50 times and can get a mostly regular pattern of the whole thing working for 3 to 5 runs, then failing after having iterated through with 8 to 14 plugins. The failure being SQLite3Error: disk I/O error at the db.run(sql,filtered_plugin) command. Thing is I change nothing between runs... I simply rerun and then hit or miss on said errors.

So this has me thinking I'm randomly crashing up against Sqlite's internal locking mechanism... but that doesn't explain why I occasionally also get an error when simply re-initializing my database table with the following command:

function initialize(file_path) {
  fs.mkdirSync(path.dirname(file_path), { recursive: true }, (error) => {
    if (error) {
      console.error('Error creating subdirectory:', error);
    }
  });
  db = new Database(file_path)
  db.exec(`
      CREATE TABLE IF NOT EXISTS plugin (
      ${Object.entries(plugin_table_schema).map(([colName, colType]) => `${colName} ${colType}`).join(', ')}
      );
    `);
}

The full error I get is:

     SQLite3Error: disk I/O error
        at Database._handleError (c:\Users\Artemis\Documents\Businesses\Scious\projects\bask\node_modules\node-sqlite3-wasm\dist\node-sqlite3-wasm.js:8:8902)
        at Database.exec (c:\Users\Artemis\Documents\Businesses\Scious\projects\bask\node_modules\node-sqlite3-wasm\dist\node-sqlite3-wasm.js:8:8190)
        at Object.initialize (c:\Users\Artemis\Documents\Businesses\Scious\projects\bask\src\database.js:56:6)
        at Context.<anonymous> (c:\Users\Artemis\Documents\Businesses\Scious\projects\bask\test\suite\bask_pull.test.js:25:14)

Have any tips for debugging these issues? Thank you in advance :)

Also, I did send some $$ via that Paypall link. Thank you again for your work on this!!

[tndrle]

I received your donation. Thank you so much.

[zeluspudding]

As for your question on what I'd expect out of a v1.0, I suppose I don't really need anything in particular outside of just knowing there would be stability and non-breaking changes across certain versions. But as long this project follows common versioning standards, the question of which versions are backward compatible should be obvious to myself and others :)

[zeluspudding]

Small update (I apologize - didn't mean for this discussion to become an issue post) I've added a 50 millisecond delay between each loop of the upsert_plugins function defined above and that seems to have eliminated the frequent I/O issues I seeing. Back when I was using better-sqlite3 I didn't experience any locking issues (using this same code) but I also know I was using sqlite via the db.pragma('journal_mode = WAL') mode which I believe sped up db reads/writes. Does node-sqlite3-wasm have or plan to add an equivalent method for setting journal_mode?

At this point, the built in delays have solved the errors I got while upserting plugins in a loop but I still occasionally (and in currently no predictable way) get SQLite3Error: disk I/O errors when initializing my db with:

  db.exec(`
      CREATE TABLE IF NOT EXISTS plugin (
      ${Object.entries(plugin_table_schema).map(([colName, colType]) => `${colName} ${colType}`).join(', ')}
      );
    `);

Any ideas how I can better debug this bit? Thank you again!

[tndrle]

I haven't experienced disk I/O errors before. I don't know if this is related to SQLite's locking but I wouldn't expect it (just a feeling). Do you have any anti-virus software active? I read that there can be problems with concurrent file access. SQLite's Windows VFS does some special handling iirc.

Concerning the pragma: You should be able to set pragmas with db.run('PRAGMA journal_mode = WAL')
According to https://www.sqlite.org/wal.html#noshm, this should work for WAL:

db.run("PRAGMA locking_mode = EXCLUSIVE");
db.run("PRAGMA journal_mode = WAL");

[zeluspudding]

Thank you so much for sharing that tip! I think that was my problem all along. Initializing my database in WAL mode seems to have solved all my problems. Thank you again!

function initialize(file_path) {
  fs.mkdirSync(path.dirname(file_path), { recursive: true }, (error) => {
    if (error) {
      console.error('Error creating subdirectory:', error);
    }
  });
  db = new Database(file_path)
  db.run("PRAGMA locking_mode = EXCLUSIVE");
  db.run("PRAGMA journal_mode = WAL");
  db.exec(`
      CREATE TABLE IF NOT EXISTS plugin (
      ${Object.entries(plugin_table_schema).map(([colName, colType]) => `${colName} ${colType}`).join(', ')}
      );
    `);
}

[tndrle]

Good to hear that. Let me know if the problems appear again or new ones come up