Better-sqlite3: Question: how do I do begin Transaction & end Transaction ?

[qiulang]

Hi,
I used to the idiom that put all my insert inside a begin Transaction & end Transaction block.
But how do I do that with better-sqlite, checking your document I only see
.transaction(function) -> function

But that won't work for my case, e.g I need to scan n files, each file with a bunch of insert clauses,

begin Transaction
scan files -> each file, bulk insert
end Transaction

How do I do that with better-sqlite ?

[qiulang]

OK I find I can just calldb.exec('BEGIN TRANSACTION') but that leads to my second question when using this vs .transaction(function) -> function

[JoshuaWise]

@qiulang I would recommend scanning the input files asynchronously before performing your transaction.

const { promises: fs } = require('fs'); // requires nodejs 10
const db = require('better-sqlite3')('my-data.db');

const execMulti = db.transaction((strings) => {
  for (const str of strings) db.exec(str);
});

async function readAndExecuteFiles(filenames) {
  const files = await Promise.all(filenames.map(name => fs.readFile(name, 'utf8')));
  execMulti(files);
}

There's no point in opening a transaction before you've fetched all the data (files) necessary to perform the transaction.

[qiulang]

Thanks for the reply.
I have about 4000 files to scan, so to read them all before db.transaction is unrealistic. I know my way is not good enough but I can't came up with a better one.

Of course I can come up with a "bulk" read, say 100 files each time. But I am not sure if it is worth the trouble. lol

[JoshuaWise]

@qiulang if your program is just a script (i.e., it's not doing other things in parallel with the task you describe), then there's nothing wrong with just managing the transaction manually, as you described in your second post.

async function readAndExecuteFiles(filenames) {
  db.exec('BEGIN');
  try {
    for (const name of filenames) db.exec(await fs.readFile(name, 'utf8'));
    db.exec('COMMIT');
  } catch (err) {
    if (db.inTransaction) db.exec('ROLLBACK');
    throw err;
  }
}

Keeping a SQLite3 transaction open across an asynchronous operation is generally discouraged, because it prevents other parts of the program from using the database during the operation. However, for scripts that are designed to only do one thing at a time, that doesn't actually matter. It matters, for example, in a web server.

[JoshuaWise]

@qiulang has your question been answered?

[qiulang]

Oh yes I would also like to tell you that I did a little performance test for db.transaction vs one transaction.

So I used a "bulk" read (try 100 files, 200files, 400 files each many time) for my total 4000 files,
then test for one db.transaction for one bulk read (like your codes showed) vs just one transaction.
In my test one transaction is always much faster, but of course that is predictable.

B/C for my program, during the import process there should be no other part access DB so I go with one transaction.

Thank you for your detailed reply!