Defining a Model in Sequelize

Sequelize·63 min read·Jan 1, 2025

In Sequelize, a model is an abstraction that represents a table in the database and that tells several things about the entity it represents, such as its name, the name of its columns, their data types and constraints, and so on.

Defining a model

To create a new model, you can use the define() method of the database handler:

const { Sequelize } = require('sequelize');

const database = new Sequelize(/* ... */);

database.define(<model>, {
  <column>: {
    <property>: <value>,
    ...
  }
}, <options>?);

Where:

<model> is the name of the database model.
<column> is the name of the column in the database.
<property> is either the type of the column or a constraint on the column.
<value> is the value of the property.
<options> is an optional object used to configure the model.

Configuring the table name

By default, Sequelize automatically pluralizes the model name and uses that as the database table name.

This pluralization is done under the hood by a library called inflection, so that irregular plurals are computed correctly (e.g., user -> users, person -> people).

This means that, if your model name is user, Sequelize will perform operations on the corresponding MySQL table named users.

Preventing pluralization

To prevent the auto-pluralization of a model name, you can pass an optional object to the define() method containing the freezeTableName property set to true:

database.define(<model>, {
  <columns>
}, {
  freezeTableName: true
});

Alternatively, to set this behavior globally for all the models of the Sequelize instance, you can pass the same object to the define property of the constructor's configuration object:

new Sequelize(<credentials>, {
  define: {
    freezeTableName: true,
  }
});

Providing an explicit table name

To provide an explicit table name rather than relying on Sequelize's inference rules, you can use the tableName property:

database.define(<model>, {
  <columns>
}, {
  tableName: 'table_name'
});

Note: This method is usually preferred as it is more explicit and less error-prone.

Defining the column type

Just like in a physical database table, every column of a model must have an explicit data type, which is defined through the type property:

database.define(<model>, {
  <column>: {
    type: <value>
  }
});

Common data types

In Sequelize, standard datatypes are available through the DataTypes object exported by the module:

const { DataTypes } = require('sequelize');

Here is a list of the most common SQL data types and their Sequelize equivalent:

+------------------+----------------------------+
| MySQL            | Sequelize                  |
+------------------+----------------------------+
| VARCHAR(255)     | DataTypes.STRING           |
| VARCHAR(1234)    | DataTypes.STRING(1234)     |
| TEXT             | DataTypes.TEXT             |
| TINYTEXT         | DataTypes.TEXT('tiny')     |
+------------------+----------------------------+
| TINYINT(1)       | DataTypes.BOOLEAN          |
+------------------+----------------------------+
| INTEGER          | DataTypes.INTEGER          |
| INTEGER UNSIGNED | DataTypes.INTEGER.UNSIGNED |
| BIGINT           | DataTypes.BIGINT           |
| FLOAT            | DataTypes.FLOAT            |
| DECIMAL          | DataTypes.DECIMAL          |
+------------------+----------------------------+
| TIME             | DataTypes.TIME             |
| DATE             | DataTypes.DATEONLY         |
| DATETIME         | DataTypes.DATE             |
+------------------+----------------------------+
| CHAR(36)         | DataTypes.UUID             |
+------------------+----------------------------+

Example

Let's consider this model:

const Transaction = database.define('employee', {
  id: {
    type: DataTypes.INTEGER
  },
  full_name: {
    type: DataTypes.STRING(120)
  },
  hire_date: {
    type: DataTypes.DATE
  },
  salary: {
    type: DataTypes.FLOAT
  }
});

Which corresponds to this SQL statement:

CREATE TABLE employees (
  id INTEGER,
  full_name VARCHAR(120),
  hire_date DATE,
  salary FLOAT
);

Setting a default value

By default, Sequelize assumes that the default value of a column is NULL.

To specify a custom default value instead, you can use the defaultValue property of the column:

database.define(<model>, {
  <column>: {
    type: <type>,
    defaultValue: <value>
  }
});

Example

Let's consider this model:

const Deposit = database.define('deposit', {
  id: {
    type: DataTypes.INTEGER
  },
  account: {
    type: DataTypes.STRING(12),
    defaultValue: '9651-156-321'
  },
  amount: {
    type: DataTypes.DECIMAL(6, 2)
  },
  date: {
    type: DataTypes.DATE,
    defaultValue: DataTypes.NOW
  }
});

Which corresponds to this SQL statement:

CREATE TABLE deposits (
  id INTEGER,
  account VARCHAR(12) DEFAULT '9651-156-321',
  amount DECIMAL(6, 2),
  date DATETIME DEFAULT NOW()
);

Incrementing values automatically

To automatically increment the value of a column by 1 when a new row is inserted, you can set the autoIncrement property of the column to true.

database.define(<model>, {
  <column>: {
    type: <type>,
    autoIncrement: true
  }
});

Note: In SQL, this property only works with the INTEGER data type and primary keys.

Defining column constraints

A constraint is a rule defined at the SQL level.

If a constraint check fails, an error will be thrown by the database and Sequelize will forward this error to JavaScript.

Allowing `NULL` values

To allow null values in a column, you can set the allowNull property to true:

database.define(<model>, {
  <column>: {
    type: <type>,
    allowNull: true
  }
});

Note: In SQL, this translates to the NULL / NOT NULL constraints.

Example

Let's consider this model:

const Contract = database.define('contract', {
  id: {
    type: DataTypes.INTEGER,
    allowNull: false
  },
  type: {
    type: DataTypes.ENUM('temporary', 'fulltime'),
    defaultValue: 'temporary'
  },
  start_date: {
    type: DataTypes.DATEONLY,
    allowNull: false
  },
  end_date: {
    type: DataTypes.DATEONLY,
    allowNull: true
  },
  salary: {
    type: DataTypes.DECIMAL(6, 2),
    allowNull: false
  }
});

Which corresponds to this SQL statement:

CREATE TABLE contracts (
  id INT NOT NULL,
  type ENUM('temporary', 'fulltime') DEFAULT 'temporary',
  start_date DATE NOT NULL,
  end_date DATE,
  salary DECIMAL(6, 2) NOT NULL
);

Enforcing unique values

To define a column as unique, you can set the unique property to true:

database.define(<model>, {
  <column>: {
    type: <type>,
    unique: true
  }
});

Note: In SQL, this translates to the UNIQUE constraint.

Example

Let's consider this model:

const Movie = database.define('movie', {
  id: {
    type: DataTypes.INTEGER,
    allowNull: false,
    unique: true
  },
  title: {
    type: DataTypes.STRING,
    allowNull: false
  },
  genre: {
    type: DataTypes.STRING,
    allowNull: true
  },
  year: {
    type: DataTypes.SMALLINT.UNSIGNED,
    allowNull: false
  }
});

Which corresponds to this SQL statement:

CREATE TABLE movies (
  id INT UNIQUE NOT NULL,
  title VARCHAR(255) NOT NULL,
  genre VARCHAR(255),
  year SMALLINT UNSIGNED NOT NULL
);

Disabling and renaming timestamps

By default, Sequelize automatically adds and manages the fields createdAt and updatedAt to every model, using the data type DataTypes.DATE, where:

createdAt contains the creation timestamp.
updatedAt contains the latest update timestamp.

To disable this behavior, you can set the timestamps property to false:

sequelize.define('model', {
  // columns
}, {
  timestamps: false
});

To only disable one of them, you can set the createdAt or updatedAt property to false:

sequelize.define('model', {
  // columns
}, {
  createdAt: false
});

Finally, to rename one or both of them, you can provide a string instead of a boolean value:

sequelize.define('model', {
  // columns
}, {
  createdAt: 'created_at_timestamp'
});

Validating data

In addition to constraints, Sequelize provides a list of built-in model validators implemented by the validator library, that allow you check and validate the data format of columns through the validate property:

{
  <column>: {
    type: <type>,
    validate: {
      <validator>: <value>,
      ...
    }
  }
}

Note: These validators are only executed upon CREATE and UPDATE operations.

Validating numbers

To check numbers, you can use:

isNumeric: true: to only allow numbers.
isInt: true: to check for valid integers.
isFloat: true: to check for floating point numbers.
isDecimal: true: to check for any numbers.
max: <number>: to only allow values <= number.
min: <number>: to only allow values >= number.

Example

For example:

{
  latitude: {
    type: DataTypes.FLOAT,
    validate: {
      isDecimal: true,
      min: -90,
      max: 90
    }
  }
}

Validating strings

To check strings, you can use:

isAlpha: true: to only allow letters.
isAlphanumeric: true: to only allow letters and digits.
isLowercase: true: to check for lowercase letters.
isUppercase: true: to check for uppercase letters.
notEmpty: true: to not allow empty strings.
contains: <string>: to check for a substring.
notContains: <string>: to not allow a substring.
len: [<number>,<number>]: to only allow length between values.

Example

For example:

{
  pseudo: {
    type: DataTypes.STRING(20),
    validate: {
      isAlphanumeric: true,
      isLowercase: true,
      notEmpty: true,
      len: [6, 20]
    }
  }
}

Validating regular expressions

To match a field against a regular expression, you can use:

is: /pattern/: to check if the field matches a pattern.
not: /pattern/: to check if the field doesn't match a pattern.

Example

For example:

{
  name: {
    type: DataTypes.STRING(50),
    validate: {
      is: /^([a-zA-Z-]+\s)?$/i
    }
  }
}

Validating common expressions

To check common expressions, such as email addresses or URLs, you can use:

isEmail: true: to check email addresses.
isUrl: true: to check URLs.
isIP: true: to check IPv4 or IPv6 addresses.

Example

For example:

{
  email: {
    type: DataTypes.STRING(320),
    validate: {
      isEmail: true
    }
  }
}

Validating dates

To check dates, you can use:

isDate: true: to only allow date strings.
isAfter: "2011-11-05": to only allow date strings after a specific date.
isBefore: "2011-11-05": to only allow date strings before a specific date.

Example

For example:

{
  start_date: {
    type: DataTypes.DATEONLY,
    validate: {
      isAfter: '2025-01-01'
    }
  }
}

Validating exact values

To check exact values, you can use:

isIn: [['foo', 'bar']]: to check if the value is one of these.
notIn: [['foo', 'bar']]: to check the value is not one of these.
equals: 'value': to only allow a specific value.

Example

For example:

{
  genre: {
    type: DataTypes.STRING,
    validate: {
      isIn: [['Action', 'Comedy', 'Drama', 'Horror']]
    }
  }
}

Synchronizing a model

To synchronize a model, which means physically creating its corresponding table in the database by executing an SQL query, you can use its asynchronous sync() method:

await model.sync(options?);

This method can be called in three different ways:

Calling sync() without arguments will create the table only if it doesn't exist.
Calling sync({ force: true }) will first delete (i.e. drop) the table if it exists and recreate it.
Calling sync({ alter: true }) will check the current state of the table (its columns, their data types, etc) and perform the necessary changes to ensure it matches the model's properties.

Synchronizing all models

To synchronize all existing models at once, you can use the sync() method of the database handler instantiated from the Sequelize class:

await database.synch(options?);

Note: Keep in mind that the usage of this method with the force and the alter properties are potentially destructive operations, and are therefore not recommended in production.

🗒️ Summary

Here's a summary of what you've learned in this lesson:

A model is an abstraction that represents a table in the database.
The define() method of the database handler is used to create a new model.
The freezeTableName property is used to prevent the pluralization of a model's name.
The tableName property is used to provide an explicit table name.
The type property is used to define the data type of a column using the DataTypes object.
The defaultValue property is used to set the default value of a column (i.e. DEFAULT in SQL).
The autoIncrement property is used to automatically increment the value of a column upon creation (i.e. AUTO_INCREMENT in SQL).
The allowNull property is used to allow or prevent the value of a column to be NULL (i.e. NULL, NOT NULL in SQL).
The unique property is used to define the value of a column as unique (i.e. UNIQUE in SQL).
The timestamps property is used to prevent the automatic creation of createdAt and updatedAt columns.
The validate property is used to set a data validator on a column to enforce its data type or format.
The sync() method of the model is used to physically create its corresponding table in the database.

Unlock the Build RESTful APIs in Node.js module

Learn how to build real database-backed and framework-powered RESTful APIs with MySQL, Sequelize, and Express.

You get immediate access to:

37 focused lessons across MySQL, Sequelize, backend, and Express
4 real-world projects with commented solutions
Ongoing updates to this bundle
Lifetime access to this bundle

Unlock this module

Defining a Model in Sequelize

Defining a model

Configuring the table name

Preventing pluralization

Providing an explicit table name

Defining the column type

Common data types

Example

Setting a default value

Example

Incrementing values automatically

Defining column constraints

Allowing NULL values

Example

Enforcing unique values

Example

Disabling and renaming timestamps

Validating data

Validating numbers

Example

Validating strings

Example

Validating regular expressions

Example

Validating common expressions

Example

Validating dates

Example

Validating exact values

Example

Synchronizing a model

Synchronizing all models

🗒️ Summary

Unlock the Build RESTful APIs in Node.js module

Allowing `NULL` values