CODEWITHSUNDEEP
mysqlnode.jsuuidsequelize.js
Sudhi Pulla
Sudhi Pulla

Reputation: 604

Find row by UUID stored as binary in SequelizeJS

I have a Sequelize object called Org which represents a row in the organisations table stored in MySQL. This table has a UUID primary key(id) stored as a 16 byte varbinary. If I have the UUID of an object (bfaf1440-3086-11e3-b965-22000af9141e) as a string in my JavaScript code, what is the right way to pass it as a parameter in the where clause in Sequelize?

Following are the options I've tried

Model: (for an existing MySQL table)

var uuid = require('node-uuid');

module.exports = function(sequelize, Sequelize) {
  return sequelize.define('Org', {
    id: {
      type: Sequelize.BLOB, //changing this to Sequelize.UUID does not make any difference
      primaryKey: true,

      get: function() {
        if (this.getDataValue('id')) {
          return uuid.unparse(this.getDataValue('id'));
        }
      }
    },
    name: Sequelize.STRING,
  }, {
    tableName: 'organisation',
    timestamps: false,
    }
  });
};

Option 1: Pass UUID as byte buffer using node-uuid

Org.find({
    where: {
    id: uuid.parse(orgId)
    }
}).then(function(org) {
    success(org);
}).catch(function(err) {
    next(err);
});

Executing (default): SELECT `id`, `name` FROM `Organisation` AS `Org`
    WHERE `Org`.`id` IN (191,175,20,64,48,134,17,227,185,101,34,0,10,249,20,30);

Sequelize treats the byte buffer as multiple values and so I get multiple matches and the top most record (not the one that has the right UUID) gets returned.

Option 2: Write a raw SQL query and pass the UUID as a HEX value

sequelize.query('SELECT * from organisation where id = x:id', Org, {plain: true}, {
      id: orgId.replace(/-/g, '')
}).then(function(org) {
    success(org);
}).catch(function(err) {
    next(err);
});

Executing (default): SELECT * from organisation 
    where id = x'bfaf1440308611e3b96522000af9141e'

I get the correct record, but this approach is not really useful as I have more complex relationships in the DB and writing too many queries by hand beats the purpose of the ORM.

I'm using Sequelize 2.0.0-rc3.

Upvotes: 1

Views: 4027

Answers (2)

Alexandre Costa
Alexandre Costa

Reputation: 1

If the accepted answer didn't work for you, here's what worked for me.

Note: My objective is to find an instance of an event based on a column which is not the primary key.

// guard clause
if (!uuid.validate(uuid_code))
  return
 
const _event = await event.findOne({ where: { uuid_secret: uuid_code } })
  
// yet another guard clause
if (_event === null)
  return
  
// your code here

Upvotes: 0

Sudhi Pulla
Sudhi Pulla

Reputation: 604

Solved it by supplying a fixed size empty Buffer object to uuid.parse().

Got it working initially using ByteBuffer, but then realised that the same can be achieved using uuid.parse()

Org.find({
  where: {
    id: uuid.parse(orgId, new Buffer(16))
  }
}).then(function(org) {
  console.log('Something happened');
  console.log(org);
}).catch(function(err) {
  console.log(err);
});

Executing (default): SELECT `id`, `name` FROM `Organisation` AS `Org` 
   WHERE `Org`.`id`=X'bfaf1440308611e3b96522000af9141e';

Upvotes: 3

Related Questions