SQLite3 + Node.js - Should I open and close the database for each INSERT or keep it always open?

Question

I have a Node.js process monitoring an application, writing to the database when a certain action happens. There is a frontend for this database, which causes concurrency errors when both are trying to access the data. The internal processes dont matter too much, but theres a write to the database about 3 times a second (which I believe to be considered still quite slow). Here is my INSERT statement:

db.run(query, data, function(e)
{
    if (e)
    {
        console.log("Could not insert a Sync!")
        console.log(e.message)
    }
});

My question is, should I open and close the database for each insert:

db = new sql.Database('../db', sql.OPEN_READWRITE, (e) =>
{
    if (e)
    {
        console.log("Could not connect to the DB!")
        console.log(e.message)
    }
})

db.run(query, data, function(e)
{
    if (e)
    {
        console.log("Could not insert a Sync!")
        console.log(e.message)
    }
});

db.close()

Or open at the start of the process and just leave the db object open? Im hoping to reduce the amount of concurrency errors, WAL option I am looking into. Thanks

Answer 1

Constantly opening and closing the db connection isn't going to help your concurrency problem and isn't very efficient. Leave it open.

WAL mode lets you have one writer and multiple readers at the same time. Other journal modes let you have multiple active readers at the same time with no writers, or one writer and no readers until the writer is finished. So if the other application using the database is only a reader, WAL is probably going to be what you want.

Anything that tries to read or write the database can be written so if it gets a locking error, it tries again after a brief pause before eventually giving up after a few failed attempts (The C api has a way to do this automatically, not sure about Node, but there's always the busy_timeout pragma).

You should also make sure that any prepared statements you execute are reset or finalized when you're done with them so they release locks and allow other queries to run.

If it's still a problem after all that, you might need a different database.