Why am I unable to set false (0) as a default value for my table using Knex?

Question

I have been using Knex with node/express to build my database and I have run into an issue where when I set the default value for a boolean, it always returns null. (using sqlite)

exports.up = async function(knex) {
    await knex.schema.createTable("users", (table) => {
        table.increments()
        table.boolean("admin")
            .notNullable()
            .defaultTo(false) // this is throwing an error.  Not defaulting to false.
    })
};

I have also tried using 0 instead of false because I know it typically defaults to 1's and 0's for true and false, but it still is returning null no matter if I put true/false || 1/0.

Does anybody see an issue as to why this defaults to null and not true or false?

Answer 1

SQlite driver does not support automatically converting javascript boolean -> 0/1. It does not convert batabase-boolean -> js true/false either, because sqlite does not have boolean datatype at all, but it uses integer instead of it.

https://www.sqlite.org/datatype3.html

table.boolean("admin")
            .notNullable()
            .defaultTo(0)

should work, but if you are going to use sqlite as your main database, you might actually consider using integer type instead of boolean.