How do I access the underlying better-sqlite3 from a knex.js connection?

Question

I'm wondering how I might get ahold of the better-sqlite3 connection that knex is using so that I can use better-sqlite3 methods directly on a knex connection. Specifically I'd like to use better-sqlite3's backup method on the same connection as knex is using. The backup method will gracefully include any mutations to the database while it's backing up, but only if the mutations are being made on the same connection as the backup. So if I go the simple route of just opening a better-sqlite3 connection to do the backup, I'd also have to make sure my knex connection is shut down while the backup is ongoing, which isn't ideal.

I've been playing around with the knex connection object a bit to see if the better-sqlite3 connection is easily accessible and I haven't seen it obviously available.

Answer 1

I figured a way to do it, not sure if it's the best/only way but I'll leave my solution here for posterity.

When you create a new knex instance one of the config options you can set is pool, within pool you can set an afterCreate function. That function is passed the raw connection to the database. So my solution is to create the connection and then from the afterCreate function assign the connection to a variable that then gets used for any better-sqlite3 specific method calls. A simple version looks like this:

let betterSqlite3;

const db = knex({
  client: "better-sqlite3",
  connection: {
    filename: "mydatabase.db",
  },
  pool: {
    afterCreate: function (connection, done) {
      betterSqlite3 = connection;
      done();
    },
  },
});
db.client.acquireConnection(); // this adds a connection to the pool triggering the afterCreate function.

Knex default size for sqlite3 pools is min 1 and max 1 meaning that the connection added to the pool won't timeout. So from here on out you can use db for knex operations and betterSqlite3 for direct better-sqlite3 method calls. As this example stand, if you log betterSqlite3 on the last line it'll still be undefined because acquireConnection() and the callback function it triggers aren't synchronous. You could do something along these lines to make sure you have a reference to the connection:

const makeConnections = () => {
  return new Promise((res, rej) => {
    const db = knex({
      client: "better-sqlite3",
      useNullAsDefault: true,
      connection: {
        filename: "mydatabase.db",
      },
      pool: {
        afterCreate: function (connection, done) {
          res([connection, db]);
          done();
        },
      },
    });

    db.client.acquireConnection(); // this adds a connection to the pool triggering the afterCreate function.
  });
};

const getConnections = async () => {
  const [sqlite3Conn, knexConn] = await makeConnections();
};

getConnections();

Also note that if you're doing this in Node you must manually close the Knex connection when you're done with it or Node will hang. https://knexjs.org/faq/recipes.html#node-instance-doesn-t-stop-after-using-knex