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?
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.
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.
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.
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.
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.
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();
}
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With