Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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?

like image 885
user2921009 Avatar asked Jun 24 '16 01:06

user2921009


People also ask

What is KNEX in PostgreSQL?

Knex.js (pronounced /kəˈnɛks/) is a "batteries included" SQL query builder for Postgres, MSSQL, MySQL, MariaDB, SQLite3, Oracle, and Amazon Redshift designed to be flexible, portable, and fun to use.

What is the use of KNEX?

Knex self describes as “a ‘batteries included’ SQL query builder for Postgres, MSSQL, MySQL, MariaDB, SQLite3, Oracle, and Amazon Redshift designed to be flexible, portable, and fun to use”. Basically, it allows you to write SQL queries in your Node application.

How do I build a custom KNEX file using JavaScript?

Knex can be built using a JavaScript build tool such as browserify or webpack. In fact, this documentation uses a webpack build which includes knex. View source on this page to see the browser build in-action (the global knex variable). The knex module is itself a function which takes a configuration object for Knex, accepting a few parameters.

How do I add KNEX to my Express application?

This is also where you will create your migrations and your seeds, as well as run queries such as .select, .insert, .update, and .delete. To get started with Knex.js, run npm install knex followed by npm install database-of-your-choice. The database driver will allow your Express application to interact with your database.


1 Answers

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();
}
like image 164
user2921009 Avatar answered Oct 15 '22 00:10

user2921009