sqlite3 + node: when to close db?

Question

I'm using better-sqlite3 on Node, but I suspect my questions are applicable to node-sqlite3 as well.

I basically have 2 simple questions, relating to a server-rendered website:

• Do I need to explicitly call .close() on the database? I seem to remember reading somewhere that it will automatically close when the current scope (like the current function) exits. What if I never call .close() in a web server scenario, taking a lot of requests?

• If you have a bunch of different components (authentication, authorisation, localisation, payment, etc...) and each component may or may not need to access the database throughout the lifetime of a request (which are quite short-lived, except for payment), is it better to

  1. have one db connection for the lifetime of the server and pass that around
  2. have one db connection for the lifetime of the request and pass that around
  3. open a new connection every time I need something, maybe 2-3 times per request (and close it either explicitly or implicitly when the function returns, if that's a thing)

Thank you

Otter

Answer 1

Joshua Wise's (better-sqlite3's creator) answer over on GitHub:

---

Database connections are automatically closed when they are garbage collected, which is non-deterministic. If you want to know that the connection is closed (rather than guessing), you should call .close().

You can just open one database connection for the entire thread (the entire process if you're not using worker threads), and share that connection between every request. Node.js is single-threaded, so you don't have to worry about simultaneous access, even if multiple requests are being handled concurrently. The one caveat is that you should never have a SQLite transaction open across multiple ticks of the event loop (i.e., don't use await between BEGIN and COMMIT), because then other requests could accidentally inject SQL into your transactions. Also, SQLite transactions are serialized (you can't have more than one at a time), so you should open and close them as quickly as possible; keeping them open across ticks of the event loop is bad for performance.

Otter

It means when the program ends it is closed automatically, right? – Trixy

As per my understanding, yes. – Parvis