Better-sqlite3: Better-SQLite-Pool

[Custardcs]

This is more of a question for you Josh.

Why would you use pool over regular transaction insert?

[JoshuaWise]

I personally wouldn't use better-sqlite-pool. It's possible there's something I'm missing, but it seems like an anti-pattern. The best way to use better-sqlite3 in a single-threaded environment such as Node.js is to only use a single connection for the entire application, and to only run transactions synchronously (within a single event-loop tick).

Using multiple connections could enable asynchronous transactions, but that may result in serious lockups.

[Custardcs]

so there is a reason why im asking.. at the moment I have a file called db_Conn.js

looks like

  //sqlite
  let Sqlite = require('better-sqlite3');
  //path
  var path = require("path");
  var fs = require("fs");
  //OS
  let os = require('os');
  console.log(os.platform());
  let DOCUMENTS = 'My Documents';
  if(os.platform() == "darwin"){
          DOCUMENTS = "Documents"
  }
  //DB PATH
  var HomeDir = os.homedir();
  var db_path = path.join(HomeDir, DOCUMENTS);

  //create directory
  try {
      fs.mkdirSync(path.join(db_path, "db"));
  } catch (e) {
     console.warn(e);
  }


  exports.CONNECTION = function (DatabaseName) {
      var db = new Sqlite(path.join(db_path, 'db/' + DatabaseName + '.db3'));
      return db;
  }

then i have actions.js

  const DATABASE_CONNECTIONS = require('./EX_JS_DATABASE_CONNECTIONS.js');
  var db;
  const log = require('electron-log');

  exports.TRANSACTIONS = function (DatabaseName, Query) {
      db = DATABASE_CONNECTIONS.CONNECTION(DatabaseName);
      //trans commit
      let begin = db.prepare('BEGIN');
      let commit = db.prepare('COMMIT');
      let rollback = db.prepare('ROLLBACK');
      try {
          //begin
          begin.run();
          Query.forEach(element => {
              var stmnt = db.prepare(element);
              stmnt.run();
          });

          commit.run();
      } catch (e) {
          rollback.run();
          log.warn(e);
      }
  }

  exports.SELECT = function (DatabaseName, Query) {
      try {
          db = DATABASE_CONNECTIONS.CONNECTION(DatabaseName);
          var Rows = db.prepare(Query).all();
          db.close();
          return Rows;
      } catch (e) {
          log.warn(e);
          db.close();
      }
  }

now if I'm inserting and querying the DB at the same time im worried I face a lock up.

lets say i have a transaction with 20million records to insert.

and a user is logging in and has to run through 1 million users... will i face an issue like this?

[JoshuaWise]

Node.js is single-threaded, so even with multiple connections, every database operation (on any connection) blocks all database operations on other connections within the same process. The best strategy is to make your operations fast. Read operations can be made fast by creating well-written indexes. Write operations can be made fast by using WAL mode. See here and here to understand how things work under the hood when using Node.js and SQLite3.

If your operations are inevitably slow and you can't make them fast, you probably don't want to use SQLite3 at all. However, you might be able to do multiple slow concurrent operations by creating multiple database connections and doing your operations incrementally.

For example, you could run a slow SELECT statement asynchronously like this:

async function select(sql) {
  const iterator = Database('mydata.db').prepare(sql).iterate();
  const rows = [];
  while (true) {
    const entry = iterator.next();
    if (entry.done) break;
    else rows.push(entry.value);
    await new Promise(setImmediate);
  }
  return rows;
}

And you could run a huge number of INSERT statements asynchronously like this:

async function insert(sqlArray) {
  const db = Database('mydata.db');
  db.prepare('BEGIN').run();
  try {
    for (const sql of sqlArray) {
      db.prepare(sql).run();
      await new Promise(setImmediate);
    }
    db.prepare('COMMIT').run();
  } catch (err) {
    if (db.inTransaction) db.prepare('ROLLBACK').run();
    throw err;
  }
}

It's important for the above functions to perform their operations incrementally, by using await new Promise(setImmediate) to allow your program to perform other operations concurrently. I'm not suggesting that running queries asynchronously like this is good practice, but it is possible. If you take this route, then yes, a connection pool could speed things up slightly.

[brandonros]

I'm not suggesting that running queries asynchronously like this is good practice

Are you suggesting that instead blocking the event loop while reading/writing to the database is the better practice?

[JoshuaWise]

Are you suggesting that instead blocking the event loop while reading/writing to the database is the better practice?

I'm suggesting that opening a separate connection for every query will have a huge overhead. If your queries are unavoidably slow, then that is probably the only solution. But in most cases, the better solution is to simply make your queries fast by proper use of indexes. When you get your queries fast enough (microsecond range), the overhead of threading outweighs the tiny amount of time you're blocking the thread. In databases like MySQL and PostgreSQL this is not possible because of network latency, but in SQLite3 it is very possible.

[JoshuaWise]

Closing due to inactivity