import { DialectTableFilter } from '@site/src/components/dialect-table-filter.tsx'; import { Deprecated } from '@site/src/components/deprecated.tsx'; import JsDefaultCaution from '../_fragments/_js-default-caution.mdx'; import UuidSupportTable from '../_fragments/_uuid-support-table.mdx';

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.

:::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.

:::

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.

:::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.

:::

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

:::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.

:::

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

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

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.

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.

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,
  },
});

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.

:::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.

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

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.

:::info Querying JSON

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

:::

:::warning 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

:::

:::note

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

:::

:::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));

:::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.

// 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))] });

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,
  },
});

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.

:::

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.