prepared statement .get(...) leaves database locked while .all(...) works fine

[felixniemeyer]

Issue Summary

I have 2 processes, one stores stuff on the sqlite db the other reads. I'm not using wal and I think I don't need to because writes are very infrequent and the db is not very busy overall.

One process that reads stuff is an node express web server. It uses node-sqlite3 to access the db.

It has one api-function that returns a resource by its primary key. So I really want zero rows or one. That's why I thought "get" is the right method for me.

The problem is, after using get, the database remains in a locked state such that the other process that occasionally writes to the db is being blocked from doing so.

When I'm using .all instead of .get the database is accessible fine after the request.

Steps to Reproduce

I'm using a prepared statement for getting the resource by it's primary key.

  getResourceQuery = db.prepare(queries.resourceBase + " AND b.resource_name == ?")

(queries.resourceBase is quite complex, I hope it does not play a role in the observed misbehavior here)

The following lines using .get leave the db locked.

  app.get('/resources/:resourceName', (req, res) => {
    console.log("looking up resource", req.params.resourceName)
    getResourceQuery.get(req.params.resourceName, (err, row) => {
      if(err == null) {
        console.log("result from query", row) 
        res.send(row) 
      } else {
        res.send("Error: " + err)
      }
    })
  })

The following lines using .all works fine, meaning that after the request the database is unlocked eventually.

  app.get('/resources/:resourceName', (req, res) => {
    console.log("looking up resource", req.params.resourceName)
    getResourceQuery.all(req.params.resourceName, (err, rows) => {
      if(err == null) {
        console.log("result from query", rows[0])
        res.send(rows[0]) 
      } else {
        res.send("Error: " + err)
      }
    })
  })

For what it's worth, I setup the db connection like this:

(async () => {
  await new Promise<void>((resolve, reject) => {
    console.log("trying to connect to", process.env.SQLITE_DB)
    db = new sqlite3.Database(process.env.SQLITE_DB!, err => { //sqlite3.OPEN_READONLY,
      if(err) {
        reject("could not connect to the db") 
      } else {
        prepareStatements()
        resolve()
      }
    })
    db.configure('busyTimeout', 30000)
  })
  runServer() // sets up express web server
})()

I have a deadline in a few days, so I will go with .all for now and can't spend much time on creating a reproducible minimal example here. But please remind me to do so, if we can't find a solution otherwise already.

Version

version "5.1.2"

Node.js Version

v16.16.0

How did you install the library?

yarn add -D sqlite3 # i think