Datatypes

Below are some of the datatypes supported by sequelize. For a full and updated list, see DataTypes.

  1. Sequelize.STRING                      // VARCHAR(255)
  2. Sequelize.STRING(1234)                // VARCHAR(1234)
  3. Sequelize.STRING.BINARY               // VARCHAR BINARY
  4. Sequelize.TEXT                        // TEXT
  5. Sequelize.TEXT('tiny')                // TINYTEXT
  6. Sequelize.CITEXT                      // CITEXT      PostgreSQL and SQLite only.
  8. Sequelize.INTEGER                     // INTEGER
  9. Sequelize.BIGINT                      // BIGINT
  10. Sequelize.BIGINT(11)                  // BIGINT(11)
  12. Sequelize.FLOAT                       // FLOAT
  13. Sequelize.FLOAT(11)                   // FLOAT(11)
  14. Sequelize.FLOAT(11, 10)               // FLOAT(11,10)
  16. Sequelize.REAL                        // REAL        PostgreSQL only.
  17. Sequelize.REAL(11)                    // REAL(11)    PostgreSQL only.
  18. Sequelize.REAL(11, 12)                // REAL(11,12) PostgreSQL only.
  20. Sequelize.DOUBLE                      // DOUBLE
  21. Sequelize.DOUBLE(11)                  // DOUBLE(11)
  22. Sequelize.DOUBLE(11, 10)              // DOUBLE(11,10)
  24. Sequelize.DECIMAL                     // DECIMAL
  25. Sequelize.DECIMAL(10, 2)              // DECIMAL(10,2)
  27. Sequelize.DATE                        // DATETIME for mysql / sqlite, TIMESTAMP WITH TIME ZONE for postgres
  28. Sequelize.DATE(6)                     // DATETIME(6) for mysql 5.6.4+. Fractional seconds support with up to 6 digits of precision
  29. Sequelize.DATEONLY                    // DATE without time.
  30. Sequelize.BOOLEAN                     // TINYINT(1)
  32. Sequelize.ENUM('value 1', 'value 2')  // An ENUM with allowed values 'value 1' and 'value 2'
  33. Sequelize.ARRAY(Sequelize.TEXT)       // Defines an array. PostgreSQL only.
  34. Sequelize.ARRAY(Sequelize.ENUM)       // Defines an array of ENUM. PostgreSQL only.
  36. Sequelize.JSON                        // JSON column. PostgreSQL, SQLite and MySQL only.
  37. Sequelize.JSONB                       // JSONB column. PostgreSQL only.
  39. Sequelize.BLOB                        // BLOB (bytea for PostgreSQL)
  40. Sequelize.BLOB('tiny')                // TINYBLOB (bytea for PostgreSQL. Other options are medium and long)
  42. Sequelize.UUID                        // UUID datatype for PostgreSQL and SQLite, CHAR(36) BINARY for MySQL (use defaultValue: Sequelize.UUIDV1 or Sequelize.UUIDV4 to make sequelize generate the ids automatically)
  44. Sequelize.CIDR                        // CIDR datatype for PostgreSQL
  45. Sequelize.INET                        // INET datatype for PostgreSQL
  46. Sequelize.MACADDR                     // MACADDR datatype for PostgreSQL
  48. Sequelize.RANGE(Sequelize.INTEGER)    // Defines int4range range. PostgreSQL only.
  49. Sequelize.RANGE(Sequelize.BIGINT)     // Defined int8range range. PostgreSQL only.
  50. Sequelize.RANGE(Sequelize.DATE)       // Defines tstzrange range. PostgreSQL only.
  51. Sequelize.RANGE(Sequelize.DATEONLY)   // Defines daterange range. PostgreSQL only.
  52. Sequelize.RANGE(Sequelize.DECIMAL)    // Defines numrange range. PostgreSQL only.
  54. Sequelize.ARRAY(Sequelize.RANGE(Sequelize.DATE)) // Defines array of tstzrange ranges. PostgreSQL only.
  56. Sequelize.GEOMETRY                    // Spatial column.  PostgreSQL (with PostGIS) or MySQL only.
  57. Sequelize.GEOMETRY('POINT')           // Spatial column with geometry type. PostgreSQL (with PostGIS) or MySQL only.
  58. Sequelize.GEOMETRY('POINT', 4326)     // Spatial column with geometry type and SRID.  PostgreSQL (with PostGIS) or MySQL only.

The BLOB datatype allows you to insert data both as strings and as buffers. When you do a find or findAll on a model which has a BLOB column, that data will always be returned as a buffer.

If you are working with the PostgreSQL TIMESTAMP WITHOUT TIME ZONE and you need to parse it to a different timezone, please use the pg library's own parser:

  1. require('pg').types.setTypeParser(1114, stringValue => {
  2.   return new Date(stringValue + '+0000');
  3.   // e.g., UTC offset. Use any offset that you would like.
  4. });

In addition to the type mentioned above, integer, bigint, float and double also support unsigned and zerofill properties, which can be combined in any order:Be aware that this does not apply for PostgreSQL!

  1. Sequelize.INTEGER.UNSIGNED              // INTEGER UNSIGNED
  2. Sequelize.INTEGER(11).UNSIGNED          // INTEGER(11) UNSIGNED
  3. Sequelize.INTEGER(11).ZEROFILL          // INTEGER(11) ZEROFILL
  4. Sequelize.INTEGER(11).ZEROFILL.UNSIGNED // INTEGER(11) UNSIGNED ZEROFILL
  5. Sequelize.INTEGER(11).UNSIGNED.ZEROFILL // INTEGER(11) UNSIGNED ZEROFILL

The examples above only show integer, but the same can be done with bigint and float

Usage in object notation:

  1. // for enums:
  2. class MyModel extends Model {}
  3. MyModel.init({
  4.   states: {
  5.     type: Sequelize.ENUM,
  6.     values: ['active', 'pending', 'deleted']
  7.   }
  8. }, { sequelize })

Array(ENUM)

Its only supported with PostgreSQL.

Array(Enum) type require special treatment. Whenever Sequelize will talk to database it has to typecast Array values with ENUM name.

So this enum name must follow this pattern enum<table_name><col_name>. If you are using sync then correct name will automatically be generated.

Range types

Since range types have extra information for their bound inclusion/exclusion it's notvery straightforward to just use a tuple to represent them in javascript.

When supplying ranges as values you can choose from the following APIs:

  1. // defaults to '["2016-01-01 00:00:00+00:00", "2016-02-01 00:00:00+00:00")'
  2. // inclusive lower bound, exclusive upper bound
  3. Timeline.create({ range: [new Date(Date.UTC(2016, 0, 1)), new Date(Date.UTC(2016, 1, 1))] });
  5. // control inclusion
  6. const range = [
  7.   { value: new Date(Date.UTC(2016, 0, 1)), inclusive: false },
  8.   { value: new Date(Date.UTC(2016, 1, 1)), inclusive: true },
  9. ];
  10. // '("2016-01-01 00:00:00+00:00", "2016-02-01 00:00:00+00:00"]'
  12. // composite form
  13. const range = [
  14.   { value: new Date(Date.UTC(2016, 0, 1)), inclusive: false },
  15.   new Date(Date.UTC(2016, 1, 1)),
  16. ];
  17. // '("2016-01-01 00:00:00+00:00", "2016-02-01 00:00:00+00:00")'
  19. Timeline.create({ range });

However, please note that whenever you get back a value that is range you willreceive:

  1. // stored value: ("2016-01-01 00:00:00+00:00", "2016-02-01 00:00:00+00:00"]
  2. range // [{ value: Date, inclusive: false }, { value: Date, inclusive: true }]

You will need to call reload after updating an instance with a range type or use returning: true option.

Special Cases

  1. // empty range:
  2. Timeline.create({ range: [] }); // range = 'empty'
  4. // Unbounded range:
  5. Timeline.create({ range: [null, null] }); // range = '[,)'
  6. // range = '[,"2016-01-01 00:00:00+00:00")'
  7. Timeline.create({ range: [null, new Date(Date.UTC(2016, 0, 1))] });
  9. // Infinite range:
  10. // range = '[-infinity,"2016-01-01 00:00:00+00:00")'
  11. Timeline.create({ range: [-Infinity, new Date(Date.UTC(2016, 0, 1))] });