Model attribute DataType for MySQL SET()?

[justin hyland]

I was wondering if there was any way to create a table that has a column with MySQL's SET column type, I didn't see it in the DataType list, so I'm guessing it doesn't exist..

If I needed to use SET, would there be a way to accomplish this? 

Thanks!

-J

[Mick Hansen]

You can either implement a custom data type per the data types file or simply use a string for type.

--

Mick Hansen

@mhansendev
mhansen.io

[justin hyland]

Its probably best if i dont implement files that are in the sequelize module, that will make updates difficult, is there a way to implement my own Data Types file?

Thank you

[Mick Hansen]

You could mimic the pattern from here: https://github.com/sequelize/sequelize/blob/master/lib/data-types.js i suppose.

[justin hyland]

Right, but im saying i shouldnt edit the libraries js files, that would mess with git updates. Is there a way to have my own data-types.js file included and used by sequelize, without messing with any sequelize files thenselves?..

[Mick Hansen]

You wouldn't need to, you just need to follow the pattern.

In your own code:

SET = Sequelize.ABSTRACT.inherits(function () {

});

etc

[justin hyland]

Mick, was wondering if you wouldn't mind helping me out a little here.

The SET and ENUM types both have the same format when creating MySQL tables, they are both just

table_name SET('a','b','c') NOT NULL

So with that info, I was thinking I could basically just copy the ENUM DataType code from here, and basically just s/ENUM/SET/g, then make a few alterations to make it work outside of that file.

Here is the resulting code:

"use strict";


const CONFIG    = require( '../config' );
const UTIL      = require('util');
const SEQUELIZE = require( 'sequelize' );


/**
 * Create the SET DataType, which has the same format as ENUM when creating tables, so
 * this is basically the ENUM DataType code from here:
 * https://github.com/sequelize/sequelize/blob/master/lib/data-types.js#L738-L761
 * Just altered to be placed in here, independent of the Sequelize lib
 */
var SET = SEQUELIZE.ABSTRACT.inherits(function(value) {
    var options = typeof value === 'object' && !Array.isArray(value) && value || {
            values: Array.prototype.slice.call(arguments).reduce(function(result, element) {
                return result.concat(Array.isArray(element) ? element : [element]);
            }, [])
        };
    if (!(this instanceof SET)) return new SET(options);
    this.values = options.values;
    this.options = options;
});


SET.prototype.key = SET.key = 'SET';
SET.prototype.validate = function(value) {
    if (!_.contains(this.values, value)) {
        throw new Error(UTIL.format('ERROR: %j is not a valid choice in %j', value, this.values));
    }


    return true;
};


module.exports = function ( sequelize, DataTypes ) {
    var _ = sequelize.Utils._;


    var serversColumns = {
        computerId: {
            type: DataTypes.INTEGER,
            primaryKey: true,
            autoIncrement: true,
            allowNull: false,
            field: 'computer_id'
        },
        hostname: {
            type: DataTypes.STRING,
            unique: true,
            allowNull: false
        },
        operatingSystem: {
            type: DataTypes.ENUM('rhel 4', 'rhel 5', 'rhel 6', 'centos 4', 'centos 5', 'centos 6'),
            allowNull: false,
            field: 'operating_system'
        },
        applications: {
            type: SET('apache','nginx','nagios'),
            allowNull: true
        },
        notes: {
            type: DataTypes.TEXT,
            allowNull: true
        }
    };




    // Define the Sequelize model using the columns gathered above, and set any default settings
    return sequelize.define( 'Computer', serversColumns, {
            //freezeTableName: true,
            tableName: 'computers',
            //underscored: true,
            paranoid: true,
            timestamps: true,
            createdAt: 'created_at',
            updatedAt: 'updated_at',
            deletedAt: 'deleted_at'
        }
    );
}

However, despite it basically being the same as the ENUM type, it doesn't work properly. Heres the generated query:

CREATE TABLE IF NOT EXISTS `computers` (`computer_id` INTEGER NOT NULL auto_increment , `hostname` VARCHAR(255) NOT NULL UNIQUE, `operating_system` ENUM('rhel 4', 'rhel 5', 'rhel 6', 'centos 4', 'centos 5', 'centos 6') NOT NULL, `applications` SET, `notes` TEXT, `created_at` DATETIME NOT NULL, `updated_at` DATETIME NOT NULL, `deleted_at` DATETIME, PRIMARY KEY (`computer_id`)) ENGINE=InnoDB;

As you can see, the SET has no defined options, it should be SET('apache','nginx','nagios'), (Full console output here)

Can you perhaps tell me what I'm doing wrong? Im not not very familiar with the SequelizeJS library code, and I'm not as experienced in JS as I am PHP/Perl/Bash (Im more of a Linux admin than a web developer, I just have an idea for a product I would like to make!)

Any help is appreciated, thanks!!

[Mick Hansen]

Hey Justin,

ENUM is dialect specific, so you need to copy the toSql method for mysql (although i do not slight you for not knowing that :)): https://github.com/sequelize/sequelize/blob/master/lib/dialects/mysql/data-types.js#L75

[justin hyland]

Ill give this a shot sometime today. Thanks Mick!

And sorry for the amount of questions I post. I promise I try things and Google them before I post a question though, lol. Just seems like most of the Sequelize articles/blogs/tutorials are for older versions. I might start posting my own SequelizeJS articles on my blog soon, Its an awesome ORM, only thing I wish it had that it doesn't is a better way to tie in my own features, like a way to extend the core features. Basically just a Plug-in system. 

I come from PHP, and SequelizeJS is pretty similar to the Eloquent ORM by Laravel, which is awesome.

Thanks again!

[Mick Hansen]

No worries about the questions, that's what the forums are for :)

Yeah i really hope to be able to expand the offerings of Sequelize to allow for a great range of plugins.