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?

Community · Accepted Answer · 2020-06-20 09:12:55Z

21

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();
}

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Jun 24, 2016 at 18:50

user2921009

8432 gold badges12 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Ties Over a year ago

This works! Note that you put the typeCast attribute in the connection object.

Mike Rippon Over a year ago

I'm using MySQL 5.7 and need to look for ['LONG', 'TINY'].includes(field.type) for some reason, even though all my fields are TINYINT(1). No idea what causes the variance, otherwise this works great.

Hannes Oberreiter Over a year ago

The given function to return null will also return null for 0. You need to change it into if (value === null) return null; and return value === '1'

Roman Vala · Accepted Answer · 2018-09-07 12:19:03Z

-1

I know this is old, but you can put this into your knex migration:

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

it will still use TINYINT so you may still use your casting function

answered Sep 7, 2018 at 12:19

Roman Vala

11 silver badge1 bronze badge

Collectives™ on Stack Overflow

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

2 Answers 2

EDIT

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

EDIT

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related