Knex.js & MySQL: Casting Integer into Boolean for bulk select

Question

I use Knex for a server that communicates with a MySQL database.

I have select statements that potentially can return a large number of records from the database. A few of the cells in these records are boolean, which actually means that they are just integers (either 0 or 1). In JavaScript I need them as booleans so I could ship them in JSON as actual 'true' or 'false' values instead of '0' and '1'. So far, the only solution I found was to run the results of the query through a loop changing each tinyint record into a boolean. However, I was wondering, is there a way to configure the query builder to automatically return boolean values for certain cells?

Answer 1

OK. After much search, I found an answer here:

https://github.com/tgriesser/knex/issues/1240.

The trick is to configure the underlined mysql driver that Knex is using with a typeCast property:

var db = require('knex')({
    client: 'mysql',
    connection: {
        host: 'localhost',
        user: 'mysql',
        password: 'mysql',
        database: 'mysql',
        typeCast: function(field, next) {
            if (field.type == 'TINY' && field.length == 1) {
                return (field.string() == '1'); // 1 = true, 0 = false
            } 
            return next();
        }
    }
});

db('my_table')
    .select()
    .then((rows) => {
        //Each row's boolean field is now true/false instead of 1/0
    });

This is not a complete solution because it does not help in cases that the boolean (tinyint) field can be NULL. (I tried to add this option to the casting function, but it not only messed up the results on the tinyint fields, it also messed up other cells for some reason). So, if anybody knows how to account for the NULL case with boolean values, it will be great. But in any case, this is a fairly good solution.

EDIT

This functions returns null when the database field is null.

(field, next) => {
    console.log('TypeCasting', field.type, field.length);
    if (field.type == 'TINY' && field.length == 1) {
        let value = field.string();
        return value ? (value == '1') : null;
    }
    return next();
}