How can i define a column as tinyint(4) using sequelize ORM

Question

I need to define my table as

CREATE TABLE `test` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `o_id` int(11) unsigned NOT NULL,
  `m_name` varchar(45) NOT NULL,
  `o_name` varchar(45) NOT NULL,
  `customer_id` int(11) unsigned NOT NULL,
  `client_id` tinyint(4) unsigned DEFAULT '1',
  `set_id` tinyint(4) unsigned DEFAULT NULL,
  `s1` tinyint(4) unsigned DEFAULT NULL,
  `s2` tinyint(4) unsigned DEFAULT NULL,
  `s3` tinyint(4) unsigned DEFAULT NULL,
  `review` varchar(2045) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `br_o_id_idx` (`order_id`),
  KEY `br_date_idx` (`created_at`),
  KEY `br_on_idx` (`operator_name`),
  KEY `br_mn_idx` (`merchant_name`)
)

but as i am looking on sequelize documentation , it does not have support for tiny int with its size.

Answer 1

From the lack of ZERO_FILL in your table definition, I suspect tinyint(4) probably does not do what you think it does. From 11.2.5 Numeric Type Attributes:

MySQL supports an extension for optionally specifying the display width of integer data types in parentheses following the base keyword for the type.

...

The display width does not constrain the range of values that can be stored in the column.

...

For example, a column specified as SMALLINT(3) has the usual SMALLINT range of -32768 to 32767, and values outside the range permitted by three digits are displayed in full using more than three digits.

I'm not sure if other RDBMSs treat the number in parens differently, but from perusing the sequelize source it looks like they're under the same, incorrect, impression you are.

That being said, the important part of your schema, being that you want to store those fields as TINYINTs (using only a byte of storage to contain values between 0-255), is sadly not available in the Sequelize DataTypes. I might suggest opening a PR to add it...

On the other hand, if you really are looking for the ZEROFILL functionality, and need to specify that display width of 4, you could do something like Sequelize.INTEGER(4).ZEROFILL, but obviously, that would be pretty wasteful of space in your DB.

Answer 2

For MySQL, the Sequelize.BOOLEAN data type maps to TINYINT(1). See

https://github.com/sequelize/sequelize/blob/3e5b8772ef75169685fc96024366bca9958fee63/lib/data-types.js#L397

and

http://docs.sequelizejs.com/en/v3/api/datatypes/

As noted by @user866762, the number in parentheses only affects how the data is displayed, not how it is stored. So, TINYINT(1) vs. TINYINT(4) should have no effect on your data.