Always on db or open/close for each query

[hyperknot]

What do you think about the following pattern?

async function runQuery(query: string) {
  db = await sqlite3.open_v2('test')

  const results = []
  for await (const stmt of sqlite3.statements(db, query)) {
    const columns = sqlite3.column_names(stmt)

    const rows = []
    while ((await sqlite3.step(stmt)) === SQLite.SQLITE_ROW) {
      rows.push(sqlite3.row(stmt))
    }
    results.push({ columns, rows })
  }

  await sqlite3.close(db)
  return { results }
}

I mean, I'd wrap each query in an open/close call, so 99.9% of the time the db would be closed. As an alternative to having a single db open at app init time and then keeping it on until the browser tab is closed.

I'm using OPFSCoopSyncVFS. Would this possibly avoid possible corruptions with multi-tab usage? Or with OPFSCoopSyncVFS, corruptions are not really a possibility?

I run the full benchmark and it only became 2 sec vs. 2.2 sec in total, so not a big difference.

[rhashimoto]

Or with OPFSCoopSyncVFS, corruptions are not really a possibility?

On principle, I would never say this! The strongest statement you'll ever get is that I'm not aware of any corruption bug in OPFSCoopSyncVFS.

Actually, I think that in the absence of known bugs, opening and closing the database on every transaction is more likely to trigger unknown bugs because there is so much less testing and usage of contention during open/close. Offhand I can't remember whether I test that at all. The chance of there being an open/close contention bug is probably significantly higher than a transaction contention bug, and you're not really avoiding transaction contention anyway.

[hyperknot]

Thank you! I'll just keep it open then.

[hyperknot]

Do I need to close the connection before closing the browser tab? I don't know how to detect it though. I looked for rg 'sqlite3.close' and found it only in the following examples.

contention/contention-worker.js
file/verifier.js
write-hint/worker.js

[rhashimoto]

It's good practice, but rarely matters as by design SQLite must deal with aborted connections.

[hyperknot]

Thanks!