Below notes are w.r.t. Sequelize v6

Mapping of data types across Sequelize, Postgres and JavaScript

Below is not exhaustive list but covers some advanced types:

Read is what you get when you read using Sequelize. Write is what you can write to database using Sequelize. They are explained more in following sections.

declare properties to fix TypeScript errors

Consider a model defined like this:

class Foo extends Model { 
}

Foo.init({
    // WARNING: note this does not prevent an empty field (empty UUID) even though we set allowNull: false
    id: { type: DataTypes.UUID, allowNull: false, field: 'booking_id' }, 
}, {
    // Other model options go here
    sequelize: sql, // We need to pass the connection instance
    tableName: 'bookings'
});

If you are using TypeScript, the TypeScript compiler will complain when you try to access a property like so:

foo.id;

To fix this, we need to declare id like so:

class Foo extends Model { 
    declare id: string;
}

Reading and Writing UUIDs

Read UUID

A field defined as DataTypes.UUID will come out as a string when read from the database.

Write UUID

A field defined as DataTypes.UUID can be assigned a string (e.g., uuid.v4()) before writing to the database.

Reading and Writing Dates

Read Date

A field defined as DataTypes.DATEONLY will come out as a string when read from the database. To convert it into a JavaScript Date object, you can define a custom getter like so:

endDate: { type: DataTypes.DATEONLY, allowNull: false, get() { return parseISO(this.getDataValue('endDate')) }, field: 'end_date' },

Write Date

A field defined as DataTypes.DATEONLY can hold a JavaScript Date before writing to the database.

Reading and Writing Timestamps

Read Timestamp

A field defined as DataTypes.DATE will come out as a JavaScript Date object when read from the database. This is due to the way the underlying pg library works. As documented here:

node-postgres will convert instances of JavaScript date objects into the expected input value for your PostgreSQL server. Likewise, when reading a date, timestamp, or timestamptz column value back into JavaScript, node-postgres will parse the value into an instance of a JavaScript Date object.

Write Timestamp

As mentioned above, a field defined as DataTypes.DATE can hold a JavaScript Date before writing to the database.

Reading and Writing Decimals (or INT8)

Reading Decimal

A field defined as DataTypes.DECIMAL (or DataTypes.BIGINT) will come out as a JavaScript string when read from the database! This is because of the way the underlying pg library works. see:

By default the PostgreSQL backend server returns everything as strings.

You can convert it to a number by defining a custom getter (refer this):

dollarValue: { type: DataTypes.DECIMAL, allowNull: false, get() { return parseFloat(this.getDataValue('dollarValue'))}, field: 'dollar_value' }

or even convert it to a decimal (requires the decimal.js library):

dollarValue: { type: DataTypes.DECIMAL, allowNull: false, get() { return new Decimal(this.getDataValue('dollarValue'))}, field: 'dollar_value' }

WARNING: The established pattern of using types.setTypeParser to parse BIGINT and DECIMAL columns does NOT work with Sequelize. Refer this thread for details. This is quite unfortunate and one of the things I dislike about Sequelize (but not a deal-breaker).

Writing Decimal

A field defined as DataTypes.DECIMAL can hold a Decimal before writing to the database.

Fun Fact

decimal is not same as decimal.js!

> var Decimal = require('decimal.js')
undefined
> var D2 = require('decimal')
undefined
> x = new Decimal('-100.001')
-100.001
> y = new D2('-200.0002')
Decimal {
  internal: '-200.0002',
  as_int: { value: -2000002, exp: -4 },
  add: [Function (anonymous)],
  sub: [Function (anonymous)],
  mul: [Function (anonymous)],
  div: [Function (anonymous)],
  toString: [Function (anonymous)],
  toNumber: [Function (anonymous)]
}
> y.toFixed
undefined
> x.toFixed
[Function (anonymous)]