Need a VARBINARY data type

[dantman]

Sequelize currently doesn't have a native string type that maps to an easily indexable VARBINARY in MySQL.

This is going to be a growing issue due to utf8mb4, under which the practice (that Sequelize also uses) of using VARCHAR(255) and InnoDB leads to indexes that are too large and cause CREATE TABLE to fail. With the new charset limitations, some columns will simply use a smaller index size; while others will be better off using a VARBINARY. For example, a sorted title index would likely be better off with a utf8mb4 index with a smaller size and a column storing the string identifier for a facebook/google/etc... account would be best off switching to a VARBINARY.

Note that STRING.BINARY will not work because that is for VARCHAR(255) BINARY which still use utf8mb4 and just use the _bin charset, they still use 4 bytes per character in indexes. Also BLOB makes for indexing issues, our inherent problem.

Naming ideas: STRING.RAW, VARBINARY

This should be relatively simple for someone who knows how data-types.js works to do. I can't submit a patch set since I don't understand enough about how the oids in the pg data-types.js file works.

[castarco]

Not only VARBINARY is needed, we need support for BINARY too.

Both options are far different from VARCHAR and CHAR, since BINARYis treated like an opaque vale, without the risk of encoding-issues.

At our company we're using BINARY to encode our IDs, which are similar to UUIDs... but not the same. And we're using a BINARY column to decrease memory usage.

[Celadora]

You can use type: "binary(bytes)" to define a binary column as a temporary solution until this feature is done.

[jorrit]

Wouldn't STRING(num).BINARY be the right data type to map to VARBINARY in MySQL? It seems dialects/mysql/data-types.js doesn't do anything with the BINARY part yet.

[stale]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. If this is still an issue, just leave a comment 🙂

[dantman]

We still need a proper way to use varbinary.

[rodriamaro]

We need it

[vmadman]

+1

I will look into adding it, if I can, but I suspect that it is more complicated than meets the eye... given that no one has taken this on already.

Edit:

After a small amount of investigating, it really doesn't seem all that important, since Sequelize doesn't do much with the type, anyway. There might be some advanced reasons to need the data type, but, on the surface, it doesn't seem like it would give you very much.

I found this...

• Stack Overflow: sequelize-js-how-to-map-a-varbinary-type-from-mysql
• See also: Source: data-types.js

[DalfDev]

+1

[hilary138]

After a small amount of investigating, it really doesn't seem all that important, since Sequelize doesn't do much with the type, anyway. There might be some advanced reasons to need the data type, but, on the surface, it doesn't seem like it would give you very much.

I found this...

* [Stack Overflow: sequelize-js-how-to-map-a-varbinary-type-from-mysql](https://stackoverflow.com/questions/13888689/sequelize-js-how-to-map-a-varbinary-type-from-mysql)

* **See also:** [Source: data-types.js](https://github.com/sequelize/sequelize/blob/master/lib/dialects/mysql/data-types.js)

That's an easy workaround, but it's nearly useless to the average user if it's not documented.

[Benjamin-Willard]

+1

[WikiRik]

@ephys maybe this would be a nice addition to sequelize/website#11 ?

[ephys]

Done :) I've documented how to use raw sql types in sequelize/website#11:

I think we should also support this DataType natively

[WikiRik]

Done :) I've documented how to use raw sql types in sequelize/website#11:

Thanks!

I think we should also support this DataType natively

Native support would indeed be nice. If somebody reads this and wants to implement it, feel free to make a PR for this :)