Clarification of the `close` method

[timonson]

Hi @dyedgreen I hope you are doing great! I would like to make sure that I understand the significance of the close method correctly. Maybe you could also add a small server example like the following one to your examples? I am assuming that the close method needs to be called between each request in the following example and would love to see this confirmed. Do I have to create a new DB instance after closing the database, as well (like in the following example)? Thank you!

const hostname = "0.0.0.0";
const port = 8080;
const listener = Deno.listen({ hostname, port });
console.log(`Listening on ${hostname}:${port}`);
for await (const conn of listener) {
  const db = new DB("test.db");
  db.query("CREATE TABLE IF NOT EXISTS people (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT)");
  db.query("INSERT INTO people (name) VALUES (?)", ["someName"]);
  db.close();
  Deno.copy(conn, conn);
}

[dyedgreen]

Hi @timonson. That's a great question! What you are doing will work, but you can get much better performance by reusing the database handle.

Generally close is used to free up the file handles for the database file. If you want to access the database after closing it you will need to obtain a new handle by invoking the constructor again.

But if you are always reading to/ from the same database (or a small number of databases), there is no reason to open and close it on every request. Something like the below example will work better:

const hostname = "0.0.0.0";
const port = 8080;
const listener = Deno.listen({ hostname, port });

const db = new DB("test.db");
db.query("CREATE TABLE IF NOT EXISTS people (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT)");

console.log(`Listening on ${hostname}:${port}`);
for await (const conn of listener) {
  db.query("INSERT INTO people (name) VALUES (?)", ["someName"]);
  Deno.copy(conn, conn);
}

db.close();

[dyedgreen]

Regarding documenting this better. We could add an example to the examples section of the docs. Do you think that would have been helpful/ valuable to you in addressing this question? 😃

[timonson]

These explanation and example helped a lot, thank you @dyedgreen! I would definitely add this kind of server example to the documentation because at the very least it removes some doubts.

[authcompanion]

@dyedgreen 👋 Are there any downsides/disadvantages to never using close ? Just was curious what could happen. Use case being a webserver

[dyedgreen]

Generally, it's good practice to close the database when your program shuts down. But if you intend to keep the database open for the entire duration of the program, not closing it should not really change too much, since the OS will reclaim any open file descriptors that might still be around.