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)	TEXT	VARCHAR(255)	VARCHAR(255)	VARCHAR(255)
STRING(100)	VARCHAR(100)	VARCHAR(100)	VARCHAR(100)	NVARCHAR(100)	TEXT	VARCHAR(100)	VARCHAR(100)	VARCHAR(100)
STRING.BINARY	❌	VARCHAR(255) BINARY	VARCHAR(255) BINARY	❌	TEXT COLLATE BINARY	VARCHAR(255) BINARY	VARCHAR(255) FOR BIT DATA	VARCHAR(255) FOR BIT DATA
STRING(100).BINARY	❌	VARCHAR(100) BINARY	VARCHAR(100) BINARY	❌	TEXT COLLATE BINARY	VARCHAR(100) BINARY	VARCHAR(100) FOR BIT DATA	VARCHAR(100) FOR BIT DATA
TEXT	TEXT	TEXT	TEXT	NVARCHAR(MAX)	TEXT	TEXT	CLOB(2147483647)	CLOB(2147483647)
TEXT('tiny')	TEXT	TINYTEXT	TINYTEXT	NVARCHAR(256)	TEXT	TEXT	VARCHAR(256)	VARCHAR(256)
TEXT('medium')	TEXT	MEDIUMTEXT	MEDIUMTEXT	NVARCHAR(MAX)	TEXT	TEXT	VARCHAR(16777216)	VARCHAR(16777216)
TEXT('long')	TEXT	LONGTEXT	LONGTEXT	NVARCHAR(MAX)	TEXT	TEXT	CLOB(2147483647)	CLOB(2147483647)
CHAR	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.BINARY	❌	CHAR(255) BINARY	CHAR(255) BINARY	❌	❌	CHAR(255) BINARY	CHAR(255) FOR BIT DATA	CHAR(255) FOR BIT DATA
CHAR(100).BINARY	❌	CHAR(100) BINARY	CHAR(100) BINARY	❌	❌	CHAR(100) BINARY	CHAR(255) FOR BIT DATA	CHAR(255) FOR BIT DATA
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	SMALLINT1	TINYINT	TINYINT	SMALLINT2	INTEGER	INTEGER	SMALLINT1	SMALLINT1
TINYINT(1)	❌	TINYINT(1)	TINYINT(1)	❌	❌	`❌	❌	❌
TINYINT.UNSIGNED	SMALLINT	TINYINT UNSIGNED	TINYINT UNSIGNED	TINYINT2	INTEGER	INTEGER	SMALLINT	SMALLINT
TINYINT.ZEROFILL	❌	TINYINT ZEROFILL	TINYINT ZEROFILL	❌	❌	❌	❌	❌
SMALLINT	SMALLINT	SMALLINT	SMALLINT	SMALLINT	INTEGER	INTEGER	SMALLINT	SMALLINT
SMALLINT(1)	❌	SMALLINT(1)	SMALLINT(1)	❌	❌	❌	❌	❌
SMALLINT.UNSIGNED	INTEGER3	SMALLINT UNSIGNED	SMALLINT UNSIGNED	INTEGER3	INTEGER	INTEGER	INTEGER3	INTEGER3
SMALLINT.ZEROFILL	❌	SMALLINT ZEROFILL	SMALLINT ZEROFILL	❌	❌	❌	❌	❌
MEDIUMINT	INTEGER	MEDIUMINT	MEDIUMINT	INTEGER1	INTEGER	INTEGER	INTEGER	INTEGER
MEDIUMINT(1)	❌	MEDIUMINT(1)	MEDIUMINT(1)	❌	❌	❌	❌	❌
MEDIUMINT.UNSIGNED	INTEGER	MEDIUMINT UNSIGNED	MEDIUMINT UNSIGNED	INTEGER	INTEGER	INTEGER	INTEGER	INTEGER
MEDIUMINT.ZEROFILL	❌	MEDIUMINT ZEROFILL	MEDIUMINT ZEROFILL	❌	❌	❌	❌	❌
INTEGER	INTEGER	INTEGER	INTEGER	INTEGER	INTEGER	INTEGER	INTEGER	INTEGER
INTEGER(1)	❌	INTEGER(1)	INTEGER(1)	❌	❌	❌	❌	❌
INTEGER.UNSIGNED	BIGINT	INTEGER UNSIGNED	INTEGER UNSIGNED	BIGINT	INTEGER	INTEGER	BIGINT	BIGINT
INTEGER.ZEROFILL	❌	INTEGER ZEROFILL	INTEGER ZEROFILL	❌	❌	❌	❌	❌
BIGINT	BIGINT	BIGINT	BIGINT	BIGINT	❌	INTEGER	BIGINT	BIGINT
BIGINT(1)	❌	BIGINT(1)	BIGINT(1)	❌	❌	❌	❌	❌
BIGINT.UNSIGNED	❌	BIGINT UNSIGNED	BIGINT UNSIGNED	❌	❌	INTEGER	❌	❌
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.

Approximate Decimal Numbers

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

• FLOAT is meant to be a single-precision floating point type.
• DOUBLE is meant to be a double-precision floating point type.

AllPostgreSQLMariaDBMySQLMSSQLSQLiteSnowflakedb2ibmi

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

info

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

Exact Decimal Numbers

• DECIMAL is meant to be an unconstrained decimal type.
• DECIMAL(precision, scale) is meant to be a constrained decimal type.

AllPostgreSQLMariaDBMySQLMSSQLSQLiteSnowflakedb2ibmi

Sequelize DataType	PostgreSQL	MariaDB	MySQL	MSSQL	SQLite	Snowflake	db2	ibmi
DECIMAL	DECIMAL	❌	❌	❌	❌	❌	❌	❌
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(p, s).UNSIGNED	DECIMAL(p, s)	DECIMAL(p, s) UNSIGNED	DECIMAL(p, s) UNSIGNED	DECIMAL(p, s)	❌	DECIMAL(p, s)	DECIMAL(p, s)	DECIMAL(p, s)
DECIMAL(p, s).ZEROFILL	❌	DECIMAL(p, s) ZEROFILL	DECIMAL(p, s) 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 Approximate 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.DECIMAL(1, 2).UNSIGNED.ZEROFILLwill result in a column of type DECIMAL(1, 2) UNSIGNED ZEROFILL in MySQL.

Dates

AllPostgreSQLMariaDBMySQLMSSQLSQLiteSnowflakedb2ibmi

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

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()	❌	NOW	CURRENT TIME	NOW

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

caution

The generation of values for DataTypes.NOW and other JavaScript functions are not handled by the Database, but by Sequelize itself. This means that they will only be used when using Model methods. They will not be used in raw queries, in migrations, and all other places where Sequelize does not have access to the Model.

Read about SQL based alternatives in Dynamic SQL default values.

UUIDs

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

You can also use DataTypes.UUID.V4 and DataTypes.UUID.V1 to limit which version of UUID is accepted by the attribute to v4 or v1 respectively.

AllPostgreSQLMariaDBMySQLMSSQLSQLiteSnowflakedb2ibmi

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

Built-in Default Values for UUID

Sequelize can generate UUIDs automatically for these attributes, simply use sql.uuidV1 or sql.uuidV4 as the default value:

MyModel.init({
  myUuid: {
    type: DataTypes.UUID.V4,
    defaultValue: sql.uuidV4, // Or sql.uuidV1
  },
});

In supported dialects, Sequelize will set the default value to the appropriate function, as shown in the table below. These dialects can also use these two functions in migrations.

In all other dialects, Sequelize will generate the UUID value itself, in JavaScript. This means that they cannot use these two functions in migrations.

AllPostgreSQLMariaDBMySQLMSSQLSQLiteSnowflakedb2ibmi

	PostgreSQL	MariaDB	MySQL	MSSQL	SQLite	Snowflake	db2	ibmi
uuidV1	uuid_generate_v1 (requires uuid-ossp)	UUID	UUID	N/A	N/A	N/A	N/A	N/A
uuidV4	pg >= v13: gen_random_uuid pg < v13: uuid_generate_v4 (requires uuid-ossp)	N/A	N/A	NEWID	N/A	N/A	N/A	N/A

The postgres dialect requires the uuid-ossp extension to be enabled to be able to generate v1 UUIDs. If this extension is not available, you can force Sequelize to generate the UUIDs itself by using sql.uuidV1.asJavaScript instead.

MyModel.init({
  myUuid: {
    type: DataTypes.UUID.V1,
    defaultValue: sql.uuidV1.asJavaScript,
  },
});

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(1M)
BLOB('tiny')	BYTEA	TINYBLOB	TINYBLOB	VARBINARY(256)	BLOB	TINYBLOB	BLOB(255)	BLOB(255)
BLOB('medium')	BYTEA	MEDIUMBLOB	MEDIUMBLOB	VARBINARY(MAX)	BLOB	MEDIUMBLOB	BLOB(16M)	BLOB(16M)
BLOB('long')	BYTEA	LONGBLOB	LONGBLOB	VARBINARY(MAX)	BLOB	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	NVARCHAR(MAX)	TEXT	❌	❌	❌
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.

SQL NULL vs JSON 'null'

If your column is nullable, be aware that inserting null will insert the JSON 'null' value by default instead of the SQL NULL value.

Read more about how null is handled in JSON columns

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.

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	❌	❌	❌	❌	❌	❌	❌
MACADDR8	MACADDR8	❌	❌	❌	❌	❌	❌	❌

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.

Footnotes

1. When an int type is not available, Sequelize uses a bigger int type. ↩ ↩² ↩³ ↩⁴

2. TINYINT in SQL Server is unsigned. DataTypes.TINYINT.UNSIGNED therefore maps to TINYINT, and DataTypes.TINYINT maps to SMALLINT. ↩ ↩²

3. When an unsigned int type is not available, Sequelize uses a bigger int type to ensure the type covers all possible unsigned integer values of the smaller int type. ↩ ↩² ↩³ ↩⁴

4. Unlike other dialects, in SQLite, REAL is a double-precision floating point number type. ↩

5. Unlike other dialects, in Snowflake, FLOAT is a double-precision floating point number type. ↩