Ben Nadel
On User Experience (UX) Design, JavaScript, ColdFusion, Node.js, Life, and Love.
Ben Nadel at the New York ColdFusion User Group (Jun. 2010) with: Andy Matthews
Ben Nadel at the New York ColdFusion User Group (Jun. 2010) with: Andy Matthews@commadelimited )

Casting Bit Fields To Booleans Using The Node.js MySQL Driver

By Ben Nadel on

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.



Looking For A New Job?

Ooops, there are no jobs. Post one now for only $29 and own this real estate!

100% of job board revenue is donated to Kiva. Loans that change livesFind out more »

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.

Reply to this Comment

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

Reply to this Comment

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)))
```

Reply to this Comment

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!!

Reply to this Comment

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.

Reply to this Comment

@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.

Reply to this Comment

Post A Comment

You — Get Out Of My Dreams, Get Into My Comments
Live in the Now
Oops!
NEW: Some basic markdown formatting is now supported: bold, italic, blockquotes, lists, fenced code-blocks. Read more about markdown syntax »
Comment Etiquette: Please do not post spam. Please keep the comments on-topic. Please do not post unrelated questions or large chunks of code. And, above all, please be nice to each other - we're trying to have a good conversation here.