2

this is more of a basic question, trying to clear up my understanding…

usually if I have a simple program such as below, it creates a db connection and then closes it once the script exits

import Database from 'better-sqlite3';
const db = new Database('./path/to/db.sqlite');

function foo(db) {
    return db.prepare('SELECT * FROM table').all(); 
}

console.log(foo(db));

but what actually happens when I am running a nodejs REST server that starts up and then may run for days, weeks, months, without shutting it down? Is a new db connection created every time the user makes a query and then the connection is closed when the results are sent back? For example, using fastify

fastify.get('/', async (request, reply) => {
  return foo(db)
})

would it be better to create a connection when node starts so it is not recreated everytime a user hits the server? Very likely I don't really understand how this works hence my question.

Note, sqlite3 + node: when to close db? is a similar question but not quite what I am asking.

punkish
  • 13,598
  • 26
  • 66
  • 101
  • In TSQL, you create a connection pool that manages the connections for you, opening and closing as needed. I made this mistake and closed the pool every time, which works but starts to... draaaaaag. – somethinghere Jun 01 '23 at 10:34

1 Answers1

0

You need to use a connection pool. This will ensure that there are no connection leaks and manage the available connections properly.

Suggested code:

const { Pool } = require("better-sqlite-pool");

var pool = new Pool("./path/to/db.sqlite");

function foo() {
    pool.acquire().then(db => {
        var retval = db.prepare('SELECT * FROM table').all();
        db.release();
        return retval;
    });
}


fastify.get('/', async (request, reply) => {
  return foo();
})
John Williams
  • 4,252
  • 2
  • 9
  • 18
  • wow, I had no idea about a connection pool. I can't quite understand what it does or why it is required (other than what you write… no connection leaks, etc.). I was more concerned about performance as I don't want to connection closing needlessly after every request – punkish Jun 01 '23 at 18:08