Better-sqlite3: Temporary Databases?

[mramato]

First, thanks for an awesome project. It's so much better than the other Node-based options I've tried.

I would like to create a temporary databases as defined by sqlite here: https://www.sqlite.org/inmemorydb.html.

This is separate from the memory option because the database can grow outside the bounds of memory and will be written to a temporary file that is deleted on disconnect.

Unfortunately, better-sqlite3 won't let you create a database with an empty string (which according to sqlite3 is how you create a temporary database). So unless I'm missing something there's no way to use temporary databases. Is there any reason better-sqlite3 can't be updated to support this feature?

[JoshuaWise]

@mramato It's true that supporting this feature would be very easy (I could just remove a simple if-statement). The problem is that SQLite3 databases aren't automatically closed when the node process closes abruptly (due to a thrown error, process.exit(), or SIGINT). People who don't notice this subtlety may experience tremendous unexpected disk usage.

Therefore, I've made the design decision to force users to be explicitly aware of the files they create, so they know to delete them properly when no longer needed.

[mramato]

Perhaps there could be a compromise where a developer has to pass temporary : true option to to the Sqlite3 constructor and also specify an empty string as the name. This lets users who explicitly want this feature opt into it, and it also provides a space in the better-sqlite3 documentation to mention any caveats you are concerned about. (i.e. documentation for the options). What do you think?

[brettz9]

The temporary: true solution sounds like a good compromise to me... I'd like to ensure if we are able to switch to better-sqlite3 (for IndexedDBShim`), that we can still offer the same options as node-sqlite3...

[JoshuaWise]

High-level libraries that wish to wrap better-sqlite3, such as IndexedDBShim, could implement their own temporary database implementation, and arguably make it better than the simple solution provided by SQLite3 itself.

An author could use their own convention for specifying the temp directory (perhaps inspired by the SQLite3's convention), and use uuids for filenames. It might seem like reinventing the wheel, but such an implementation could go the extra mile to ensure that the temporary databases are closed even when the process closes abruptly.

const bsql = require('better-sqlite3');
const uuid = require('uuid/v4');
const path = require('path');
const fs = require('fs');

const tempDir = path.resolve(process.env.TMPDIR || '/tmp', 'indexeddbshim');
const instances = new Set();
const onclose = () => {
  for (const instance of [...instances]) instance.close();
  process.exit();
};

process.on('exit', onclose);
process.on('SIGTERM', onclose);
process.on('SIGINT', onclose);
process.on('SIGHUP', onclose);

class IndexedDBShim {
  constructor() {
    this.db = bsql(path.join(tempDir, uuid()));
    instances.add(this);
  }
  close() {
    this.db.close();
    fs.unlinkSync(this.db.name);
    instances.delete(this);
  }
}

I just don't like the idea of resources being leaked unintentionally. I personally think a solution like the one depicted above would be better, but if this issue gains a lot of attention I could be swayed.

[mramato]

@JoshuaWise It's possible I may be missing something, but I don't think that's how the temporary databases feature in SQLite3 works. From the link in my original description:

Even though a disk file is allocated for each temporary database, in practice the temporary database usually resides in the in-memory pager cache and hence is very little difference between a pure in-memory database created by ":memory:" and a temporary database created by an empty filename. The sole difference is that a ":memory:" database must remain in memory at all times whereas parts of a temporary database might be flushed to disk if database becomes large or if SQLite comes under memory pressure.

So the use case here is:

1. You need a temporary database
2. The database might fit into memory, or it might not, you don't know ahead of time.
3. You want the speed of a :memory: database if it does fit, but are okay if some parts have to go to disk because it gets too big.

Your proposed solution seems no different then deleting a disk-based database after you are done with it, which has would have performance overhead over the SQLite3 temporary database and does not work as the SQLite3 documentation describes.

[JoshuaWise]

@mramato If it's performance you're after, then you could utilize:

db.pragma('main.journal_mode = MEMORY');
db.pragma('main.synchronous = OFF');
db.pragma('main.mmap_size = 2147483647');

With the above pragma, I suspect you'll get performance characteristics very similar to a true temporary database. The other advantage here is that you can limit how much memory is used for containing the database (mmap_size), instead of just using up all of your memory blindly.

[mramato]

It's not just about performance, it's about using a feature as implemented by the sqlite3 team. I would expect an API wrapper to expose functionality, not change it. As maintainer, you obviously have final say in the project, but I don't understand why you wouldn't just expose the feature as-is since several of your users seem interested in it.

A workaround I found is to use a :memory: database and then attach to a new temporary database:

const db = new Sqlite3(':memory:');
db.exec('ATTACH DATABASE "" AS t');

Of course this means you need to use t. to reference the temporary DB when creating it, but I believe all other behavior is exactly the same as if better-sqlite3 exposed the underlying feature.

[JoshuaWise]

@mramato You do have some good points. I'll add the feature in a minor version coming soon.

[JoshuaWise]

Temporary databases are now supported in v5.3.0 by providing an empty filename.

I haven't added it to the documentation yet, because I plan on doing that in the next major version, when I remove support for named in-memory databases.

Originally, I thought named in-memory databases were simply more powerful than anonymous in-memory databases, because they allow you to open multiple connections to the same in-memory database. However, in retrospect, opening multiple connections within the same process to the same database in SQLite3 is virtually always an anti-pattern. In the next major version, I'll remove support for options.memory when creating a new database, and document that the proper way to open an in-memory database is to use a filename of either ":memory:" or "".

[mramato]

@JoshuaWise Thanks for this and thanks again for an awesome library!