Casting Bit Fields To Booleans Using The Node.js MySQL Driver
In MySQL, it's a common pattern to use a BIT(1) field to store Boolean flags like isActive, isAdmin, or canDelete. If you use Node.js and the MySQL driver to retrieve these fields, however, you will find out that the MySQL driver converts BIT(1) fields to Node.js Buffer allocations, not Boolean values. This makes sense because BIT fields can contain multiple bit; so, the driver can't make any valid assumptions about the data. But, for those of us who just want BIT(1) fields to come back as Boolean values, we can use custom type-casting functions.
Before we look at type-casting, let's just run a SQL query with a basic MySQL connection configuration to see how BIT(1) fields are treated. In the following code, we're going to query a table, friend, that has a BIT(1) column, isBFF:
// Import the core node modules.
var mysql = require( "mysql" );
// Create the connection to your database. We're going to use the default
// type casting algorithms.
var db = mysql.createConnection({
host: "localhost",
user: "root",
password: "",
database: "testing"
});
// Gather records that we know contain a BIT column.
db.query(
`
SELECT
id,
name,
isBFF -- This is a BIT field.
FROM
friend
`,
function handleResults( error, rows ) {
console.log( "Results:" );
console.log( rows );
}
);
// Gracefully close the connection to the database (queued queries will still run).
db.end();
When we run this code, we get the following terminal output:
Results:
[
RowDataPacket { id: 1, name: 'Tricia', isBFF: <Buffer 00> },
RowDataPacket { id: 2, name: 'Joanna', isBFF: <Buffer 01> },
RowDataPacket { id: 3, name: 'Sarah', isBFF: <Buffer 00> },
RowDataPacket { id: 4, name: 'Kimmie', isBFF: <Buffer 01> },
RowDataPacket { id: 5, name: 'Amanda', isBFF: <Buffer 00> }
]
As you can see, the isBFF BIT(1) field came back as a Buffer (output here as a single hexadecimal value).
In Node.js, from what I understand, the Buffer data type is a collection of 8-bit unsigned integers. As such, other than being empty, a Buffer cannot contain less than 8 bits. And, in fact, if we look at the MySQL driver documentation, it states that it will zero-pad BIT fields in order to create a valid byte.
BUFFER: BIT (last byte will be filled with 0 bits as necessary)
This means that our BIT(1) fields for "0" and "1" actually come back as the bit configurations:
- 0000 0000
- 0000 0001
... respectively. Which, of course, code for the 8-bit integer values 0 and 1.
Now, if we want BIT(1) fields to come back as Boolean values, we can tell the MySQL driver to type-cast BIT(1) to its Boolean equivalent. This can be done at the Connection level or at the Query level. For the sake of this demo, we'll provide the type-casting configuration at the Connection level:
// Import the core node modules.
var mysql = require( "mysql" );
// Create the connection to your database. This time, we're going to use our own custom
// type casting function to manually coerce some of the columns.
var db = mysql.createConnection({
host: "localhost",
user: "root",
password: "",
database: "testing",
typeCast: function castField( field, useDefaultTypeCasting ) {
// We only want to cast bit fields that have a single-bit in them. If the field
// has more than one bit, then we cannot assume it is supposed to be a Boolean.
if ( ( field.type === "BIT" ) && ( field.length === 1 ) ) {
var bytes = field.buffer();
// A Buffer in Node represents a collection of 8-bit unsigned integers.
// Therefore, our single "bit field" comes back as the bits '0000 0001',
// which is equivalent to the number 1.
return( bytes[ 0 ] === 1 );
}
return( useDefaultTypeCasting() );
}
});
// Gather records that we know contain a BIT column.
db.query(
`
SELECT
id,
name,
isBFF -- This is a BIT field.
FROM
friend
`,
function handleResults( error, rows ) {
console.log( "Results:" );
console.log( rows );
}
);
// Gracefully close the connection to the database (queued queries will still run).
db.end();
Here, in our typeCast configuration option, we are inspecting the field to see if its a BIT field of length 1 bit. And, if it is, we're going to parse the field into a Buffer, which will zero-pad the bits to form a valid byte. Then, we just check to see if the resultant byte equals 1. Doing this will cast the 1-bit to a True and the 0-bit to a False.
Now, when we run this code, we get the following output:
Results:
[
RowDataPacket { id: 1, name: 'Tricia', isBFF: false },
RowDataPacket { id: 2, name: 'Joanna', isBFF: true },
RowDataPacket { id: 3, name: 'Sarah', isBFF: false },
RowDataPacket { id: 4, name: 'Kimmie', isBFF: true },
RowDataPacket { id: 5, name: 'Amanda', isBFF: false }
]
As you can see, the isBFF field came back as an actual Boolean value, which is exactly what we would want in the vast majority of cases. And, of course, if we run into a case where we don't want this, we can always override the typeCast option at the query level.
When bringing data from MySQL into Node.js, you're crossing the boundary between two completely different Type systems. As such, some sort of translation will be required. In my case, I often use BIT(1) fields as "flags" and therefore want them to be brought over to Node.js as Booleans. With the MySQL driver, this can be done with some explicit type-casting. But, type-casting BIT fields requires a rudimentary understanding of Node.js Buffers, which is something many developers (including myself) rarely deal with. Hopefully this post can help shed some light on the underlying bit consumption.
Want to use code from this post? Check out the license.
Reader Comments
@All,
I just want to quickly point out that this code doesn't account for the (hopefully rare) case in which a BIT(1) field would be NULL. If it is NULL, then:
field.buffer() === null
Just a heads-up.
I had been stuck on this for long time till now. I also had tried:
1. Changing field type in table to tinyint(1)
But that would create trouble for existing working repositories using that DB
2. Casting BIT fields to boolean in every individual query
That was really pain in the ass and totally a bad practice since it required lots of effort and missing any BIT field would make me land in server error.
Thank you very very much for this post. Totally life saving!! :D
@Akash Deep,
Absolutely my pleasure - glad this was able to get you over the hurdle.
My 'bools' were TinyInts.
This is how I ended up implementing `typeCast` (ClojureScript, but easy to translate to js):
```
(fn [field use-default-type-casting]
(if (and (= "TINY" (.-type field))
(= 1 (.-length field)))
(= (str (.buffer field)) "1")
(use-default-type-casting)))
```
@Mikkel,
.... having flash-backs to school where I had to use LISP to solve a maze :D
I was looking to solve this same issue for a small project of mine - and this is the first site I visited from the Google Search. Problem Solved. Thank you very much!!
Hey guys, great article,
I'm wondering, how I can get the exact type of a column in the database. I mean, the extracted fields from the NPM MYSQL query look like this:
FieldPacket {
catalog: 'def',
db: 'lookupdata',
table: 'actor',
orgTable: 'actor',
name: 'actor_id',
orgName: 'actor_id',
charsetNr: 63,
length: 5,
type: 2,
flags: 16931,
decimals: 0,
default: undefined,
zeroFill: false,
protocol41: true }
But I want to know what kind of type is this one: (Is it a number, or integer, or anything)
type: 2
Any suggest or comment would nice. Thanks.
@Julian,
With the driver, I believe that you have the option to accept a 3rd optional argument in the callback:
function callback( error, rows, fields ) { ... }
If you define the "fields" parameter, then that _should_ have all the information about the actual type of field in question. I haven't used the field much, so I am not too familiar with the structure it returns. But, I assume it provides things like the field-name and field-type and maybe default value, etc.
@Ben,
Hi Ben, thanks for the reply, I could know the number of the types using the library mysql.Types, and voila.
@Julian,
Boom! That's how we do it -- #teamwork :D