Data Types

Sequelize provides a lot of built-in data types. To access a built-in data type, you must import DataTypes:

// Import the built-in data types
import { DataTypes } from '@sequelize/core';

Below is a series of support table describing which SQL Type is used for each Sequelize DataType.

info

Most of our DataTypes also accept option bags. Click on one of our DataTypes in the tables below to view their signature.

A ❌ means the dialect does not support that DataType.

Strings

AllPostgreSQLMariaDBMySQLMSSQLSQLiteSnowflakedb2ibmi

Sequelize DataType	PostgreSQL	MariaDB	MySQL	MSSQL	SQLite	Snowflake	db2	ibmi
STRING	VARCHAR(255)	VARCHAR(255)	VARCHAR(255)	NVARCHAR(255)	VARCHAR(255)	VARCHAR(255)	VARCHAR(255)	VARCHAR(255)
STRING(100)	VARCHAR(100)	VARCHAR(100)	VARCHAR(100)	NVARCHAR(100)	VARCHAR(100)	VARCHAR(100)	VARCHAR(100)	VARCHAR(100)
STRING.BINARY	BYTEA	VARCHAR(255) BINARY	VARCHAR(255) BINARY	BINARY(255)	VARCHAR BINARY(255)	VARCHAR(255) BINARY	VARCHAR(255) FOR BIT DATA	BINARY(255)
STRING(100).BINARY	❌	VARCHAR(100) BINARY	VARCHAR(100) BINARY	BINARY(100)	VARCHAR BINARY(100)	VARCHAR(100) BINARY	VARCHAR(100) FOR BIT DATA	BINARY(100)
TEXT	TEXT	TEXT	TEXT	NVARCHAR(MAX)	TEXT	TEXT	VARCHAR(32672)	VARCHAR(8192)
TEXT('tiny')	❌	TINYTEXT	TINYTEXT	NVARCHAR(256)	❌	❌	VARCHAR(256)	VARCHAR(256)
TEXT('medium')	❌	MEDIUMTEXT	MEDIUMTEXT	❌	❌	❌	VARCHAR(8192)	VARCHAR(8192)
TEXT('long')	❌	LONGTEXT	LONGTEXT	❌	❌	❌	CLOB(65536)	CLOB(65536)
CHAR	CHAR(255)	CHAR(255)	CHAR(255)	CHAR(255)	CHAR(255)	CHAR(255)	CHAR(255)	CHAR(255)
CHAR(100)	CHAR(100)	CHAR(100)	CHAR(100)	CHAR(100)	CHAR(100)	CHAR(100)	CHAR(100)	CHAR(100)
CHAR.BINARY	BYTEA	CHAR(255) BINARY	CHAR(255) BINARY	CHAR(255) BINARY	CHAR BINARY(255)	CHAR(255) BINARY	CHAR(255) BINARY	CLOB(255)
CHAR(100).BINARY	❌	CHAR(100) BINARY	CHAR(100) BINARY	CHAR(100) BINARY	CHAR BINARY(100)	CHAR(100) BINARY	CHAR(100) BINARY	CLOB(100)
CITEXT	CITEXT	❌	❌	❌	TEXT COLLATE NOCASE	❌	❌	❌
TSVECTOR	TSVECTOR	❌	❌	❌	❌	❌	❌	❌

Boolean

AllPostgreSQLMariaDBMySQLMSSQLSQLiteSnowflakedb2ibmi

Sequelize DataType	PostgreSQL	MariaDB	MySQL	MSSQL	SQLite	Snowflake	db2	ibmi
BOOLEAN	BOOLEAN	TINYINT(1)	TINYINT(1)	BIT	TINYINT(1)	BOOLEAN	BOOLEAN	SMALLINT

Integers

AllPostgreSQLMariaDBMySQLMSSQLSQLiteSnowflakedb2ibmi

Sequelize DataType	PostgreSQL	MariaDB	MySQL	MSSQL	SQLite	Snowflake	db2	ibmi
TINYINT	❌	TINYINT	TINYINT	TINYINT	❌	TINYINT	TINYINT	TINYINT
TINYINT(1)	❌	TINYINT(1)	TINYINT(1)	❌	❌	TINYINT(1)	❌	TINYINT(1)
TINYINT.UNSIGNED	❌	TINYINT UNSIGNED	TINYINT UNSIGNED	❌	❌	❌	❌	TINYINT UNSIGNED
TINYINT.ZEROFILL	❌	TINYINT ZEROFILL	TINYINT ZEROFILL	❌	❌	❌	❌	TINYINT ZEROFILL
SMALLINT	SMALLINT	SMALLINT	SMALLINT	SMALLINT	❌	SMALLINT	SMALLINT	SMALLINT
SMALLINT(1)	❌	SMALLINT(1)	SMALLINT(1)	❌	❌	SMALLINT(1)	❌	❌
SMALLINT.UNSIGNED	❌	SMALLINT UNSIGNED	SMALLINT UNSIGNED	❌	❌	❌	❌	❌
SMALLINT.ZEROFILL	❌	SMALLINT ZEROFILL	SMALLINT ZEROFILL	❌	❌	❌	❌	❌
MEDIUMINT	❌	MEDIUMINT	MEDIUMINT	❌	❌	❌	MEDIUMINT	MEDIUMINT
MEDIUMINT(1)	❌	MEDIUMINT(1)	MEDIUMINT(1)	❌	❌	❌	MEDIUMINT(1)	MEDIUMINT(1)
MEDIUMINT.UNSIGNED	❌	MEDIUMINT UNSIGNED	MEDIUMINT UNSIGNED	❌	❌	❌	MEDIUMINT UNSIGNED	MEDIUMINT UNSIGNED
MEDIUMINT.ZEROFILL	❌	MEDIUMINT ZEROFILL	MEDIUMINT ZEROFILL	❌	❌	❌	MEDIUMINT ZEROFILL	MEDIUMINT ZEROFILL
INTEGER	INTEGER	INTEGER	INTEGER	INTEGER	INTEGER	INTEGER	INTEGER	INTEGER
INTEGER(1)	❌	INTEGER(1)	INTEGER(1)	❌	❌	INTEGER(1)	❌	❌
INTEGER.UNSIGNED	❌	INTEGER UNSIGNED	INTEGER UNSIGNED	❌	❌	❌	❌	❌
INTEGER.ZEROFILL	❌	INTEGER ZEROFILL	INTEGER ZEROFILL	❌	❌	❌	❌	❌
BIGINT	BIGINT	BIGINT	BIGINT	BIGINT	❌	BIGINT	BIGINT	BIGINT
BIGINT(1)	❌	BIGINT(1)	BIGINT(1)	❌	❌	BIGINT(1)	❌	❌
BIGINT.UNSIGNED	❌	BIGINT UNSIGNED	BIGINT UNSIGNED	❌	❌	❌	❌	❌
BIGINT.ZEROFILL	❌	BIGINT ZEROFILL	BIGINT ZEROFILL	❌	❌	❌	❌	❌

caution

The JavaScript number type can represent ints ranging from -9007199254740991 to 9007199254740991.

If your SQL type supports integer values outside this range, we recommend using bigint or string to represent your integers.

info

Numeric options can be combined:
DataTypes.INTEGER(1).UNSIGNED.ZEROFILLwill result in a column of type INTEGER(1) UNSIGNED ZEROFILL in MySQL.

Inexact Decimal Numbers

The types in the following table are typically represented as an IEEE 754 floating point number, like the JavaScript number type.

AllPostgreSQLMariaDBMySQLMSSQLSQLiteSnowflakedb2ibmi

Sequelize DataType	PostgreSQL	MariaDB	MySQL	MSSQL	SQLite	Snowflake	db2	ibmi
REAL	REAL	REAL	REAL	REAL	REAL	REAL	REAL	REAL
REAL(11, 10)	❌	REAL(11,10)	REAL(11,10)	❌	❌	❌	❌	❌
REAL.UNSIGNED	❌	REAL UNSIGNED	REAL UNSIGNED	❌	❌	❌	❌	❌
REAL.ZEROFILL	❌	REAL ZEROFILL	REAL ZEROFILL	❌	❌	❌	❌	❌
FLOAT	FLOAT	FLOAT	FLOAT	FLOAT	❌	FLOAT	FLOAT	FLOAT
FLOAT(11)	FLOAT(11)	❌	FLOAT(11)	FLOAT(11)	❌	❌	FLOAT(11)	FLOAT(11)
FLOAT(11, 10)	❌	FLOAT(11,10)	FLOAT(11,10)	❌	❌	❌	❌	❌
FLOAT.UNSIGNED	❌	FLOAT UNSIGNED	FLOAT UNSIGNED	❌	❌	❌	❌	❌
FLOAT.ZEROFILL	❌	FLOAT ZEROFILL	FLOAT ZEROFILL	❌	❌	❌	❌	❌
DOUBLE	DOUBLE PRECISION	DOUBLE PRECISION	DOUBLE PRECISION	DOUBLE PRECISION	❌	DOUBLE PRECISION	DOUBLE	DOUBLE PRECISION
DOUBLE(11, 10)	❌	DOUBLE PRECISION(11, 10)	DOUBLE PRECISION(11, 10)	❌	❌	❌	❌	DOUBLE PRECISION(11, 10)
DOUBLE.UNSIGNED	❌	DOUBLE PRECISION UNSIGNED	DOUBLE PRECISION UNSIGNED	❌	❌	❌	❌	DOUBLE PRECISION UNSIGNED
DOUBLE.ZEROFILL	❌	DOUBLE PRECISION ZEROFILL	DOUBLE PRECISION ZEROFILL	❌	❌	❌	❌	DOUBLE PRECISION ZEROFILL

caution

The first parameter in FLOAT(11) and FLOAT(11,10) have different meanings.
Refer to your dialect's manual to learn more about the difference.

info

Numeric options can be combined:
DataTypes.INTEGER(1).UNSIGNED.ZEROFILLwill result in a column of type INTEGER(1) UNSIGNED ZEROFILL in MySQL.

Exact Decimal Numbers

AllPostgreSQLMariaDBMySQLMSSQLSQLiteSnowflakedb2ibmi

Sequelize DataType	PostgreSQL	MariaDB	MySQL	MSSQL	SQLite	Snowflake	db2	ibmi
DECIMAL	DECIMAL	DECIMAL	DECIMAL	DECIMAL	DECIMAL	DECIMAL	DECIMAL	DECIMAL
DECIMAL(11)	DECIMAL(11)	DECIMAL(11)	DECIMAL(11)	DECIMAL(11)	❌	DECIMAL(11)	DECIMAL(11)	DECIMAL(11)
DECIMAL(11, 10)	DECIMAL(11, 10)	DECIMAL(11,10)	DECIMAL(11,10)	DECIMAL(11,10)	❌	DECIMAL(11,10)	DECIMAL(11,10)	DECIMAL(11,10)
DECIMAL.UNSIGNED	❌	DECIMAL UNSIGNED	DECIMAL UNSIGNED	❌	❌	❌	❌	❌
DECIMAL.ZEROFILL	❌	DECIMAL ZEROFILL	DECIMAL ZEROFILL	❌	❌	❌	❌	❌

caution

Exact Decimal Numbers are not representable in JavaScript yet. The JavaScript number type is a double-precision 64-bit binary format IEEE 754 value, better represented by Inexact Decimal types.

To avoid any loss of precision, we recommend using string to represent Exact Decimal Numbers in JavaScript.

info

Numeric options can be combined:
DataTypes.INTEGER(1).UNSIGNED.ZEROFILLwill result in a column of type INTEGER(1) UNSIGNED ZEROFILL in MySQL.

Dates

AllPostgreSQLMariaDBMySQLMSSQLSQLiteSnowflakedb2ibmi

Sequelize DataType	PostgreSQL	MariaDB	MySQL	MSSQL	SQLite	Snowflake	db2	ibmi
DATE	TIMESTAMP WITH TIME ZONE	DATETIME	DATETIME	DATETIMEOFFSET	❌	TIMESTAMP	TIMESTAMP	TIMESTAMP
DATE(6)	❌	DATETIME(6)	DATETIME(6)	❌	❌	❌	TIMESTAMP(6)	❌
DATEONLY	DATE	DATE	DATE	DATE	❌	DATE	DATE	DATE
TIME	TIME	TIME	TIME	TIME	❌	TIME	TIME	TIME

Built-in Default Values for Dates

Along with regular default values, Sequelize provides DataTypes.NOW which will use the appropriate native SQL function based on your dialect.

AllPostgreSQLMariaDBMySQLMSSQLSQLiteSnowflakedb2ibmi

Sequelize DataType	PostgreSQL	MariaDB	MySQL	MSSQL	SQLite	Snowflake	db2	ibmi
NOW	NOW	NOW	NOW	GETDATE()	❌	❌	CURRENT TIME	NOW

MyModel.init({
  myDate: {
    type: DataTypes.DATE,
    defaultValue: DataTypes.NOW,
  },
});

UUIDs

For UUIDs, use DataTypes.UUID. It becomes the UUID data type for PostgreSQL and SQLite, and CHAR(36) for MySQL.

AllPostgreSQLMariaDBMySQLMSSQLSQLiteSnowflakedb2ibmi

Sequelize DataType	PostgreSQL	MariaDB	MySQL	MSSQL	SQLite	Snowflake	db2	ibmi
UUID	UUID	CHAR(36) BINARY	CHAR(36) BINARY	CHAR(36)	❌	VARCHAR(36)	CHAR(36) FOR BIT DATA	CHAR(36)

Built-in Default Values for UUID

Sequelize can generate UUIDs automatically for these fields, simply use DataTypes.UUIDV1 or DataTypes.UUIDV4 as the default value:

MyModel.init({
  myUuid: {
    type: DataTypes.UUID,
    defaultValue: DataTypes.UUIDV4, // Or DataTypes.UUIDV1
  },
});

caution

The generation of values for DataTypes.UUIDV1 and DataTypes.UUIDV4 is done by the Sequelize layer, in JavaScript. For this reason, it is only used when interacting with Models. It cannot be used in migrations.

If your dialect provides a built-in SQL function to generate UUIDs, you can use fn to set a default value on the SQL layer. Making it usable with raw queries, and migrations.

import { fn } from '@sequelize/core';

MyModel.init({
  myUuid: {
    type: DataTypes.UUID,
    // 'uuid_generate_v4' is only available in postgres + uuid-ossp
    // other dialects may support this function under different names.
    defaultValue: fn('uuid_generate_v4'),
  },
});

BLOBs

The blob datatype allows you to insert data both as strings and buffers. However, when a blob is retrieved from database with Sequelize, it will always be retrieved as a Node Buffer.

AllPostgreSQLMariaDBMySQLMSSQLSQLiteSnowflakedb2ibmi

Sequelize DataType	PostgreSQL	MariaDB	MySQL	MSSQL	SQLite	Snowflake	db2	ibmi
BLOB	BYTEA	BLOB	BLOB	VARBINARY(MAX)	BLOB	❌	BLOB	BLOB(1M)
BLOB('tiny')	❌	TINYBLOB	TINYBLOB	VARBINARY(256)	❌	❌	BLOB(255)	BLOB(255)
BLOB('medium')	❌	MEDIUMBLOB	MEDIUMBLOB	❌	❌	❌	BLOB(16M)	BLOB(16M)
BLOB('long')	❌	LONGBLOB	LONGBLOB	❌	❌	❌	BLOB(2G)	BLOB(2G)

ENUMs

note

Enums are only available in PostgreSQL, MariaDB, and MySQL

The ENUM is a data type that accepts only a few values, specified as a list.

DataTypes.ENUM('foo', 'bar') // An ENUM with allowed values 'foo' and 'bar'

See the API Reference for DataTypes.ENUM for more information about the options this DataType accepts.

JSON & JSONB

The DataTypes.JSON data type is only supported for SQLite, MySQL, MariaDB and PostgreSQL. However, there is a minimum support for MSSQL (see below).

AllPostgreSQLMariaDBMySQLMSSQLSQLiteSnowflakedb2ibmi

Sequelize DataType	PostgreSQL	MariaDB	MySQL	MSSQL	SQLite	Snowflake	db2	ibmi
JSON	JSON	JSON	JSON	❌	❌	❌	❌	❌
JSONB	JSONB	❌	❌	❌	❌	❌	❌	❌

The JSON data type in PostgreSQL stores the value as plain text, as opposed to binary representation.

If you simply want to store and retrieve a JSON representation, using JSON will take less disk space and less time to build from its input representation. However, if you want to do any operations on the JSON value, you should prefer the JSONB data type.

Querying JSON

Sequelize provides a special syntax to query the contents of a JSON object. Read more about querying JSON.

Miscellaneous DataTypes

AllPostgreSQLMariaDBMySQLMSSQLSQLiteSnowflakedb2ibmi

Sequelize DataType	PostgreSQL	MariaDB	MySQL	MSSQL	SQLite	Snowflake	db2	ibmi
GEOMETRY	GEOMETRY	GEOMETRY	GEOMETRY	❌	❌	❌	❌	❌
GEOMETRY('POINT')	GEOMETRY(POINT)	POINT	POINT	❌	❌	❌	❌	❌
GEOMETRY('POINT', 4326)	GEOMETRY(POINT,4326)	❌	❌	❌	❌	❌	❌	❌
GEOMETRY('POLYGON')	GEOMETRY(POLYGON)	POLYGON	POLYGON	❌	❌	❌	❌	❌
GEOMETRY('LINESTRING')	GEOMETRY(LINESTRING)	LINESTRING	LINESTRING	❌	❌	❌	❌	❌
GEOGRAPHY	GEOGRAPHY	❌	❌	❌	❌	❌	❌	❌
HSTORE	HSTORE	❌	❌	❌	❌	❌	❌	❌

note

In Postgres, the GEOMETRY and GEOGRAPHY types are implemented by the PostGIS extension.

In Postgres, You must install the pg-hstore package if you use DataTypes.HSTORE

DataTypes exclusive to PostgreSQL

Arrays

note

Arrays are only available in PostgreSQL.

// Defines an array of DataTypes.SOMETHING.
DataTypes.ARRAY(/* DataTypes.SOMETHING */)

// VARCHAR(255)[]
DataTypes.ARRAY(DataTypes.STRING)

// VARCHAR(255)[][]
DataTypes.ARRAY(DataTypes.ARRAY(DataTypes.STRING))

Ranges

note

Ranges are only available in PostgreSQL.

DataTypes.RANGE(DataTypes.INTEGER)    // int4range
DataTypes.RANGE(DataTypes.BIGINT)     // int8range
DataTypes.RANGE(DataTypes.DATE)       // tstzrange
DataTypes.RANGE(DataTypes.DATEONLY)   // daterange
DataTypes.RANGE(DataTypes.DECIMAL)    // numrange

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

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

// defaults to inclusive lower bound, exclusive upper bound
const range = [
  new Date(Date.UTC(2016, 0, 1)),
  new Date(Date.UTC(2016, 1, 1))
];
// '["2016-01-01 00:00:00+00:00", "2016-02-01 00:00:00+00:00")'

// control inclusion
const range = [
  { value: new Date(Date.UTC(2016, 0, 1)), inclusive: false },
  { value: new Date(Date.UTC(2016, 1, 1)), inclusive: true },
];
// '("2016-01-01 00:00:00+00:00", "2016-02-01 00:00:00+00:00"]'

// composite form
const range = [
  { value: new Date(Date.UTC(2016, 0, 1)), inclusive: false },
  new Date(Date.UTC(2016, 1, 1)),
];
// '("2016-01-01 00:00:00+00:00", "2016-02-01 00:00:00+00:00")'

const Timeline = sequelize.define('Timeline', {
  range: DataTypes.RANGE(DataTypes.DATE)
});

await Timeline.create({ range });

However, retrieved range values always come in the form of an array of objects. For example, if the stored value is ("2016-01-01 00:00:00+00:00", "2016-02-01 00:00:00+00:00"], after a finder query you will get:

[
  { value: Date, inclusive: false },
  { value: Date, inclusive: true }
]

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

Special Cases

// empty range:
Timeline.create({ range: [] }); // range = 'empty'

// Unbounded range:
Timeline.create({ range: [null, null] }); // range = '[,)'
// range = '[,"2016-01-01 00:00:00+00:00")'
Timeline.create({ range: [null, new Date(Date.UTC(2016, 0, 1))] });

// Infinite range:
// range = '[-infinity,"2016-01-01 00:00:00+00:00")'
Timeline.create({ range: [-Infinity, new Date(Date.UTC(2016, 0, 1))] });

In TypeScript

Use the Range type provided by Sequelize to properly type your range:

import { Model, InferAttributes, Range } from '@sequelize/core';

class User extends Model<InferAttributes<User>> {
  declare myDateRange: Range<Date>;
}

User.init({
  myDateRange: {
    type: DataTypes.RANGE(DataTypes.DATE),
    allowNull: false,
  }
});

Network Addresses

AllPostgreSQLMariaDBMySQLMSSQLSQLiteSnowflakedb2ibmi

Sequelize DataType	PostgreSQL	MariaDB	MySQL	MSSQL	SQLite	Snowflake	db2	ibmi
CIDR	CIDR	❌	❌	❌	❌	❌	❌	❌
INET	INET	❌	❌	❌	❌	❌	❌	❌
MACADDR	MACADDR	❌	❌	❌	❌	❌	❌	❌

Virtual

DataTypes.VIRTUAL is a special DataType used to declare virtual attributes. It does not create an actual column.

caution

Unlike GENERATED columns, DataTypes.VIRTUAL columns are handled in the JavaScript Layer. They are not created on the database table. See the issue about generated columns to learn more.

Custom Data Types

Databases support more Data Types that are not covered by the ones built-in in Sequelize. If you need to use such a Data Types, you can create your own DataType.

It is also possible to use a raw SQL string as the type of your attribute. This string will be used as-is as the type of your column when creating the table.

User = sequelize.define('user', {
  password: {
    type: 'VARBINARY(50)',
  },
});

Caution: Sequelize will not do any extra type transformation or validation on an attribute declared like this. Use wisely!

And, of course, you can open a feature request in the Sequelize repository to request the addition of a new built-in DataType.