Home Reference Source RepositoryJoin us on Slack

Variable

Static Public Summary
public

A convenience class holding commonly used data types.

public

A collection of properties related to deferrable constraints.

public

Op: {"eq": *, "ne": *, "gte": *, "gt": *, "lte": *, "lt": *, "not": *, "is": *, "in": *, "notIn": *, "like": *, "notLike": *, "iLike": *, "notILike": *, "regexp": *, "notRegexp": *, "iRegexp": *, "notIRegexp": *, "between": *, "notBetween": *, "overlap": *, "contains": *, "contained": *, "adjacent": *, "strictLeft": *, "strictRight": *, "noExtendRight": *, "noExtendLeft": *, "and": *, "or": *, "any": *, "all": *, "values": *, "col": *, "placeholder": *, "join": *, "raw": *}

Operator symbols to be used when querying data

public

An enum of query types used by sequelize.query

public

An enum of table hints to be used in mssql for querying with table hints

Static Public

public DataTypes: * source

A convenience class holding commonly used data types. The datatypes are used when defining a new model using Sequelize.define, like this:

sequelize.define('model', {
  column: DataTypes.INTEGER
})

When defining a model you can just as easily pass a string as type, but often using the types defined here is beneficial. For example, using DataTypes.BLOB, mean that that column will be returned as an instance of Buffer when being fetched by sequelize.

To provide a length for the data type, you can invoke it like a function: INTEGER(2)

Some data types have special properties that can be accessed in order to change the data type. For example, to get an unsigned integer with zerofill you can do DataTypes.INTEGER.UNSIGNED.ZEROFILL. The order you access the properties in do not matter, so DataTypes.INTEGER.ZEROFILL.UNSIGNED is fine as well.

  • All number types (INTEGER, BIGINT, FLOAT, DOUBLE, REAL, DECIMAL) expose the properties UNSIGNED and ZEROFILL
  • The CHAR and STRING types expose the BINARY property

Three of the values provided here (NOW, UUIDV1 and UUIDV4) are special default values, that should not be used to define types. Instead they are used as shorthands for defining default values. For example, to get a uuid field with a default value generated following v1 of the UUID standard:

sequelize.define('model',` {
  uuid: {
    type: DataTypes.UUID,
    defaultValue: DataTypes.UUIDV1,
    primaryKey: true
  }
})

There may be times when you want to generate your own UUID conforming to some other algorithm. This is accomplished using the defaultValue property as well, but instead of specifying one of the supplied UUID types, you return a value from a function.

sequelize.define('model', {
  uuid: {
    type: DataTypes.UUID,
    defaultValue: function() {
      return generateMyId()
    },
    primaryKey: true
  }
})

Properties:

NameTypeAttributeDescription
STRING function(length=255: integer)

A variable length string

CHAR function(length=255: integer)

A fixed length string.

TEXT function(length: string)

An unlimited length text column. Available lengths: tiny, medium, long

TINYINT function(length: integer)

A 8 bit integer.

SMALLINT function(length: integer)

A 16 bit integer.

MEDIUMINT function(length: integer)

A 24 bit integer.

INTEGER function(length=255: integer)

A 32 bit integer.

BIGINT function(length: integer)

A 64 bit integer. Note: an attribute defined as BIGINT will be treated like a string due this feature from node-postgres to prevent precision loss. To have this attribute as a number, this is a possible workaround.

FLOAT function(length: integer, decimals: integer)

Floating point number (4-byte precision).

DOUBLE function(length: integer, decimals: integer)

Floating point number (8-byte precision).

DECIMAL function(precision: integer, scale: integer)

Decimal number.

REAL function(length: integer, decimals: integer)

Floating point number (4-byte precision).

BOOLEAN function

A boolean / tinyint column, depending on dialect

BLOB function(length: string)

Binary storage. Available lengths: tiny, medium, long

ENUM function(values: string[])

An enumeration. DataTypes.ENUM('value', 'another value').

DATE function(length: integer)

A datetime column

DATEONLY function

A date only column (no timestamp)

TIME function

A time column

NOW function

A default value of the current timestamp

UUID function

A column storing a unique universal identifier. Use with UUIDV1 or UUIDV4 for default values.

UUIDV1 function

A default unique universal identifier generated following the UUID v1 standard

UUIDV4 function

A default unique universal identifier generated following the UUID v4 standard

HSTORE function

A key / value store column. Only available in Postgres.

JSON function

A JSON string column. Available in MySQL, Postgres and SQLite

JSONB function

A binary storage JSON column. Only available in Postgres.

ARRAY function(type: DataTypes)

An array of type, e.g. DataTypes.ARRAY(DataTypes.DECIMAL). Only available in Postgres.

RANGE function(type: DataTypes)

Range types are data types representing a range of values of some element type (called the range's subtype). Only available in Postgres. See the Postgres documentation for more details

GEOMETRY function(type: string, srid: string)

A column storing Geometry information. It is only available in PostgreSQL (with PostGIS) or MySQL. In MySQL, allowable Geometry types are POINT, LINESTRING, POLYGON.

GeoJSON is accepted as input and returned as output. In PostGIS, the GeoJSON is parsed using the PostGIS function ST_GeomFromGeoJSON. In MySQL it is parsed using the function GeomFromText. Therefore, one can just follow the GeoJSON spec for handling geometry objects. See the following examples:

// Create a new point:
const point = { type: 'Point', coordinates: [39.807222,-76.984722]};

User.create({username: 'username', geometry: point });

// Create a new linestring:
const line = { type: 'LineString', 'coordinates': [ [100.0, 0.0], [101.0, 1.0] ] };

User.create({username: 'username', geometry: line });

// Create a new polygon:
const polygon = { type: 'Polygon', coordinates: [
                [ [100.0, 0.0], [101.0, 0.0], [101.0, 1.0],
                  [100.0, 1.0], [100.0, 0.0] ]
                ]};

User.create({username: 'username', geometry: polygon });

// Create a new point with a custom SRID:
const point = {
  type: 'Point',
  coordinates: [39.807222,-76.984722],
  crs: { type: 'name', properties: { name: 'EPSG:4326'} }
};

User.create({username: 'username', geometry: point })
GEOGRAPHY function(type: string, srid: string)

A geography datatype represents two dimensional spacial objects in an elliptic coord system.

VIRTUAL function(returnType: DataTypes, fields: string[])

A virtual value that is not stored in the DB. This could for example be useful if you want to provide a default value in your model that is returned to the user but not stored in the DB.

You could also use it to validate a value before permuting and storing it. Checking password length before hashing it for example:

sequelize.define('user', {
  password_hash: DataTypes.STRING,
  password: {
    type: DataTypes.VIRTUAL,
    set: function (val) {
       // Remember to set the data value, otherwise it won't be validated
       this.setDataValue('password', val);
       this.setDataValue('password_hash', this.salt + val);
     },
     validate: {
        isLongEnough: function (val) {
          if (val.length < 7) {
            throw new Error("Please choose a longer password")
         }
      }
    }
  }
})

In the above code the password is stored plainly in the password field so it can be validated, but is never stored in the DB.

VIRTUAL also takes a return type and dependency fields as arguments If a virtual attribute is present in attributes it will automatically pull in the extra fields as well. Return type is mostly useful for setups that rely on types like GraphQL.

{
  active: {
    type: new DataTypes.VIRTUAL(DataTypes.BOOLEAN, ['createdAt']),
    get: function() {
      return this.get('createdAt') > Date.now() - (7 * 24 * 60 * 60 * 1000)
    }
  }
}

public Deferrable: * source

A collection of properties related to deferrable constraints. It can be used to make foreign key constraints deferrable and to set the constraints within a transaction. This is only supported in PostgreSQL.

The foreign keys can be configured like this. It will create a foreign key that will check the constraints immediately when the data was inserted.

sequelize.define('Model', {
  foreign_id: {
    type: Sequelize.INTEGER,
    references: {
      model: OtherModel,
      key: 'id',
      deferrable: Sequelize.Deferrable.INITIALLY_IMMEDIATE
    }
  }
});

The constraints can be configured in a transaction like this. It will trigger a query once the transaction has been started and set the constraints to be checked at the very end of the transaction.

sequelize.transaction({
  deferrable: Sequelize.Deferrable.SET_DEFERRED
});

Properties:

NameTypeAttributeDescription
INITIALLY_DEFERRED *

Defer constraints checks to the end of transactions.

INITIALLY_IMMEDIATE *

Trigger the constraint checks immediately

NOT *

Set the constraints to not deferred. This is the default in PostgreSQL and it make it impossible to dynamically defer the constraints within a transaction.

SET_DEFERRED *
SET_IMMEDIATE *

public Op: {"eq": *, "ne": *, "gte": *, "gt": *, "lte": *, "lt": *, "not": *, "is": *, "in": *, "notIn": *, "like": *, "notLike": *, "iLike": *, "notILike": *, "regexp": *, "notRegexp": *, "iRegexp": *, "notIRegexp": *, "between": *, "notBetween": *, "overlap": *, "contains": *, "contained": *, "adjacent": *, "strictLeft": *, "strictRight": *, "noExtendRight": *, "noExtendLeft": *, "and": *, "or": *, "any": *, "all": *, "values": *, "col": *, "placeholder": *, "join": *, "raw": *} source

Operator symbols to be used when querying data

Properties:

NameTypeAttributeDescription
eq *
ne *
gte *
gt *
lte *
lt *
not *
is *
in *
notIn *
like *
notLike *
iLike *
notILike *
regexp *
notRegexp *
iRegexp *
notIRegexp *
between *
notBetween *
overlap *
contains *
contained *
adjacent *
strictLeft *
strictRight *
noExtendRight *
noExtendLeft *
and *
or *
any *
all *
values *
col *
placeholder *
join *

See:

public QueryTypes: * source

An enum of query types used by sequelize.query

Properties:

NameTypeAttributeDescription
SELECT *
INSERT *
UPDATE *
BULKUPDATE *
BULKDELETE *
DELETE *
UPSERT *
VERSION *
SHOWTABLES *
SHOWINDEXES *
DESCRIBE *
RAW *
FOREIGNKEYS *
SHOWCONSTRAINTS *

See:

public TableHints: * source

An enum of table hints to be used in mssql for querying with table hints

Properties:

NameTypeAttributeDescription
NOLOCK *
READUNCOMMITTED *
UPDLOCK *
REPEATABLEREAD *
SERIALIZABLE *
READCOMMITTED *
TABLOCK *
TABLOCKX *
PAGLOCK *
ROWLOCK *
NOWAIT *
READPAST *
XLOCK *
SNAPSHOT *
NOEXPAND *