Booleans in better-sqlite3

[joshkel]

Environment

Knex version: 1.0.1
Database + version: better-sqlite3 7.4.6
OS: macOS 12.1

Bug

I'm trying out the new better-sqlite3 driver in Knex 1.x (due to the maintenance questions I saw with sqlite3 and @vscode/sqlite3). I ran into a problem within my application's code: Knex's better-sqlite3 driver encodes booleans as string literals ('true' and 'false'), instead of as 0 and 1. This seems like a bug:

• It's incompatible with Knex's sqlite3 driver, so the better-sqlite3 driver can't be used as a drop-in replacement.
• It's inconsistent with SQLIte's documentation.
• 0 and 1 are truthy and falsy in JavaScript, so they can often be used without further processing; both 'true' and 'false' are truthy.

const knex = require('knex');

async function main() {
  for (const client of ['sqlite3', 'better-sqlite3']) {
    console.log('Testing ' + client);

    const db = knex({ client, connection: ':memory:', useNullAsDefault: true });
    await db.schema.createTable('test', (table) => {
      table.boolean('value').notNullable();
    });

    await db('test').insert([{ value: true }, { value: false }]);
    console.log(await db('test').select());

    await db.destroy();
    console.log();
  }
}

main();

[toBeOfUse]

(For future reference, this issue is fixed in PR #4982, it can now be closed at the maintainers' convenience I believe, thanks @joshkel for finding and linking to the exact line of code that had to be changed)

[kibertoad]

Released in 1.0.2