Skip to main content
Version: v7 - alpha

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

Sequelize DataTypePostgreSQLMariaDBMySQLMSSQLSQLiteSnowflakedb2ibmi
STRINGVARCHAR(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.BINARYBYTEAVARCHAR(255) BINARYVARCHAR(255) BINARYBINARY(255)VARCHAR BINARY(255)VARCHAR(255) BINARYVARCHAR(255) FOR BIT DATABINARY(255)
STRING(100).BINARYVARCHAR(100) BINARYVARCHAR(100) BINARYBINARY(100)VARCHAR BINARY(100)VARCHAR(100) BINARYVARCHAR(100) FOR BIT DATABINARY(100)
TEXTTEXTTEXTTEXTNVARCHAR(MAX)TEXTTEXTVARCHAR(32672)VARCHAR(8192)
TEXT('tiny')TINYTEXTTINYTEXTNVARCHAR(256)VARCHAR(256)VARCHAR(256)
TEXT('medium')MEDIUMTEXTMEDIUMTEXTVARCHAR(8192)VARCHAR(8192)
TEXT('long')LONGTEXTLONGTEXTCLOB(65536)CLOB(65536)
CHARCHAR(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.BINARYBYTEACHAR(255) BINARYCHAR(255) BINARYCHAR(255) BINARYCHAR BINARY(255)CHAR(255) BINARYCHAR(255) BINARYCLOB(255)
CHAR(100).BINARYCHAR(100) BINARYCHAR(100) BINARYCHAR(100) BINARYCHAR BINARY(100)CHAR(100) BINARYCHAR(100) BINARYCLOB(100)
CITEXTCITEXTTEXT COLLATE NOCASE
TSVECTORTSVECTOR

Boolean

Sequelize DataTypePostgreSQLMariaDBMySQLMSSQLSQLiteSnowflakedb2ibmi
BOOLEANBOOLEANTINYINT(1)TINYINT(1)BITTINYINT(1)BOOLEANBOOLEANSMALLINT

Integers

Sequelize DataTypePostgreSQLMariaDBMySQLMSSQLSQLiteSnowflakedb2ibmi
TINYINTTINYINTTINYINTTINYINTTINYINTTINYINTTINYINT
TINYINT(1)TINYINT(1)TINYINT(1)TINYINT(1)TINYINT(1)
TINYINT.UNSIGNEDTINYINT UNSIGNEDTINYINT UNSIGNEDTINYINT UNSIGNED
TINYINT.ZEROFILLTINYINT ZEROFILLTINYINT ZEROFILLTINYINT ZEROFILL
SMALLINTSMALLINTSMALLINTSMALLINTSMALLINTSMALLINTSMALLINTSMALLINT
SMALLINT(1)SMALLINT(1)SMALLINT(1)SMALLINT(1)
SMALLINT.UNSIGNEDSMALLINT UNSIGNEDSMALLINT UNSIGNED
SMALLINT.ZEROFILLSMALLINT ZEROFILLSMALLINT ZEROFILL
MEDIUMINTMEDIUMINTMEDIUMINTMEDIUMINTMEDIUMINT
MEDIUMINT(1)MEDIUMINT(1)MEDIUMINT(1)MEDIUMINT(1)MEDIUMINT(1)
MEDIUMINT.UNSIGNEDMEDIUMINT UNSIGNEDMEDIUMINT UNSIGNEDMEDIUMINT UNSIGNEDMEDIUMINT UNSIGNED
MEDIUMINT.ZEROFILLMEDIUMINT ZEROFILLMEDIUMINT ZEROFILLMEDIUMINT ZEROFILLMEDIUMINT ZEROFILL
INTEGERINTEGERINTEGERINTEGERINTEGERINTEGERINTEGERINTEGERINTEGER
INTEGER(1)INTEGER(1)INTEGER(1)INTEGER(1)
INTEGER.UNSIGNEDINTEGER UNSIGNEDINTEGER UNSIGNED
INTEGER.ZEROFILLINTEGER ZEROFILLINTEGER ZEROFILL
BIGINTBIGINTBIGINTBIGINTBIGINTBIGINTBIGINTBIGINT
BIGINT(1)BIGINT(1)BIGINT(1)BIGINT(1)
BIGINT.UNSIGNEDBIGINT UNSIGNEDBIGINT UNSIGNED
BIGINT.ZEROFILLBIGINT ZEROFILLBIGINT 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.

Sequelize DataTypePostgreSQLMariaDBMySQLMSSQLSQLiteSnowflakedb2ibmi
REALREALREALREALREALREALREALREALREAL
REAL(11, 10)REAL(11,10)REAL(11,10)
REAL.UNSIGNEDREAL UNSIGNEDREAL UNSIGNED
REAL.ZEROFILLREAL ZEROFILLREAL ZEROFILL
FLOATFLOATFLOATFLOATFLOATFLOATFLOATFLOAT
FLOAT(11)FLOAT(11)FLOAT(11)FLOAT(11)FLOAT(11)FLOAT(11)
FLOAT(11, 10)FLOAT(11,10)FLOAT(11,10)
FLOAT.UNSIGNEDFLOAT UNSIGNEDFLOAT UNSIGNED
FLOAT.ZEROFILLFLOAT ZEROFILLFLOAT ZEROFILL
DOUBLEDOUBLE PRECISIONDOUBLE PRECISIONDOUBLE PRECISIONDOUBLE PRECISIONDOUBLE PRECISIONDOUBLEDOUBLE PRECISION
DOUBLE(11, 10)DOUBLE PRECISION(11, 10)DOUBLE PRECISION(11, 10)DOUBLE PRECISION(11, 10)
DOUBLE.UNSIGNEDDOUBLE PRECISION UNSIGNEDDOUBLE PRECISION UNSIGNEDDOUBLE PRECISION UNSIGNED
DOUBLE.ZEROFILLDOUBLE PRECISION ZEROFILLDOUBLE PRECISION ZEROFILLDOUBLE 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

Sequelize DataTypePostgreSQLMariaDBMySQLMSSQLSQLiteSnowflakedb2ibmi
DECIMALDECIMALDECIMALDECIMALDECIMALDECIMALDECIMALDECIMALDECIMAL
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.UNSIGNEDDECIMAL UNSIGNEDDECIMAL UNSIGNED
DECIMAL.ZEROFILLDECIMAL ZEROFILLDECIMAL 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

Sequelize DataTypePostgreSQLMariaDBMySQLMSSQLSQLiteSnowflakedb2ibmi
DATETIMESTAMP WITH TIME ZONEDATETIMEDATETIMEDATETIMEOFFSETTIMESTAMPTIMESTAMPTIMESTAMP
DATE(6)DATETIME(6)DATETIME(6)TIMESTAMP(6)
DATEONLYDATEDATEDATEDATEDATEDATEDATE
TIMETIMETIMETIMETIMETIMETIMETIME

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.

Sequelize DataTypePostgreSQLMariaDBMySQLMSSQLSQLiteSnowflakedb2ibmi
NOWNOWNOWNOWGETDATE()CURRENT TIMENOW
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.

Sequelize DataTypePostgreSQLMariaDBMySQLMSSQLSQLiteSnowflakedb2ibmi
UUIDUUIDCHAR(36) BINARYCHAR(36) BINARYCHAR(36)VARCHAR(36)CHAR(36) FOR BIT DATACHAR(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.

Sequelize DataTypePostgreSQLMariaDBMySQLMSSQLSQLiteSnowflakedb2ibmi
BLOBBYTEABLOBBLOBVARBINARY(MAX)BLOBBLOBBLOB(1M)
BLOB('tiny')TINYBLOBTINYBLOBVARBINARY(256)BLOB(255)BLOB(255)
BLOB('medium')MEDIUMBLOBMEDIUMBLOBBLOB(16M)BLOB(16M)
BLOB('medium')LONGBLOBLONGBLOBBLOB(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).

Sequelize DataTypePostgreSQLMariaDBMySQLMSSQLSQLiteSnowflakedb2ibmi
JSONJSONJSONJSON
JSONBJSONB

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

Sequelize DataTypePostgreSQLMariaDBMySQLMSSQLSQLiteSnowflakedb2ibmi
GEOMETRYGEOMETRYGEOMETRYGEOMETRY
GEOMETRY('POINT')GEOMETRY(POINT)POINTPOINT
GEOMETRY('POINT', 4326)GEOMETRY(POINT,4326)
GEOMETRY('POLYGON')GEOMETRY(POLYGON)POLYGONPOLYGON
GEOMETRY('LINESTRING')GEOMETRY(LINESTRING)LINESTRINGLINESTRING
GEOGRAPHYGEOGRAPHY
HSTOREHSTORE
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

Sequelize DataTypePostgreSQLMariaDBMySQLMSSQLSQLiteSnowflakedb2ibmi
CIDRCIDR
INETINET
MACADDRMACADDR

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.