Knex migration, Bookshelf ORM, PostgreSQL and Boolean

Adding a boolean column to PostgreSQL database with Knex migration and using it with bookshelf ORM.

I needed to add a boolean-value column to a PostgreSQL database table using a Knex migration, and then use this new column with Bookshelf.js ORM.

I tried simply adding the new column by simple adding it with table like this:

table.boolean(‘is_template’);

However that would turn out to be complicated when using the column with Bookshelf since the old rows in the table would have NULL in the database. Where we would need to check for NULL, FALSE and TRUE.

To solve this you can use notNullable and defaultTo.
It will make the column not nullable, and if no value is set for the column a default will be used, and in this case it is false:

table.boolean(‘is_template’).notNullable().defaultTo(false);

Now you will only have false values for old rows for the newly added column. This will allow us to only look for FALSE or TRUE values. Instead of NULL, FALSE and TRUE values when querying the table via Bookshelf:

.where({id: Id, is_template: ‘FALSE’})

Oh, and above is an example of how to use multiple where clauses with Bookshelf.js